-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 
  Previous topic | Next topic 
Author Message
carlolf
 Post subject: How do you set parameters for IN operator?
PostPosted: Wed Mar 26, 2008 12:28 pm 
Pro
Pro

Joined: Tue Jun 12, 2007 4:13 am
Posts: 209
Location: Berlin, Germany
I have the following condition in a JPA named query:

where prj.projectNumber in (:prjNumbers)

Now, in my app I have a list of strings which are the arguments for the in operator. My problem is: if I quote the single string arguments with
Code:
"'\'"
, Hibernate produces this:
and (singleproj0_.PROJECT_NO in ('\'D000012008'\','\'P120921995\','\'P141321996'\'),

but when I do not quote then Hibernate produces this:
and (singleproj0_.PROJECT_NO in ('D000012008,P120921995,P141321996').

Neither is correct and so the query has no results.

Any suggestions how to handle this situation?

Carlo
Top
 Profile  
 
maciejziaja
 Post subject:
PostPosted: Wed Mar 26, 2008 1:25 pm 
Newbie

Joined: Thu May 17, 2007 9:49 am
Posts: 9
the easiest solution is to create string - D000012008','P120921995','P141321996 without the quotes at the beginning and the end of the string...
Top
 Profile  
 
sanne.grinovero
 Post subject:
PostPosted: Wed Mar 26, 2008 5:54 pm 
Hibernate Team
Hibernate Team

Joined: Fri Oct 05, 2007 4:47 pm
Posts: 2536
Location: Third rock from the Sun
Hi, maybe this is usefull:
Code:
s.createQuery(" ... where prj.projectNumber in (:prjNumbers)").setParameterList("prjNumbers", pks);


regards,
Sanne
Top
 Profile  
 
carlolf
 Post subject:
PostPosted: Thu Mar 27, 2008 3:11 am 
Pro
Pro

Joined: Tue Jun 12, 2007 4:13 am
Posts: 209
Location: Berlin, Germany
maciejziaja wrote:
the easiest solution is to create string - D000012008','P120921995','P141321996 without the quotes at the beginning and the end of the string...

Sorry, this does not work as expected; the resulting string Hibernate creates is (as PreparedStatemtent):
Code:
('P130032002\', \'P130112004\', \'P130292000\', \'Z117792006')
which is not what we want.

I wonder if this bad result can be qualified as a Hibernate bug?

Carlo
Top
 Profile  
 
carlolf
 Post subject: Parameter lists for IN operator
PostPosted: Thu Mar 27, 2008 3:19 am 
Pro
Pro

Joined: Tue Jun 12, 2007 4:13 am
Posts: 209
Location: Berlin, Germany
s.grinovero wrote:
Hi, maybe this is usefull:
Code:
...setParameterList("prjNumbers", pks);


Hi Sanne,

this method is indeed something I expected to use. Unfortunately it is not supported by JPA, only on native Hibernate queries (which I try to only use if I have no chance with JPA).

Fortunately in my special case, I do have the option to use a subquery which produces as result what I tried to do manually. Anyway, I can imagine to come into situations where I would need to manually build that parameter list and then could use such a method.

Carlo
Top
 Profile  
 
carlolf
 Post subject:
PostPosted: Thu Mar 27, 2008 6:37 am 
Pro
Pro

Joined: Tue Jun 12, 2007 4:13 am
Posts: 209
Location: Berlin, Germany
OOPS! Found this example in the EntityManager doc:
Code:
List names = new ArrayList();
names.add("Izi");
names.add("Fritz");
Query q = em.createQuery("select cat from DomesticCat cat where cat.name in (:namesList)");
q.setParameter("namesList", names);


Huch, I didn't know that setParameter supports lists!

Carlo
Top
 Profile  
 
maciejziaja
 Post subject:
PostPosted: Thu Mar 27, 2008 7:49 am 
Newbie

Joined: Thu May 17, 2007 9:49 am
Posts: 9
Hi Carlo
Quote:
Huch, I didn't know that setParameter supports lists!

I might be wrong but I think it will only work when you are using jpa with hibernate. Hibernate Query implementation supports that. I don't think it will work without hibernate.
And the other thing...
Quote:
('P130032002\', \'P130112004\', \'P130292000\', \'Z117792006')

Why are you using "\'"? You can set it like that and it is working "P130032002','P130112004', 'P130292000', 'Z117792006". You don't need to use \ before ', At least not in java. I am using this when I have to constrain on some particular properties from Object list.

Maciej
Top
 Profile  
 
carlolf
 Post subject:
PostPosted: Thu Mar 27, 2008 8:58 am 
Pro
Pro

Joined: Tue Jun 12, 2007 4:13 am
Posts: 209
Location: Berlin, Germany
maciejziaja wrote:
And the other thing...
Quote:
('P130032002\', \'P130112004\', \'P130292000\', \'Z117792006')

Why are you using "\'"?
Maciej

Hi Maciej,

I tried using bld.append('\'') and bld.append("'") - same results. That is: Hibernate is converting the single quote to "\'".

Carlo
Top
 Profile  
 
maciejziaja
 Post subject:
PostPosted: Thu Mar 27, 2008 9:16 am 
Newbie

Joined: Thu May 17, 2007 9:49 am
Posts: 9
carlolf wrote:
I tried using bld.append('\'') and bld.append("'") - same results. That is: Hibernate is converting the single quote to "\'".

Sorry, but you have to be wrong. If it would be like you say, it would be a major bug in hibernate, because queries like that
Code:
from Cat where name='Fritz'
would not work. You can check it in hql(http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html) or ejbql documentation.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.