JPQL include elementCollection map in select statement

Question

I have an @Entity class Company with several attributes, referencing a companies Table in my db. One of them represents a Map companyProperties where the companies table is extended by a company_properties table, and the properties are saved in key-value format.

@Entity
@Table(name = "companies")
public class Company extends AbstractEntity {

    private static final String TABLE_NAME = "companies";

    @Id
    @GeneratedValue(generator = TABLE_NAME + SEQUENCE_SUFFIX)
    @SequenceGenerator(name = TABLE_NAME + SEQUENCE_SUFFIX, sequenceName = TABLE_NAME + SEQUENCE_SUFFIX, allocationSize = SEQUENCE_ALLOCATION_SIZE)
    private Long id;

    //some attributes

    @ElementCollection
    @CollectionTable(name = "company_properties", joinColumns = @JoinColumn(name = "companyid"))
    @MapKeyColumn(name = "propname")
    @Column(name = "propvalue")
    private Map<String, String> companyProperties;

    //getters and setters
}

The entity manager is able to perform properly find clauses

Company company = entityManager.find(Company.class, companyId);

However, I am not able to perform JPQL Queries in this entity and retrieve the Map accordingly. Since the object is big, I just need to select some of the attributes in my entity class. I also do not want to filter by companyProperties but to retrieve all of them coming with the proper assigned companyid Foreign Key. What I have tried to do is the following:

TypedQuery<Company> query = entityManager.createQuery("SELECT c.id, c.name, c.companyProperties " +
            "FROM Company as c where c.id = :id", Company.class);
query.setParameter("id", companyId);
Company result = query.getSingleResult();

The error I get is:

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Problem compiling [SELECT c.id, c.name, c.companyProperties FROM Company as c where c.id = :id]. [21, 40] The state field path 'c.companyProperties' cannot be resolved to a collection type. org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1616) org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1636) com.sun.enterprise.container.common.impl.EntityManagerWrapper.createQuery(EntityManagerWrapper.java:476)

Trying to do it with joins (the furthest point I got was with

Query query = entityManager.createQuery("SELECT c.id, c.name, p " +
            "FROM Company c LEFT JOIN c.companyProperties p  where c.id = :id");

does not give me either the correct results (it only returns the value of the property and not a list of them with key-value).

How can I define the right query to do this?

you cannot include multi-valued items in the result clause as any JPQL doc would tell you — Neil Stockton

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2017-07-15T10:36:02

Your JPA syntax looks off to me. In your first query you were selecting individual fields in the Company entity. But this isn't how JPA works; when you query you get back the entire object, with which you can access any field you want. I propose the following code instead:

TypedQuery<Company> query = entityManager.createQuery("from Company as c where c.id = :id", Company.class);
query.setParameter("id", companyId);
Company result = query.getSingleResult();

Similarly, for the second join query I suggest the following code:

Query query = entityManager.createQuery("SELECT c" +
        "FROM Company c LEFT JOIN c.companyProperties p WHERE c.id = :id");
query.setParameter("id", companyId);
List<Company> companies = query.getResultList();

The reason why only select a Company and not a property entity is that properties would appear as a collection inside the Company class. Assuming a one to many exists between companies and properties, you could access the propeties from each Company entity.

JPQL include elementCollection map in select statement

2 Answers