[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

[Hibernate] HQL Query not working

Posted on 2007-07-25
14
Medium Priority
?
2,629 Views
Last Modified: 2013-11-24
Hi ,

My HQL Query is not working,

any help ?

Thank you,


query =
"SELECT COUNT(*) FROM " +
"(" +
"SELECT userId " +
"FROM Booking " +
"WHERE tripId = '"+tripIdOutward+"' " +
"AND status LIKE 'Active' " +
"AND userId IN (" +
"SELECT userId " +
"FROM Booking " +
"WHERE tripId = '"+tripIdReturn+"' " +
"AND status = 'Active' " +
")" +
")";
results = session.find(query);



[25/07/07 11:11:27:880 CEST] 5f1d4c26 SystemErr R net.sf.hibernate.QueryException: in expected: SELECT [SELECT COUNT(*) FROM (SELECT USERID FROM TB_ATB_BOOKING WHERE TRIPID='449' AND STATUS = 'Active' AND USERID IN (SELECT USERID FROM TB_ATB_BOOKING WHERE TRIPID = '451' AND STATUS = 'Active' ))]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1554)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1525)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1513)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1505)
[...]
0
Comment
Question by:matthew016
  • 5
  • 5
  • 4
14 Comments
 
LVL 92

Expert Comment

by:objects
ID: 19563948
looks more like sql than hql :)
0
 
LVL 13

Expert Comment

by:Bart Cremers
ID: 19563957
You're mixing HQL and native SQL here. The query you want to execute is a native query and should be executed using:

session.createSQLQuery(query).list();

to get this in HQL you should start from

select count(distinct booking.userID) from Booking booking where ...

0
 
LVL 92

Assisted Solution

by:objects
objects earned 996 total points
ID: 19563974
first subselect also appears unnecessary

SELECT COUNT(*) FROM TB_ATB_BOOKING WHERE TRIPID='449' AND STATUS = 'Active' AND USERID IN (SELECT USERID FROM TB_ATB_BOOKING WHERE TRIPID = '451' AND STATUS = 'Active' )
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Author Comment

by:matthew016
ID: 19563990
Hi,

I have always used results = session.find(query); and it works for all the other methods.

I chanegd to this :

            query =
                  "SELECT COUNT(distinct bookId) FROM " +
                  "(" +
                        "SELECT userId " +
                        "FROM Booking " +
                        "WHERE tripId = '"+tripIdOutward+"' " +
                        "AND status LIKE 'Active' " +
                        "AND userId IN (" +
                              "SELECT userId " +
                              "FROM Booking " +
                              "WHERE tripId = '"+tripIdReturn+"' " +
                              "AND status LIKE 'Active' " +
                        ")" +
                  ")";
            results = session.find(query);



But I have the same error
0
 
LVL 13

Accepted Solution

by:
Bart Cremers earned 1004 total points
ID: 19564022
try

                        SELECT count(userId) " +
                        "FROM Booking " +
                        "WHERE tripId = '"+tripIdOutward+"' " +
                        "AND status LIKE 'Active' " +
                        "AND userId IN (" +
                              "SELECT userId " +
                              "FROM Booking " +
                              "WHERE tripId = '"+tripIdReturn+"' " +
                              "AND status LIKE 'Active' " +
                        ")" +
0
 
LVL 9

Author Comment

by:matthew016
ID: 19564220
The error has changed now

[25/07/07 12:56:20:250 CEST] 78f4cc26 SystemErr     R net.sf.hibernate.QueryException: undefined alias: userId [SELECT count(userId) FROM com.fortis.atb.bl.dto.Booking WHERE tripId = '449' AND status LIKE 'Active' AND userId IN (SELECT userId FROM com.fortis.atb.bl.dto.Booking WHERE tripId = '451' AND status LIKE 'Active' )]
      at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:103)
0
 
LVL 92

Expert Comment

by:objects
ID: 19564239
what do you mappings look like?
0
 
LVL 9

Author Comment

by:matthew016
ID: 19564249
I have put some alisas like Booking b,

now it seems to work,

Let me check
0
 
LVL 13

Expert Comment

by:Bart Cremers
ID: 19564253
Another shot (need to use shotgun tactics without seeing some code):

"SELECT count(b) " +
"FROM Booking b " +
"WHERE tripId = '"+tripIdOutward+"' " +
"AND status LIKE 'Active' " +
"AND userId IN (" +
    "SELECT userId " +
    "FROM Booking " +
    "WHERE tripId = '"+tripIdReturn+"' " +
    "AND status LIKE 'Active' " +
")";
0
 
LVL 92

Expert Comment

by:objects
ID: 19564260
count(*) should be fine
0
 
LVL 9

Author Comment

by:matthew016
ID: 19564301
YESSSSSSSS  by removing the upper select like u posted it works ! (just had to add the aliases)

I will close this question,

Could you just tell me, if you know, why it works like this and now with the useless select (because I tested it in oracle and they both works in oracle)
0
 
LVL 9

Author Comment

by:matthew016
ID: 19564310
*now = not
0
 
LVL 92

Expert Comment

by:objects
ID: 19564319
> Could you just tell me, if you know, why it works like this and now with the
> useless select (because I tested it in oracle and they both works in oracle)

because its valid in oracle sql, but not in hql.
They are not the same thing.
0
 
LVL 13

Expert Comment

by:Bart Cremers
ID: 19564338
As objects mentioned: HQL is not Oracle SQL.

Hibernate will use the HQL (which is more object oriented) and convert it using the configured dialect and the mappings to SQL (table oriented).
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
A solution for Fortify Path Manipulation.
This video teaches viewers about errors in exception handling.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question