0

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?

DanielBK
  • 892
  • 8
  • 23

1 Answers1

1

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)

Ananthapadmanabhan
  • 5,706
  • 6
  • 22
  • 39
  • Thank you, but as fas as I see, you are selecting values from only one table (Employee). You just add restrictions based on JOINs to other tables. But what I need is a possiblility to have fields from different tables in my result set. – DanielBK Sep 17 '20 at 10:28
  • @DanielBK just specify use `multiselect` to specify which all columns you need from the tables. – Ananthapadmanabhan Sep 17 '20 at 10:57
  • 1
    Yes, that worked and it is exactly what I needed! Thanks!! – DanielBK Sep 18 '20 at 08:20