Hibernate version:
2.1.4
Code between sessionFactory.openSession() and session.close():
Automagically handled by Spring's Hibernate integration components.
Name and version of the database you are using:
MS SQL Server 2000.
Dear Hibernate Team,
First off, I think Hibernate is fantastic. I'm presently trying to use it in a new app, integrated with Spring and Struts.
I've done alot of reading in this forum over the past few days, and I think I've come to the conclusion that Hibernate will not do what I want it to do. I just want to make sure that my conclusion is correct. If one of you could take a look at my situation and let me know definitively, I would greatly appreciate it.
I'm using MS SQLServer 2K for development, tho the Production database will eventually be in DB2. I have an N:N association between two entities, WORKER and EMPLOYER, realized by the association table WRKREMPLYR. WORKER's PK is a composite of COSSN and CID, EMPLOYER's PK is a UUID string surrogate auto-generated by Hibernate, and WRKREMPLYR's PK is a composite of the PKs of the two tables it associates. The abbreviated DDL for these follows.
Code:
CREATE TABLE WORKER (
COSSN char (9) ,
CID char (2) ,
LASTMOD_TS datetime,
etc...
) ON PRIMARY
GO
ALTER TABLE WORKER WITH NOCHECK ADD
CONSTRAINT PK_WORKER PRIMARY KEY CLUSTERED
(
COSSN,
CID
) ON PRIMARY
GO
CREATE TABLE EMPLOYER (
EMPLOYER_ID char (32) ,
etc...
) ON PRIMARY
GO
ALTER TABLE EMPLOYER WITH NOCHECK ADD
CONSTRAINT PK_EMPLOYER
PRIMARY KEY CLUSTERED EMPLOYER_ID
ON PRIMARY
GO
CREATE TABLE WRKREMPLYR (
COSSN char (9) ,
CID char (2) ,
EMPLOYER_ID char (32) ,
PAYFRQCY char (2)
) ON PRIMARY
GO
ALTER TABLE WRKREMPLYR ADD
CONSTRAINT PK_WRKREMPLYR PRIMARY KEY CLUSTERED
(
COSSN,
CID,
EMPLOYER_ID
) ON PRIMARY
GO
ALTER TABLE WRKREMPLYR ADD
CONSTRAINT FK_WRKREMPLYR_EMPLOYER FOREIGN KEY
(
EMPLOYER_ID
) REFERENCES EMPLOYER (
EMPLOYER_ID
),
CONSTRAINT FK_WRKREMPLYR_WORKER FOREIGN KEY
(
COSSN,
CID
) REFERENCES WORKER (
COSSN,
CID
)
GO
In this app, I'm interested in EMPLOYERs only inasmuch as they're associated with WORKERs. IOW, I consider a single worker at a time, and I want to have a collection of that worker's associated employers close at hand. Therefore, I've mapped the Worker object as shown below.
Mapping documents:Code:
<hibernate-mapping
auto-import="true"
default-access="property"
default-cascade="all"
package="gov.ssa.ssimwv.bo"
>
<class
name="Worker"
table="WORKER"
batch-size="1"
dynamic-insert="true"
dynamic-update="true"
lazy="false"
mutable="true"
optimistic-lock="version"
polymorphism="implicit"
>
<cache usage="nonstrict-read-write"/>
<composite-id
access="property"
name="id"
class="WorkerID"
>
<key-property name="cossn" type="string" column="COSSN" />
<key-property name="cid" type="string" column="CID" />
</composite-id>
<timestamp
name="lastModTs"
column="LASTMOD_TS"
access="property"
unsaved-value="null"
/>
<!--
Life would be much easier if employers were a Map,
but alas, I can't make it work. *sigh*
-->
<set
name="employers"
table="WRKREMPLYR"
lazy="false"
cascade="all"
access="property"
>
<key>
<column name="COSSN" />
<column name="CID" />
</key>
<many-to-many class="Employer">
<column name="EMPLOYER_ID" />
</many-to-many>
</set>
<property
column="FNM"
insert="true"
name="firstName"
not-null="false"
type="string"
unique="false"
update="true"
/>
more properties...
</class>
<class
name="Employer"
table="EMPLOYER"
batch-size="1"
dynamic-insert="true"
dynamic-update="true"
lazy="false"
mutable="true"
optimistic-lock="none"
polymorphism="implicit"
>
<id
name="id"
column="EMPLOYER_ID"
access="property"
length="32"
type="string"
unsaved-value="UNSAVED"
>
<generator
class="uuid.hex"
/>
</id>
<property ...
more properties...
</class>
</hibernate-mapping>
I've tried specifying the employers collection as a <map>, declaring the Worker.java bean property as a java.lang.Map. The catch seems to be that since I want the employers' keys in the Map to be their EMPLOYER_ID values, I would have to map the collection something like this:
Code:
<map>
name="employers">
table="WRKREMPLYR">
lazy="false">
cascade="all">
access="property">
>
<key>
<column name="COSSN" />
<column name="CID" />
</key>
<index column="EMPLOYER_ID" type="string" />
<many-to-many class="Employer">
<column name="EMPLOYER_ID" />
</many-to-many>
</map>
When I map like that, Hibernate chokes on the duplicate column mapping. When I try removing the <column name="EMPLOYER_ID" /> tag from the <many-to-many> tag, Hibernate generates invalid SQL that tries to refer to a column employers0_.elt, as shown in the generated SQL section below.
The generated SQL (show_sql=true):Code:
select
worker0_.COSSN as COSSN0_,
worker0_.CID as CID0_,
worker0_.LASTMOD_TS as LASTMOD_TS0_,
[i]etc...[/i]
from dbo.WORKER worker0_
where worker0_.COSSN=? and worker0_.CID=?
select
employers0_.COSSN as COSSN__,
employers0_.CID as CID__,
[b]employers0_.elt [/b]as elt__,
employers0_.EMPLOYER_ID as EMPLOYER4___,
employer1_.EMPLOYER_ID as EMPLOYER1_0_,
[i]etc...[/i]
from dbo.WRKREMPLYR employers0_
inner join dbo.EMPLOYER employer1_
on [b]employers0_.elt[/b]=employer1_.EMPLOYER_ID
where employers0_.COSSN=? and employers0_.CID=?
Am I just totally misunderstanding the documentation on many-to-many mapping? Or is my situation simply out of the scope of Hibernate? I suppose I could write custom SQL to handle all this, but I'd much rather use Hibernate's dynamic SQL generation, if it's possible.
Thanks in advance for any assistance.
Steven
Debug level Hibernate log excerpt:net.sf.hibernate.util.JDBCExceptionReporter SQL Error: 207, SQLState: 42S22
net.sf.hibernate.util.JDBCExceptionReporter [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'elt'.
net.sf.hibernate.util.JDBCExceptionReporter SQL Error: 207, SQLState: 42S22
net.sf.hibernate.util.JDBCExceptionReporter [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'elt'.
In WorkerID.hashCode(): returning 1981057290.
net.sf.hibernate.util.JDBCExceptionReporter SQL Error: 207, SQLState: 42S22
net.sf.hibernate.util.JDBCExceptionReporter [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'elt'.
net.sf.hibernate.util.JDBCExceptionReporter SQL Error: 207, SQLState: 42S22
net.sf.hibernate.util.JDBCExceptionReporter [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'elt'.
net.sf.hibernate.util.JDBCExceptionReporter could not initialize collection: [gov.ssa.ssimwv.bo.Worker.employers#gov.ssa.ssimwv.bo.WorkerID@7614890a]
net.sf.hibernate.util.JDBCExceptionReporter TRAS0014I: The following exception was logged java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'elt'.
Full stack trace of any exception that occurs:JDBCException E net.sf.hibernate.util.JDBCExceptionReporter TRAS0014I: The following exception was logged java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'elt'.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at java.lang.reflect.Method.invoke(Native Method)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:68)
at org.logicalcobwebs.cglib.proxy.Proxy$ProxyImpl$$EnhancerByCGLIB$$43775196.executeQuery(<generated>)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:990)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:965)
at net.sf.hibernate.loader.CollectionLoader.initialize(CollectionLoader.java:83)
at net.sf.hibernate.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:284)
at net.sf.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:3268)
at net.sf.hibernate.collection.PersistentCollection.forceInitialization(PersistentCollection.java:336)
at net.sf.hibernate.impl.SessionImpl.initializeNonLazyCollections(SessionImpl.java:3123)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
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.load(SessionImpl.java:1920)
at org.springframework.orm.hibernate.HibernateTemplate$3.doInHibernate(HibernateTemplate.java:198)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:150)
at org.springframework.orm.hibernate.HibernateTemplate.load(HibernateTemplate.java:196)
at gov.ssa.ssimwv.service.dao.hibernate.WorkerHibernateDAO.find(WorkerHibernateDAO.java:34)
at gov.ssa.ssimwv.service.spring.WorkerServiceSpringImpl.find(WorkerServiceSpringImpl.java:65)
at java.lang.reflect.Method.invoke(Native Method)
at org.springframework.aop.framework.AopProxyUtils.invokeJoinpointUsingReflection(AopProxyUtils.java:59)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:149)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:118)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:191)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:138)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:148)
at $Proxy4.find(Unknown Source)
at gov.ssa.ssimwv.action.SubmitSSNAction.execute(SubmitSSNAction.java:51)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
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:948)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:530)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:176)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:201)
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:610)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:431)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)
---- Begin backtrace for Nested Throwables
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'elt'.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at java.lang.reflect.Method.invoke(Native Method)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:68)
at org.logicalcobwebs.cglib.proxy.Proxy$ProxyImpl$$EnhancerByCGLIB$$43775196.executeQuery(<generated>)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:990)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:965)
at net.sf.hibernate.loader.CollectionLoader.initialize(CollectionLoader.java:83)
at net.sf.hibernate.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:284)
at net.sf.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:3268)
at net.sf.hibernate.collection.PersistentCollection.forceInitialization(PersistentCollection.java:336)
at net.sf.hibernate.impl.SessionImpl.initializeNonLazyCollections(SessionImpl.java:3123)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
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.load(SessionImpl.java:1920)
at org.springframework.orm.hibernate.HibernateTemplate$3.doInHibernate(HibernateTemplate.java:198)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:150)
at org.springframework.orm.hibernate.HibernateTemplate.load(HibernateTemplate.java:196)
at gov.ssa.ssimwv.service.dao.hibernate.WorkerHibernateDAO.find(WorkerHibernateDAO.java:34)
at gov.ssa.ssimwv.service.spring.WorkerServiceSpringImpl.find(WorkerServiceSpringImpl.java:65)
at java.lang.reflect.Method.invoke(Native Method)
at org.springframework.aop.framework.AopProxyUtils.invokeJoinpointUsingReflection(AopProxyUtils.java:59)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:149)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:118)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:191)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:138)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:148)
at $Proxy4.find(Unknown Source)
at gov.ssa.ssimwv.action.SubmitSSNAction.execute(SubmitSSNAction.java:51)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
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:948)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:530)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:176)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:201)
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:610)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:431)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:593)
[/code]