[FIXED] Hibernate query for Entities having oneToMany relation (using sub-query in select statement)

Issue

I am new to hibernate and I want to create the following query using hibernate

working sql:

select * , (select count(*) from likes as li where q.ques_id= li.ques_id) as likes_Count from question as q where q.subcategory_id =1)

but can't get around for hibernate.

I have written a method in my DAOImpl class but not working when I use the subquery for COUNT.

public List<Question> mcqsListByPage(int subCatId, int pagenumber, int pagesize) {

      String query = "select * , (select count(*) from mc.userLikes as l where qs.qs_id= l.qs_id) as count1 FROM Question qs JOIN qs.userLikes AND qs.subCategory = :catId";
        try {
            return sessionFactory.getCurrentSession().createQuery(query, Question.class)
                    .setParameter("catId", subCatId)
                    .setFirstResult((pagenumber - 1) * pagesize)
                    .setMaxResults(pagesize)
                    .getResultList();

        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

I have two entity with onetomany releation

public class Question{

       @Column(name = "subcategory_id")
    private int subCategory;

        @OneToMany(mappedBy = "qs_id", fetch = FetchType.EAGER)
    private List<Likes> userLikes = new ArrayList<Likes>();
    }


public class Likes{

        @GeneratedValue(strategy = GenerationType.AUTO)
    @Id
    private int id;

        @ManyToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "QS_ID")
    private Question qs   ;
}

I want to return a list of question along with the number of likes for every question in a List<> using hibernate.

Solution

First off, when writing queries with Hibernate, you need to forget the concepts of your database, like columns, and *. With Hibernate, you query your entities (Java classes).

Since your list of Likes is fetch eagerly, you can do something as simple as:

public List<Question> getQuestionsForSubcategory(final int subcategory) {
    final TypedQuery<Question> query = 
        em.createQuery("select q from Question q where q.subCategory = :subcategory", Question.class);
    query.setParameter("subcategory", subcategory);
    return query.getResultList();
}

Then, you can use this list to create a structure containing the number of likes:

public Map<Question, Integer> getLikesPerQuestion() {
    final List<Question> questions = getQuestionsForSubcategory(1);
    return questions.stream()
                    .collect(Collectors.toMap(q -> q, q -> q.getUserLikes().size()));

If the list of Likes was lazy fetched this would decrease performance though, and a speicific query would be better.

If you want to do this with a single database-query, it is quite difficult (impossible?) to combine a query for an entity with a custom column. But you could do something like this:

public Map<Integer, Long> getQuestionIdToNumberOfLikes() {
    TypedQuery<Object[]> query = 
         em.createQuery("SELECT q.id, count(ul) FROM Question q JOIN t.userLikes ul GROUP BY q.id", Object[].class);
         return query.getResultList()
                     .stream()
                     .collect(Collectors.toMap(o -> (Integer) o[0], o -> (Long) o[1]));

}

Answered By - Tobb