composite-id, key-many-to-one, joined query

mcueh · **Joined:** Wed May 05, 2004 3:26 am **Posts:** 25

Hibernate version:
3.0.1

Name and version of the database you are using:
PostgreSQL 7.4

Hello all.

Is it possible to use a composite-id with a key-many-to-one to another class?

e.g.

Code:

[i]table ship[/i]
------------
id (int)
code (varchar)
company (int)

[i]table company[/i]
------------
id (int)
code (varchar)

A ship has a composite-id

Code:

            <composite-id>
               <key-property name="code" />
               <key-many-to-one name="company"/>
            </composite-id>

only that company is a reference to company.id, so effectively the query should look like this

Code:

select ship_.code, ship_.company from ship ship_, company company_ where ship_.company = company_.id and ship_.code=? and company_.code=?

Couldnt find anything in the docs or in the forums.
thanks in advance.

dcminter · **Posted:** Sat Apr 23, 2005 8:24 pm

I have a passingly similar question:

Is it possible (for the sake of a legacy database which cannot be changed) to create a join property where there is a composite primary key, and the foreign key of the join is onto only one of the columns forming the primary key?

mcueh · **Joined:** Wed May 05, 2004 3:26 am **Posts:** 25

As an addition to the original post...

Hibernate version:
3.0.1

Mapping documents:
OLIShip.hbm.xml

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="gr.forthnet.enosis.oli.pojos.OLIShip"
        table="ships"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
    >

        <id
            name="code"
            column="code"
            type="java.lang.String"
        >
            <generator class="assigned">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-OLIShip.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>

        <many-to-one
            name="company"
            class="gr.forthnet.enosis.oli.pojos.OLICompany"
            cascade="save-update"
            outer-join="auto"
            update="true"
            insert="true"
            access="property"
            property-ref="id"                  
            column="company"
        />

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-OLIShip.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

OLICompany

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="gr.forthnet.enosis.oli.pojos.OLICompany"
        table="companies"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
    >

        <id
            name="code"
            column="code"
            type="java.lang.String"
        >
            <generator class="assigned">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-OLICompany.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>

        <property
            name="id"
            type="java.lang.Long"
            update="true"
            insert="true"
            access="property"
            column="id"
        />
            
        <bag
            name="ships"
            table="companies_ships"
            lazy="false"
            inverse="false"
            cascade="none"
        >

              <key
                  column="company"
              >
              </key>

              <one-to-many
                  class="gr.forthnet.enosis.oli.pojos.OLIShip"
              />
        </bag>

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-OLICompany.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

  
        OLIPort her = new OLIPort("HER");
        OLIPort pir = new OLIPort("PIR");        

        OLICompany company = new OLICompany("ANEK");
        company.setId(new Long(1));
        OLIShip ship = new OLIShip("00002", company);
        session.saveOrUpdate(ship);

Full stack trace of any exception that occurs:

Code:

(util.JDBCExceptionReporter          57  ) SQL Error: 0, SQLState: 22P02
(util.JDBCExceptionReporter          58  ) ERROR: invalid input syntax for integer: "ANEK"

(def.AbstractFlushingEventListener   277 ) Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: could not delete collection: [gr.forthnet.enosis.oli.pojos.OLICompany.ships#ANEK]
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:824)
   at org.hibernate.action.CollectionRemoveAction.execute(CollectionRemoveAction.java:22)
   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:138)
   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:678)
   at gr.forthnet.services.databases.hibernate.HiberClient.disconnect(HiberClient.java:78)
   at gr.forthnet.enosis.oli.core.Schedule.main(Schedule.java:113)
Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "ANEK"

   at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
   at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154)
   at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101)
   at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
   at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:515)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
   at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:271)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
   at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
   at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:814)
   ... 9 more
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not delete collection: [gr.forthnet.enosis.oli.pojos.OLICompany.ships#ANEK]
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:824)
   at org.hibernate.action.CollectionRemoveAction.execute(CollectionRemoveAction.java:22)
   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:138)
   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:678)
   at gr.forthnet.services.databases.hibernate.HiberClient.disconnect(HiberClient.java:78)
   at gr.forthnet.enosis.oli.core.Schedule.main(Schedule.java:113)
Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "ANEK"

   at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
   at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154)
   at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101)
   at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
   at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:515)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
   at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:271)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
   at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
   at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:814)
   ... 9 more

Name and version of the database you are using:
PostgreSQL 7.4

The generated SQL (show_sql=true):

Code:

Hibernate: select oliship_.code, oliship_.company as company0_ from ships oliship_ where oliship_.code=?
Hibernate: select olicompany_.code, olicompany_.id as id1_ from companies olicompany_ where olicompany_.code=?
Hibernate: update ships set company=null where company=?

Debug level Hibernate log excerpt:

Code:

Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not delete collection: [gr.forthnet.enosis.oli.pojos.OLICompany.ships#ANEK]
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:824)
   at org.hibernate.action.CollectionRemoveAction.execute(CollectionRemoveAction.java:22)
   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:138)
   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:678)
   at gr.forthnet.services.databases.hibernate.HiberClient.disconnect(HiberClient.java:78)
   at gr.forthnet.enosis.oli.core.Schedule.main(Schedule.java:113)
Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "ANEK"

   at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
   at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154)
   at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101)
   at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
   at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:515)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
   at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:271)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
   at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
   at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:814)
   ... 9 more

mcueh · **Joined:** Wed May 05, 2004 3:26 am **Posts:** 25

Could someone at least point me to the right direction please? :/

I've bean reading the manual, guides and searching the forum for hours and haven't found something to help the situation.

I would be gratefull.

Thx.

dcminter · **Posted:** Mon Apr 25, 2005 10:52 am

mcueh wrote:

Could someone at least point me to the right direction

I'm curious - the following error message:

Quote:

ERROR: invalid input syntax for integer: "ANEK"

Seems to imply that you've got an int column identifying the company in your Ship table, rather than a varchar...?

mcueh · **Joined:** Wed May 05, 2004 3:26 am **Posts:** 25

dcminter, thanx for taking the time to reply.

yes, the problem is the column "code" in companies is considered to be the id but the foreign key in column "company" (of ships) shows the row "id" column. So what Im trying to achieve is a join between the two tables with that FK and a query by company code.

As you can see in the mappings, both ship and company have as id the "code" column, but the join between the two tables is done by the row id.

Is it possible in a parent/children rel to specify a column other than the PK for the join?

dcminter · **Posted:** Tue Apr 26, 2005 5:50 am

mcueh wrote:

So what Im trying to achieve is a join between the two tables with that FK and a query by company code.

Ok, I don't entirely grok the problem - I'll have a think about it. Meanwhile have you investigated the property-ref attribute of the key tag and ruled that out?

mcueh · **Joined:** Wed May 05, 2004 3:26 am **Posts:** 25

After investigating further I think that the problem is in my concept.

Let me explain. The mappings should be like following
OLIShip

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="gr.forthnet.enosis.oli.pojos.OLIShip"
        table="ships"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
    >

        <id
            name="id"
            column="id"
            type="java.lang.Long"
        >
            <generator class="increment">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-OLICompany.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>
            
            <property
            name="code"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
                  unique="true"
            column="code"
        />
            
        <many-to-one
            name="company"
            class="gr.forthnet.enosis.oli.pojos.OLICompany"
            cascade="save-update"
            outer-join="auto"
            update="true"
            insert="true"
            access="property"
            column="company"
        />
            <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-OLIShip.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

OLICompany

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="gr.forthnet.enosis.oli.pojos.OLICompany"
        table="companies"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
    >

        <id
            name="id"
            column="id"
            type="java.lang.Long"
        >
            <generator class="increment">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-OLICompany.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>

                                    
            <property
            name="code"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
                  unique="true"
            column="code"
        />
            
        <bag
            name="ships"
            table="ships"
            lazy="false"
            inverse="true" 
            cascade="all"
        >

              <key
                  column="company"
              >
              </key>

              <one-to-many
                  class="gr.forthnet.enosis.oli.pojos.OLIShip"
              />
        </bag>

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-OLICompany.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

The thing is, when calling saveOrUpdate(), hibernate checks the value of the "id" to determine whether to save or update the object. Only problem is that I want hibernate to check against the "code" property instead since "id" is just a row id.
Is something like it possible or do I have to provide the functionality?