JPA – is it worth it? Horror stories with EclipseLink and Hibernate

One friend of mine brought Hibernate to our dev-team back in 2004 or so. But now he uses something much simpler and avoids ORM/JPA whenever possible. He can, because he is mostly master of his projects.

I had to get more familiar with JPA on my path. There is always more and more to learn about it. When you discover something like orphanRemoval = true on @OneToMany, it may bring you to the brink of crying. Because of happiness of course. (Stockholm Syndrome probably.) But then there are other days, when you just suffer. Days when you find bugs in JPA implementations – or something close to them. And you actually can’t choose from so many providers, can you? There are just two mainstream players.

Right now we are using EclipseLink and there are two bugs (or missing features or what) that kinda provoked our big effort to switch to Hibernate. What were our problems and what was the result of our switch? Read on…

EclipseLink and Java 8

You can avoid this problem, although you have to be extra careful. EclipseLink’s lazy list – called IndirectList – cannot be used with streams. Actually – if it didn’t compile, it would be good. Worse is it works but very badly.

Guys creating Java do their best to make streams work effortlessly, there are those default methods in interfaces, etc. But no, no luck here. IndirectList returns empty stream. Why? Because of this bug. (It works fine for IndirectSet.)

What is the cause of this problem? Well… someone decided to extend Vector, but instead of sticking with it, they also decided that the extended Vector will not be the actual backing collection, but they added their Vector delegate too. This works fine for interfaces like List, but not so for extended things. Now add “ingenious” design of Vector – full of protected fields you have to take care of… here IndirectList clearly collided with Vector badly.

Why do you need to take care of protected fields? Because creating the stream on Vector uses its special java.util.Vector.VectorSpliterator, that uses these protected values. So if you delegate to other Vector, you either delegate java.util.Vector#spliterator too (but then it won’t compile with earlier Java) or – preferably – don’t delegate to other vector and use that extended one as your backing collection. Of course, guys at Java might have used size() and get(int) instead of accessing Vector’s protected elementCount, elementData, etc. – but that would not be as efficient.

BTW: Anybody here likes Vector? I personally hate Java’s wannabe compatibility that really hurts its progress as we need to drag more and more burden with us.

JPA and counts on joins with distinct

The other trouble was related to a specific select where we used join with distinct instead of subquery with exists. We have an entity called Client that can be bound to any number of Domains, @ManyToMany mapping is used on Client side. Domain does not know about Client – hence the arrow.

jpa-client-domains

We often need to obtain Clients where any of their domains is in a provided list (actual input parameter contains IDs of those domains). Using Querydsl, it looks like this:

QDomain cd = new QDomain("cd");
JPAQuery query = new JPAQuery(em).from(QClient.client)
    .join(QClient.client.domains, cd)
        .on(cd.id.in(domainIds))
    .distinct();

I’m sure you can easily transform it mentally to Criteria or JPQL – Querydsl produces JPQL, BTW. Distinct here has a special meaning that is used by JPA specification. Now you can call query.list(QClient.client) – it produces nice join and returns only distinct Clients. You try query.count(), and it works as expected as well.

But imagine that Client has composite primary key. We actually have exactly the same case, an entity bound again to domains, everything looks the same – just that entity has composite PK. List works alright, but if you try query.count() you’ll get completely different illogical query using exists and the join to Domains is lost completely. Results are wrong, definitely not the size of the list result.

There are some bugs filed for this – for instance this one. Because there was a discussion about this behavior we decided to find out how Hibernate treats this.

Spoiler:

The trouble is that it is not the bug after all – at least not according to JPA 2.1specification which reads in section 4.8.5: The use of DISTINCT with COUNT is not supported for arguments of embeddable types or map entry types.

I found this today when I was gathering proofs for this post. Our story however would unfold in different direction. Are we using JPA? Yes. Are there other options? Yes!

Spoiler 2:

We used subselect with exists in the end, but that’s not the point. 😉

So we switch to Hibernate, right?

