Re: Criteria API; AND 1 = 1 in WHERE Clause

[Georg Nozicka <ge...@nozicka.at>]

Hello,

I tried to reduce the sample as far as possible. First the code:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<UnternehmenUrv> cq = cb.createQuery(UnternehmenUrv.class);
Root<UnternehmenUrv> untRoot = cq.from(UnternehmenUrv.class);
cq.select(untRoot);

Join&lt;UnternehmenUrv, UnameHist&gt; unameHistJoin = untRoot.join(
  UnternehmenUrv_.unameHist.getName(), JoinType.INNER);

Join&lt;UnternehmenUrv, Adresse&gt; adresseJoin = untRoot.join(
  UnternehmenUrv_.adressen.getName(), JoinType.INNER);

List<Predicate> predicates = new ArrayList<Predicate>();

ParameterExpression<String> pe = cb.parameter(String.class,
  UnameHist_.unameL.getName());
predicates.add(cb.like(
  unameHistJoin.get(UnameHist_.unameL).as(String.class), pe));

ParameterExpression<String> peStr = cb.parameter(String.class,
  Adresse_.strL.getName());
predicates.add(cb.like(adresseJoin.get(Adresse_.strL).as(String.class),
  peStr));

cq.where(cb.and(predicates.toArray(new Predicate[0])));

TypedQuery<UnternehmenUrv> tq = em.createQuery(cq);

tq.setParameter(UnameHist_.unameL.getName(), "nozick%");
tq.setParameter(Adresse_.strL.getName(), "fadenw%");

return tq.getResultList();

The toString for the CriteriaQuery instance gives the following output:
SELECT u FROM UnternehmenUrv u INNER JOIN u.unameHist ?  INNER JOIN
u.adressen ?  WHERE (u.unameHist.unameL LIKE :unameL AND u.adressen.strL
LIKE :strL)

The jpa trace for the generated SQL is like this:
SELECT t0.OID, t0.UKOID, t0.UNAME, t0.UNTID FROM udbq.TUUNT_UNTERNEHMEN t0
INNER JOIN udbq.TUUNT_UNAME_HIST t1 ON t0.OID = t1.UOID INNER JOIN
udbq.TUUNT_ADRESSE t2 ON t0.OID = t2.BEZOID WHERE (t1.UNAME_L LIKE ? ESCAPE
'\' AND t2.STR_L LIKE ? ESCAPE '\' AND 1 = 1)  [params=?, ?]

Any ideas?

--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6525216.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

[Georg Nozicka <ge...@nozicka.at>]

Hello,

this are good news, that it could be fixed. So I guess I cannot do anything
different in coding the queries so that the "1 == 1" is not added to the
query. 

The point is, that we are evaluating using JPA in general and OpenJpa in
specific as a new way to handle our persistence. Our DB gurus are very picky
about, which SQL code finally is created for specific queries and the “1 ==
1” is for the moment a construct they don’t like because they mean that it
not clear, which side effects such constructs could have and how DB
optimizers handle it in various situations. Therefore I will create a Jira
and hope that it will get fixed.

Georg

--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6527779.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

[Pinaki Poddar <pp...@apache.org>]

Hi,
It could be fixed -- the query expression tree sees a expression node but
that expression is not used in the query filter and inserts a "1==1"
condition i.e the node is always satisfied.

But how important it is to be fixed in your view?
If you think it is, please create a JIRA.

-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6526360.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

[Georg Nozicka <ge...@nozicka.at>]

Hello,

thanks for the Nits ...

I've uploaded the three *_classes. Are they enough or do you also need some
others (which are not referenced in the posted code directly)?

http://openjpa.208410.n2.nabble.com/file/n6525827/Adresse_.java
Adresse_.java 
http://openjpa.208410.n2.nabble.com/file/n6525827/UnameHist_.java
UnameHist_.java 
http://openjpa.208410.n2.nabble.com/file/n6525827/UnternehmenUrv_.java
UnternehmenUrv_.java 

Georg

--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6525827.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

[Pinaki Poddar <pp...@apache.org>]

Hi,

   Please post UnternehmenUrv_ and other such *_ classes. I know where is
the 1 = 1 clause is introduced but can not figure out why exactly.
  
   Nits:
   1.    Join<UnternehmenUrv, Adresse> adresseJoin = untRoot.join(
                UnternehmenUrv_.adressen.getName(), JoinType.INNER); 

       A simpler (and slightly more effective) form would be to get rid of
.getName(). Actually by calling getName(), the code is loosing type-safety
and OpenJPA is working twice to restore it !

     Join<UnternehmenUrv, Adresse> adresseJoin = untRoot.join(
                UnternehmenUrv_.adressen, JoinType.INNER); 

   2. cq.where(cb.and(predicates.toArray(new Predicate[0]))); 

      A better form would be (would save JVM to instantiate an extra array
unnecessarily)

      cq.where(cb.and(predicates.toArray(new
Predicate[predicates.size()]))); 


-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6525633.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.