Organizational Research By

Surprising Reserch Topic

is there such thing case expression in jpql

is there such thing case expression in jpql  using -'java,oracle,orm,jpa'

Let say there is a table:

TableA:Field1, Field2, Field3

and associated JPA entity class

public class TableA{
  private Long id;

  private Long field2;

  private Long field3;

  //... more associated getter and setter...

Is there any way to construct a JPQL statement that loosely translated to this SQL, ie how to translated the case expression to JPQL?

select field1,
  when field2 = 1 then 'One'
  when field2 = 2 then 'Two'
  else 'Other number'
from tableA;


asked Sep 30, 2015 by deepak07.s
0 votes

Related Hot Questions

3 Answers

0 votes

It has been added in JPA 2.0


SELECT, CASE WHEN (e.salary >= 100000) THEN 1 WHEN (e.salary < 100000) THEN 2 ELSE 0 END FROM Employee e


answered Sep 30, 2015 by rolvyrf
0 votes

There is certainly such thing in Hibernate so when you use Hibernate as your JPA provider then you can write your query as in this example:

    Query query = entityManager.createQuery("UPDATE MNPOperationPrintDocuments o SET o.fileDownloadCount = CASE WHEN o.fileDownloadCount IS NULL THEN 1 ELSE (o.fileDownloadCount + 1) END " +
                                            " WHERE o IN (:operations)");
    query.setParameter("operations", mnpOperationPrintDocumentsList);

    int result = query.executeUpdate();
answered Sep 30, 2015 by gauravg.gwl
0 votes

You can use the event listeners provided by Jpa to do something when you load one row of the db, ie:

@Table(name = "TableA")
public class TableA {

    @Column(name = "Field1")
    private Long id;

    @Column(name = "Field2")
    private Long field2;

    @Column(name = "Field3")
    private Long field3;

    // ... more associated getter and setter...

    private String field4;

    private void onLoad() {
        if (field2 != null) {
            switch (field2.intValue()) {
            case 1:
                field4 = "One";
            case 2:
                field4 = "Two";
                field4 = "Other Number";

(the field4 not persist in the db)

(take this like an workaround to "non implemented feature in JPA" like case statements)

answered Sep 30, 2015 by rajeshujade