Native SQL query that returns joined-subclass

petronius · **Joined:** Thu Feb 24, 2005 11:20 am **Posts:** 2

Hi,

I would appreciate all the help in regards to an issue I have when trying to execute a native SQL query that returns an instance of a joined-subclass mapping.

Here's the details:
1. Using Hibernate 2.1.8
2. The mappings
<class name="common.model.client.Organization"
table="organization"
mutable="false">

<id name="organizationId" type="int" column="org_id">
<generator class="assigned"/>
</id>

<version name="version" column="version" type="int"/>

<property name="globalClientId" column="global_client_id" type="integer"/>
<property name="active" column="is_active" type="yes_no" not-null="true"/>
<property name="shortName" column="org_short_name" type="string" not-null="true"/>
<property name="longName" column="org_long_name" type="string" />
<map name="aliases" table="org_alias" lazy="true" outer-join="false">
<key column="org_id"/>
<index column="system_code" type="string" />
<one-to-many class="common.model.client.OrgAlias" />
</map>


<joined-subclass name="common.model.client.Client" table="client">
<key column="org_id"/>
<property name="clientTypeCode" column="client_type_code" type="string" not-null="true"/>
<property name="rule144A" column="is_rule_144a" type="yes_no" not-null="true"/>
<property name="gsccNettingMember" column="is_gscc_netting_member" type="yes_no" not-null="true"/>
<property name="liquidClassCode" column="liquid_class_code" type="string" />
</joined-subclass>
</class>

3. The query:

select cl.org_id as {cl.organizationId},
cl.client_type_code as {cl.clientTypeCode},
cl.is_rule_144a as {cl.rule144A},
cl.is_gscc_netting_member as {cl.gsccNettingMember},
cl.liquid_class_code as {cl.liquidClassCode},
org.global_client_id as {cl.globalClientId},
org.is_active as {cl.active},
org.org_short_name as {cl.shortName},
org.org_long_name as {cl.longName}
from adaptor as adaptor join client_alias_profile profile on adaptor.system_code = profile.adaptor_system_code
join org_alias alias on profile.client_representation_code = alias.system_code
join client as cl on alias.org_id = cl.org_id
join organization as org on org.org_id = cl.org_id
where alias.alias_code = :adaptorAlias

4. The code:

sessionFactory = config.buildSessionFactory();
session = sessionFactory.openSession();
Client client = (Client) session.createSQLQuery(clientQuery, "cl", Client.class ).setString("adaptorAlias", "MyAlias").list().get(0);

5. The Hibernate generated query:
[junit] Hibernate: select cl.org_id as org_id0_, cl.client_type_code as client_t2_0_, cl.is_rule_144a as is_rule_3_0_, cl.is_gscc_netting_member as is_gscc_4_0_, cl.liquid_class_code as liquid_c5_0_, org.global_client_id as global_c3_0_, org.is_active as is_active0_, org.org_short_name as org_shor5_0_, org.org_long_name as org_long6_0_ from adaptor as adaptor join client_alias_profile profile on adaptor.system_code = profile.adaptor_system_code join org_alias alias on profile.client_representation_code = alias.system_code join client as cl on alias.org_id = cl.org_id join organization as org on org.org_id = cl.org_id where alias.alias_code = ?

6. The stack trace:

