CODEWITHSUNDEEP
hibernatejpajpql
Rey Libutan
Rey Libutan

Reputation: 5314

JPQL: How to "SELECT new Foo(null, null... someValue, ..)?

For example I have an entity

public class Foo {
   private String col1;
   private String col2;
   private String col3;
   private String col4;

   //getters and setters   
}

What I want to do is to select only col3 and col4. But I already have a Foo constructor like below:

public Foo (String col1, String col2) {
   this.col1 = col1;
   this.col2 = col2;
}

Thus, I cannot have another constructor for col3 and col4 because it will have the same signature.

What I am trying to accomplish so far is to make a complete constructor like:

public Foo (String col1, String col2, String col3, String col4) {
   this.col1 = col1;
   this.col2 = col2;
   this.col3 = col3;
   this.col4 = col4;
}

But when I try to do something like below in my query

SELECT new Foo(null, null, f.col3, f.col4)
FROM Foo f

I get

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected end of subtree

Although when I try

SELECT new Foo(f.col1, f.col2, f.col3, f.col4)
FROM Foo f

It works as expected.

EDIT:

I tried

Select f.col3, col4..

and the error below was thrown

org.springframework.dao.InvalidDataAccessApiUsageException: Cannot create TypedQuery for query with more than one return using requested result type [com.lala.entity.Foo]; nested exception is java.lang.IllegalArgumentException: Cannot create TypedQuery for query with more than one return using requested result type [com.lala.entity.Foo]

Upvotes: 13

Views: 12076

Answers (2)

mr.Kame
mr.Kame

Reputation: 183

I know this question is old but you can also do (if cast didn't work for you):

String query = "select new Pojo( " +
        " trim(:null), " +
        " trim(:null), " +
        " f.col3, " +
        " f.col4 " +
        " ) " +
        " from Foo as f ";

return em.createQuery(query, Pojo.class)
        .setParameter("null", null)
        .getResultList();

Edited: JPA ignores literal columns when mapping them to the constructor arguments, so wrapped with trim to let JPA aware of the literal value.

Upvotes: 5

Luca Basso Ricci
Luca Basso Ricci

Reputation: 18403

You can try (but just a try)

SELECT new Foo(cast(null as string), cast(null as string), f.col3, f.col4)
FROM Foo f

or looking about cast() operator for JPQL if it is supported (I'm not sure)
else

Session session = entityManager.unwrap(Session.class);
List<Foo> list = session.createQuery("select f.col3, f.col4 from Foo f").list()

This is Hibernate specific and you don't need specific constructor for every projections; just create an empty constructor Foo() (at least protected, I don't remember if private is allowed) and let Hibernate inject value into col3, col4 from your query (and cast() operator is supported if your underlying database support the function).

Upvotes: 11

Related Questions