java - 在构造函数 JPQL 中使用 Case

Question

我目前正在尝试通过 JPQL 中的构造函数进行查询。例如:

em 这里是 EntityManager

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

但我想在 status 中使用 Case When，所以我尝试查询:

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

我试图在 SQL Developer 中查询这个:

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

而且它工作得很好。

示例 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;
    }
}

编辑: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
}

错误:

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

有没有其他方法可以包含 Case When 方法？如果没有，请给我一些关于如何以不同的方法做到这一点的建议。谢谢。

Answer 1

您应该提供一种方法，将失败查询中的 VARCHAR2 status 从 String 转换为 Character。

试试这个:

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

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