How to debug HQL syntax error

[Pete Helgren, modificado 11 Anos atrás.]

I have a service builder method that runs a query like this:

ClassGeolocationPersistenceImpl persistence = (ClassGeolocationPersistenceImpl) classGeolocationPersistence;
Session session = persistence.openSession();
try {
Query query = session.createQuery(sqlString);
locations = query.list();
.....

The SQL syntax I am using in the string is:

SELECT * FROM ClassGeolocation WHERE ((HC_Latitude * 0.0174532925) >= 0.4827596525676906 AND (HC_Latitude*0.0174532925) <= 0.5455440462509528) AND ((HC_Longitude*0.0174532925) >= -1.7556479311452713 AND (HC_Longitude*0.0174532925) <= -1.6835370398002323) AND acos(sin(0.5141518494093217) * sin((HC_Latitude*0.0174532925)) + cos(0.5141518494093217) * cos((HC_Latitude*0.0174532925)) * cos((HC_Longitude*0.0174532925) - -1.7195924854727518)) <= 0.031392196841631075;

I can run this SQL statement successfully using SQuirreL so I know the the SQL syntax is OK but I don't know if the HQL syntax is OK and the stack trace isn't nice enough to give me an offset or any indication of where the problem occurs or what the resultant HQL that is being processed looks like.

How does one debug the HQL? Is there a way to trace the statement. The error thrown is:
ERROR [JDBCExceptionReporter:101] Incorrect syntax near ')'.

Since there are multiple ")"'s in the statement the error isn't helpful.

Suggestions? (I looked for something like an HQL "lint" editor but didn't find one, or any tools to debug the statement). FWIW I am running LR 6.0.6 Glassfish bundle on Windows 7 developing in MyEclipse IDE

[Pete Helgren, modificado 10 Anos atrás.]

I was never able to figure out what Hibernate didn't like about that syntax. Totally baffled.... and I was never able to figure out how to see the HQL that was being submitted to Hibernate for processing so I gave up with using the the createQuery method and went instead with the createSQLQuery approach. That worked, BUT the side effect is that query.list(); returns an array of objects for each record returned so it is a collection of arrays of objects. That means that you will need to iterate like so:

while (i.hasNext())
...
Object[] myObject = (Object[]) i.next();

then walk though the array and cast each object into the correct type and use the setters on your SB pojo to populate the object. It is tedious, but it works.

Not sure what about the query syntax Hibernate was cranky about....

[David H Nebinger, modificado 10 Anos atrás.]

Pete Helgren:

That means that you will need to iterate like so:

while (i.hasNext())
...
Object[] myObject = (Object[]) i.next();

then walk though the array and cast each object into the correct type and use the setters on your SB pojo to populate the object. It is tedious, but it works.

No, what it means is you should be passing in a ResultTransformer instance to handle the marshalling.

[Pete Helgren, modificado 10 Anos atrás.]

Sigh .... so much to learn about LR...

Thanks for the pointer! I wish I had known that ahead of time (30 columns of mapping is a hassle)....

[David H Nebinger, modificado 10 Anos atrás.]

ResultTransformer will still do the same 30 column transforms, but it's a cleaner way of handling the marshalling...