I must be missing something on Creating this SQLQuery.

Shino · **Posted:** Mon Sep 15, 2003 4:04 pm

Ok, so I am trying to make a simple sql query. Hence the reason I think this is a really stupid question.

For some reason, I'm getting an invalid column name SQLException. Only problem is that all of the columns are correct and I can see data coming back in the log. Not sure what is going on. If I do a "select {e}.*" it works fine.

Yes first name is spelled firstnme. All columns are spelled correctly and mapped correctly in the hbm.xml files. I'm wondering if it may have something to do with the driver.

Here is the query...

Query q = session.createSQLQuery( "select {e}.empno as {e.empno}, {e}.firstnme as {e.firstnme}, {e}.lastname as {e.lastname} from employee {e}", "e", Employee.class );

Here is what shows up in the logs...

DEBUG: opened session
DEBUG: SQL query: select {e}.empno as {e.empno}, {e}.firstnme as {e.firstnme}, {e}.lastname as {e.lastname} from employee {e}
DEBUG: flushing session
DEBUG: Flushing entities and processing referenced collections
DEBUG: Processing unreferenced collections
DEBUG: Scheduling collection removes/(re)creates/updates
DEBUG: Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
DEBUG: Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
DEBUG: Dont need to execute flush
DEBUG: about to open: 0 open PreparedStatements, 0 open ResultSets
DEBUG: total checked-out connections: 6
DEBUG: opening new JDBC connection
DEBUG: created connection to: jdbc:db2://localhost/sample, Isolation Level: 2
DEBUG: prepared statement get: select e.empno as EMPNO0_, e.firstnme as FIRSTNME0_, e.lastname as LASTNAME0_ from employee e
Hibernate: select e.empno as EMPNO0_, e.firstnme as FIRSTNME0_, e.lastname as LASTNAME0_ from employee e
DEBUG: preparing statement: select e.empno as EMPNO0_, e.firstnme as FIRSTNME0_, e.lastname as LASTNAME0_ from employee e
DEBUG: processing result set
DEBUG: returning '000010' as column: EMPNO0_
DEBUG: result row: 000010
DEBUG: Initializing object from ResultSet: 000010
DEBUG: Hydrating entity: com.cla.elms.persist.dao.employee.Employee#000010
DEBUG: returning 'CHRISTINE' as column: FIRSTNME0_
DEBUG: SQL Exception
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0611E Invalid column name. SQLSTATE=S0022
at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.rsException(SQLExceptionGenerator.java:619)
at COM.ibm.db2.jdbc.net.DB2ResultSet.findColumn(DB2ResultSet.java:1832)
at COM.ibm.db2.jdbc.net.DB2ResultSet.getString(DB2ResultSet.java:1462)
at net.sf.hibernate.type.StringType.get(StringType.java:14)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:60)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:51)
at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
at net.sf.hibernate.loader.Loader.hydrate(Loader.java:494)
at net.sf.hibernate.loader.Loader.loadFromResultSet(Loader.java:448)
at net.sf.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:417)
at net.sf.hibernate.loader.Loader.getRow(Loader.java:356)
at net.sf.hibernate.loader.Loader.doResultSet(Loader.java:190)
at net.sf.hibernate.loader.Loader.doFind(Loader.java:113)
at net.sf.hibernate.loader.Loader.find(Loader.java:736)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:69)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3544)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:47)

Any ideas?

max · **Posted:** Mon Sep 15, 2003 5:01 pm

Try to set a breakpoint/insert a system.out.println(columname); at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:60) and tell me what columnname it fails on.

Can the listed SQL execute normally in e.g. DbVisualizer (or just db2 console (or whatever name their tool has) ?

max · **Posted:** Mon Sep 15, 2003 5:05 pm

...and do you have all fields selected ?

Have you remembered the id ?
...version number ?
...timestamp ?
...possible subclasses properties ?
...?

max · **Posted:** Mon Sep 15, 2003 5:06 pm

if select {e.*} works - then you should be able to see what columns you have forgotten to include via the sql it generates....

Shino · **Posted:** Tue Sep 16, 2003 9:03 am

Max...

It never get's to the breakpoint in that method...

Here's where I am right now...

I'm using DB2's sample database.
The actual table is:

DEPARTMENT
DEPTNO
DEPTNAME
MGRNO
ADMRDEPT
LOCATION

In BatchImpl.getResultSet() I can see the query which turns out to be...

select d.deptno as DEPTNO0_, d.deptname as DEPTNAME0_ from department d

The query works in the DB2 Command line processor
The result set is coming back correctly and being handed off to hibernate.

I obviously do not have all fields selected... because I don't need them all and it would be a waste, but after digging into the hybernate code, it seems that is why it is dying. Can I just call a query and partially fill the persistant object? I don't need all the Department information, I'm just filling a select box with the deptno and deptname.

Shino · **Posted:** Wed Sep 17, 2003 9:33 am

Please guys... I still need some info on this topic.

If I only want to select a couple of columns from a table, do I have to use HQL for that, or does Hibernate not let you do that at all?

Would I have to make a "subobject"... maybe a second persistant Department object and mapping that was more of a subset of the original Department that only had what I needed in it then pass that in?

Please advise.

Thanx,

max · **Posted:** Wed Sep 17, 2003 1:00 pm

[quote="Shino"]Please guys... I still need some info on this topic.

If I only want to select a couple of columns from a table, do I have to use HQL for that, or does Hibernate not let you do that at all?
[/qoute]

Have you read the docs ? ;)

createSQLQuery currently only supports returning full hibernate objects.

If you just want a couple of columns from a table/object (let's say name and sex from a Person that got 15 other attribtes) you got the following options:

1. HQL: "select p.name, p.sex from Person as p" , result returned as a list of Object[]

2. HQL: "select new NameAndSex(p.name, p.sex) from Person as p", result returned as a list of NameAndSex objects - requires that there exist a constructor that matches the type of p.name and p.sex

3. SQL via a raw connection: "select p.name, p.sex from Person as p". Result returned as a resultset - note this is just standard JDBC.

4. If applicable look at the LightWeight design pattern http://hibernate.org/41.html. (This is probably the best match for your "subobject" reference)

So, you see - there is alot of possibilities for doing this - and each of them have strengths and weakneesses ;)

/max

Shino · **Posted:** Wed Sep 17, 2003 4:29 pm

Max,

Thanks for the info.

I did look at the documentation, but not deep enough. I actually only looked at 7.3.6 which talks a little about native sql queries but mentions nothing about being required to bring everything back. I thought that was all you guys put in there. After looking again, I noticed that you guys had another section on it which explained what you did. I apologize.

After meeting with other key members of my team, we have decided that we will make custom persistant objects to handle the queries. Thank you for the link you gave me. The link actually led me to a blank page, but I looked it up in the doc.

I would like to run something by you.

If I had something like...

Code:

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

would I be able to do something like ... make one object to handle the query.

Code:

public class DepartmentEmployees {

private int empId;
private String empName
private int deptId
private String deptName

}

with the appropriate mapping, if it is possible to map one object to 2 different tables.

Or am I making 2 different "sub"classes... one for dept and one for emp?

From the structure, I am assuming that I have to make 2 different subclasses, but I thought I would ask and see.

Also, I realize that the SQLQueries are new to Hibernate with this version. Are there plans in the future to handle what I was asking about... handling queries that don't return all columns of a table?

Thanks again for the info,
Chris