QueryDsl - case expression with string value

Question

QueryDsl 3.3.4
Hibernate 3.6.10-Final I have two entities:

public class Document {
    private Confirmation confirmation;
}

public class Confirmation {
    ...
}

I need a query like this:

SELECT count(d.id), CASE WHEN d.confirmation_id IS NULL then 'NOT_CONFIRMED' else 'CONFIRMED' END as confirmed FROM document d GROUP BY confirmed;

So it should be grouped by the result of case expression above.
Now, to translate the case part to querydsl:

StringExpression confirmExp = new CaseBuilder()
    .when(Expressions.booleanTemplate("confirmation_id is null"))
    .then(Expressions.stringTemplate("NOT_CONFIRMED"))
    .otherwise(Expressions.stringTemplate("CONFIRMED"));

I'm using .when(Expressions.booleanTemplate("confirmation_id is null")) to avoid joining on confirmation table. Running the query with such expression I'm getting an exception below.
Is this another hibernate bug or such case needs to be differently?

java.lang.IllegalStateException: No data type for node: >org.hibernate.hql.ast.tree.CaseNode +-[CASE] CaseNode: 'case' | +-[WHEN] SqlNode: 'when' | | +-[IS_NULL] IsNullLogicOperatorNode: 'is null' | | | -[IDENT] IdentNode: 'confirmation_id' {originalText=confirmation_id} | | -[IDENT] IdentNode: 'NOT_CONFIRMED' {originalText=NOT_CONFIRMED} | -[ELSE] SqlNode: 'else' | -[IDENT] IdentNode: 'CONFIRMED' {originalText=CONFIRMED}

org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:156)

Timo Westkämper · Accepted Answer · 2014-05-27 18:14:11Z

24

If you want String literals in the query you need to write it as

StringExpression confirmExp = new CaseBuilder()
    .when(Expressions.booleanTemplate("confirmation_id is null"))
    .then(Expressions.stringTemplate("'NOT_CONFIRMED'"))
    .otherwise(Expressions.stringTemplate("'CONFIRMED'"));

Expressions.stringTemplate doesn't mean that the argument is serialized as a String literal, but that the created expression is of type java.lang.String.

answered May 27, 2014 at 18:14

Timo Westkämper

22.3k5 gold badges83 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wiecia Over a year ago

Yes, this did the trick, I was nat aware of this. Thanks again @timo-westkamper! By the way - is it possible to somehow alias this case exp to use alias name in group and order clauses instead of using whole exp 3 times?

wiecia Over a year ago

there is also another problem - if when part in case clause contains more complex expression (with other relations and subquery) then I cannot use it for all group, order and bean projections. It complains about selected properites not being used is group by clause - although the generated SQL looks fine and works without any problem. Maybe some alias could do the work? So it can be used in projection and later in group by and order by parts?

Stephane Over a year ago

To stay within the abstraction and not use any column name I tried a: new CaseBuilder().when(qBTS.status.eq(com.nsn.nitro.project.data.utils.BTSStatus.PLANNED)) but it produced: case when bts3_.status=COMPLETED with the quotes missing. Same for the construct: qBTS.status.when(com.nsn.nitro.project.data.utils.BTSStatus.PLANNED) which produced: case bts3_.status when COMPLETED still without quotes.

Collectives™ on Stack Overflow

QueryDsl - case expression with string value

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related