jpa - jpa criteriabuilder 多个连接的问题
问题描述
我有一个带有 2 个外键的表。我需要创建一个聚合使用两个外键的结果的查询。
即表A“b_id”是表B“id”的外键,表A“c_id”是表C“id”的外键123是我要查询的变量id
我可以成功找到 1 个结果,其中 id = 123 连接到表 B,如下所示...
EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<A> rt = cq.from(A.class);
List<Predicate> predList = new ArrayList<>();
Join<A, B> joinB = rt.join("b_id", JoinType.INNER);
Predicate p1 = cb.equal(joinB.get("id"),123);
predList.add(p1);
cq.select(rt).where(predList.toArray(new Predicate[predList.size()]));
Query q = em.createQuery(cq);
...我可以成功找到 1 个结果,其中 id = 123 加入到表 C 中,如下所示...
EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<A> rt = cq.from(A.class);
List<Predicate> predList = new ArrayList<>();
Join<A, C> joinC = rt.join("c_id", JoinType.INNER);
Predicate p1 = cb.equal(joinC.get("id"),123);
predList.add(p1);
cq.select(rt).where(predList.toArray(new Predicate[predList.size()]));
Query q = em.createQuery(cq);
所以我想在一个查询中获得两个结果,我需要“或”如下所示的 2 个谓词,但它不起作用?
EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<A> rt = cq.from(A.class);
List<Predicate> predList = new ArrayList<>();
Join<A, B> joinB = rt.join("b_id", JoinType.INNER);
Join<A, C> joinC = rt.join("c_id", JoinType.INNER);
Predicate p1 = cb.equal(joinB.get("id"),123);
Predicate p2 = cb.equal(joinC.get("id"),123);
predList.add(cb.or(p1, p2));
cq.select(rt).where(predList.toArray(new Predicate[predList.size()]));
Query q = em.createQuery(cq);
请问我在这里做错了什么?谢谢 ...
当我应用 Twister 建议的更改时,此查询生成的 SQL 是(省略了多余的字段)
SELECT t1.my_id FROM e t3, d t2, a t1, c t0 WHERE (((t1.my_id = 123) OR (t3.my_id = 123)) AND (((t0.id = t1.id) AND (t2.id = t0.id)) AND (t3.id = t2._id)))
解决方案
CriteriaQuery 的函数 where(Predicate... limits) 对所有限制使用运算符“AND”。
如果你想对 2 个谓词使用“或”,你可以试试这个:
cq.select(rt).where(cb.or(predList.toArray(new Predicate[0])));
推荐阅读
- asp.net-web-api - Bearer token: The signature is invalid - Default ASP.NET Core 2.1 Web Api template published to Azure
- python - pandas replace NaNs with modus of another column based on second column
- graphql - 如何将变量传递给graphql查询?
- c# - 基于 RadioButtons 的 C# WPF 过滤器组合框
- python - 如何使用nose_parameterized参数化setUp方法
- reactjs - How to export function and import react hooks
- php - PHP file upload with multiple post values
- c# - FlowLayoutPanel VerticalScroll 怎么用?
- javascript - how to show user's “last seen at” time using the seconds received from the server
- haskell - Multiple type mismatches in this program