JPA CriteriaBuilder子查询多选

问题描述

我对jpa中的Subquery类有疑问。
我需要创建带有两个自定义字段的子查询，但子查询没有多选方法，而select方法有表达式输入参数（在查询中这是选择）和constact方法不合适。

I have a question about Subquery class in jpa.I need to create subquery with two custom field, but subquery doesn't have multiselect method and select method has Expression input parameter(In query this is Selection) and constact method not suitable.

此外，我对加入子查询结果有疑问，有可能吗？怎么样？

Also I have question about join subquery results, It is possible? And how to?

我有：

Chain Enitity

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不合适）

I need to create this query (this is part of query, another part I get from predicates. JPQL not suitable)

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中使用CompoundSelection进行选择而且我不能使用CriteriaBuilder构造方法。

But, subquery don't have select with CompoundSelection in params and I can't use CriteriaBuilder construct method.

推荐答案

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"))));

这篇关于JPA CriteriaBuilder子查询多选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！