createSQLQuery returns invalid column name

maddogv6 · **Joined:** Mon Jun 28, 2004 5:09 pm **Posts:** 2

I have the following simple code:
String sql = "SELECT {unit}.PICK_CODE AS {unit.pickCode} FROM UNIT {unit}";
List resorts = session.createSQLQuery(sql, "unit", Unit.class).list();

My UNIT table has 3 columns:
ID
PICK_CODE
FLOOR_NAME

My Unit.hbm.xml is correct as well as the Unit.class, because when I run the above SQL with {unit.*}, it works just fine.

But I only want to return the one column. If I run the above code, I get an "Invalid column name" error returned. When I turned on the JDBC trace, I noticed that Hibernate was trying to perform findColumn(ID0) to find the ID column of table UNIT.

After looking through the docs and forums, I discovered that "...if you list each property explicitly, you must include all properties of the class and its subclasses!". This didn't make much sense because HQL's find() will return an Object[] in this situation, so why can't createSQLQuery()? Also, why can't the method just catch the JDBC's findColumn() exception and stick in a NULL into Unit.class remainder fields.

Now you may ask, why not just use HQL to do this? Well, because what I'm really trying to do is this SQL statement: SELECT DISTINCT SUBSTR(U.PICK_CODE,1,2) FROM UNIT U. Please the Hibernate team can explain this, I'd appreciate it!

emmanuel · **Posted:** Tue Jun 29, 2004 4:39 am

createSQLQuery is here to let you do SQL but fill in an object and attach it to the sesion.
If you want proejction and SQL, then une the underlying connection.

maddogv6 · **Joined:** Mon Jun 28, 2004 5:09 pm **Posts:** 2

yes i know that createSQLQuery() is supposed to fill in an object, but what object?. From my experiment, it seems to be filling the Unit.class object, rather than just return a String object (or Object class casted from a String), just as HQL's find() method does.

Also, I didn't understand your second sentence. Please elaborate.

emmanuel · **Posted:** Tue Jul 06, 2004 11:17 am

Should say, fill a persistent object (mapped one)
projection means select a.id, a.name instead of select a.
If you absolutly need to do projection using a native SQL query you can use session.connection()

snow · **Posted:** Thu Dec 23, 2004 7:14 am

I understand your explanation, but in the book "Hibernate in action" on page 284 there is a code-example

Code:

String sql = "select u.USER_ID as {uzer.id},"
+ " u.FIRSTNAME as {uzer.firstname},"
+ " u.LASTNAME as {uzer.lastname} from USERS u";
List results =
session.createSQLQuery(sql, "uzer", User.class).list();

which is a projection. The table USER in this example is part of the Caveat Emptor application, and this table contains many other columns. Doesn't that means that it should work? Can you explain the meaning of this code, maybe I misunderstood it.
Thank you![/code]

michael · **Posted:** Thu Dec 23, 2004 9:24 am

This is not a projection, it is a query for a full User object.

emmanuel · **Posted:** Thu Dec 23, 2004 1:46 pm

all user proeprties are mandatory