How to Map a CASE Statement alias to a java pojo attribute in JPA/JPQL?

Question

JPQL :

@Query(value="SELECT emp,CASE WHEN emp.country='india' THEN 'INDIAN' ELSE 'OTHER' END AS originCountry FROM EMPLOYEE emp")   //originCountry is not a column of Entity/Table
List<Employee> findAllEmployee()

Now how to map originCountry to a java property/attribute ? ALL result set attribute will be mapped to the employee object automatically but how to map the originCountry attribute as it is not coming from database table ?

Java class :

@Table(name="employee")
@Entity
@Getter
@Setter
class Employee{

      @Id
      @Column(name="emp_id")
      private String empId;

      @Column(name="emp_name")
      private String empName;

      @Column(name="emp_address")
      private String empAddress;

      @Transient
      private String originCountry; //I want to map CASE statement alias result to this variable
      
}

Does this answer your question? [How to map calculated properties with JPA and Hibernate](https://stackoverflow.com/questions/2986318/how-to-map-calculated-properties-with-jpa-and-hibernate) — Airy, Sep 19 '22 at 09:06
Thanks for suggesting but my use case is with CASE statement. — Ajit, Sep 19 '22 at 10:04

Yuriy Tsarkov · Answer 1 · 2022-09-21T09:28:11.940

Actually, @Airy's assumption is absolutely relevant! I just have modeled the issue with a small h2-based project. Disclaimer: it's just a piece of code of a synthetic model

Given: Pet entity. It has a typeCode property of Integer.

Todo: Implement another property, that'll return pet's type name by its typeCode.

Here, we use @Transient annotation to specify that a given attribute should not be persisted, and @PostLoad annotation to make initTransient() method be invoked after entity loading. @Formula annotation is used to calculate a custom value for a typeName during entity loading

@Entity
public class Pet {

  @Transient
  public String ownerLastName;

  @PostLoad
  void initTransient() {
    ownerLastName = Optional.ofNullable(getOwner()).map(Owner::getLastName).orElse("'Doe'");
  }

  @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
  @JoinColumn(name = "r_owner_id", referencedColumnName = "id")
  private Owner owner;

  @Id
  @Column(name = "id", nullable = false)
  @GeneratedValue
  private Long id;

  @Column
  private String name;

  @Column
  private Integer typeCode;

  @Formula("case type_code when 1 then 'Cat' when 2 then 'Dog' else 'Chupacabra' end")
  private String typeName;
  
  //get, set etc

  @Override
  public String toString() {
    return new StringJoiner(", ", Pet.class.getSimpleName() + "[", "]")
        .add("id=" + getId())
        .add("name='" + getName() + "'")
        .add("typeCode=" + getTypeCode())
        .add("ownerLastName=" + getOwnerLastName())
        .add("typeName='" + getTypeName() + "'")
        .toString();
  }
}

In a bootstrap:

var cat = new Pet();
cat.setName("Oscar");
cat.setTypeCode(1);

var dog = new Pet();
dog.setName("Lucky");
dog.setTypeCode(2);

var customPet = new Pet();
customPet.setName("Smoking kills");

Set<Pet> pets = Set.of(cat, dog, customPet);

var owner = new Owner();
owner.setFirstName("John");
owner.setPets(pets);

ownerRepository.save(owner);

System.out.println("Database was successfully initialized with data: ");
System.out.println(cat);
System.out.println(owner);

Console:
Pet[id=1, name='Oscar', age=6, ownerLastName=null, typeCode=1, typeName='null', type=CAT]
Owner[id=1, lastName='null', firstName='John']

As you can see, ownerLastName=null here, but let's go on and load data.

test:

public void interact() {
    var pet1 = petRepository.findById(1L).get();
    var pet2 = petRepository.findById(2L).get();
    var pet = petRepository.findById(3L).get();
    var owner = ownerRepository.findById(1L).get();

    System.out.println(pet1);
    System.out.println(pet2);
    System.out.println(pet);
    System.out.println(owner);
}

Console:
Pet[id=1, name='Oscar', age=6, ownerLastName='Doe', typeCode=1, typeName='Cat', type=CAT]
Pet[id=2, name='Lucky', age=10, ownerLastName='Doe', typeCode=2, typeName='Dog', type=DOG]
Pet[id=3, name='Smoking kills', age=null, ownerLastName='Doe', typeCode=null, typeName='Chupacabra', type=null]
Owner[id=1, lastName='null', firstName='John']

As you can see, ownerLastName has been initialized with a default Doe value.

From Hibernate's reference of formula:

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

Thanks for the quick help, I used @Formula and find helpful. but one query -How to refer child entity attribute while using Formula ? something like : dept.deptName where dept is child entity of employee — Ajit, Sep 20 '22 at 11:57
@Formula("CASE WHEN dept.deptName='account' THEN 'Finance' ELSE 'OTHER' END") private String deptType; //How to achive this kind of use case ? — Ajit, Sep 20 '22 at 12:04
well, It differs widely from ordinary question) It seems like a bad designed schema or whatever. Use java rather than `@Formula` — Yuriy Tsarkov, Sep 20 '22 at 20:18

How to Map a CASE Statement alias to a java pojo attribute in JPA/JPQL?

1 Answers1