We got kinda carried away by some bug reports resolved as FIXED – like this one or the ones you can see in its links. But switching JPA provider isn’t that easy as promised. In the end we found out that count distinct for entities with composite PKs doesn’t work with SQL Server anyway. But we learned a lot of interesting things about how far both implementations are apart when it comes to translating JPQL to SQL. Mind you, I’m not actually sure whether everything we wrote in Querydsl (that generates JPQL) is 100% correct, but then it should say something. When it works I expect it to work after changing a JPA implementation.

Hibernate screwing deletes with “joins”

We have a lot of delete clauses that worked just fine in EclipseLink:

new JPADeleteClause(em, QGuiRolePermission.guiRolePermission)
    .where(QGuiRolePermission.guiRolePermission.guiRole.description.eq(TEST_DESCRIPTION))

This essentially means “delete all role permission assignments for role with specified description”. Piece-a-cake, right? We’re using implicit join there again, but it is the same story all over again – and it worked in EclipseLink just fine. EclipseLink creates proper SQL with exists:

DELETE FROM GUI_RolePermissions WHERE EXISTS(
  SELECT t1.gui_role_id FROM GUI_Roles t0, GUI_RolePermissions t1
    WHERE ((t0.description = ?) AND (t0.id = t1.gui_role_id)) AND t1.gui_role_id = GUI_RolePermissions.gui_role_id
      AND t1.domain_id = GUI_RolePermissions.domain_id AND t1.permission_id = GUI_RolePermissions.permission_id)

It is not perfect – and we get back to it in section Random Query Generator – but it works. Let’s just compare it to Hibernate now. This is JPQL (actually this is the same for both providers as it’s produced by Querydsl):

delete from GuiRolePermission guiRolePermission
  where guiRolePermission.guiRole.description = ?1

This does not seem alarming – but the SQL is completely off:

delete from GUI_RolePermissions cross join GUI_Roles guirole1_ where description=?

This does not work on our currently used SQL Server, whatever dialect we choose. Why not go with exists? We have helper method that takes entity (Querydsl base path) and its where condition and performs delete. Now instead of:

deleteTable(QGuiRolePermission.guiRolePermission,
    QGuiRolePermission.guiRolePermission.guiRole.description.eq(TEST_DESCRIPTION));

We have to write this:

deleteTable(QGuiRolePermission.guiRolePermission,
    new JPASubQuery().from(QGuiRolePermission.guiRolePermission)
    .where(QGuiRolePermission.guiRolePermission.guiRole.description
        .eq(DataPreparator.TEST_DESCRIPTION))
    .exists());

Not terrible… but why? EDIT 2016-01-07: There is an issue filed for this, so far not fixed.

Another Hibernate’s twist

Things may get complicated when more relationships are involved. Take this simple JPQL for instance:

delete from Security security
  where security.issuer.priority = ?1

We want to remove all Securities with issuer (Client) having specific priority value:

jpa-security-delete

There is another implicit join there, but one subquery with exists should cover it. Security class contains @ManyToMany relationships to Domain class through intermediate table Securities_Domains. That’s why we need two deletes here – and this is what EclipseLink generates (issuer is of class Client):

DELETE FROM Securities_Domains WHERE EXISTS(
  SELECT t1.id FROM "Clients" t0, Securities t1
    WHERE ((t0."priority" = ?) AND (t0."id" = t1."issuer_id"))
      AND t1.id = Securities_Domains.security_id);
DELETE FROM Securities WHERE EXISTS(
  SELECT t1.id FROM "Clients" t0, Securities t1
    WHERE ((t0."priority" = ?) AND (t0."id" = t1."issuer_id"))
      AND t1.id = Securities.id);

It works just fine. But Hibernate shows its muscles really strong here!

delete from Securities_Domains where (security_id) in (
   select id from Securities where priority=?)

Obviously right the first delete is missing join to the Client entity in that subselect – and fails spectacularly. And we’re actually lucky we don’t have any other column called priority on Securities as well. 🙂 That could hide the error for ages.

With or without id?

