JPA CriteriaBuilder Subquery multiselect

Question

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?

I have:

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

} 

Message Enitity

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

}

Wrapper for query

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

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

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; 

In Subquery I do

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

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

Engagement

It is almost impossible to help you without concrete example of your data, type of query you want to perform and code snippets. But it seems that you do not need subquery at all but probably need join. – Pickup

I agree, please post some code. – Typescript

Do you have any ideas? – Engagement

Here I am, wondering the same thing in December 2016. – Cooperage

Isn't it an option to replace join with subquery in where? – Dockyard

Answer 1

A view on database mapped as an entity will do the job you need. It is mapped as a normal table only with the tag @View instead.

I did the same on my projects.

Idiographic

Answer 2

You can call native queries from JPA, for example:

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]);
}

Phidippides

Answer 3

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

Kalimantan

Avoid replies containing only the code. Add explanations. – Tude