Using Case When inside Constructor JPQL

Question

I'm currently try to query via constructor in JPQL. For example:

em here is the EntityManager

em.createQuery("SELECT NEW {packagePath}.UserDTO(name, status) FROM User", UserDTO.class);

But I want to use Case When in status so I tried to query:

em.createQuery("SELECT NEW {packagePath}.UserDTO(name, case when status = 'A' then '1' else '0' end as status) FROM User", UserDTO.class);

I tried to query this in SQL Developer:

SELECT name, CASE WHEN status = 'A' THEN '1' ELSE '0' END AS status FROM User

and it works perfectly fine.

Where the sample DTO:

UserDTO.class

public class UserDTO {
    private String name;
    private Character status;

    // Suggested by Viacheslav Shalamov but not working.
    public UserDTO(){}

    // EDIT
    public UserDTO(String name, Character status){
         this.name = name;
         this.status = status;
    }
}

EDIT: User.class

@Entity
@Table(name = "User")
public class User {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "name", length = 100, columnDefinition = "VARCHAR2(100)")
    private String name;
    @Column(name = "pc_no", length = 3, columnDefinition = "VARCHAR2(3)")
    private String pc_no;
    @Column(name = "status")
    private Character status;

    // constructor / get and set
}

Error:

Unable to locate appropriate constructor on class. Expected arguments are: java.lang.String, java.lang.String.

Is there any other way to include the Case When method? If not, please give me some advice on how to do this in a different approach. Thank you.

Answer 1

You should provide a way to convert your VARCHAR2 status from String to Character in the failing query.

try this:

public class UserDTO {
    private String name;
    private Character status;

    public UserDTO(String name, String status){
        this.name = name;
        this.status = status.charAt(0);
    }
}