Hibernate – Using a CASE statement in HQL select

hibernatehqlnhibernate

Is there any way to do the following in HQL:

SELECT 
    case when flag = true then SUM(col1) else SUM(col2)
FROM 
    myTable

Best Solution

I guess you can (3.6, 4.3) [inline edit] ...for where-clauses:

"Simple" case, case ... when ... then ... else ... end, and "searched" case, case when ... then ... else ... end

Related Solutions

Java – How to do a limit query in JPQL or HQL

This was posted on the Hibernate forum a few years back when asked about why this worked in Hibernate 2 but not in Hibernate 3:

Limit was never a supported clause in HQL. You are meant to use setMaxResults().

So if it worked in Hibernate 2, it seems that was by coincidence, rather than by design. I think this was because the Hibernate 2 HQL parser would replace the bits of the query that it recognised as HQL, and leave the rest as it was, so you could sneak in some native SQL. Hibernate 3, however, has a proper AST HQL Parser, and it's a lot less forgiving.

I think Query.setMaxResults() really is your only option.

Mysql – Hibernate HQL – Use CASE WHEN in COUNT Statement like IF in MySQL

I am by no means an expert — when HQL stymies me, I rarely have qualms about bypassing the problem by switching to straight SQL — so I can't tell you if there is a better, more HQL-ish way to do this. But in your specific instance, where B.marker is always either 0 or 1, I suppose you could change

COUNT(CASE WHEN B.marker = 1 THEN 1 ELSE NULL END)

SUM(B.marker)

and

COUNT(CASE WHEN B.marker = 0 THEN 1 ELSE NULL END)

COUNT(*) - SUM(B.marker)

(though you may also need to wrap your SUMs in COALESCE(..., 0) — I'm not sure).

Best Solution

Related Solutions

Java – How to do a limit query in JPQL or HQL

Mysql – Hibernate HQL – Use CASE WHEN in COUNT Statement like IF in MySQL

Related Question