wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes Problem Named Queries In Hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Problem Named Queries In Hibernate" Watch "Problem Named Queries In Hibernate" New topic
Author

Problem Named Queries In Hibernate

seshu Palamanti
Ranch Hand

Joined: Jul 03, 2004
Posts: 59
Hi Every one,
I have a simple named query like this from ProductGroup pb where pb.firstProductCode IN (?)in my mapping file.

<query name="getSecondProducts"><![CDATA[
from ProductGroup pb where pb.firstProductCode IN (?)]]>
</query>

I get the named query like this.

final Query query = session.getNamedQuery("getSecondProducts")
.setString(0, sb.toString());
Where sb is a string buffer that i convert to a string to added to the query. The problem looks like Sb.toString seems not added to the query and does not return any values nor does i get an exception. But when i directly pass an SQL String Query Like this

final Query query=session.createSQLQuery("select * from pb2productgroup where firstlienproductcode in ('STRING1','STRING2') ");

The query works and i get some result.
Can any one please let me know if this is the right way to use Named Queries with "IN".
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17258
    
    6

try calling the setParameter method instaed of the setString method. We use the setParameter and everything works fine.

Of course, if could be something completely different that is causing your problems, but that is the first thing that I noticed.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
seshu Palamanti
Ranch Hand

Joined: Jul 03, 2004
Posts: 59
Hi Mark I have tried that but i still get 0 results. I am assuming you cant set a parameter to the name query some where in the middle
like from ******** IN (?) Let me know what you think ?
Thanks for your help
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17258
    
    6

Where the "?" is in the query makes no difference when calling setParameter.

Here is an exact example that we use. Now we call Stored Procedures, but that doesn't matter it is still a Statement.



And the code to use it




Mark
Thillakan Saba
Ranch Hand

Joined: May 15, 2007
Posts: 53
You can formulate the HSQL in following way,





PS : This is for those who are going to come across same problem !!!
[ November 10, 2008: Message edited by: Paul Sturrock ]

SCBCD, SCJP & MCP
HowToAskQuestions
Ashimashi Kabashi
Ranch Hand

Joined: Sep 11, 2006
Posts: 84
I believe in named queries you use ':column_name' instead of '?'

so instead of
<query name="getSecondProducts"><![CDATA[
from ProductGroup pb where pb.firstProductCode IN (?)]]>
</query>

try
<query name="getSecondProducts"><![CDATA[
from ProductGroup pb where pb.firstProductCode IN ( aram)]]>
</query>
Ashimashi Kabashi
Ranch Hand

Joined: Sep 11, 2006
Posts: 84
smile face came in the way


no space between : and param
aruna sydu
Ranch Hand

Joined: Jan 21, 2008
Posts: 98

HI seshu,

You can try and modify like this.

mapping file:




:coloumname as Ashimashi already pointed

namedquery :



The Hibernate query that will be generated is like:



2 ? as there are two parameters instead of 1 ?

Try it and let me know if it works.


Attitude Determines Altitude

SCJP4/SCWCD4/SCDJWS5/Preparing SCEA Part I
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10240
    
168

HI seshu,

You can try and modify like this.
...
Try it and let me know if it works.



Just a FYI to the posters who may not have noticed - This is a 2 year old thread


[My Blog] [JavaRanch Journal]
Vineela Gampa
Greenhorn

Joined: Aug 30, 2008
Posts: 1
Hi Iam trying to use named queries for the contained objects.

say Products has many Items.

SO how would i do named queries in this case?

<sql-query name="QUERY_GET_PRODUCTS_BY_NAME">
<return alias="product" class="Product"/>
<return-join alias="item" property="product.item"/>
SELECT {product.*}
FROM PRODUCTS product
JOIN ITEMS item
ON product.ITEM_ID = item.ITEM_ID
WHERE product.name LIKE :productName
</sql-query>

I tried to use it this way but iam geting an erro message

2009-04-03 11:38:34,828 WARN [main] (JDBCExceptionReporter.java:77) - SQL Error: 17006, SQLState: null
2009-04-03 11:38:34,828 ERROR [main] (JDBCExceptionReporter.java:78) - Invalid column name
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2216)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at eman.ism.core.hibernate.HibernateDAO.executeSQLNamedQuery(HibernateDAO.java:152)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem Named Queries In Hibernate