These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
michael mermagen
 Post subject: Using of user-defined types as primary key
PostPosted: Thu Nov 18, 2004 9:35 am 
Newbie

Joined: Thu Nov 18, 2004 5:56 am
Posts: 3
Location: Cologne, Germany
Hi,

for my current client I have to evaluate an ORM-Tool appropriate to their requirements. Hibernate fits nearly all the requirements in a really smart way. Anyway, there is one problem I cannot solve:
My client is using DB2 User-Defined Types as the data type for
primary key columns. One of the ramifications of this is that DB2 requires explicit casts in sql statements that use these types of columns.
Unfortunately, Hibernate doesn"t generate the correct sql in these cases.

In my example for a selection I am using 2 tables with a simple association:

table VEHKIND:
VKND_OID (AUTOBOERSE.OID (user-defined type based on the underlying type BIGINT))
etc..

table VEHBODTYPE:
VBTY_OID (AUTOBOERSE.OID)
VKND_OID (AUTOBOERSE.OID (foreign key to table VEHKIND))
etc..

Hibernate's generated sql looks as follow:
select vehiclekin0_.VKND_OID as VKND_OID0_, vehiclekin0_.VKND_ID as VKND_ID0_, vehiclekin0_.VKND_LABEL as VKND_LABEL0_ from AUTOBOERSE.VEHKIND vehiclekin0_ where vehiclekin0_.VKND_OID=?

The following sql works:
select vehiclekin0_.VKND_OID as VKND_OID0_, vehiclekin0_.VKND_ID as VKND_ID0_, vehiclekin0_.VKND_LABEL as VKND_LABEL0_ from AUTOBOERSE.VEHKIND vehiclekin0_ where vehiclekin0_.VKND_OID=AUTOBOERSE.OID(CAST
(? AS BIGINT))

So what I need to know is, how I can tell Hibernate to generate the explicit cast according to the user-defined type.

I hope you can help.

If you need additional informations, let me know.

Best regards,

Michael Mermagen

Hibernate version:
2.1.6

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping schema="AUTOBOERSE" package="de.ccb.orm.model">

<class name="VehicleKind" table="VEHKIND">

<id name="oid" type="de.ccb.orm.hibernate.types.OidUserType" unsaved-value="null" >
<column name="VKND_OID" sql-type="AUTOBOERSE.OID" not-null="true"/>
<generator class="increment"/>
</id>

<property name="id">
<column name="VKND_ID" sql-type="AUTOBOERSE.ID" not-null="true"/>
</property>

<property name="label">
<column name="VKND_LABEL" not-null="true"/>
</property>

</class>

<class name="VehicleBodyType" table="VEHBODTYPE">

<id name="oid" type="de.ccb.orm.hibernate.types.OidUserType" unsaved-value="null" >
<column name="VBTY_OID" sql-type="AUTOBOERSE.OID" not-null="true"/>
<generator class="increment"/>
</id>

<property name="id">
<column name="VBTY_ID" sql-type="AUTOBOERSE.ID" not-null="true"/>
</property>

<property name="label">
<column name="VBTY_LABEL" not-null="true"/>
</property>

<property name="bit">
<column name="VBTY_BIT" not-null="true"/>
</property>

<many-to-one name="vehicleKind" class="VehicleKind" column="VKND_OID"/>

</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Session session = HibernateUtil.currentSession();

Query q = session.createQuery("from de.ccb.orm.model.VehicleBodyType as vehicleBodyType");
List l = q.list();

HibernateUtil.closeSession();

