jpa criteriabuilder join maps

Question

I have the following criteria builder query

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Object> critQuery = cb.createQuery();

    Root<Role> role = critQuery.from(Role.class);

    //create a join between role and permission
    MapJoin<Role,String,Permission> perm = role.joinMap("permissions");
    critQuery.multiselect(role.get("label"), perm.key(), perm.value());

    //this line throws NPE
    Query query = em.createQuery(critQuery);

The last line throws a null pointer exception.

java.lang.NullPointerException
at org.hibernate.ejb.criteria.path.AbstractPathImpl.prepareAlias(AbstractPathImpl.java:246)
at org.hibernate.ejb.criteria.path.AbstractPathImpl.render(AbstractPathImpl.java:253)
at org.hibernate.ejb.criteria.path.AbstractPathImpl.renderProjection(AbstractPathImpl.java:261)

Very interesting! Check the [source code](http://grepcode.com/file/repository.jboss.org/nexus/content/repositories/releases/org.hibernate/hibernate-entitymanager/3.5.0-CR-1/org/hibernate/ejb/criteria/path/AbstractPathImpl.java#AbstractPathImpl.getPathSource%28%29), I think this is some classpath issue. — CMR, Apr 07 '11 at 13:19

Edwin Dalorzo · Accepted Answer · 2011-04-09T17:30:57.710

I have exactly the same problem. After hours of dealing with the issue, and after debugging the Hibernate source code, and after checking over and over again the examples in books and in the JPA 2.0 Specification, I decided to give it a try in EclipseLink.

So, I created a very simple example: an employee with a map of phone numbers, where the key is the type of phone (home, office, mobile) and the value was the phone number.

@ElementCollection(fetch=FetchType.EAGER)
@CollectionTable(name="emp_phone")
@MapKeyColumn(name="phone_type")
@Column(name="phone_num")
private Map<String, String> phoneNumbers;

I could verify that this works perfectly with EclipseLink 2.1 and OpenJPA 2.1.0, but it fails in Hibernate 3.5.3, 3.6.1., 3.6.3

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> criteria = builder.createQuery(Employee.class);
Root<Employee> employeeRoot = criteria.from(Employee.class);
criteria.select(employeeRoot);
MapJoin<Employee, String, String> phoneRoot = employeeRoot.joinMap("phoneNumbers");

criteria.where(builder.equal(phoneRoot.key(), "HOME"));

System.out.println(entityManager.createQuery(criteria).getResultList());

I thought, well if Criteria API fails, perhaps I can do it with a named query. Interestingly, Hibernate does not support the KEY, VALUE or ENTRY keywords, and therefore queries proved to be malformed.

http://opensource.atlassian.com/projects/hibernate/browse/HHH-5396

This is what run:

String query = "SELECT e FROM Employee e JOIN e.phoneNumbers p WHERE KEY(p) IN ('HOME')";
System.out.println(entityManager.createQuery(query, Employee.class).getResultList());

In hibernate it generates the following SQL query:

   select
        employee0_.id as id0_,
        employee0_.name as name0_ 
    from
        Employee employee0_ 
    inner join
        emp_phone phonenumbe1_ 
            on employee0_.id=phonenumbe1_.Employee_id 
    where
        KEY(phonenumbe1_.phone_num) in (
            'HOME'
        )

Which is evidently malformed.

Again, in EclipseLink and OpenJPA this works.

So, evidently, something must be wrong with Hibernate. I have submitted an bug in the Hibernate Jira Issue Tracker

http://opensource.atlassian.com/projects/hibernate/browse/HHH-6103

And have posted the question in the Hibernate Users Forum

https://forum.hibernate.org/viewtopic.php?f=1&t=1010411

Thanks for all that effort. I am sure lot of ppl will find it useful — user373201, Apr 08 '11 at 17:30
I run into the same problem and I would be very pleased if there were a proper hibernate solution out there. Every hint is welcome :) — Tobias Sarnow, May 15 '12 at 12:44
@Tobias If you review the Hibernate forum you will notice that I suggested two workarounds there. — Edwin Dalorzo, May 15 '12 at 13:08
@edalorzo You mean the Hibernate Bug-tracking page. I think I'll consider using option #2 -> 2. Do not use map, use another kind of collection. In my case I created a class (kind of an entry class with key and value) and then use a set collection to hold the information of this entry objects. Correctly implemting equals() and hashCode let me look for items in the set just using the key value. — Tobias Sarnow, May 16 '12 at 07:42
@Tobias Exactly, that was the workaround that I used, because I had no choice, I could not change the JPA provider. This solution worked just fine, and the good thing is that you can later change it for map, as we originally intended, when Hibernate guys fix this thing (if ever). How about upvoting my answer? I am strugling to reach 5000 points in SO and if this helped you, you upvote is more than welcome :-) — Edwin Dalorzo, May 16 '12 at 12:51
The link to the bug is now: https://hibernate.atlassian.net/browse/HHH-6103 — FelixJongleur42, Nov 19 '21 at 16:35

dcrall · Answer 2 · 2013-09-17T19:30:05.850

For those stuck on Hibernate 3.6, I was able to join through a map and limit the results with a predicate. This solution comes with a significant caveat. This technique only works if the map key you're interested in exists for every record. If it does not, you will lose records that you might otherwise expect in the result set.

My case is very similar to Edwin's example. We have a Person entity with a map of current names where the map key is a name type and the value is a Name object. In my case, I wanted to retrieve a person's current standard name.

Root<Person> person = query.from(Person.class);
Join currentName = person.join("currentNames");

NameLookup standardName = NameLookup.lookup("ST");

Predicate useridMatches = criteria.equal(person.get("userid"), "user1");
Predicate isStandardName = criteria.equal(currentName.get("nameType"), standardName);
Predicate useridAndStandardName = criteria.and(useridMatches, isStandardName);
query.where(useridAndStandardName);

In JPQL, we typically use the WITH clause to limit the join.

inner join person.currentNames currentStandardName 
with currentStandardName.nameType.id = :standardNameLookupId

jpa criteriabuilder join maps

2 Answers2

Linked