Hi,
I just took two days looking for a solution to solve this problem.
I explain first the context :
I have a mapping on InfoOpFinanciere (quite simple one) with a association bag on another object InfoFluxFinancier.
Code:
<bag name="lstFluxFinanciers" table="RO_FLX_ENT" lazy="false"
where="lstfluxfin0_1_.RO_DDS_FLX =
(select max(fluxfinancier2.RO_DDS_FLX) from
@dbschema@.RO_FLX_GEN as fluxfinancier2 where
fluxfinancier2.RP_NI_DOSS = lstfluxfin0_.RP_NI_DOSS
and fluxfinancier2.RO_NI_OPERF = lstfluxfin0_.RO_NI_OPERF
and fluxfinancier2.RO_NI_FLX = lstfluxfin0_.RO_NI_FLX)"
inverse="true" cascade="none">
<key update="false" on-delete="noaction">
<column name="RP_NI_DOSS" />
<column name="RO_NI_OPERF" />
</key>
<one-to-many class="fr.project.InfoFluxFinancier"/>
</bag>
The where clause in this bag is made to retrieve the last row of data from the RO_FLX_ENT table.
The object InfoFluxFinancier is mapped into two tables : RO_FLX_ENT and RO_FLEX_GEN.
The GEN table is made to keep trace of each update or insert, so is based on a timestamp property.
So for each row of ENT table, we have multiple rows on GEN for the same key but with a different timestamp.
While retrieving object, we get the row of GEN matching ENT key with the last timestamp.
The object InfoFluxFinancier is abstact.
It has two child classes : InfoOrdreEncaiss and InfoOrdreDecais
Here is the mapping for InfoFluxFinancier :
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="fr.project.InfoFluxFinancier" table="RO_FLX_ENT" discriminator-value="FIN">
<composite-id name="idFluxFinancierPK" class="fr.project.IdFluxFinancierPK">
<key-property name="idDossier" column="RP_NI_DOSS" type="java.lang.Long" />
<key-property name="idOperation" column="RO_NI_OPERF" type="java.lang.Long" />
<key-property name="idFluxFinancier" column="RO_NI_FLX" type="java.lang.Long" />
</composite-id>
<discriminator column="RO_CN_FLX" type="java.lang.String" />
<timestamp name="tsLock" column="RX_DMA_LOCK"/>
<property name="tsCreation" type="java.util.Date" column="RO_DCR_FLX" />
<property name="cdTypeFluxFinancier" type="java.lang.String" column="RO_CN_FLX" update="false" insert="false"/>
<!-- PIECE OF MAPPING I ADDED TO MAKE THE BAG WORKS -->
<join table="RO_FLX_GEN" fetch="select">
<subselect>
SELECT * FROM @dbschema@.RO_FLX_GEN gen1
WHERE RO_DDS_FLX = (SELECT max(RO_DDS_FLX)
FROM @dbschema@.RO_FLX_GEN gen2
where gen1.RP_NI_DOSS = gen2.RP_NI_DOSS
and gen1.RO_NI_OPERF = gen2.RO_NI_OPERF
and gen1.RO_NI_FLX = gen2.RO_NI_FLX)
</subselect>
<key>
<column name="RP_NI_DOSS" />
<column name="RO_NI_OPERF" />
<column name="RO_NI_FLX" />
</key>
<property name="tsSituation" type="java.util.Date" column="RO_DDS_FLX" />
<property name="cdEtat" type="java.lang.String" column="RO_CE_FLX" />
<property name="mt" type="float" column="RO_MT_FLX" />
<property name="indicateurMtHorsTaxe" type="boolean" column="RO_CI_MT_HT" />
<property name="cdDevise" type="java.lang.String" column="RO_CN_DEVISE" />
<property name="commentaire" type="java.lang.String" column="RO_LC_FLX" />
<sql-update>
INSERT INTO @dbschema@.RO_FLX_GEN (RO_DDS_FLX, RO_CE_FLX, RO_MT_FLX, RO_CI_MT_HT, RO_CN_DEVISE, RO_LC_FLX, RP_NI_DOSS, RO_NI_OPERF, RO_NI_FLX)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
</sql-update>
</join>
<!-- END OF THE ADD -->
<subclass name="fr.project.InfoOrdreDecais" discriminator-value="DEC">
<!-- I skipped here all the bag associations to keep this post cleaned-->
<join table="RO_FLX_GEN" fetch="select">
<subselect>
SELECT * FROM @dbschema@.RO_FLX_GEN gen1
WHERE RO_DDS_FLX = (SELECT max(RO_DDS_FLX)
FROM @dbschema@.RO_FLX_GEN gen2
where gen1.RP_NI_DOSS = gen2.RP_NI_DOSS
and gen1.RO_NI_OPERF = gen2.RO_NI_OPERF
and gen1.RO_NI_FLX = gen2.RO_NI_FLX)
</subselect>
<key>
<column name="RP_NI_DOSS" />
<column name="RO_NI_OPERF" />
<column name="RO_NI_FLX" />
</key>
<property name="tsSituation" type="java.util.Date" column="RO_DDS_FLX" />
<property name="cdEtat" type="java.lang.String" column="RO_CE_FLX" />
<property name="mt" type="float" column="RO_MT_FLX" />
<property name="indicateurMtHorsTaxe" type="boolean" column="RO_CI_MT_HT" />
<property name="cdDevise" type="java.lang.String" column="RO_CN_DEVISE" />
<property name="commentaire" type="java.lang.String" column="RO_LC_FLX" />
<!--idPersonneDestinataire -->
<component name="idDestinataireFK" class="fr.project.IdPersonnePK">
<property name="idPersonne" type="java.lang.Long" column="RR_NI_PERS_DEST" />
<property name="idSinistre" type="java.lang.Long" column="RP_NI_DOSS" update="false" insert="false" />
</component>
<!--idPersonneBénéficiaire -->
<component name="idBeneficiaireFK" class="fr.project.IdPersonnePK">
<property name="idPersonne" type="java.lang.Long" column="RR_NI_PERS_BENEF" />
<property name="idSinistre" type="java.lang.Long" column="RP_NI_DOSS" update="false" insert="false" />
</component>
<!--idPersonnePourCompte-->
<component name="idBenefCompteFK" class="fr.project.IdPersonnePK">
<property name="idPersonne" type="java.lang.Long" column="RR_NI_PERS_CPTE" />
<property name="idSinistre" type="java.lang.Long" column="RP_NI_DOSS" update="false" insert="false" />
</component>
<sql-update>
INSERT INTO @dbschema@.RO_FLX_GEN (RO_DDS_FLX, RO_CE_FLX, RO_MT_FLX, RO_CI_MT_HT, RO_CN_DEVISE, RO_LC_FLX, RR_NI_PERS_DEST, RR_NI_PERS_BENEF, RR_NI_PERS_CPTE, RP_NI_DOSS, RO_NI_OPERF, RO_NI_FLX)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
</sql-update>
</join>
</subclass>
<subclass name="fr.project.InfoOrdreEncaiss" discriminator-value="ENC">
<!-- Some bag associations declaration here... -->
<join table="RO_FLX_GEN" fetch="select">
<subselect>
SELECT * FROM @dbschema@.RO_FLX_GEN gen1
WHERE RO_DDS_FLX = (SELECT max(RO_DDS_FLX)
FROM @dbschema@.RO_FLX_GEN gen2
where gen1.RP_NI_DOSS = gen2.RP_NI_DOSS
and gen1.RO_NI_OPERF = gen2.RO_NI_OPERF
and gen1.RO_NI_FLX = gen2.RO_NI_FLX)
</subselect>
<key>
<column name="RP_NI_DOSS" />
<column name="RO_NI_OPERF" />
<column name="RO_NI_FLX" />
</key>
<property name="tsSituation" type="java.util.Date" column="RO_DDS_FLX" />
<property name="cdEtat" type="java.lang.String" column="RO_CE_FLX" />
<property name="mt" type="float" column="RO_MT_FLX" />
<property name="indicateurMtHorsTaxe" type="boolean" column="RO_CI_MT_HT" />
<property name="cdDevise" type="java.lang.String" column="RO_CN_DEVISE" />
<property name="commentaire" type="java.lang.String" column="RO_LC_FLX" />
<!--idPersonneEmetteur -->
<component name="idEmetteurFK" class="fr.project.IdPersonnePK">
<property name="idPersonne" type="java.lang.Long" column="RR_NI_PERS_EMETT" />
<property name="idSinistre" type="java.lang.Long" column="RP_NI_DOSS" update="false" insert="false" />
</component>
<sql-update>
INSERT INTO @dbschema@.RO_FLX_GEN (RO_DDS_FLX, RO_CE_FLX, RO_MT_FLX, RO_CI_MT_HT, RO_CN_DEVISE, RO_LC_FLX, RR_NI_PERS_EMETT, RP_NI_DOSS, RO_NI_OPERF, RO_NI_FLX)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
</sql-update>
</join>
</subclass>
<loader query-ref="flux.load" />
</class>
<query name="flux.load">
from InfoFluxFinancier as flu
where flu.idFluxFinancierPK.idDossier= ?
and flu.idFluxFinancierPK.idOperation= ?
and flu.idFluxFinancierPK.idFluxFinancier= ?
</query>
<query name="flux.rechercherLstIdFlx">
from InfoFluxFinancier as flu
where flu.idFluxFinancierPK.idDossier= :idDossier
and flu.idFluxFinancierPK.idOperation= :idOperationFinanciere
and flu.idFluxFinancierPK.idFluxFinancier= :idFlux
</query>
<query name="flux.rechercherIdOpF">
from InfoFluxFinancier as flu
where flu.idFluxFinancierPK.idDossier= :idDossier
and flu.idFluxFinancierPK.idOperation= :idOperationFinanciere
</query>
</hibernate-mapping>
Sorry for the incovenience about the size of this mapping.
The problem is here :
To make the bag from InfoOpFinanciere work, I need to access the column RO_DDS_FLX, which match the property tsSituation of InfoFluxFinancier.
This property is on the GEN table of the mapping InfoFluxFinancier, so I had to add a join property (I don't know other choice) to access the property tsSituation on InfoFluxFinancier
This join, once added , make work the InfoOpFinancier bag, but crash the InfoFluxFinancier mapping : I can't anymore access (load, update or save) this object through Hibernate.
Here is the stack trace while saving a simple object.
Quote:
16:53:59,757 DEBUG JDBCTransaction:46 - begin
16:53:59,757 DEBUG ConnectionManager:296 - opening JDBC connection
16:53:59,757 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 0
16:53:59,757 DEBUG DriverManagerConnectionProvider:99 - using pooled JDBC connection, pool size: 0
16:53:59,757 DEBUG JDBCTransaction:50 - current autocommit status: true
16:53:59,757 DEBUG JDBCTransaction:52 - disabling autocommit
16:53:59,788 DEBUG AbstractBatcher:290 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
16:53:59,788 DEBUG SQL:324 - insert into RG02.RO_FLX_ENT (RX_DMA_LOCK, RO_DCR_FLX, RO_CN_FLX, RP_NI_DOSS, RO_NI_OPERF, RO_NI_FLX) values (?, ?, 'DEC', ?, ?, ?)
Hibernate: insert into RG02.RO_FLX_ENT (RX_DMA_LOCK, RO_DCR_FLX, RO_CN_FLX, RP_NI_DOSS, RO_NI_OPERF, RO_NI_FLX) values (?, ?, 'DEC', ?, ?, ?)
16:53:59,788 DEBUG AbstractBatcher:378 - preparing statement
16:53:59,804 DEBUG TimestampType:59 - binding '2006-07-26 16:53:59' to parameter: 1
16:53:59,819 DEBUG TimestampType:59 - binding '2006-07-26 16:53:59' to parameter: 2
16:53:59,819 DEBUG LongType:59 - binding '1' to parameter: 3
16:53:59,819 DEBUG LongType:59 - binding '1' to parameter: 4
16:53:59,819 DEBUG LongType:59 - binding '1255' to parameter: 5
16:53:59,819 DEBUG AbstractBatcher:27 - Adding to batch
16:53:59,819 DEBUG AbstractBatcher:54 - Executing batch size: 1
16:53:59,819 DEBUG AbstractBatcher:84 - success of batch update unknown: 0
16:53:59,819 DEBUG AbstractBatcher:298 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
16:53:59,819 DEBUG AbstractBatcher:416 - closing statement
16:53:59,835 DEBUG AbstractBatcher:290 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
16:53:59,835 DEBUG SQL:324 - insert into (
SELECT * FROM RG02.RO_FLX_GEN gen1
WHERE RO_DDS_FLX = (SELECT max(RO_DDS_FLX)
FROM RG02.RO_FLX_GEN gen2
where gen1.RP_NI_DOSS = gen2.RP_NI_DOSS
and gen1.RO_NI_OPERF = gen2.RO_NI_OPERF
and gen1.RO_NI_FLX = gen2.RO_NI_FLX)
) (RO_DDS_FLX, RO_CE_FLX, RO_MT_FLX, RO_CI_MT_HT, RO_CN_DEVISE, RO_LC_FLX, RP_NI_DOSS, RO_NI_OPERF, RO_NI_FLX) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into (
SELECT * FROM RG02.RO_FLX_GEN gen1
WHERE RO_DDS_FLX = (SELECT max(RO_DDS_FLX)
FROM RG02.RO_FLX_GEN gen2
where gen1.RP_NI_DOSS = gen2.RP_NI_DOSS
and gen1.RO_NI_OPERF = gen2.RO_NI_OPERF
and gen1.RO_NI_FLX = gen2.RO_NI_FLX)
) (RO_DDS_FLX, RO_CE_FLX, RO_MT_FLX, RO_CI_MT_HT, RO_CN_DEVISE, RO_LC_FLX, RP_NI_DOSS, RO_NI_OPERF, RO_NI_FLX) values (?, ?, ?, ?, ?, ?, ?, ?, ?)
16:53:59,835 DEBUG AbstractBatcher:378 - preparing statement
16:53:59,929 DEBUG TimestampType:59 - binding '2006-07-26 16:53:59' to parameter: 1
16:53:59,929 DEBUG StringType:52 - binding null to parameter: 2
16:53:59,929 DEBUG FloatType:59 - binding '1.0' to parameter: 3
16:53:59,929 DEBUG BooleanType:59 - binding 'true' to parameter: 4
16:53:59,929 DEBUG StringType:59 - binding 'cdDevise' to parameter: 5
16:53:59,929 DEBUG StringType:52 - binding null to parameter: 6
16:53:59,929 DEBUG LongType:59 - binding '1' to parameter: 7
16:53:59,929 DEBUG LongType:59 - binding '1' to parameter: 8
16:53:59,929 DEBUG LongType:59 - binding '1255' to parameter: 9
16:53:59,960 DEBUG AbstractBatcher:298 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
16:53:59,976 DEBUG AbstractBatcher:416 - closing statement
16:53:59,976 DEBUG AbstractBatcher:290 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
16:53:59,976 DEBUG SQL:324 - insert into (
SELECT * FROM RG02.RO_FLX_GEN gen1
WHERE RO_DDS_FLX = (SELECT max(RO_DDS_FLX)
FROM RG02.RO_FLX_GEN gen2
where gen1.RP_NI_DOSS = gen2.RP_NI_DOSS
and gen1.RO_NI_OPERF = gen2.RO_NI_OPERF
and gen1.RO_NI_FLX = gen2.RO_NI_FLX)
) (RO_DDS_FLX, RO_CE_FLX, RO_MT_FLX, RO_CI_MT_HT, RO_CN_DEVISE, RO_LC_FLX, RR_NI_PERS_DEST, RR_NI_PERS_BENEF, RR_NI_PERS_CPTE, RP_NI_DOSS, RO_NI_OPERF, RO_NI_FLX) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into (
SELECT * FROM RG02.RO_FLX_GEN gen1
WHERE RO_DDS_FLX = (SELECT max(RO_DDS_FLX)
FROM RG02.RO_FLX_GEN gen2
where gen1.RP_NI_DOSS = gen2.RP_NI_DOSS
and gen1.RO_NI_OPERF = gen2.RO_NI_OPERF
and gen1.RO_NI_FLX = gen2.RO_NI_FLX)
) (RO_DDS_FLX, RO_CE_FLX, RO_MT_FLX, RO_CI_MT_HT, RO_CN_DEVISE, RO_LC_FLX, RR_NI_PERS_DEST, RR_NI_PERS_BENEF, RR_NI_PERS_CPTE, RP_NI_DOSS, RO_NI_OPERF, RO_NI_FLX) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
16:53:59,976 DEBUG AbstractBatcher:378 - preparing statement
16:54:00,116 DEBUG TimestampType:59 - binding '2006-07-26 16:53:59' to parameter: 1
16:54:00,132 DEBUG StringType:52 - binding null to parameter: 2
16:54:00,132 DEBUG FloatType:59 - binding '1.0' to parameter: 3
16:54:00,132 DEBUG BooleanType:59 - binding 'true' to parameter: 4
16:54:00,132 DEBUG StringType:59 - binding 'cdDevise' to parameter: 5
16:54:00,132 DEBUG StringType:52 - binding null to parameter: 6
16:54:00,132 DEBUG LongType:52 - binding null to parameter: 7
16:54:00,132 DEBUG LongType:52 - binding null to parameter: 8
16:54:00,132 DEBUG LongType:52 - binding null to parameter: 9
16:54:00,132 DEBUG LongType:59 - binding '1' to parameter: 10
16:54:00,132 DEBUG LongType:59 - binding '1' to parameter: 11
16:54:00,132 DEBUG LongType:59 - binding '1255' to parameter: 12
16:54:00,148 DEBUG AbstractBatcher:298 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
16:54:00,148 DEBUG AbstractBatcher:416 - closing statement
16:54:00,163 WARN JDBCExceptionReporter:71 - SQL Error: -803, SQLState: 23505
16:54:00,163 ERROR JDBCExceptionReporter:72 - [IBM][CLI Driver][DB2/6000] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "RG02.RO_FLX_GEN" from having duplicate rows for those columns. SQLSTATE=23505
16:54:00,163 ERROR AbstractFlushingEventListener:277 - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: could not insert: [fr.smabtp.utilitaires.taxinomie.InfoOrdreDecais]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:63)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1869)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2200)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:46)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
at fr.smabtp.jamos.dao.hibernate.A.flush(Unknown Source)
at fr.smabtp.prestation.flux.TestFluxFinancier.testCreer(TestFluxFinancier.java:84)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))
at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
at junit.framework.TestCase.runTest(TestCase.java:154)
at fr.smabtp.jamos.test.JamosTestCase.runBare(Unknown Source)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:392)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:276)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:167)
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/6000] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "RG02.RO_FLX_GEN" from having duplicate rows for those columns. SQLSTATE=23505
at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:256)
at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:427)
at COM.ibm.db2.jdbc.net.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:405)
at COM.ibm.db2.jdbc.net.DB2PreparedStatement.executeUpdate(DB2PreparedStatement.java:1111)
at com.p6spy.engine.logging.P6LogPreparedStatement.executeUpdate(P6LogPreparedStatement.java:183)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1856)
Don't know why Hibernate made two inserts one beside one.
The idea will be to remove the join from the abstract class (without it, it work well except for the bag of InfoOpFinanciere) and find a way to access the tsSituation of GEN table of InfoFluxFinancier from infoOpFinanciere without having to write it (using the tsSituation of child classes ?)
Can anybody help to solve this sharp problem ?
Thanks for help
Login
FAQ
Search
