2. The Missing Parts
In this part we’ll briefly compare the JPA standard with capabilities offered by various ORM providers and go through the things missing in the Java Persistence Query Language (JPQL) when compared with SQL.
Compared to ORM providers
Couple of years back this section would be a long one – but the JPA narrowed the gap with 2.0 and virtually closed it with 2.1. There are still things that may be missing when we compare it with ORM providers. The list of various ORM features not included in the JPA is probably long. However, the right question is how much a feature is really missed, how often we use it and how serious impact this omission has.
In most cases we can get out of the comfortable “standard” zone and use capabilities of a particular ORM without affecting parts that can be pure JPA. Maybe we add some provider-specific annotations in our mapping – so be it, let’s be pragmatic.
One particular feature I once missed a lot is something that allows me to go through a result
set just like in JDBC, but using entities. These don’t have to be put into the persistence context
as I can process them as they come. Actually – they must not be put there, because they just
bloat the heap without any real use. It’s like streaming the result set. This may be extremely
handy when we produce some very long exports that are streamed straight to the client browser
or a file. Hibernate offers ScrollableResults, I personally used JDBC with Spring JdbcTemplate
instead and solved the problem without JPA altogether – obviously, I had to do the mapping myself,
while Hibernate can do it for us. Even so, as mentioned in this
StackOverflow answer this may still cause
OutOfMemoryError or similar memory related issue, this time not on JPA/ORM level, but because
of silly JDBC driver (or even because of database limitations, but that’s rare).
Another area that is not covered by JPA that much is caching. JPA is rather vague about the structure of caches, although it does specify some configuration options and annotations. But the ORM implementations can still differ significantly. We tackle this topic in a separate chapter.
Finally, with the introduction of ON clause we could get much more low-level with our queries
when it suits us. ON is intended for additional JOIN conditions but we would have some new
ways how to approach the relations which can be bothersome from time to time. We could use ON
to explicitly add our primary JOIN condition – but all this is for naught because JPA 2.1
does not allow to use root entity (representing the table itself) in the JOIN clause. More about
this topic and how to do it with EclipseLink and Hibernate (5.1.0 and higher) in the chapter
Removing to-one altogether.
Comparing JPQL to SQL
JPQL gets more and more powerful with every new specification version, but obviously it cannot match SQL with proprietary features. Being “object query language” it does have its expressiveness on its own though – for instance we may simply say “delete all the dogs where breed name is <this and this>” without explicitly using joins (although this relies on to-one mapping we will try to get rid of). When object relations are mapped properly, joins are implied using the mapping and JPA provider will take care of generating the proper SQL for us. We may also ask for dog owners with their dogs and ORM can load it in a single query and provide it as a list of owners, each with their list of dogs – this is the difference between relational and object view.
But there are some very useful constructs that are clearly missing. I personally never needed right outer join, so far I was always able to choose the right entity to start with and then using left outer joins, but this one may hit us sometimes. There is also no full outer join, but this relates to the fact we’re working with objects, not with rows – although technically we may work with tuples (and with relations in RDBMS meaning). This dumbs down the JPA capabilities a bit, but in many cases it may be a good way and actually simplify things, provided we understand SQL – which we should.
When compared to SQL, probably the most striking JPQL limitations are related to subqueries. Some scenarios can be replaced by JOINs, but some can’t. For instance, we cannot put subqueries into a SELECT clause – this would allow for aggregated results per row. We cannot put them into a FROM clause and use the select result as any other table – or, in relational database sense, as a relation). This would allow us, among other things, to count rows for results that current JPA does not support.1
JPA offers a palette of favourite functions, but of course it does not provide all possible functions.2
Before JPA 2.1 we’d have to use ORM provider custom functionality to overcome this,
or fallback to native SQL.
Just because we are consciously sacrificing database portability it does not mean we don’t want to use JPQL.
It provides us with FUNCTION construct where the name of the called function is the first
argument with other arguments following behind.
Easy to use and very flexible – this effectively closes the gap for functions we can use.
Other missing parts
The best way how to find corner cases that are not supported in the specification [JPspec] is simply to search for “not supported” string. Some of these are related to embeddables, some are pretty natural (e.g. “applying setMaxResults or setFirstResult to a query involving fetch joins over collections is undefined”).3
What I dearly miss in JPA is better control over fetching of to-one relations. Current solution is trying to be transparent both in terms of object-relational mapping and Java language features, but it may kill our performance or require caching, potentially a lot of it. While to-many relations can be loaded lazily with special collection implementation enabling it, to-one cannot work like this without bytecode modification. I believe though, that developers should be allowed to decide that this and this to-one relationship will not be loaded and only detached entities with IDs will be provided. But let’s wait with this discussion for the opinionated part.