JPA2 critera query in-expression on FK issues n select statements, where n is #values

Question

I have the same issue using hibernate against DB2 and MySQL.

Here is a test:

        EntityManager em = emf.createEntityManager();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Customers> query = cb.createQuery(Customers.class);
        Root<Customers> root = query.from(Customers.class);

        ArrayList<String> strList = new ArrayList<String>();
        strList.add("ADMIN");
        strList.add("SYSADMIN");
        strList.add("SALES");

        ArrayList<Predicate> predicateList = new ArrayList<Predicate>();

        Path<Groups> groups = root.get(Customers_.groups);
        Path<String> groupName = groups.get(Groups_.name);
        In<String> in = cb.in(groupName);
        for (String s : strList) { //has a value
            in = in.value(s);
        }
        predicateList.add(in);
        Predicate[] predicates = new Predicate[predicateList.size()];

        query.where(predicateList.toArray(predicates));
        TypedQuery<Customers> typedQuery = em.createQuery(query);
        this.outList = typedQuery.getResultList();

Generates the query I need followed by three unneeded queries (An additional one for as many values are in strList). The following is printed in the log (I formated the first query to set it apart.) The first query does exactly what I want it is the next three queries which in production are causing unneeded IO that I take exception with. Note this issue does not happen if the in expression is not on a FK.

INFO: Hibernate: 
select 
  customers0_.id as id0_, customers0_.fname as fname0_, customers0_.groups as groups0_, customers0_.lname as lname0_ 
from 
  test.customers customers0_ 
where 
  customers0_.groups in (? , ? , ?)

INFO: Hibernate: select groups0_.name as name1_0_ from test.groups groups0_ where groups0_.name=?
INFO: Hibernate: select groups0_.name as name1_0_ from test.groups groups0_ where groups0_.name=?
INFO: Hibernate: select groups0_.name as name1_0_ from test.groups groups0_ where groups0_.name=?

Why the extra three queries? How do I prevent them? I need the answer as a Criteria Query.

---

Here are the entity objects:

@Entity
@Table(name = "customers", catalog = "test", schema = "")
public class Customers implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id", nullable = false)
    private Integer id;
    @Column(name = "fname", length = 45)
    private String fname;
    @Column(name = "lname", length = 45)
    private String lname;
    @JoinColumn(name = "groups", referencedColumnName = "name")
    @ManyToOne
    private Groups groups;

...getters and setters... 
}

Next entity

@Entity
@Table(name = "groups", catalog = "test", schema = "")
public class Groups implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @Column(name = "name", nullable = false, length = 45, unique = true)
    private String name;
    @OneToMany(mappedBy = "groups")
    private Collection<Customers> customersCollection;

...getters and setters... 
}

Edit ~~~~~~~~~~~~~~~~~~~~~~~~~ SOLUTION ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~:

Adding the second line to the above code resolves the issue (thank you Clement):

Root<Customers> root = query.from(Customers.class);
root.fetch(Customers_.groups, JoinType.LEFT); //that's it that's all now it will not create the extra queries

Answer 1

The reason for the additional query is because of the fact that you are using a field: private Groups groups; for the association from Customers to Groups. Since hibernate cannot intercept direct field access, it must fetch the Groups object when you fetch a Customer. It does so for criteria queries in an N+1 select manner (instead of, for instance, figuring that out and doing a join automatically or a subselect).

To fix this, you can tell hibernate to fetch the association as well:

root.fetch(Customers_.groups, JoinType.LEFT); // LEFT join since your schema could have a customer with a null group.