Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem Named Queries In Hibernate

 
seshu Palamanti
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
seshu Palamanti
Ranch Hand
Posts: 63
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 53
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Ashimashi Kabashi
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 84
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
smile face came in the way


no space between : and param
 
aruna sydu
Ranch Hand
Posts: 98
MyEclipse IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jaikiran Pai
Marshal
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Vineela Gampa
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic