CODEWITHSUNDEEP
javajpajpql
Santiago Perez Chavez
Santiago Perez Chavez

Reputation: 542

JPQL include elementCollection map in select statement

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?

Upvotes: 1

Views: 1860

Answers (2)

Nestor Sokil
Nestor Sokil

Reputation: 2272

You are expecting to get a complete Company object when doing select only on particular fields, which is not possible. If you really want to save some memory (which in most cases would not be that much of a success) and select only some field, then you should expect a List<Object[]>:

List<Object[]> results = entityManager.createQuery("SELECT c.id, c.name, p " +
        "FROM Company c LEFT JOIN c.companyProperties p  where c.id = :id")
      .setParameter("id", companyId)
      .getResultList();

Here the results will contain a single array of the selected fields. You can use getSingleResult, but be aware that it will throw an exception if no results were found.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

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.

Upvotes: 2

Related Questions