• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2789
  • Last Modified:

[Hibernate] HQL Query not working

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
matthew016
Asked:
matthew016
  • 5
  • 5
  • 4
2 Solutions
 
objectsCommented:
looks more like sql than hql :)
0
 
Bart CremersJava ArchitectCommented:
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
 
objectsCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
matthew016Author Commented:
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
 
Bart CremersJava ArchitectCommented:
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
 
matthew016Author Commented:
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
 
objectsCommented:
what do you mappings look like?
0
 
matthew016Author Commented:
I have put some alisas like Booking b,

now it seems to work,

Let me check
0
 
Bart CremersJava ArchitectCommented:
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
 
objectsCommented:
count(*) should be fine
0
 
matthew016Author Commented:
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
 
matthew016Author Commented:
*now = not
0
 
objectsCommented:
> 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
 
Bart CremersJava ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now