Native SQL calling

Shino · **Posted:** Fri Sep 12, 2003 2:23 pm

All,

Our team is working on using Hibernate but using the native SQL calls as opposed to HQL so it is easier on our DBA...

Here is my question.

We have a sample query in jdbc...

Code:

select e.emp_name, d.dept_name
from employee e, department d
where e.eid = ?
and e.dept_id = d.dept_id

Going by the 2 examples I have seen,
This is what I'm seeing it should be translated too right now.

Code:

select {employee}.emp_name as {employee.empName}, 
{department}.dept_name as (department.deptName}
from employee {employee}, department {department}
where {employee}.eid = :eid
and {employee}.dept_id = {department}.dept_id

1) is this correct?
2) can this be simplified any or are all of the mappings needed even with the mapping we have done in hibernate.cfg.xml
3) does Hibernate translate this straight to SQL or does it actually go from this to HQL then to SQL?
4) would this work:

Code:

select {employee}.emp_name, {department}.dept_name
from employee {employee}, department {department}
where {employee}.eid = :eid
and {employee}.dept_id = {department}.dept_id

5) Can we use anything as the table alias or is it bound to the object name by reflection?
6) the examples I have seen of this are both on the roadmap, are there other examples available

sorry if I beat this to death.

max · **Posted:** Sat Sep 13, 2003 4:12 am

Shino wrote:

All,

Our team is working on using Hibernate but using the native SQL calls as opposed to HQL so it is easier on our DBA...
[/qoute]

Why not just show him the log of executed query statements ? ;)
(just kidding ...but then again, it would make your life easier ;)

Quote:

Code:

select {employee}.emp_name as {employee.empName}, 
{department}.dept_name as (department.deptName}
from employee {employee}, department {department}
where {employee}.eid = :eid
and {employee}.dept_id = {department}.dept_id

1) is this correct?

It looks fine and dandy by me.

Quote:

2) can this be simplified any or are all of the mappings needed even with the mapping we have done in hibernate.cfg.xml

eh - simplified ? The only thing you have done is replacing short names with longer curlier names ... if you don't like the length then just do:

Code:

select {e}.emp_name as {e.empName}, 
{d}.dept_name as {d.deptName}
from employee {e}, department {d}
where {e}.eid = :eid
and {e}.dept_id = {d}.dept_id

And remember that the {e} and {d}'s meaning is defined by your createSQLQuery call!

String aliases[] = new String[] {"e", "d"};
Class classes[] = new Class[] { Employee.class, Department.class };
createSQLQuery(sqlstr, aliases, classes);

Quote:

3) does Hibernate translate this straight to SQL or does it actually go from this to HQL then to SQL?
[/qoute]

It goes almost straight to SQL (it IS SQL!) - the only thing hibernate does is a simple string replace from the alias names and the property names to their mapped table names and property names! It is REALLY REALLY simple - nothing fancy ;)

You could even write it without the alias names - but then you would have to know which column name Hibernate would expect for eg. dept_name (it would probably be something like "dept_name0_"

Quote:

4) would this work:

Code:

select {employee}.emp_name, {department}.dept_name
from employee {employee}, department {department}
where {employee}.eid = :eid
and {employee}.dept_id = {department}.dept_id

Probably not since you are not using the expected column names by hibernate. (note: the above is actually the ideal way to have createSQLQuery(), but due to the need to limit the columnnames and making them unique Hibernate does some processing of these to ensure the uniquenes - createSQLQuery() has to live by these rules)

Quote:

5) Can we use anything as the table alias or is it bound to the object name by reflection?

There are NO reflection used in this!
You can use anything you want as table aliases - we just provide you with a HQL version of the "table" name to make it easier to write the sql....

Quote:

6) the examples I have seen of this are both on the roadmap, are there other examples available

As always - look in the unit test and search for createSQLQuery

Quote:

sorry if I beat this to death.

I'm actually really surprised that people don't get the simplicity and no-magic there is about createSQLQuery() - most people think we are doing something really hardcore processing or manipulation of the string put into createSQLQuery....we DON'T!, ok ? ;)

We just do a simple string replace, nothing more, nothing less!

gavin · **Posted:** Sat Sep 13, 2003 5:39 am

Hey Max, I forget:

why exactly did we decide that the aliases need to be passed into createSQLQuery()?

Why can't SQLQueryLoader scan the input SQL string looking for strings of the form "{employee", {department"?

max · **Posted:** Sat Sep 13, 2003 5:44 am

gavin wrote:

Hey Max, I forget:

why exactly did we decide that the aliases need to be passed into createSQLQuery()?

Why can't SQLQueryLoader scan the input SQL string looking for strings of the form "{employee", {department"?

well - it was made this way coz' it was how it was first suggested ;)

But thinking about it i can see at least a couple of points:

1. it's explict

2. what if you got 2 employee's in the query? What distinguishes the one from the other ?

/max

max · **Posted:** Sat Sep 13, 2003 5:45 am

...shortness - I like to use {e} instead of {employee}

gavin · **Posted:** Sat Sep 13, 2003 5:46 am

(1) Sure, but I don't see any room for ambiguity here

(2) thats fine! you still have {employee1}, {employee2} or whatever you like....

gavin · **Posted:** Sat Sep 13, 2003 5:47 am

OH! now i remember ... LOL!

We have to be able to figure out the type! hah.

