Java & SQL, Stronger Together

I’ve recently presented my new talk “Java & SQL — Stronger Together” as a free webinar.

The (updated) slides are available as PDF download (10MB), the recording is also available at Vimeo.

Key takeaways:

• Software layers make many tasks easier but don’t expose 100% of the lower layers functionality.

• What is the right level of abstraction for any given problem?

• The ORM-cycle: Load data into application, change it there, store changes.

• Pattern #1: Whenever the cycle is broken, ORM might not be the right layer for this task. Example: Lists.

• SQL has evolved beyond the relational idea. Recursion is an example for a non-relational operation that can traverse graphs like adjacency lists.

• We can use native SQL to efficiently load required entities into the persistence context prior to running the actual business logic.

• If the actual business logic needs an entity that is already in the persistence context, it doesn’t need to run a query to fetch it.

• Pattern #2: Searching & pre-loading entities with modern SQL can be much more efficient than loading them individually when accessing them.

There are two gotchas that I didn’t mention in the talk (but now mention them in the updated slides):

• The persistence context is usually accessed by the @Id attribute. If the business logic loads entities by other means, it will typically not be able to use the cached version in the persistence context.

  A few years back I stumbled upon this article which demos an EclipseLink feature for that (@CacheIndex).

• Running a native query to pre-load entities triggers a flush, which might have negative side effects.

  These queries should be triggered at the very beginning of a transaction, not in the middle. Code re-use might accidentally trigger such a query in the middle of a transaction.