How can i retrieve a list of entites + an additional value with JPA/Hibernate?

There are two tables, report and reportcomment. Every reportcomment is assign (via foreign key) to a report, therefore a report has zero to many reportcomments.

If I need a list, that gives me all reports with the respective number of comments of every report, I would do the following (with SQL):

SELECT r.*, c.cnt
FROM report r
LEFT JOIN (
    SELECT ir.id AS report_id, COUNT(ic.id) AS cnt
    FROM report ir
    INNER JOIN reportcomment ic ON ir.id = ic.report_id
    GROUP BY ir.id
) c ON c.report_id = r.id

I开发者_C百科 would like to retrieve such a list with JPA/Hibernate and store the c.cnt somewhere in my Report entity object. How could that be achieved?

---

I think the simpliest way would be to create a transient field in Report and convert tuples returned by the appropriate query manully, something like this:

List<Object[]> tuples = em.createQuery(
    "SELECT r, COUNT(c) " +
    "FROM ReportComment c RIGHT JOIN c.report r" +
    "GROUP BY r.id, ...").getResultList();

List<Report> reports = new ArrayList<Report>();
for (Object[] tuple: tuples) {
    Report r = (Report) tuple[0];
    r.setCommentCount((Long) tuple[1]);
    reports.add(r);
}

---

I believe @SqlResultSetMapping may work for you.

http://download.oracle.com/javaee/5/api/javax/persistence/SqlResultSetMapping.html

http://blogs.oracle.com/JPQL01/entry/native_query_in_java_persistence

---

I'm sure there are a number of reasons that what you're working on isn't designed this way. There's probably a greater number of reasons why you don't want to redesign it. I realize this might not answer your question at all, but if I were you and I had the time, I'd have the inclination to make something like this:

public class Comment { 
...
List<CommentReport> commentReports = new ArrayList<CommentReport>();

@OneToMany(mappedBy="comment")
public List<CommentReports> getCommentReports() {
    return commentReports;
}

public void setCommentReports(List<CommentReport> commentReports) {
    this.commentReports = commentReports;
}

@Transient
public int countReports() {
    return commentReports.size();
}

What i've proposed assumes you're working in a web application and are using some sort of open-session-in-view. Otherwise you'd probably have to fetch those comments eagerly which could be bad.

But, if you're going to use hibernate why not go a bit further? It's whole purpose is to abstract and hide database code and what i've provided is a step in that direction.