JPA CriteriaBuilder子查询多选

Question

我有一个关于jpa中的子查询类的问题。我需要创建两个自定义字段的子查询，但是子查询没有多选方法，选择方法有表达式输入参数（在查询中这是选择）和配置方法不合适。

此外，我对联接子查询结果有疑问，是否可能？怎么做呢？

我有：

链延伸度

public class Chain {

    @Id
    @Column(name = "chain_id")
    @GeneratedValue(generator = "seq_cha_id", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "seq_cha_id", sequenceName = "SEQ_CHA_ID", allocationSize = 1)
    private Long id;

    @Column(name = "user_id")
    private Long userId;

    @Column(name = "operator_id")
    private Long operatorId;

    @Column(name = "subject")
    private String subject;
 
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "chain")
    private List<Message> messages;

    @Column(name = "status")
    private Status status;

    public Long getOperatorId() {
        return operatorId;
    }

    public void setOperatorId(Long operatorId) {
        this.operatorId = operatorId;
    }

    public Status getStatus() {
        return status;
    }

    public void setStatus(Status status) {
        this.status = status;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getSubject() {
        return subject;
    }

    public void setSubject(String theme) {
        this.subject = theme;
    }

    public List<Message> getMessages() {
        return messages;
    }

    public void setMessages(List<Message> messages) {
        this.messages = messages;
    }

} 

消息实体

public class Message {

    @Id
    @Column(name = "message_id")
    @GeneratedValue(generator = "seq_mess_id", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "seq_mess_id", sequenceName = "SEQ_MESS_ID", allocationSize = 1)
    private Long id;
 
    @Column(name = "user_id")
    private Long userId;

    @Column(name = "message", nullable = true, length = 4000)
    private String message;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "chain_id")
    private Chain chain;

    @Column(name = "creation_date")
    private Date date;
    @Column(name = "status")
    private Status status;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public Chain getChain() {
        return chain;
    }

    public void setChain(Chain chain) {
        this.chain = chain;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public Status getStatus() {
        return status;
    }

    public void setStatus(Status status) {
        this.status = status;
    } 

}

查询包装

public class MessageWrapper {
    private final Long chainId;
    private final Long messageId;

    public MessageWrapper(Long chainId, Long messageId) {
        this.chainId = chainId;
        this.messageId = messageId;
    }
}

我需要创建这个查询（这是查询的一部分，我从谓词中获得的另一部分。JPQL不合适）

SELECT ch.* 
FROM   hl_chain ch, 
       (SELECT mes.chain_id, 
               max(message_id) message_id 
        FROM   hl_message mes 
        GROUP  BY chain_id) mes 
WHERE  mes.chain_id = ch.chain_id 
ORDER  BY message_id; 

在子查询中我做

Subquery<MessageWrapper> subquery = criteriaQuery.subquery(MessageWrapper.class);
Root<Message> subRoot = subquery.from(Message.class);
subquery.select(cb.construct(
    MessageWrapper.class,
    subRoot.get(Message_.chain),
    cb.max(subRoot.get(Message_.id))
));

但是，子查询在params中没有带有Compound选择的选择，我不能使用CriteriaBuilder构造方法。

Answer 1

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<MessageWrapper> q = cb.createQuery(MessageWrapper.class);
Root<Chain> c = q.from(Chain.class);
Join<Chain, Message> m = p.join("messages");
q.groupBy(c.get("id"));
q.select(cb.construct(MessageWrapper.class, c.get("id"), cb.max(m.get("id"))));

Answer 2

您可以从JPA调用本机查询，例如：

Query q = em.createNativeQuery("SELECT p.firstname, p.lastname FROM Person p");
List<Object[]> persons= q.getResultList();

for (Object[] p : persons) {
    System.out.println("Person "
            + p[0]
            + " "
            + p[1]);
}

Answer 3

映射为实体的数据库视图将完成您需要的工作。它仅用标签@View映射为普通表。

我在我的项目上也做了同样的事情。