1

I have an Entity MyEntity which has an ElementCollection Map in it as shown below. The Map<String, MyEmbeddableObject>, values are an Embeddable object MyEmbeddableObject and keys are 'String'. The db tables and JPA mapping everything is working correctly in system currently. I just need a query to get the required info.

Assuming that MyEmbeddableObject has some 'String' properties in it, is there a way to write a JPA NamedQuery so that I can filter the results based on comparison of properties from MyEmbeddableObject ? Also the query should return (the select clause) MyEmbeddableObject and not MyEntity. findMyEmbeddableObjectByAttr1 is what I have tried and doesn't work. It complains the JPA Query Grammar is wrong.

I could get it done using Native query, but ideally I want to be able to do it with JPA NamedQuery for all the disadvantages associated with Native query.

  @Entity
  @NamedQueries({
  @NamedQuery(name = "findMyEmbeddableObjectByAttr1",
         query = "Select n from MyEntity v, in (v.myMap) n WHERE n.attr1 = :someUserPassedValue")
    })
  public class MyEntity {

    @ElementCollection
    @MapKeyColumn(name="someKey")
    private Map<String, MyEmbeddableObject> myMap;
  }

  @Embeddable
  public class MyEmbeddableObject {
      private String attr1;
      private String attr2;
  }

All my SO searches and Google searches showed how one can write query to filter by Map's Keys or by comparing the entire Map value object. Comparison of Map value is an easy option if values are primitive type like String, int. But in my case its an Complex object.

These SO questions are the closest I could get to answers :

Execute "MEMBER OF" query against 'ElementCollection' Map fields in JP-QL (JPA 2.0)

LIKE query on values of Map ElementCollection in entity in HQL

Any help, greatly appreciated! Thanks.

Community
  • 1
  • 1
kan
  • 11
  • 3
  • Your query seems correct to me, can you post the error message? I tried following query and it was OK: `select lifeEvent from Person p, in (p.lifeEvents) lifeEvent where lifeEvent.place = :place` (p.lifeEvents is a map with embeddable values, place is of String type) – OndroMih Sep 21 '15 at 18:36
  • @OndrejM JDBCExceptionReporter:100 - SQL Error: 0, SQLState: 42703 JDBCExceptionReporter:101 - ERROR: column myMap1_.objectid does not exist Position: 8 ERROR http-nio-127.0.0.1-7441-exec-1 BaseRestController:388 - Exception : 'org.hibernate.exception.SQLGrammarException: could not execute query; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query' – kan Sep 21 '15 at 19:36
  • I enabled debug logs for hibernate and found the issue was, JPA was trying to refer column `objectid` of `myMap1` and hence the exception. So the generated sql was like `SELECT myMap.objectId ..` and the db table doesn't have that column. Not sure why it was trying to select `objectid` when I wanted JPQL to SELECT **entire** `MyEmbeddableObject` !? Luckily for me even getting the count of result seems enough so following query works `SELECT n.attr1 from MyEntity v, in (v.myMap) n WHERE n.attr1 = :someUserPassedValue` . It will great to know of any way of selecting entire MyEmbeddableObject. :( – kan Sep 21 '15 at 21:00
  • It is also wierd that you got `myMap1_` for the table name - it is usually derved from parent entity and name of field, I would expect something like `myEntity_myMap`. Are you sure you do not overwrite default mappings somewhere, e.g. in orm file? – OndroMih Sep 21 '15 at 21:15
  • Yeah, not sure why the generated query has `myMap1_.objectid as coll_0_0` in SELECT clause. My EntityManagerFactory bean is of type `org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean` with its `jpaPropertyMap` has `hibernate.ejb.naming_strategy = org.hibernate.cfg.ImprovedNamingStrategy` and `hibernate.ejb.interceptor.session_scoped = mypackage.modelsupport.DomainObjectInterceptor` . I think its probably `ImprovedNamingStrategy` that adds underscores. But **real** question is why generated query tries to refer parent Entity property `objectId` in SELECT? – kan Sep 22 '15 at 00:43
  • It seems that hibernate somehow maps `MyEmbeddableObject.attr1` in JPQL to `objectId` column in SQL. Try to define the column explicitely using `@Column(name="attr1") private String attr1;` (where attr1 is the name of SQL column) amd see what happens. I guess this is worth asking a completely new question... it is not related to JPA itself, but with how hibernate generates table and column names and why it generated `objectId` column for your field. – OndroMih Sep 23 '15 at 07:31

0 Answers0