Love that song… I mean the U2 one, not the Hibernate’s one. When you see JPQL with equality test on two entities you assume it is performed on their ids. So if you actually specify those ids, it should be still the same, right? Maybe it’s just some JPA myth after all. Consider this JPQL:

delete from MetaObjectSetup metaObjectSetup
  where not exists (select 1 from Permission permission
    where permission.metaObjectSetup = metaObjectSetup)

This produces query that does not work properly, probably taking last id from permission1_.

delete from META_Object_Setups where  not (exists (
  select 1 from Permissions permission1_
    where permission1_.meta_object_setup_id=id))

Version working with Hibernate must perform eq on id fields explicitly (EclipseLink doesn’t mind either):

deleteTable(QMetaObjectSetup.metaObjectSetup,
   new JPASubQuery().from(QPermission.permission)
    .where(QPermission.permission.metaObjectSetup.id
        .eq(QMetaObjectSetup.metaObjectSetup.id))
    .notExists());

SQL result:

delete from META_Object_Setups where  not (exists (
  select 1 from Permissions permission1_
    where permission1_.meta_object_setup_id=META_Object_Setups.id))

Experiences like these were the last drop for us and we reverted all the efforts to start using Hibernate.

Happy ending?

We switched back to EclipseLink after this. I don’t remember so much resistance from Hibernate like… ever. Maybe our JPQLs were too loose for it, joins were not explicit, aliases were missing, etc. But in the end it did not solve our problem.

It is really shame that count and distinct are not possible in the way making using query.list and query.count operations consistent in libraries like Querydsl. It is also shame that when it is not supported (as officially stated in the specification) it does not throw exception and does something fishy instead – silently.

You can do the same in SQL wrapping the select into another one with count – but JPQL does not support queries in the FROM clause. Pitty. However this is one of those cases when you can’t go wrong with correlated subquery. You just have to remember that subquery does not imply any equality implied by JPA joins (it can’t actually, there are cases when this would be an obstacle) and you have to do it yourselves – see the last examples from previous part (With Or Without Id).

This is all really crazy. Remotely, it reminds me horror stories about JSF and bugs of its various implementations (always different). Sure, things are really complicated, but then maybe the trouble is they are. Maybe it can be simpler. Maybe it’s wrong that I have to define @ManyToOne to be able to express joins. (Edit: Not true for JPA 2.1 as fixed in this post.) Any @XToOne has consequences often unseen even by experienced JPA users. Maybe some jOOQ or Querydsl over plain SQL is better than this stuff. I just don’t know…

Random Query Generator in EclipseLink

Let’s look at something else while we stick with JPA. Here we are back to Eclipse and there is one very easy thing I want to do in SQL:

