英文:
Hibernate: Query to select values from multiple tables using CriteriaQuery
问题
假设我有一个查询如下：
Select a.valA, b.valB
from tableA a join tableB b on a.joinCol = b.joinCol
where a.someCol = 1.
我想使用 Hibernate（和 Spring Data）在一个查询中将其执行到数据库中。我知道，我可以只写：
Query query = em.createQuery(...);
List<Object[]> resultRows = (List<Object[]>) query.getResultList();
但我的问题是 - 是否有可能以类型安全的方式完成，例如使用 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?
答案1
得分: 1
简单示例，其中雇员与他可能拥有的多个工作之间存在多对多的关系：
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);
}
但是发现在这里还有一个替代答案：
https://stackoverflow.com/questions/3424696/jpa-criteria-api-how-to-add-join-clause-as-general-sentence-as-possible
英文:
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 :
https://stackoverflow.com/questions/3424696/jpa-criteria-api-how-to-add-join-clause-as-general-sentence-as-possible
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论