asked on

[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)
[...]

Mick Barry

looks more like sql than hql :)

Bart Cremers

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 ...

SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

matthew016

ASKER

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

ASKER CERTIFIED SOLUTION

Bart Cremers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

matthew016

ASKER

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)

Mick Barry

what do you mappings look like?

matthew016

ASKER

I have put some alisas like Booking b,

now it seems to work,

Let me check

Bart Cremers

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' " +
")";

Mick Barry

count(*) should be fine

matthew016

ASKER

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)

matthew016

ASKER

*now = not

Mick Barry

> 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.

Bart Cremers

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).