|
I have a query that correctly finds one record that matches the query, but the value that is returned is null. Running the same query in sqlplus returns shows the expected record. By removing the composite key and making dataId be the key the problem goes away, however, I like to understand why. Any explanation would be appreciated.
BTW, I have rewritten the method using criteria and hibernate is unable to resolve the owningSystem regardless of whether I use the name or column. With the compoiste key removed that works fine too.
Hibernate version: 3.1
Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.gxs.iefe.alias">
<class
name="AliasTable"
table="IEFE.ALIAS_TABLE"
>
<meta attribute="sync-DAO">false</meta>
<composite-id name="id" class="AliasTablePK">
<key-property
name="owningSystem"
column="OWNING_SYSTEM"
type="string"
/>
<key-property
name="owningAccount"
column="OWNING_ACCOUNT"
type="string"
/>
<key-property
name="owningUserId"
column="OWNING_USER_ID"
type="string"
/>
<key-property
name="tableName"
column="TABLE_NAME"
type="string"
/>
</composite-id>
<property
name="updatedTime"
column="UPDATED_TIME"
type="date"
not-null="false"
length="7"
/>
<property
name="updatedAccount"
column="UPDATED_ACCOUNT"
type="string"
not-null="false"
length="8"
/>
<property
name="updatedUserId"
column="UPDATED_USER_ID"
type="string"
not-null="false"
length="8"
/>
<property
name="dataId"
column="DATA_ID"
type="long"
not-null="false"
length="22"
/>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
private AliasTable findAliasTableRecord(
IeAddress theOwner,
String theTable,
Session theSession
) throws Exception
{
if ( null == theOwner )
{
throw new Exception( "theOwner is null!" );
}
Trace.trace( TraceLevel.DEBUG, this, "findAliasTableRecord",
"{ AliasTable: " + theTable + " owner: " +
theOwner.getSystem() + "." +
theOwner.getAccount() + "." +
theOwner.getUserId() );
Query q = null;
AliasTable anAliasTable = null;
// The system is always present, however, the account and user ID may not
// be present for global and organizational tables.
if ( null != theOwner.getAccount() && ! "".equals( theOwner.getAccount() ))
{
if ( null != theOwner.getUserId() && ! "".equals( theOwner.getUserId() ) )
{
q = theSession.createQuery(
"from AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT = :ac and OWNING_USER_ID = :user and TABLE_NAME = :tbl" );
q.setString( "sys", theOwner.getSystem() );
q.setString( "ac", theOwner.getAccount() );
q.setString( "user", theOwner.getUserId() );
q.setString( "tbl", theTable );
}
else
{
q = theSession.createQuery(
"from AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT = :ac and OWNING_ACCOUNT is null and TABLE_NAME = :tbl" );
q.setString( "sys", theOwner.getSystem() );
q.setString( "ac", theOwner.getAccount() );
q.setString( "tbl", theTable );
}
}
else
{
q = theSession.createQuery(
"from AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT is null and OWNING_USER_ID is null and TABLE_NAME = :tbl" );
q.setString( "sys", theOwner.getSystem() );
q.setString( "tbl", theTable );
}
Trace.trace( TraceLevel.DEBUG, this, "findAliasTableRecord", q.toString() );
// Retrieve the data
List anAliasTableList = q.list();
if ( null == anAliasTableList || 0 == anAliasTableList.size() )
{
throw new Exception(
"AliasTable: " + theTable + " owner: " +
theOwner.getSystem() + "." +
theOwner.getAccount() + "." +
theOwner.getUserId() +
" was not found!" );
}
Trace.trace( TraceLevel.DEBUG, this, "findAliasTableRecord",
"AliasTableList.size: " + anAliasTableList.size() +
" AliasTableList: " + anAliasTableList.toString() );
if ( 1 < anAliasTableList.size() )
{
throw new Exception(
"Found more than 1 match for AliasTable: " + theTable + " owner: " +
theOwner.getSystem() + "." +
theOwner.getAccount() + "." +
theOwner.getUserId() );
}
anAliasTable = (AliasTable)anAliasTableList.get( 0 );
Trace.trace( TraceLevel.DEBUG, this, "findAliasTableRecord",
"} AliasTable: " + theTable + " owner: " +
theOwner.getSystem() + "." +
theOwner.getAccount() + "." +
theOwner.getUserId() +
" was" + ( null == anAliasTable ? " not " : " " ) + "found." );
return anAliasTable;
}
private AliasTable findAliasTableRecord3(
IeAddress theOwner,
String theTable,
Session theSession
) throws Exception
{
if ( null == theOwner )
{
throw new Exception( "theOwner is null!" );
}
Trace.trace( TraceLevel.DEBUG, this, "findAliasTableRecord",
"{ AliasTable: " + theTable + " owner: " +
theOwner.getSystem() + "." +
theOwner.getAccount() + "." +
theOwner.getUserId() );
Criteria aCriteria = theSession.createCriteria( AliasTable.class );
AliasTable anAliasTable = null;
// The system is always present, however, the account and user ID may not
// be present for global and organizational tables.
if ( null != theOwner.getAccount() && ! "".equals( theOwner.getAccount() ))
{
if ( null != theOwner.getUserId() && ! "".equals( theOwner.getUserId() ) )
{
aCriteria.add( Restrictions.eq( "owningSystem", theOwner.getSystem() ) );
aCriteria.add( Restrictions.eq( "owningAccount", theOwner.getAccount() ) );
aCriteria.add( Restrictions.eq( "owningUserId", theOwner.getUserId() ) );
aCriteria.add( Restrictions.eq( "tableName", theTable ) );
}
else
{
aCriteria.add( Restrictions.eq( "owningSystem", theOwner.getSystem() ) );
aCriteria.add( Restrictions.eq( "owningAccount", theOwner.getAccount() ) );
aCriteria.add( Restrictions.isNull( "owningUserId" ) );
aCriteria.add( Restrictions.eq( "tableName", theTable ) );
}
}
else
{
//aCriteria.add( Restrictions.eq( "OWNING_SYSTEM", theOwner.getSystem() ) );
aCriteria.add( Restrictions.eq( "owningSystem", theOwner.getSystem() ) );
aCriteria.add( Restrictions.isNull( "owningAccount" ) );
aCriteria.add( Restrictions.isNull( "owningUserId" ) );
aCriteria.add( Restrictions.eq( "tableName", theTable ) );
}
Trace.trace( TraceLevel.DEBUG, this, "findAliasTableRecord",
aCriteria.toString() );
// Retrieve the data
List anAliasTableList = aCriteria.list();
if ( null == anAliasTableList || 0 == anAliasTableList.size() )
{
throw new Exception(
"AliasTable: " + theTable + " owner: " +
theOwner.getSystem() + "." +
theOwner.getAccount() + "." +
theOwner.getUserId() +
" was not found!" );
}
Trace.trace( TraceLevel.DEBUG, this, "findAliasTableRecord",
"AliasTableList.size: " + anAliasTableList.size() +
" AliasTableList: " + anAliasTableList.toString() );
if ( 1 < anAliasTableList.size() )
{
throw new Exception(
"Found more than 1 match for AliasTable: " + theTable + " owner: " +
theOwner.getSystem() + "." +
theOwner.getAccount() + "." +
theOwner.getUserId() );
}
anAliasTable = (AliasTable)anAliasTableList.get( 0 );
Trace.trace( TraceLevel.DEBUG, this, "findAliasTableRecord",
"} AliasTable: " + theTable + " owner: " +
theOwner.getSystem() + "." +
theOwner.getAccount() + "." +
theOwner.getUserId() +
" was" + ( null == anAliasTable ? " not " : " " ) + "found." );
return anAliasTable;
}
Full stack trace of any exception that occurs:
My code generates one because one object is returned but that object is null.
Name and version of the database you are using: Oracle 9
The generated SQL (show_sql=true):
select
aliastable0_.OWNING_SYSTEM as OWNING1_0_,
aliastable0_.OWNING_ACCOUNT as OWNING2_0_,
aliastable0_.OWNING_USER_ID as OWNING3_0_,
aliastable0_.TABLE_NAME as TABLE4_0_,
aliastable0_.UPDATED_TIME as UPDATED5_0_,
aliastable0_.UPDATED_ACCOUNT as UPDATED6_0_,
aliastable0_.UPDATED_USER_ID as UPDATED7_0_,
aliastable0_.DATA_ID as DATA8_0_
from
IEFE.ALIAS_TABLE aliastable0_
where
OWNING_SYSTEM=?
and (
OWNING_ACCOUNT is null
)
and (
OWNING_USER_ID is null
)
and TABLE_NAME=?
Debug level Hibernate log excerpt:
20060929 08:00:26,814 Thread-0 throwQueryException() : no errors
20060929 08:00:26,824 Thread-0 HQL: from com.gxs.iefe.alias.AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT is null and OWNING_USER_ID is null and TABLE_NAME = :tbl
20060929 08:00:26,824 Thread-0 SQL: select aliastable0_.OWNING_SYSTEM as OWNING1_0_, aliastable0_.OWNING_ACCOUNT as OWNING2_0_, aliastable0_.OWNING_USER_ID as OWNING3_0_, aliastable0_.TABLE_NAME as TABLE4_0_, aliastable0_.UPDATED_TIME as UPDATED5_0_, aliastable0_.UPDATED_ACCOUNT as UPDATED6_0_, aliastable0_.UPDATED_USER_ID as UPDATED7_0_, aliastable0_.DATA_ID as DATA8_0_ from IEFE.ALIAS_TABLE aliastable0_ where OWNING_SYSTEM=? and (OWNING_ACCOUNT is null) and (OWNING_USER_ID is null) and TABLE_NAME=?
20060929 08:00:26,824 Thread-0 throwQueryException() : no errors
20060929 08:00:26,854 Thread-0 HQL param location recognition took 20 mills (from AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT is null and OWNING_USER_ID is null and TABLE_NAME = :tbl)
20060929 08:00:26,884 Thread-0 (10) com.gxs.iefe.alias.Alias:findAliasTableRecord QueryImpl(from AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT is null and OWNING_USER_ID is null and TABLE_NAME = :tbl)
20060929 08:00:26,884 Thread-0 located HQL query plan in cache (from AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT is null and OWNING_USER_ID is null and TABLE_NAME = :tbl)
20060929 08:00:26,884 Thread-0 find: from AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT is null and OWNING_USER_ID is null and TABLE_NAME = :tbl
20060929 08:00:26,884 Thread-0 named parameters: {sys=D47, tbl=G48}
20060929 08:00:26,884 Thread-0 about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
20060929 08:00:26,884 Thread-0
select
aliastable0_.OWNING_SYSTEM as OWNING1_0_,
aliastable0_.OWNING_ACCOUNT as OWNING2_0_,
aliastable0_.OWNING_USER_ID as OWNING3_0_,
aliastable0_.TABLE_NAME as TABLE4_0_,
aliastable0_.UPDATED_TIME as UPDATED5_0_,
aliastable0_.UPDATED_ACCOUNT as UPDATED6_0_,
aliastable0_.UPDATED_USER_ID as UPDATED7_0_,
aliastable0_.DATA_ID as DATA8_0_
from
IEFE.ALIAS_TABLE aliastable0_
where
OWNING_SYSTEM=?
and (
OWNING_ACCOUNT is null
)
and (
OWNING_USER_ID is null
)
and TABLE_NAME=?
Hibernate:
select
aliastable0_.OWNING_SYSTEM as OWNING1_0_,
aliastable0_.OWNING_ACCOUNT as OWNING2_0_,
aliastable0_.OWNING_USER_ID as OWNING3_0_,
aliastable0_.TABLE_NAME as TABLE4_0_,
aliastable0_.UPDATED_TIME as UPDATED5_0_,
aliastable0_.UPDATED_ACCOUNT as UPDATED6_0_,
aliastable0_.UPDATED_USER_ID as UPDATED7_0_,
aliastable0_.DATA_ID as DATA8_0_
from
IEFE.ALIAS_TABLE aliastable0_
where
OWNING_SYSTEM=?
and (
OWNING_ACCOUNT is null
)
and (
OWNING_USER_ID is null
)
and TABLE_NAME=?
20060929 08:00:26,884 Thread-0 preparing statement
20060929 08:00:26,894 Thread-0 bindNamedParameters() D47 -> sys [1]
20060929 08:00:26,894 Thread-0 bindNamedParameters() G48 -> tbl [2]
20060929 08:00:27,234 Thread-0 about to open ResultSet (open ResultSets: 0, globally: 0)
20060929 08:00:27,234 Thread-0 processing result set
20060929 08:00:27,234 Thread-0 result set row: 0
20060929 08:00:27,234 Thread-0 result row: null
20060929 08:00:27,335 Thread-0 done processing result set (1 rows)
20060929 08:00:27,335 Thread-0 about to close ResultSet (open ResultSets: 1, globally: 1)
20060929 08:00:27,335 Thread-0 about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
20060929 08:00:27,335 Thread-0 closing statement
20060929 08:00:27,425 Thread-0 total objects hydrated: 0
20060929 08:00:27,425 Thread-0 initializing non-lazy collections
20060929 08:00:27,425 Thread-0 located HQL query plan in cache (from AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT is null and OWNING_USER_ID is null and TABLE_NAME = :tbl)
20060929 08:00:27,425 Thread-0 find: from AliasTable where OWNING_SYSTEM = :sys and OWNING_ACCOUNT is null and OWNING_USER_ID is null and TABLE_NAME = :tbl
20060929 08:00:27,425 Thread-0 named parameters: {sys=D47, tbl=G48}
20060929 08:00:27,425 Thread-0 about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
20060929 08:00:27,435 Thread-0
select
aliastable0_.OWNING_SYSTEM as OWNING1_0_,
aliastable0_.OWNING_ACCOUNT as OWNING2_0_,
aliastable0_.OWNING_USER_ID as OWNING3_0_,
aliastable0_.TABLE_NAME as TABLE4_0_,
aliastable0_.UPDATED_TIME as UPDATED5_0_,
aliastable0_.UPDATED_ACCOUNT as UPDATED6_0_,
aliastable0_.UPDATED_USER_ID as UPDATED7_0_,
aliastable0_.DATA_ID as DATA8_0_
from
IEFE.ALIAS_TABLE aliastable0_
where
OWNING_SYSTEM=?
and (
OWNING_ACCOUNT is null
)
and (
OWNING_USER_ID is null
)
and TABLE_NAME=?
Problems with Session and transaction handling? No
|
Login
FAQ
Search