Using native SQL

DangerMouse · **Joined:** Fri Feb 17, 2006 1:28 pm **Posts:** 24

Hello all,

I think I need to create a native SQL query that is something like this:

Code:

SELECT Customer.* FROM Order, Customer WHERE Order.client_id = 1 AND Order.customer_id = Customer.customer_id GROUP BY (Customer.customer_id)

I have tried to use session.createSQLQuery() but nothing will execute it and give me back the reqired result. Has anyone else had this problem.

Really what im looking for is a way to create querys over multiple tables what returns either a specific result or any array of results.

Any ideas?

jt_1000 · **Posted:** Tue Feb 21, 2006 4:48 pm

setup aliases for your tables and use the
q.addEntity(alias,class)

You'll also need to put your Cutomer.* in brackets like {ct.*} where ct is the alias....

So your query, with ct and od as the aliases would be:

Code:

SELECT {ct.*} FROM Order od, Customer ct WHERE od.client_id = 1 AND od.customer_id = ct.customer_id GROUP BY (ct.customer_id)

Code:

q.addEntity("ct",Customer.class);
q.addJoin("od","ct.orders"); //ct.orders refers to your collection variable in your customer class.

Even though it is supposed to be straight sql, hibernate needs to parse the sql string and put it in a suitable SQL string for DB, and determine which classes are being mapped for the results. So the SQL may fail at hibernate's layer and not the DB.

lastly, check out:
http://www.hibernate.org/hib_docs/v3/reference/en/pdf/hibernate_reference.pdf
page 161 for example from docs.

DangerMouse · **Joined:** Fri Feb 17, 2006 1:28 pm **Posts:** 24

Hey JT,

Thanks for the reply. I tried it but it didnt work. I think it may have something to do with the addJoin. Im not to sure what that is. Can you explain that part a little more?

Thanks.

max · **Posted:** Wed Feb 22, 2006 8:49 am

show me how you actually call this and what exception or result you get.

DangerMouse · **Joined:** Fri Feb 17, 2006 1:28 pm **Posts:** 24

Really what I want to do is select all the Customers which relate to a client. They are related through the Orders table.

So usually the query would simply be

Code:

SELECT Customer.* FROM Order, Customer where Order.client_id = 100 AND Order.customer_id = Customer.customer_id

In the Example that JT gave, i understood most of it. The problem was with the join.

When he uses ct.orders, i dont understand where the ct.orders value is or what its meant to be.

This is what i tried.

Code:

List customers = session.createSQLQuery("SELECT {ct.*} FROM Order odr, Customer ct WHERE odr.client_id = 1 AND ord.customer_id = ct.customer_id")
.addEntity("ct", Customer.class)
.addJoin("odr", "ct.orders")
.list();

If you could tell me what ct.orders is and what it might relate to or how this problem would be fixed, id really appreciate it

Thanks.

max · **Posted:** Wed Feb 22, 2006 9:18 am

i don't know why you are trying to do a addJoin...why not just do addEntity ?

and again, you must tell what the code actually result in...an exception or some weird result ? I don't want to play guessing games.

DangerMouse · **Joined:** Fri Feb 17, 2006 1:28 pm **Posts:** 24

I assumed that you had to specify the join parameter, but if it can be done without the join, then all the better. These are the errors that im getting when i run the following command:

Code:

List customers = session.createSQLQuery("SELECT {ct.*} FROM DirectOrder ddr, Customer ct WHERE ddr.client_id = 1 AND ddr.customer_id = ct.customer_id")
.addEntity("ct", Customer.class)
.addEntity("ddr", DirectOrder.class)
.list();.

Errors:

Code:

16:07:28,082 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: S0022
16:07:28,082 ERROR [JDBCExceptionReporter] Column 'direct1_7_1_' not found.

Any ideas?

Thanks

max · **Posted:** Wed Feb 22, 2006 12:15 pm

you are only returning one entity - so only add one entity.

jt_1000 · **Posted:** Wed Feb 22, 2006 12:49 pm

Max - there is very little doc on the addJoin(...) - almost none in the API guide, and the reference has this:

Quote:

The addJoin() method may be used to load associations to other entities and collections.

Can you explain it further - ie, when you'd want to use it?

max · **Posted:** Wed Feb 22, 2006 1:29 pm

see the unit tests.

e.g. org.hibernate.test.sql.GeneralTest in cvs.

it basically allows you to do the same thing as what the "fetch" keyword does in hql..

it is used when you want to return entities and at the same time fetch/join in assocations to other entities and collections....much more efficient.

DangerMouse · **Joined:** Fri Feb 17, 2006 1:28 pm **Posts:** 24

Thanks for that Max. It worked a treat. And thanks JT too. This problem was really annoying me.

Thanks.