Hi everyone,

I'm a newbie to Hibernate and i'm just having issues with linking two tables using a bi-directional one-to-many association.

Background info:

Using hibernate on an existing db which is required to map data and objects across multiple schemas.

First table, LA_Licenses has a varchar primary key in the format '10D136012' and exists on schema LA. It has a foreign key reference to the second table which uses a numeric primary key.

When i try to associate to the second table, i am getting an invalid number exception. I installed irontrack sql spy and can see that it seems to be using the varchar primary key as the value to reference to the second table, even though i declared the foreign key column as the key in the set.

Does anyone have an idea as to why it would use the pk from the first table rather than the fk which i told it to use (well at least i think i told it!)

Also, i used hibernate's tools to generate the hbm files from the schema and then the pojo generator's from them and yet it still uses the incorrect column.

Anyway, any help is very much appreciated.

Regards,
John

Hibernate version: 3.2.4.sp1

Mapping documents:

First class mapping doc:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 26/06/2007 09:55:03 by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
<class name="com.wis.model.LaLicenses" table="LA_LICENSES" schema="LA">
<id name="license" type="string">
<column name="LICENSE" length="11" />

<generator class="assigned" />
</id>
..
..
..
<set name="dlwcAccountRolesesForDlwcAccountId">
<key column="DLWC_ACCOUNT_ID"/>
<one-to-many class="com.wis.model.DlwcAccountRoles" />
</set>
</class>
</hibernate-mapping>


Second class mapping doc:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 26/06/2007 09:55:03 by Hibernate Tools 3.2.0.b9 -->
<hibernate-mapping>
<class name="com.wis.model.DlwcAccountRoles" table="DLWC_ACCOUNT_ROLES" schema="DLWC">
<id name="dlwcAcroId" type="long">
<column name="DLWC_ACRO_ID" precision="10" scale="0" />

<generator class="assigned" />
</id>
..
..
..
<many-to-one name="laLicensesByDlwcAccountId" class="com.wis.model.LaLicenses" insert="false" update="false" fetch="select">
<column name="DLWC_ACCOUNT_ID" length="10" />
</many-to-one>

</class>
</hibernate-mapping>



Code to call hibernate from spring app:

logger.info("Entered retrieveLicence() with licenceNumber=["+licenceNumber+"]");

licence = (LaLicenses)getHibernateTemplate().get(LaLicenses.class, licenceNumber);
logger.info("licence account id =["+licence.getDlwcAccountId()+"]");
logger.info("licence=["+licence+"]");

logger.info("get the accounts");
Iterator rolesIter = licence.getDlwcAccountRolesesForDlwcAccountId().iterator();
while(rolesIter.hasNext()){
DlwcAccountRoles role = (DlwcAccountRoles)rolesIter.next();
logger.info("role=["+role+"]");
}


Full stack trace of any exception that occurs:

app.log:

2007-06-26 13:54:34,640 INFO [com.wis.service.WisHibernateDataService] - get the apps
2007-06-26 13:54:34,671 INFO [com.wis.service.WisHibernateDataService] - get the accounts
2007-06-26 13:54:34,671 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1722, SQLState: 42000
2007-06-26 13:54:34,671 ERROR [org.hibernate.util.JDBCExceptionReporter] - ORA-01722: invalid number

2007-06-26 13:54:34,687 ERROR [org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/wis].[wis]] - Servlet.service() for servlet wis threw exception
java.sql.SQLException: ORA-01722: invalid number



Name and version of the database you are using: Oracle 9i

The generated SQL (show_sql=true):

select dlwcaccoun0_.DLWC_ACCOUNT_ID ..... from DLWC.DLWC_ACCOUNT_ROLES dlwcaccoun0_ where dlwcaccoun0_.DLWC_ACCOUNT_ID='10D136012'





Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html