DELETE FROM Contacts WHERE EXISTS(
  SELECT t1.id FROM Clients t0 WHERE ((t0.priority = ?) AND t0.id = Contacts.id)

In plain wording – I want to remove all Client’s Contacts when this Client has some specific priority. If you ask for mapping… I hope there is nothing screwed here:

@Entity
@Table(name = "Clients")
public class Client {
...
    @OneToMany(mappedBy = "client", cascade = CascadeType.ALL)
    private List<ClientContact> contacts;
...
}

@Entity
@Table(name = "Contacts")
public class ClientContact {
...
    @Column(name = "client_id", insertable = false, updatable = false)
    private Integer clientId;

    @ManyToOne
    @JoinColumn(name = "client_id")
    private Client client;
...
}

Back reference to client is mapped both ways, but one mapping is read-only. Both primary keys are simple Integers. No miracles here. And now three ways we tried, always with Querydsl, JPQL and SQL:

new JPADeleteClause(em, QClientContact.clientContact).where(
    new JPASubQuery().from(QClient.client)
        .where(QClient.client.priority.eq(DataPreparator.TEST_CLIENT_PRIORITY)
            .and(QClient.client.eq(QClientContact.clientContact.client)))
        .exists());

// JPQL - logically exactly what we want
delete from ClientContact clientContact
  where exists (select client from Client client
    where client.priority = ?1 and client = clientContact.client)

// SQL contains double EXISTS and two unnecessary Clients more
DELETE FROM Contacts WHERE EXISTS(
  SELECT t0.id FROM Contacts t0 WHERE EXISTS (
    SELECT ? FROM Clients t2, Clients t1
      WHERE ((t2.priority = ?) AND (t2.id = t0.client_id)))  AND t0.id = Contacts.id)
bind => [1, 47]

Ok, it works, right? Can we help it when we mention id equality explicitly (client.id.eq(…client.id))?

new JPADeleteClause(em, QClientContact.clientContact).where(
    new JPASubQuery().from(QClient.client)
        .where(QClient.client.priority.eq(DataPreparator.TEST_CLIENT_PRIORITY)
            .and(QClient.client.id.eq(QClientContact.clientContact.client.id)))
        .exists());

// JPQL looks promising again, I bet this must generate proper query - or at least the same
delete from ClientContact clientContact
  where exists (select client from Client client
    where client.priority = ?1 and client.id = clientContact.client.id)

// Three clients?!
DELETE FROM Contacts WHERE EXISTS(
  SELECT t0.id FROM Contacts t0 WHERE EXISTS (
    SELECT ? FROM Clients t3, Clients t2, Clients t1
      WHERE (((t2.priority = ?) AND (t2.id = t3.id)) AND (t3.id = t0.client_id)))
        AND t0.id = Contacts.id)
bind => [1, 47]

You gotta be kidding me, right? What for?! I always believed that if id is entitie’s @Id, than it can do virtually the same with or without it. I even dreamt about using foreign key directly – but that’s way beyond capabilities of current JPA providers, so it seems.

Ok, let’s try something lame. Something I’d write only in JPA, not in real SQL, of course. Something with a lot of implicit identity equality hidden between the lines:

new JPADeleteClause(em, QClientContact.clientContact)
  .where(QClientContact.clientContact.client.priority
    .eq(DataPreparator.TEST_CLIENT_PRIORITY));

// not like SQL at all, but pretty logical if you know the mapping
delete from ClientContact clientContact
  where clientContact.client.priority = ?1

/* Surprise! Still one unnecessary Contacts in inner join that could go through FK,
 * but definitely the best result so far.
 * Mind though - this refused to work with Hibernate, at least with SQL Server dialect (any). */
DELETE FROM Contacts WHERE EXISTS(
  SELECT t1.id FROM Clients t0, Contacts t1
    WHERE ((t0.priority = ?) AND (t0.id = t1.client_id)) AND t1.id = Contacts.id)
bind => [47]

I hope this demonstrates the impotence of this field of human activity after more than a decade of effort. I’m not saying it all sucks – but after seeing this I’m not far from that.

With JPA doing too many things you don’t want or expect (unless you’re an expert) and many limitations compared to SQL I’d expect at least semi-good SQL. This is not close really.

Ramble On

So that’s another day with JPA. With proper query (or best approximation when it’s not critical) there is just one last thing we have to pay attention to… those IndirectLists that can’t be streamed. Just one last thing I said? Ouch… of course, we have to check our SQLs still and watch for any bugs or gray zones.

Yeah, we’re staying with JPA for a while on this project. But the next one I’ll start with metamodel directly over DB. I hope I can’t suffer more that way. 🙂 And maybe I’ll be able to do ad-hoc joins without @XToY annotations that always bring in stuff you don’t want. That’s actually one of my biggest gripes with JPA.

Many people avoid as much relation mappings as possible while they still can express their joins. Many people avoid @ManyToMany and map the association table explicitly, so they can reach all entities A for B’s id (or list of ids) – with a single join. Otherwise EclipseLink stubbornly joins all three tables as it’s understanding of PKs and FKs is obviously lacking.

Lessons learned? Not that much actually. But we’re definitely not switching provider for another couple of months at least!

Advertisements

JPA Pagination of entities with @OneToMany/@ManyToMany

Displaying paginated results of entities with their *-to-many sub-entities is a tricky thing and many people actually don’t even know about the problems. Let’s check what JPA does and what it does not for you. Besides standard JPA I’ll use Querydsl for queries – this effectively results in JPQL. Querydsl is type-safe and compared to Criteria API much more readable.

Source of N+1 select problem

Imagine having an entity called User that can have many Roles. Mapping looks like this:

@JoinTable(name = "user_roles",
  joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
  inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
@ManyToMany(fetch = FetchType.EAGER)
private Set<Role> roles;

You want to display a paginated list of users that contains list of their roles – for instance as tiny icons with tooltips containing role names.

Naive approach is to query the Users and then wait for whatever may come:

QUser u = QUser.user; // we will use this alias further
List<User> result = new JPAQuery(em).from(u)
    .where(...)
    .offset((page - 1) * pageSize)
    .limit(pageSize)
    .list(u);

Now something may or may not happen. Simple select from USER is definitely executed and if you don’t touch user.role that will be all. But you want those roles and you want to display them – for instance when you iterate the results. Each such access executes another select from ROLE where user_id = iterated user’s id. This is the infamous N+1 select problem (although the order is rather 1+N :-)). It is extremely bad for selects with large result sets, while for pages N has reasonable limits. But still – no reason to do additional 25 selects instead of… how many actually?

Left join for the help?

Were the mapping @…ToOne, then you could just left join this entity (e.g. user’s Address assuming there is a single address needed for a user). Left join in this case does not change the number of rows in the result – even for optional addresses, whereas inner join would omit users without addresses (also surprisingly common error for programmers who try to avoid SQL for no good reasons).

Left join for *ToOne relations is good solution for both paginated and non-paginated results.

But for *ToMany paginating and left join don’t go well together. The problem is that SQL can only paginate rows of the resulting join, it cannot know the proper offset and limit to select for instance second user only. Here we want page 2 with page size 2:

List<User> result = new JPAQuery(em).from(u)
    .where(...)
    .leftJoin(u.roles).fetch()
    .offset(2).limit(2)
    .list(u);

The result is delivered in a single select – but what a result…

We definitely don’t want User1 with a single RoleC and User2 with RoleA. That is not only not the page we want (we should get User3 on the final page 2), but it’s just plain disinformation (aka lie).

(Don’t) Use Eager!

For most cases EAGER mapping for *ToMany is not a good default. But in case you’re asking what will happen… JPA provider at least knows at the time of select from USER that we want the ROLEs too. EclipseLink performs N additional selects to get users’ roles although it utilizes any cached data possible. Actually I tested it with cache turned off with these lines in persistence.xml:

<property name="eclipselink.query-results-cache" value="false"/>
<property name="eclipselink.cache.shared.default" value="false"/>
<property name="eclipselink.cache.size.default" value="0"/>
<property name="eclipselink.cache.type.default" value="None"/>

Cache can bring some relief, but it actually just obscures the problem and may mislead you that the problem is not there. Especially if you create the data using JPA just before the test. 🙂

In case of LAZY JPA provider has to wait when we touch the role list – and it always loads only the one that is needed. But for our page we need to check all of them, so we will end up with N selects again. Provider has no way to optimize for this, not to mention that you may end up with LazyInitializationException (your fault) unless you use OSIV (open session in view), which I consider bad design after my previous experiences with it.

If provider knows that you want the roles upfront (EAGER) it may optimize, but I would not count on that. EclipseLink does not, and you simply cannot know. So let’s just do it ourselves, shall we? And switch that EAGER back to LAZY, which is default, so you can just as well remove the fetch parameter altogether.

1+1 select, no problem

We need to get the proper page of users and then load their roles. There are two ways how to do this:

  1. if you have reverse mappings (role.users) then you can get List<User> and then select roles containing these roles;
  2. if there is no reverse mappings, you list users first and then again with left join to roles – again only where user is in the first list.

In both cases list of user IDs may be enough. Because our role does not have reverse mapping (I don’t like it that much for @ManyToMany relations) we end up with code like this:

List<Integer> uids = find.queryFrom(u)
    .where(...)
    .orderBy(u.login.asc())
    .offset(2).limit(2)
    .list(u.id);
List<GuiUser> users = find.queryFrom(u)
    .where(u.id.in(uids))
    .leftJoin(u.guiRoles).fetch()
    .orderBy(u.login.asc())
    .distinct()
    .list(u);

What you display is the result of the second query, obviously. There are two important points here though:

  • You have to repeat the order. While in the first select it is essential for paging (paging without order is meaningless, right?) in the second case you could do it in memory, but there is no reason why, as you let DB to order just a single page and you save Java lines of code. Not to mention Java possibly (and quite probably!) using different collator… this really is not worth it.
  • You have to use distinct() before the list() of the second query. This not only adds distinct to the select, but also instructs Querydsl or JPA (really don’t know who does the job, sorry :-)) to give you the list of distinct users, not list of size of that joined select. Such distinct is required in most leftJoins on *ToMany entities (unless you create projection results for them).

