• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem Named Queries In Hibernate

 
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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".
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 69
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 53
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 84
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
smile face came in the way


no space between : and param
 
Ranch Hand
Posts: 98
MyEclipse IDE Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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)
 
Hey cool! They got a blimp! But I have a tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic