Too much parameters binded in custom sql-delete

jchicoineau · **Joined:** Mon Apr 23, 2007 10:06 am **Posts:** 6

Hi there,

I've got a problem with Hibernate, related to custom sql-delete in a composite-element set.
My properties' table has got five column :
o : a foreign key to the parent entity
o, g, m : making a unique key
t, v : effective data.
I want to use only the columns making the unicity in the one-row custom sql-delete.
It seems that Hibernate tries to bind too much parameters in my custom sql-delete.

Did I really miss something in my Hibernate use?

(by the way, I may have lost myself in the doc or while googling, and am ready to be flamed on public place ;-) ...)

maven package there

Hibernate version: 3.0.5 (but also fails with 3.1.3 and 3.2.3.ga)
Name and version of the database you are using: hsqldb 1.8.0.7

Mapping document:

Code:

   <class name="Entity" table="entities">
        
        <id name="id" type="long">
            <generator class="sequence">
                <param name="sequence">SEQ_ENTITY_ID</param>
            </generator>
        </id>

        <property name="name" type="string" access="property" />

        <set name="properties" table="properties" cascade="all-delete-orphan" lazy="false">
            <key column="o" />
            <composite-element class="Property" >
                <parent   name="owner"    />
                <property name="group"   column="g" />
                <property name="meaning" column="m" />
                <property name="type"    column="t" />
                <property name="value"   column="v" />
            </composite-element>
            <!-- hibernate-generated sql-delete : <![CDATA[ delete from properties where o=? and g=? and m=? and t=? and v=? ]]> -->
            <sql-delete><![CDATA[ DELETE FROM properties WHERE o=? AND g=? AND m=? ]]></sql-delete>

        </set>

    </class>

Entity code :

Code:

well, basic bean pojo

TestCase code fragment :

Code:

            logger.log(level,"persisting");
            
            session = sfactory.openSession();
            transaction = session.beginTransaction();
            session.save(e);
            transaction.commit();
            session.close();

            logger.log(level,"loading");
            
            session = sfactory.openSession();
            temp = (Entity) session.load(Entity.class, e.getId());
            Hibernate.initialize(temp);
            session.close();

            assertNotNull(temp);
            assertNotNull(temp.getProperties());

            logger.log(level,"altering");
            
            java.util.Iterator it = temp.getProperties().iterator();
            if (it.hasNext()) {
                it.next();
                it.remove();
            }

            logger.log(level,"updating");
            
            session = sfactory.openSession();
            transaction = session.beginTransaction();
            session.update(temp);
            transaction.commit();
            session.close();

The output

Code:

TRACE | TestEntity : updating
DEBUG | org.hibernate.impl.SessionImpl : opened session at timestamp: 4823427468148736
DEBUG | org.hibernate.transaction.JDBCTransaction : begin
DEBUG | org.hibernate.jdbc.ConnectionManager : opening JDBC connection
DEBUG | org.hibernate.connection.DriverManagerConnectionProvider : total checked-out connections: 0
DEBUG | org.hibernate.connection.DriverManagerConnectionProvider : using pooled JDBC connection, pool size: 0
DEBUG | org.hibernate.transaction.JDBCTransaction : current autocommit status: false
DEBUG | org.hibernate.jdbc.JDBCContext : after transaction begin
DEBUG | org.hibernate.event.def.DefaultSaveOrUpdateEventListener : updating detached instance
DEBUG | org.hibernate.event.def.DefaultSaveOrUpdateEventListener : updating [Entity#1]
DEBUG | org.hibernate.event.def.DefaultSaveOrUpdateEventListener : updating [Entity#1]
DEBUG | org.hibernate.engine.Cascade : processing cascade ACTION_SAVE_UPDATE for: Entity
DEBUG | org.hibernate.engine.Cascade : cascade ACTION_SAVE_UPDATE for collection: Entity.properties
DEBUG | org.hibernate.engine.Cascade : done cascade ACTION_SAVE_UPDATE for collection: Entity.properties
DEBUG | org.hibernate.engine.Cascade : done processing cascade ACTION_SAVE_UPDATE for: Entity
DEBUG | org.hibernate.transaction.JDBCTransaction : commit
DEBUG | org.hibernate.impl.SessionImpl : automatically flushing session
DEBUG | org.hibernate.event.def.AbstractFlushingEventListener : flushing session
DEBUG | org.hibernate.event.def.AbstractFlushingEventListener : processing flush-time cascades
DEBUG | org.hibernate.engine.Cascade : processing cascade ACTION_SAVE_UPDATE for: Entity
DEBUG | org.hibernate.engine.Cascade : cascade ACTION_SAVE_UPDATE for collection: Entity.properties
DEBUG | org.hibernate.engine.Cascade : done cascade ACTION_SAVE_UPDATE for collection: Entity.properties
DEBUG | org.hibernate.engine.Cascade : done processing cascade ACTION_SAVE_UPDATE for: Entity
DEBUG | org.hibernate.event.def.AbstractFlushingEventListener : dirty checking collections
DEBUG | org.hibernate.engine.CollectionEntry : Collection dirty: [Entity.properties#1]
DEBUG | org.hibernate.event.def.AbstractFlushingEventListener : Flushing entities and processing referenced collections
DEBUG | org.hibernate.event.def.DefaultFlushEntityEventListener : Updating entity: [Entity#1]
DEBUG | org.hibernate.engine.Collections : Collection found: [Entity.properties#1], was: [Entity.properties#1] (initialized)
DEBUG | org.hibernate.event.def.AbstractFlushingEventListener : Processing unreferenced collections
DEBUG | org.hibernate.event.def.AbstractFlushingEventListener : Scheduling collection removes/(re)creates/updates
DEBUG | org.hibernate.event.def.AbstractFlushingEventListener : Flushed: 0 insertions, 1 updates, 0 deletions to 1 objects
DEBUG | org.hibernate.event.def.AbstractFlushingEventListener : Flushed: 0 (re)creations, 1 updates, 0 removals to 1 collections
DEBUG | org.hibernate.pretty.Printer : listing entities:
DEBUG | org.hibernate.pretty.Printer : Entity{properties=[component[group,meaning,type,value]{value=11, group=11, type=11, meaning=11}, component[group,meaning,type,value]{value=12, group=12, type=12, meaning=12}, component[group,meaning,type,value]{value=15, group=15, type=15, meaning=15}, component[group,meaning,type,value]{value=14, group=14, type=14, meaning=14}, component[group,meaning,type,value]{value=16, group=16, type=16, meaning=16}, component[group,meaning,type,value]{value=3, group=3, type=3, meaning=3}, component[group,meaning,type,value]{value=4, group=4, type=4, meaning=4}, component[group,meaning,type,value]{value=6, group=6, type=6, meaning=6}, component[group,meaning,type,value]{value=5, group=5, type=5, meaning=5}, component[group,meaning,type,value]{value=0, group=0, type=0, meaning=0}, component[group,meaning,type,value]{value=7, group=7, type=7, meaning=7}, component[group,meaning,type,value]{value=8, group=8, type=8, meaning=8}, component[group,meaning,type,value]{value=2, group=2, type=2, meaning=2}, component[group,meaning,type,value]{value=1, group=1, type=1, meaning=1}, component[group,meaning,type,value]{value=17, group=17, type=17, meaning=17}, component[group,meaning,type,value]{value=18, group=18, type=18, meaning=18}, component[group,meaning,type,value]{value=19, group=19, type=19, meaning=19}, component[group,meaning,type,value]{value=10, group=10, type=10, meaning=10}, component[group,meaning,type,value]{value=9, group=9, type=9, meaning=9}], name=dummy, id=1}
DEBUG | org.hibernate.event.def.AbstractFlushingEventListener : executing flush
DEBUG | org.hibernate.jdbc.ConnectionManager : registering flush begin
DEBUG | org.hibernate.persister.entity.AbstractEntityPersister : Updating entity: [Entity#1]
DEBUG | org.hibernate.jdbc.AbstractBatcher : about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG | org.hibernate.SQL : update entities set name=? where id=?
Hibernate: update entities set name=? where id=?
DEBUG | org.hibernate.jdbc.AbstractBatcher : preparing statement
DEBUG | org.hibernate.persister.entity.AbstractEntityPersister : Dehydrating entity: [Entity#1]
DEBUG | org.hibernate.type.StringType : binding 'dummy' to parameter: 1
DEBUG | org.hibernate.type.LongType : binding '1' to parameter: 2
DEBUG | org.hibernate.jdbc.AbstractBatcher : Executing batch size: 1
DEBUG | org.hibernate.jdbc.AbstractBatcher : about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG | org.hibernate.jdbc.AbstractBatcher : closing statement
DEBUG | org.hibernate.persister.collection.AbstractCollectionPersister : Deleting rows of collection: [Entity.properties#1]
DEBUG | org.hibernate.jdbc.AbstractBatcher : about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG | org.hibernate.SQL : DELETE FROM properties WHERE o=? AND g=? AND m=?
Hibernate: DELETE FROM properties WHERE o=? AND g=? AND m=?
DEBUG | org.hibernate.jdbc.AbstractBatcher : preparing statement
DEBUG | org.hibernate.type.LongType : binding '1' to parameter: 1
DEBUG | org.hibernate.type.IntegerType : binding '13' to parameter: 2
DEBUG | org.hibernate.type.IntegerType : binding '13' to parameter: 3
DEBUG | org.hibernate.type.IntegerType : binding '13' to parameter: 4
INFO  | org.hibernate.type.IntegerType : could not bind value '13' to parameter: 4; Invalid argument in JDBC call: parameter index out of range: 4
DEBUG | org.hibernate.jdbc.AbstractBatcher : about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG | org.hibernate.jdbc.AbstractBatcher : closing statement
DEBUG | org.hibernate.util.JDBCExceptionReporter : could not delete collection rows: [Entity.properties#1] [DELETE FROM properties WHERE o=? AND g=? AND m=?]
java.sql.SQLException: Invalid argument in JDBC call: parameter index out of range: 4
        at org.hsqldb.jdbc.Util.sqlException(Lorg.hsqldb.HsqlException;)Ljava.sql.SQLException;(Unknown Source)
        at org.hsqldb.jdbc.Util.sqlException(ILjava.lang.String;)Ljava.sql.SQLException;(Unknown Source)
        at org.hsqldb.jdbc.jdbcPreparedStatement.checkSetParameterIndex(IZ)V(Unknown Source)
        at org.hsqldb.jdbc.jdbcPreparedStatement.setIntParameter(II)V(Unknown Source)
        at org.hsqldb.jdbc.jdbcPreparedStatement.setInt(II)V(Unknown Source)
        at org.hibernate.type.IntegerType.set(IntegerType.java:41)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:136)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:116)
        at org.hibernate.type.ComponentType.nullSafeSet(ComponentType.java:307)
        at org.hibernate.persister.collection.AbstractCollectionPersister.writeElementToWhere(AbstractCollectionPersister.java:784)
        at org.hibernate.persister.collection.AbstractCollectionPersister.deleteRows(AbstractCollectionPersister.java:1256)
        at org.hibernate.action.CollectionUpdateAction.execute(CollectionUpdateAction.java:54)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
        at TestEntity.testEntity(TestEntity.java:75)
        at jrockit.reflect.VirtualNativeMethodInvoker.invoke(Ljava.lang.Object;[Ljava.lang.Object;)Ljava.lang.Object;(Unknown Source)
        at java.lang.reflect.Method.invoke(Ljava.lang.Object;[Ljava.lang.Object;I)Ljava.lang.Object;(Unknown Source)
        at junit.framework.TestCase.runTest(TestCase.java:154)
WARN  | org.hibernate.util.JDBCExceptionReporter : SQL Error: -62, SQLState: SOO10
ERROR | org.hibernate.util.JDBCExceptionReporter : Invalid argument in JDBC call: parameter index out of range: 4
ERROR | org.hibernate.event.def.AbstractFlushingEventListener : Could not synchronize database state with session

max · **Posted:** Thu Apr 26, 2007 5:56 pm

you map it as a component; hence it has to delete for all the parts - that is the semantics.

jchicoineau · **Joined:** Mon Apr 23, 2007 10:06 am **Posts:** 6

max wrote:

you map it as a component; hence it has to delete for all the parts - that is the semantics.

hmm...erf...I missed it, thanks!

looks like I'll need some refactoring.

jchicoineau · **Joined:** Mon Apr 23, 2007 10:06 am **Posts:** 6

jchicoineau wrote:

max wrote:

you map it as a component; hence it has to delete for all the parts - that is the semantics.

hmm...erf...I missed it, thanks!

looks like I'll need some refactoring.

I'm still wondering,
would it be undesirable to override hibernate component semantics with custom sql?

max · **Posted:** Fri Apr 27, 2007 6:32 am

well how would you ensure consistency if overrided it via custom delete ?

you could of course just write som custom-sql that ignored the 3rd and 4th parameter - that would override it; but be really really weird.

jchicoineau · **Joined:** Mon Apr 23, 2007 10:06 am **Posts:** 6

max wrote:

well how would you ensure consistency if overrided it via custom delete ?

Well in my case, the custom-delete targets what make my component's unicity (i.e. {o,g,m}, and it is sufficient for 1-row deleting) . The equals() and hashCode() ensure this concistency.

In fact, I would need a way to specify some business key for my components and still have a set-like collection and some components semantic.

To refer to my example : my properties are not only really tied to my entity, but they also have an inner unicity constraint.

But maybe what I should alter is the set-like collection. From the docs, I found that this semantic - take all columns for deleting a row - is set-specific. Maybe I should go for a map. Moving to a many-to-one relation appears to me like breaking the component life-cycle but it may be also a not-so-bad solution (my 'entity-properties pattern' is duplicated among several classes, and I fear that moving to a many-to-one relation would badly duplicate mappings and/or code)

edit : or maybe I should refactor my model deeper ;-)