CODEWITHSUNDEEP
hibernatehashmapfilteringjpa-2.0h2
Manu
Manu

Reputation: 545

HIBERNATE - JPA2 - H2 - Querying @ElementCollections HashMap by key

I'm using hibernate-entitymanager 3.6.4.Final and h2 database 1.3.155

I'm using the H2Dialect.

I'm having trouble filtering records by elements in an @ElementCollection. Here is my entity

@Entity
public class Item {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @MapKeyColumn(name="name", length=50)
  @Column(name="value", length=100)
  protected Map<String, String> attributes;
  /*  etc.  */
}

Here is my query :

Item item = em.createQuery("FROM Item i JOIN i.attributes a WHERE KEY(a)='myAttrName'").getSingleResult();

Here is the error message :

4971 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 90022, SQLState: 90022
4971 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Function "KEY" not found; SQL statement:
Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query

This is quite strange because the functions KEY() and VALUE() are available in the doc here

Am I missing some configuration ? Any idea ?

Thanks in advance

Upvotes: 3

Views: 9286

Answers (1)

Edwin Dalorzo
Edwin Dalorzo

Reputation: 78589

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

https://hibernate.atlassian.net/browse/HHH-6103

And have posted the question in the Hibernate Users Forum

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

At the end I had to solve the problem using a collection of embeddable objects containing the same entries as the map. I was forced to implement equals and hasHCode in the embeddable in order to satisfy the same behavior.

Upvotes: 7

Related Questions