Full stack trace of any exception that occurs:
[18.11.04 14:00:16:796 CET] 4f1c4f1c SystemOut O Hibernate: select vehiclebod0_.VBTY_OID as VBTY_OID, vehiclebod0_.VBTY_ID as VBTY_ID, vehiclebod0_.VBTY_LABEL as VBTY_LABEL, vehiclebod0_.VBTY_BIT as VBTY_BIT, vehiclebod0_.VKND_OID as VKND_OID from AUTOBOERSE.VEHBODTYPE vehiclebod0_
[18.11.04 14:00:17:078 CET] 4f1c4f1c SystemOut O Hibernate: select vehiclekin0_.VKND_OID as VKND_OID0_, vehiclekin0_.VKND_ID as VKND_ID0_, vehiclekin0_.VKND_LABEL as VKND_LABEL0_ from AUTOBOERSE.VEHKIND vehiclekin0_ where vehiclekin0_.VKND_OID=?
[18.11.04 14:00:17:218 CET] 4f1c4f1c JDBCException W net.sf.hibernate.util.JDBCExceptionReporter SQL Error: -432, SQLState: 42841
[18.11.04 14:00:17:234 CET] 4f1c4f1c JDBCException E net.sf.hibernate.util.JDBCExceptionReporter [SQL0432] Eine Parametermarkierung kann nicht die benutzerdefinierte Art OID haben.
[18.11.04 14:00:17:265 CET] 4f1c4f1c JDBCException W net.sf.hibernate.util.JDBCExceptionReporter SQL Error: -432, SQLState: 42841
[18.11.04 14:00:17:265 CET] 4f1c4f1c JDBCException E net.sf.hibernate.util.JDBCExceptionReporter [SQL0432] Eine Parametermarkierung kann nicht die benutzerdefinierte Art OID haben.
[18.11.04 14:00:17:265 CET] 4f1c4f1c JDBCException E net.sf.hibernate.util.JDBCExceptionReporter could not load: [de.ccb.orm.model.VehicleKind#de.ccb.orm.model.value.Oid@75687568]
[18.11.04 14:00:17:281 CET] 4f1c4f1c JDBCException E net.sf.hibernate.util.JDBCExceptionReporter TRAS0014I: Die folgende Ausnahmebedingung wurde protokolliert: java.sql.SQLException: [SQL0432] Eine Parametermarkierung kann nicht die benutzerdefinierte Art OID haben.
at java.lang.Throwable.<init>(Throwable.java)
at java.lang.Throwable.<init>(Throwable.java)
at java.sql.SQLException.<init>(SQLException.java:52)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:388)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1088)
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:208)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1419)
at com.ibm.as400.access.AS400JDBCConnectionHandle.prepareStatement(AS400JDBCConnectionHandle.java:669)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1458)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1424)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:257)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:232)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:65)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:779)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:265)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:911)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:931)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:59)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:51)
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:419)
at net.sf.hibernate.impl.SessionImpl.doLoad(SessionImpl.java:2117)
at net.sf.hibernate.impl.SessionImpl.doLoadByClass(SessionImpl.java:1991)
at net.sf.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:1953)
at net.sf.hibernate.type.ManyToOneType.resolveIdentifier(ManyToOneType.java:69)
at net.sf.hibernate.type.EntityType.resolveIdentifier(EntityType.java:204)
at net.sf.hibernate.impl.SessionImpl.initializeEntity(SessionImpl.java:2205)
at net.sf.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:315)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:305)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at de.ccb.orm.hibernate.servlets.SearchVehicleAd.service(SearchVehicleAd.java)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:983)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:564)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:200)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:276)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:443)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java)
.
java.sql.SQLException: [SQL0432] Eine Parametermarkierung kann nicht die benutzerdefinierte Art OID haben.
at java.lang.Throwable.<init>(Throwable.java)
at java.lang.Throwable.<init>(Throwable.java)
at java.sql.SQLException.<init>(SQLException.java:52)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:388)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1088)
at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:208)
at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1419)
at com.ibm.as400.access.AS400JDBCConnectionHandle.prepareStatement(AS400JDBCConnectionHandle.java:669)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1458)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.prepareStatement(WSJdbcConnection.java:1424)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:257)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:232)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:65)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:779)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:265)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:911)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:931)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:59)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:51)
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:419)
at net.sf.hibernate.impl.SessionImpl.doLoad(SessionImpl.java:2117)
at net.sf.hibernate.impl.SessionImpl.doLoadByClass(SessionImpl.java:1991)
at net.sf.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:1953)
at net.sf.hibernate.type.ManyToOneType.resolveIdentifier(ManyToOneType.java:69)
at net.sf.hibernate.type.EntityType.resolveIdentifier(EntityType.java:204)
at net.sf.hibernate.impl.SessionImpl.initializeEntity(SessionImpl.java:2205)
at net.sf.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:315)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:305)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at de.ccb.orm.hibernate.servlets.SearchVehicleAd.service(SearchVehicleAd.java)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:983)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:564)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:200)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:276)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:443)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java)

Name and version of the database you are using:
DB2/400 V5R2 M0 + several Patches

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:
Top
 Profile  
 
fraggle
 Post subject:
PostPosted: Thu Nov 18, 2004 12:38 pm 
Newbie

Joined: Sat Nov 13, 2004 8:09 am
Posts: 14
Location: Hamburg
Why You are using a user defined type for the id? How does they look like?
Top
 Profile  
 
michael mermagen
 Post subject:
PostPosted: Fri Nov 19, 2004 5:45 am 
Newbie

Joined: Thu Nov 18, 2004 5:56 am
Posts: 3
Location: Cologne, Germany
Generally we are using a user-defined types for primary key columns for better database semantics. The user-defined type we are using is defined as follows:

CREATE TYPE AUTOBOERSE.OID AS BIGINT;

So the underlying database type is BIGINT.
Top
 Profile  
 
christian
 Post subject:
PostPosted: Fri Nov 19, 2004 5:46 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Why not use a Long on the Java side and let Hibernate do the mapping? What are "better database semantics"?

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.

Top
 Profile  
 
christian
 Post subject:
PostPosted: Fri Nov 19, 2004 5:51 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Oh, you are using a UDT in your database! I thought you mean a custom Hibernate UserType.

Hibernate currently has no support for database UDTs, we recommend to not use them for portability reasons. Of course, sometimes a custom Hibernate UserType is all you need to make it work anyway, but I'm afraid not in your case (Hibernate currently doesn't support CAST). Patches and contributions welcome.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.

Top
 Profile  
 
michael mermagen
 Post subject:
PostPosted: Fri Nov 19, 2004 8:42 am 
Newbie

Joined: Thu Nov 18, 2004 5:56 am
Posts: 3
Location: Cologne, Germany
Thank you for this information.

We are using a lot of UDT's in our database, but there is no problem, if it is not the primary key, because in HQL it is generally possible to create queries with a condition in the where-clause which refers to a UDT in the database:

Query q = session.createQuery("from de.ccb.orm.autoboerse.objects.VehicleBodyType as vehicleBodyType where vehicleBodyType.code=AUTOBOERSE.CODE('LIMO')");

So we consider to alter the primary keys of UDT's into the underlying database type and retain the other UDT's.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.