createSQLQuery troubles

jwboring · **Posted:** Mon Sep 08, 2003 4:31 pm

Any documentation on session.createSQLQuery? I'm finding it beyond my capabilities today.

Gets the rs fine but blows up in NullableType.nullSafeGet, name = BID_ALT_SKEY0_. Why is it appending 0_ to the end? Am I suppose to use SQL or HQL in the 1st parm?

jeff.boring@siemens.com

DRIVER CODE:

Code:

sess = sf.openSession();
xac = sess.beginTransaction();
Long id = new Long(1);
sql = "Select BID_ALT_SKEY, BID_SKEY, BID_ALT_ID from TGBPDBA2.BP_M_BID_ALT aaa where BID_ALT_SKEY = :id " ;
Query q = sess.createSQLQuery( sql, "aaa", BpMBidAlt.class );  
q.setParameter("id", id );
List myList = q.list();// blows up here

STACK TRACE:

Code:

16:11:25,823 DEBUG SessionImpl:3413 - SQL query: Select BID_ALT_SKEY, BID_SKEY, BID_ALT_ID from TGBPDBA2.BP_M_BID_ALT aaa where BID_ALT_SKEY = :id 
16:11:25,873 DEBUG SessionImpl:2043 - flushing session
16:11:25,883 DEBUG SessionImpl:2149 - Flushing entities and processing referenced collections
16:11:25,883 DEBUG SessionImpl:2433 - Processing unreferenced collections
16:11:25,883 DEBUG SessionImpl:2444 - Scheduling collection removes/(re)creates/updates
16:11:25,893 DEBUG SessionImpl:2055 - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
16:11:25,893 DEBUG SessionImpl:2060 - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
16:11:25,903 DEBUG SessionImpl:1603 - Dont need to execute flush
16:11:25,903 DEBUG BatcherImpl:176 - about to open: 0 open PreparedStatements, 0 open ResultSets
16:11:25,913 DEBUG SessionFactoryImpl:393 - prepared statement get: Select BID_ALT_SKEY, BID_SKEY, BID_ALT_ID from TGBPDBA2.BP_M_BID_ALT aaa where BID_ALT_SKEY = ? 
Hibernate: Select BID_ALT_SKEY, BID_SKEY, BID_ALT_ID from TGBPDBA2.BP_M_BID_ALT aaa where BID_ALT_SKEY = ? 
16:11:25,923 DEBUG PreparedStatementCache:78 - preparing statement: Select BID_ALT_SKEY, BID_SKEY, BID_ALT_ID from TGBPDBA2.BP_M_BID_ALT aaa where BID_ALT_SKEY = ? 
16:11:26,013 DEBUG LongType:44 - binding '1' to parameter: 1
16:11:26,264 DEBUG Loader:179 - processing result set
16:12:41,490 DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.SQLException: Invalid column name
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:210)
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:273)
   at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:4383)
   at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:667)
   at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1432)
   at net.sf.hibernate.type.LongType.get(LongType.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.loader.Loader.getKeyFromResultSet(Loader.java:291)
   at net.sf.hibernate.loader.Loader.doResultSet(Loader.java:185)
   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:3433)
   at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:47)
   at bidAlt.performace.Driver.main(Driver.java:111)

gavin · **Posted:** Mon Sep 08, 2003 6:22 pm

You have to use the {aaa.xxx} or {aaa.*} escapes to get Hibernate to generate the required aliases for you. This stuff is in the documented examples....

In your case, probably this will do:

Code:

sql = "Select {aaa.*} from TGBPDBA2.BP_M_BID_ALT aaa where BID_ALT_SKEY = :id " ; 

For more fine-grained control, you can specify each property individually.

jwboring · **Posted:** Tue Sep 09, 2003 12:28 pm

I need to learn how to specify each property individually and found this in the test package.

Code:

public void testFindBySQLDiscriminatedSameSession() throws Exception {
      Session session = sessions.openSession();
      session.delete("from A");
      A savedA = new A();
      session.save(savedA);

      B savedB = new B();
      session.save(savedB);
      session.flush();

      Query query = session.createSQLQuery("select id as {a.id}, clazz as {a.class}, name as {a.name}, count_ as {a.count} from {a} s", "a", A.class);
      List list = query.list();

      assertNotNull(list);
      assertEquals(2, list.size());

      A a1 = (A) list.get(0);
      A a2 = (A) list.get(1);

      assertTrue((a1 instanceof A && a2 instanceof B) || (a1 instanceof B && a2 instanceof A));
      assertFalse(a1 instanceof B && a2 instanceof B);

      if (a1 instanceof B) {
         assertSame(a1, savedB);
         assertSame(a2, savedA);
      } 
      else {
         assertSame(a2, savedB);
         assertSame(a1, savedA);
      }

      session.close();
}

I can't tell what is what. "a" is an alias but it is also a table.

- Is "from {a} s" referring to the alias a or the table name A? And what the heck is s" here?
- I assume in "id as {a.id}, clazz as {a.class}, name as {a.name}, count_ as {a.count}" means "colname as {alias.colname}" ?

Sorry to be so stupid but I have other good qualities :-)

Jeff

max · **Posted:** Tue Sep 09, 2003 3:48 pm

{a} alone is the table name for A.class

{a.someproperty} is the alias name used by hibernate to get that property

s is just a name - it could probably be left out in this small example.