Hibernate criteria multi select query with join

Question

Below is my entity class where I am retrieving this entity using criteria builder but I need to fetch only id, title and tags.

Question.java

@Entity
@Table(name = "QUESTION_TITLE")
public class Question {

   @Id
   @Column(name = "id")
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;

   @Column(name = "title")
   private String title;

   @OneToOne(mappedBy = "question", cascade = CascadeType.ALL)
   private Body body;

   @ManyToMany
   @JoinTable(name = "QUESTION_TAGS", joinColumns = @JoinColumn(name = "question_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "tag_id", referencedColumnName = "id"))
   private Collection<Tags> tags;

   @ManyToOne(targetEntity = Registration.class, fetch = FetchType.LAZY)
   @JoinColumn(nullable = false, name = "user_id", referencedColumnName = "id")
   private Registration registration;
}

below is my code to fetch.i am don't know how would project Tags table.

javax.persistence.criteria.CriteriaQuery<Question> query = getCriteriaBuilder().createQuery(Question.class);
Root<Question> question = query.from(Question.class);
query.multiselect(question.get("id"), question.get("title"), question.get("tags"));
List<Question> questionList = entityManager.createQuery(query).getResultList();

SternK · Answer 1 · 2020-02-27T07:35:18.240

You can use a tuple criteria query:

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Tuple> criteria = builder.createTupleQuery();
Root<Question> root = criteria.from(Question.class);
Path<Long> qId = root.get("id");
Path<String> qTitle = root.get("title");
Join<Question, Tag> qTag = root.join("tags", JoinType.INNER);

criteria.multiselect(qId, qTitle, qTag);
List<Tuple> tuples = session.createQuery(criteria).getResultList();
for (Tuple tuple : tuples)
{
   Long id = tuple.get(qId);
   String title = tuple.get(qTitle);
   Tag tag = tuple.get(qTag);
}

Another alternative to selecting multiple values is select an object that will "wrap" the multiple values. For this approach you should declare a wrapper class:

public class QuestionTag
{
   private final Long id;
   private final String title;
   private final Tag tag;

   public QuestionTag(Long id, String title, Tag tag)
   {
      this.id = id;
      this.title = title;
      this.tag = tag;
   }

   public Long getId()
   {
      return id;
   }

   public String getTitle()
   {
      return title;
   }

   public Tag getTag()
   {
      return tag;
   }
}

And then use it in the query:

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<QuestionTag> criteria = builder.createQuery(QuestionTag.class);
Root<Question> root = criteria.from(Question.class);
Path<Long> qId = root.get("id");
Path<String> qTitle = root.get("title");
Join<Question, Tag> qTag = root.join("tags", JoinType.INNER);
criteria.select(builder.construct(QuestionTag.class, qId, qTitle, qTag));

List<QuestionTag> wrappers = session.createQuery(criteria).getResultList();

this is working but why i can't get tags in collection as declared in entity; — Aashish Verma, Feb 24 '20 at 07:38
@AashishVerma I can not prove it by reference from the documentation, but I am afraid that you should select the whole entity to achieve it. You can declare all associations as lazy and fetch only necessary association `tags`. — SternK, Feb 24 '20 at 08:41

score 1 · Answer 2 · answered Feb 23 '20 at 17:45

If you want to fetch only id, title and tags of the Question entity, you can use CriteriaQuery with Tuple.

Try the following code:

   CriteriaQuery<Tuple> query = getCriteriaBuilder().createTupleQuery();
   Root<Question> question = query.from(Question.class);
   query.multiselect(question.get("id"), question.get("title"), question.get("tags"));
   List<Tuple> questionList = entityManager.createQuery(query).getResultList();

score 1 · Answer 3 · answered Nov 30 '22 at 09:36

Use criteriabuilder to construct the value. Note(generate constructor using that fields in the QuestionPojo class so that it will map). Hope it helps

@Autowired
EntityManager manager;

CriteriaBuilder cb = manager.getCriteriaBuilder();  
CriteriaQuery<QuestionPojo> query = cb.createQuery(QuestionPojo.class); 
Root<Question> question = query.from(Question.class);
query.select(getSelection(question,cb)); 
List<QuestionPojo> res = manager.createQuery(query).setFirstResult(pageNo * pageSize).setMaxResults(pageSize).getResultList();      


private static Selection<QuestionPojo> getSelection(Root<Question> question,CriteriaBuilder builder) {
    return builder.construct(QuestionPojo.class,question.get("id"), question.get("title"), question.get("tags"));
}

Hibernate criteria multi select query with join

3 Answers3

Linked