1+1 for OneToMany

Now let’s pretend we have different situation – some @OneToMany case, like an Invoice and its Items. Item will have invoice attribute and Invoice will have @OneToMany items mapped by Item.invoice – like this:

@OneToMany(<b>mappedBy = "invoice"</b>)
private Set<Item> items;

And now the select part:

QInvoice i = QInvoice.invoice;
Map<Integer, Invoice> invoices = find.queryFrom(i)
    .where(...)
    .orderBy(i.id.asc())
    .offset(2).limit(2)
    .map(i.id, i);
// this performs items = new HashSet<>() for each invoice
invoices.values().forEach(Invoice::initItems);

QItem ii = QItem.item;
List<Item> items = find.queryFrom(ii)
    .where(ii.invoice.in(invoices.values()))
    .list(ii);
for (Item item : items) {
    // addItem is always nicer than getItems().add(item)
    invoices.get(item.getInvoice().getId()).addItem(item);
}

Always check what selects are generated in the end. In my case ii.invoice.in performs no additional join and puts ids into the IN list – and that’s how it should be.

Also note the usage of map(i.id, i) – this returns LinkedHashMap, so the order is preserved (cool!) and makes getting the invoice for item much easier. Difference in select is just one redundant id which is no problem at all.

Conclusion

We demonstrated the essential problem with pagination of one/many-to-many joins and checked two solutions that are both based on one additional select. These solutions are not necessary if pagination is not required, obviously. There may be other ways to do it, there are even some extensions for specific JPA providers (like @BatchFetch for EclipseLink), but I’d prefer this for couple of reasons:

  • Full control over selects – well, at least as full as you can get with Querydsl over JPQL. You can select into DTOs, you can enumerate required columns – there is much more you can do around the basic idea. (Funny. Just minutes after writing this sentence I travelled home from work, reading neat little book SQL Performance Explained – and coincidentally I was going through the part about Joins and Nested Loops. Markus Winard provided also short analysis of ORMs in various languages, how they perform joins and what you can do about it. The final tip was pretty clear about it: Get to know your ORM and take control of joins.)
  • It scales! There is no N. As long as your pagination is reasonably fast (but this is separate problem and you have to do what you have to do), second select goes after IDs, which should be pretty fast anyway.
  • It is unrelated to any specific features of any specific JPA provider.
  • And after all – you have full control over your contract. Don’t leave presentation layer trigger your selects. Even better – close that persistence context after leaving the service layer.

The only problem you should be aware of is limitation about amount of expressions in the IN clause. Check it out, read documentation (both for your JPA provider and RDBMS), experiment, find out what the maximum page size will be – and (obviously) cover it in your tests (at least when it crashes the first time ;-)).

The most important takeaway from this post should be: 1) EAGER is not a solution, 2) result of the left join with “to-many” cardinality cannot be reasonably paginated in SQL. We’re not talking about specific solutions here – hell, clever DB guys can even provide you procedures delivering you the right page plus total count of all records – all in a single call to DB. But that’s beyond basic SQL/JPA. 🙂