Me stupid.

Shino · **Posted:** Mon Sep 15, 2003 8:12 am

Hey guys,

Thanks for the info. You answered all of my questions the way I thought you would, except for one...

max wrote:

4) would this work:

Code:

select {e}.emp_name, {d}.dept_name
from employee {e}, department {d}
where {e}.eid = :eid
and {e}.dept_id = {d}.dept_id

Probably not since you are not using the expected column names by hibernate. (note: the above is actually the ideal way to have createSQLQuery(), but due to the need to limit the columnnames and making them unique Hibernate does some processing of these to ensure the uniquenes - createSQLQuery() has to live by these rules)
[/quote]

So the "as {e.empName}" portion is required to assure that the correct column name is assigned to the correct object property... In the case that we have 2 tables that have the same column name in the same query?

max · **Posted:** Mon Sep 15, 2003 11:23 am

Shino wrote:

Hey guys,

Thanks for the info. You answered all of my questions the way I thought you would, except for one...

max wrote:

4) would this work:

Code:

select {e}.emp_name, {d}.dept_name
from employee {e}, department {d}
where {e}.eid = :eid
and {e}.dept_id = {d}.dept_id

Probably not since you are not using the expected column names by hibernate. (note: the above is actually the ideal way to have createSQLQuery(), but due to the need to limit the columnnames and making them unique Hibernate does some processing of these to ensure the uniquenes - createSQLQuery() has to live by these rules)

So the "as {e.empName}" portion is required to assure that the correct column name is assigned to the correct object property... In the case that we have 2 tables that have the same column name in the same query?[/quote]

eh - what ? How can that be problem ? You would have to distinguish them in raw sql too! Please provide an example where that is a problem....

/max

Shino · **Posted:** Mon Sep 15, 2003 11:34 am

max wrote:

Shino wrote:

Code:

select {e}.emp_name, {d}.dept_name
from employee {e}, department {d}
where {e}.eid = :eid
and {e}.dept_id = {d}.dept_id

So the "as {e.empName}" portion is required to assure that the correct column name is assigned to the correct object property... In the case that we have 2 tables that have the same column name in the same query?

eh - what ? How can that be problem ? You would have to distinguish them in raw sql too! Please provide an example where that is a problem....

/max

It's not a problem... =)
just basically making sure that is the reasoning.

The main question is ... Is it always required to put the "as {alias.property}" after each column in the select statement ... even if no two columns being selected have the same column name?

max · **Posted:** Mon Sep 15, 2003 11:51 am

Shino wrote:

max wrote:

Shino wrote:

Code:

select {e}.emp_name, {d}.dept_name
from employee {e}, department {d}
where {e}.eid = :eid
and {e}.dept_id = {d}.dept_id

So the "as {e.empName}" portion is required to assure that the correct column name is assigned to the correct object property... In the case that we have 2 tables that have the same column name in the same query?

eh - what ? How can that be problem ? You would have to distinguish them in raw sql too! Please provide an example where that is a problem....

/max

It's not a problem... =)
just basically making sure that is the reasoning.

The main question is ... Is it always required to put the "as {alias.property}" after each column in the select statement ... even if no two columns being selected have the same column name?

The non-precise answer is: "Yes, it is required."

The precise answer is: No - it is not *always* required - but i can't see why you would not do it ;)

It is ALWAYS required that the columnnames returned by the resultset matches what Hibernate want it to be - and to get that the easist way is to use "as {alias.property}"

Shino · **Posted:** Mon Sep 15, 2003 1:54 pm

My main reasoning for not wanting to do this is because a valid sql query for this example does not need it, like the first jdbc example I posted.

It's just a seperation of concerns issue. Our DBA may be writing some of the more complicated queries and there's not really a reason for him to know anything about our objects on the java side to write the query. So we will just edit them when they come to us.

max · **Posted:** Mon Sep 15, 2003 2:11 pm

Shino wrote:

My main reasoning for not wanting to do this is because a valid sql query for this example does not need it, like the first jdbc example I posted.

It's just a seperation of concerns issue. Our DBA may be writing some of the more complicated queries and there's not really a reason for him to know anything about our objects on the java side to write the query. So we will just edit them when they come to us.

I understand this - but unfortunately is almost impossible to do - even if hibernate's resultset reader could handle it - the problems arise as soon as you get name "collisions" when e.g. joining stuff - then it is waaay easier to do (and easy to understand) as it is currently implemented.

/max

Shino · **Posted:** Mon Sep 15, 2003 2:15 pm

Sounds good.

Thank for the info Max, it is much appreciated!

melquiades · **Posted:** Thu Nov 06, 2003 7:47 pm

The thread makes sense so far.

What about joined subtypes? That is, suppose I have the query:

Code:

SELECT {subtype.*}
  FROM SUBTYPE {subtype}
       INNER JOIN SUPERTYPE {super} ON {subtype}.id = {super}.id

How do I tell hibernate to substitute {super} with the name of the joined supertype?

It looks like I can cheat as follows:

Code:

SELECT {subtype.*}
  FROM SUBTYPE {subtype}
       INNER JOIN SUPERTYPE {subtype}_1_ ON {subtype}.id = {subtype}_1_.id

...but now I'm just presuming that Hibernate will always suffix the joined supertype in the same way -- yuck!

Does this question make sense? Is there an answer yet?