CODEWITHSUNDEEP
hibernatecaseformula
DD.
DD.

Reputation: 21981

Hibernate @Formula Case statement

I'm trying to create the following formula in hibernate:

@Formula(value = "case when orderStatus=com.mypackage.model.OrderStatus.REJECTED then 0 else 1 ")
private int openStatus;

I get the following Exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.OrderStatus.REJECTED then 0 else 1 as formula0_ from Orders order0_ order by c' at line 1

Is it possible to use the case in formula?

Upvotes: 1

Views: 10374

Answers (3)

luisbong
luisbong

Reputation: 31

I think that you have to add an "END" at the end of the SQL-Query. Try the following:

@Formula(value = "case when orderStatus=com.mypackage.model.OrderStatus.REJECTED then 0 else 1 end")
private int openStatus;

Upvotes: 3

user1438038
user1438038

Reputation: 6059

In the @Formula annotation only common SQL code is allowed. When Hibernate queries the database, the formula will be inserted as a sub-select. No HQL or Java code is allowed in the formula's value.

From the Hibernate documentation:

You can use a SQL fragment (aka formula) instead of mapping a property into a column.

Assuming we have a getter annotated such that:

@Formula("(SELECT COUNT(*) from t_child c WHERE c.parent_id = parent_id)")
public int getChildCount() {
    return childCount;
}

Then Hibernate will generate this query:

SELECT this_.parent_id                        AS parent1_0_1_,
       this_.name_                            AS name2_0_1_,

       -- Block start
       (SELECT COUNT(*)
        FROM   t_child c
        WHERE  c.parent_id = this_.parent_id) AS formula0_1_,
       -- Block end

       tchild1_.parent_id                     AS parent3_3_,
       tchild1_.child_id                      AS child1_3_,
       tchild1_.child_id                      AS child1_1_0_,
       tchild1_.parent_id                     AS parent3_1_0_,
       tchild1_.name_                         AS name2_1_0_
FROM   test.t_parent this_
       LEFT OUTER JOIN test.t_child tchild1_
                    ON this_.parent_id = tchild1_.parent_id
WHERE  this_.parent_id =?

I tried to highlight the block that is created due to the @Formula annotation. Hope this helps to understand, how Hibernate formulas work and which format is expected for the formula.

Upvotes: 2

Alex
Alex

Reputation: 11579

Try to write it this way:

@Formula(value = "case when orderStatus='REJECTED' then 0 else 1 ")
private int openStatus;

Upvotes: 1

Related Questions