Dear all,
I am new to Hibernate. I am doing a project which uses Hibernate as the bridge to access database.
My problem is:
The user will give a zip code range(either 3 or 5 digits), I will have to get the total pieces of mails grouped by the 3-digit or 5-digit zip code. My problem is the data(zip code) in the database is of size 5 digits or more. So I would like to use substring(zipcode from 1 for 3) or something similar in HQL. I can get the queries in SQL, but no luck with HQL. I've even tried using native SQL, nothing helps.
Anybody helps?

My HQL kinda looks like:
totalQuery="select count(doc.id) as total, " +
"substring(doc.addressBlockZip from 1 for " + fromZip.length()+ ") as zip " +
"from DocumentVO as doc " +
"group by zip " +
"having zip between '"+fromZip+"' and '"+toZip+
"' order by zip asc";


Thanks a lot
Jessica

Can't do that. Use session.createSQLQuery()

I did try using native SQL.
I made the query very simple:
"SELECT {doc}.id" +" FROM Documents AS {doc} limit 10";/*Documents is the table in the database*/
I got following error:
java.sql.SQLException: Column 'id0_' not found.
...
I don't know why it's happening. My query could not be simpler.
Any thoughts?

Thank you

You can onyl query for hibernate mapped classes using createSQLQuery. So you cant query for the id, only for full Documents

You don't need to use a native SQL query as far as i can tell. Just use whatever SQL function your database provides for doing substrings.

Really? I thought something like substring(doc.addressBlockZip from 1 for " + fromZip.length()+ ") will not work (multiple parameters). Correct me if I am wrong ...

Ok, since galvin mentioned that I can use substring function, I tried the following query(I can execute the query sucessfully in MySQL):

totalQuery="select count(doc.id) as total, " +
"substring(doc.addressBlockZip from 1 for " + fromZip.length()+ ") as zip " +"from DocumentVO as doc " +"group by zip " +"having zip between '"+fromZip+"' and '"+toZip+"' order by zip asc";

I got the following error messages:
net.sf.hibernate.QueryException: , expected in SELECT [select count(doc.id) as total, substring(doc.addressBlockZip from 1 for 3) as zip from DocumentVO as doc group by zip having zip between '333' and '444' order by zip asc]
...

Could you point out where I did wrong?

Again, many thanks!

Jessica

Okay, what I know for sure is you can't use custom SQL functions in the SELECT part of the HQL. Only in the where part.

Something like substring(foo from 1 to 3) will not work, but substring(foo, 1, 3) will. (It can even be made to work in the select clause if you add support to the Dialect.)

Actually, scratch that - I think select clause SQL functions don't support multiple arguments yet.

I've been meaning to get back to this but lots of paid work and with the AST parser coming along (which makes this a snack to do) I have not addressed this issue (also need to deal with constants as arguments aswell).

It works! (with a Oracle DB)

Example:
Query sqlQuery = session.createQuery("select substr(quiz.a_StartDate,1,2) from E_Quiz quiz");