CODEWITHSUNDEEP
javasqlhibernatehql
DanielBK
DanielBK

Reputation: 952

Hibernate: Query to select values from multiple tables using CriteriaQuery

Let's say, I have a query like

Select a.valA, b.valB
from tableA a join tableB b on a.joinCol = b.joinCol
where a.someCol = 1.

I want to execute it using Hibernate (and Spring Data) in one query to the database. I know, I can write just

Query query = em.createQuery(...);
List<Object[]> resultRows = (List<Object[]>)query.getResultList();

But my question would be - is it possible to do it in a typesafe way, using CriteriaQuery for example? The difficulty is, that, as you see, I need to select values from different tables. Is there some way to do this?

Upvotes: 0

Views: 3197

Answers (1)

Ananthapadmanabhan
Ananthapadmanabhan

Reputation: 6226

Simple example where an Employee has many to many relation to several jobs that he may have :

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

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

but saw that there is an alternate answer here : JPA Criteria API - How to add JOIN clause (as general sentence as possible)

Upvotes: 1

Related Questions