CODEWITHSUNDEEP
javahibernatespring-data-jpa
Aashish Verma
Aashish Verma

Reputation: 103

Hibernate criteria multi select query with join

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

Upvotes: 3

Views: 13092

Answers (3)

Jayasuriya
Jayasuriya

Reputation: 26

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

Upvotes: 1

SternK
SternK

Reputation: 13041

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

Upvotes: 4

Petar Bivolarski
Petar Bivolarski

Reputation: 1757

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

Upvotes: 1

Related Questions