[junit] 09:14:30,964[ModalContext] WARN util.JDBCExceptionReporter.logExceptions():57 - SQL Error: 0, SQLState: S0022
[junit] 09:14:30,964[ModalContext] ERROR util.JDBCExceptionReporter.logExceptions():58 - S0022: Invalid column name 'client_t2_31_0_'.
[junit] net.sf.hibernate.exception.SQLGrammarException: error performing findBySQL
[junit] at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:58)
[junit] at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
[junit] at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131)
[junit] at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3870)
[junit] at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
[junit] at mappings.TestQueries.testGetAdaptorClientWithNativeSQL(TestQueries.java:112)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[junit] at java.lang.reflect.Method.invoke(Method.java:324)
[junit] at junit.framework.TestCase.runTest(TestCase.java:154)
[junit] at junit.framework.TestCase.runBare(TestCase.java:127)
[junit] at junit.framework.TestResult$1.protect(TestResult.java:106)
[junit] at junit.framework.TestResult.runProtected(TestResult.java:124)
[junit] at junit.framework.TestResult.run(TestResult.java:109)
[junit] at junit.framework.TestCase.run(TestCase.java:118)
[junit] at junit.framework.TestSuite.runTest(TestSuite.java:208)
[junit] at junit.framework.TestSuite.run(TestSuite.java:203)
[junit] at junit.framework.TestSuite.runTest(TestSuite.java:208)
[junit] at junit.framework.TestSuite.run(TestSuite.java:203)
[junit] at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:289)
[junit] at org.apache.tools.ant.taskdefs.optional.junit.JUnitTask.executeInVM(JUnitTask.java:1061)
[junit] at org.apache.tools.ant.taskdefs.optional.junit.JUnitTask.execute(JUnitTask.java:676)
[junit] at org.apache.tools.ant.taskdefs.optional.junit.JUnitTask.executeOrQueue(JUnitTask.java:1413)
[junit] at org.apache.tools.ant.taskdefs.optional.junit.JUnitTask.execute(JUnitTask.java:633)
[junit] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[junit] at org.apache.tools.ant.Task.perform(Task.java:364)
[junit] at org.apache.tools.ant.Target.execute(Target.java:341)
[junit] at org.apache.tools.ant.Target.performTasks(Target.java:369)
[junit] at org.apache.tools.ant.Project.executeTarget(Project.java:1214)
[junit] at org.apache.tools.ant.Project.executeTargets(Project.java:1062)
[junit] at org.eclipse.ant.internal.core.ant.InternalAntRunner.run(InternalAntRunner.java:633)
[junit] at org.eclipse.ant.internal.core.ant.InternalAntRunner.run(InternalAntRunner.java:412)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[junit] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[junit] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[junit] at java.lang.reflect.Method.invoke(Method.java:324)
[junit] at org.eclipse.ant.core.AntRunner.run(AntRunner.java:350)
[junit] at org.eclipse.ant.internal.ui.launchConfigurations.AntLaunchDelegate.runInSameVM(AntLaunchDelegate.java:200)
[junit] at org.eclipse.ant.internal.ui.launchConfigurations.AntLaunchDelegate.launch(AntLaunchDelegate.java:163)
[junit] at org.eclipse.debug.internal.core.LaunchConfiguration.launch(LaunchConfiguration.java:569)
[junit] at org.eclipse.debug.internal.core.LaunchConfiguration.launch(LaunchConfiguration.java:150)
[junit] at org.eclipse.debug.internal.ui.DebugUIPlugin.buildAndLaunch(DebugUIPlugin.java:792)
[junit] at org.eclipse.debug.internal.ui.DebugUIPlugin$5.run(DebugUIPlugin.java:865)
[junit] at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:101)
[junit] Caused by: java.sql.SQLException: S0022: Invalid column name 'client_t2_31_0_'.
[junit] at com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:493)
[junit] at com.sybase.jdbc2.tds.TdsResultSet.findColumnByLabel(TdsResultSet.java:461)
[junit] at com.sybase.jdbc2.jdbc.SybResultSet.findColumn(SybResultSet.java:505)
[junit] at com.sybase.jdbc2.jdbc.SybResultSet.getString(SybResultSet.java:318)
[junit] at net.sf.hibernate.type.StringType.get(StringType.java:18)
[junit] at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
[junit] at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
[junit] at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:67)
[junit] at net.sf.hibernate.loader.Loader.hydrate(Loader.java:690)
[junit] at net.sf.hibernate.loader.Loader.loadFromResultSet(Loader.java:631)
[junit] at net.sf.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:590)
[junit] at net.sf.hibernate.loader.Loader.getRow(Loader.java:505)
[junit] at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:218)
[junit] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:285)
[junit] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
[junit] at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
[junit] at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
[junit] at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
[junit] at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3867)
[junit] ... 41 more

7. IMHO:

Hibernate generates a different alias naming scheme for the native SQL in this case. Just to compare, here's the generated query for loading a Client:

[junit] Hibernate: select client0_.org_id as org_id1_, client0_.client_type_code as client_t2_26_1_, client0_.is_rule_144a as is_rule_3_26_1_, client0_.is_gscc_netting_member as is_gscc_4_26_1_, client0_.liquid_class_code as liquid_c5_26_1_, client0__1_.version as version25_1_, client0__1_.global_client_id as global_c3_25_1_, client0__1_.is_active as is_active25_1_, client0__1_.org_short_name as org_shor5_25_1_, client0__1_.org_long_name as org_long6_25_1_, aliases1_.org_id as org_id__, aliases1_.system_code as system_c2___, aliases1_.org_id as org_id0_, aliases1_.system_code as system_c2_0_, aliases1_.alias_code as alias_code0_, aliases1_.effective_date as effectiv4_0_, aliases1_.created_dt as created_dt0_, aliases1_.last_updated_dt as last_upd6_0_, aliases1_.last_updated_user as last_upd7_0_ from client client0_ inner join organization client0__1_ on client0_.org_id=client0__1_.org_id left outer join org_alias aliases1_ on client0_.org_id=aliases1_.org_id where client0_.org_id=?

In this case, client_type_code field has the right alias "client_t2_26_1", while in my native SQL the field has a different alias "client_t2_0_".

Can you please help me figure out how to solve this ?

For you info, I have tried returning a Client class as well as the Organization super-class.

Thanks for all the help !

Regards,
Eugen

mmeissn0 · **Joined:** Wed Aug 10, 2005 1:12 am **Posts:** 1

Hallo,

did somebody solve the problem?
Or can give a hint for a solution?

Markus

max · **Posted:** Wed Aug 10, 2005 3:11 am

joined-subclass has known limitations when using native sql.

try using {alias.*} if possible. That should work for joined-subclass.

In Hibernate 3.1 you can use <return-property> to allow singular column references.

twashing · **Joined:** Sun May 09, 2004 2:45 pm **Posts:** 14

I'm using straight JDBC to get the results for complex joins.

The advantage of using Hibernate as a map between a DB and your object domain disappears when I have to use specialized 1-off code to handle common SQL queries. Now, I'm not new to Hibernate, but can someone tell me the advantage that HQL gives abouve SQL?

/tim