第二次执行相同的条件查询会生成错误的SQL [英] Second execution of the same criteriaQuery generates wrong sql
问题描述
我不是JPA持久性标准API专家，有时使用它会感到非常头疼。
I'm not a JPA persistence criteria API guru and sometimes I get very terrible headaches using it.
昨天我注意到一种新的非常奇怪的行为。我将发布的代码是对现有功能代码的改编，因此不要将重点放在琐碎的错误上。我使用的是glassfish 3.1.1和相应的eclipse持久性插件以及Mysql DB。
Yesterday I noticed a new very weird behaviour. The code I will post is an adaptation of existing functioning code, so don't focus on trivial errors. I'm using glassfish 3.1.1 and the corresponding eclipse persistence plugin and Mysql DB.
我写了一个CriteriaQuery来过滤来自不同表的数据。如果此条件查询第二次执行两次，则会生成错误的SQL查询。我不知道为什么。
I have written a criteriaQuery which filters data from different tables. If this criteriaquery is executed twice the second time it generates wrong SQL query. I cannot figure out why.
public CriteriaQuery createQuery4Count(EntityManager em) {
Calendar lastDate4Search = GregorianCalendar.getInstance();
javax.persistence.criteria.CriteriaBuilder cb = em.getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery cq = cb.createQuery();
javax.persistence.criteria.Root<Permessimercepath> checkPointRt = cq.from(Permessimercepath.class);
javax.persistence.criteria.Path<Permessimerce> permessimerceClass = checkPointRt.get(Permessimercepath_.permessimerce);
Predicate checkPointDatePredicate = cb.isNull(checkPointRt.get(Permessimercepath_.dataTransito));
Predicate checkPointAreaPredicate = cb.equal(checkPointRt.get(Permessimercepath_.iDArea), area);
Predicate datePredicate = cb.greaterThanOrEqualTo(permessimerceClass.get(Permessimerce_.datafine), lastDate4Search.getTime());
Predicate isValidPredicate = cb.lt(permessimerceClass.get(Permessimerce_.statopermesso), Permessimerce.COMPLETED);
cq.where(cb.and(checkPointAreaPredicate, checkPointDatePredicate, datePredicate, isValidPredicate));
cq.select(cb.countDistinct(checkPointRt));
return cq;
}
CriteriaQuery myCriteriaQuery = createQuery4Count(getEntityManager())
javax.persistence.Query q = getEntityManager().createQuery(myCriteriaQuery );
Long Result = ((Long) q.getSingleResult()).intValue();
// second query created with the same criteriaQuery
q = getEntityManager().createQuery(myCriteriaQuery );
Long Result2 = ((Long) q.getSingleResult()).intValue();
生成的sql是
// First and correct one
SELECT COUNT(t0.ID_permesso) FROM permessimercepath t0 WHERE EXISTS (SELECT t1.ID_permesso FROM permessimerce t2, permessimercepath t1 WHERE ((((t0.ID_permesso = t1.ID_permesso) AND (t0.CheckPointIndex = t1.CheckPointIndex)) AND ((((t1.ID_Area = ?) AND (t1.DataTransito IS NULL)) AND (t2.Data_fine >= ?)) AND (t2.Stato_permesso < ?))) AND (t2.ID_permesso = t1.ID_permesso)))
bind => [3 parameters bound]
// Second and wrong one
SELECT COUNT(t0.ID_permesso) FROM permessimercepath t0, permessimerce t2, permessimercepath t1 WHERE (((((t1.ID_Area = ?) AND (t1.DataTransito IS NULL)) AND (t2.Data_fine >= ?)) AND (t2.Stato_permesso < ?)) AND (t2.ID_permesso = t1.ID_permesso))
如果没有人知道为什么会发生，我可以尝试以更简单的方式进行复制。
If nobody has an idea on why it happens I can try to reproduce it in a simpler way.
感谢
Filippo
Thanks Filippo
推荐答案
您似乎发现了实施中的错误。稍微相似（或者说更复杂但又不同）的情况在Hibernate中可以正常工作。另外， CriteriaQuery ：
It looks likely that you spotted bug in implementation. Slightly similar (or so to say more complex but different) case works fine with Hibernate. Also, section "6.8 Query Modification" from JPA Specification encourages reuse of CriteriaQuery:
可以在创建和执行
TypedQuery对象之前或之后修改CriteriaQuery对象。以
为例，这种修改可能需要替换where
谓词或选择列表。因此，修改可能导致相同的
相同的CriteriaQuery基础被几个查询实例重用。
A CriteriaQuery object may be modified, either before or after TypedQuery objects have been created and executed from it. For example, such modification may entail replacement of the where predicate or the select list. Modifications may thus result in the same CriteriaQuery "base" being reused for several query instances.
这篇关于第二次执行相同的条件查询会生成错误的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
