Hi all,
I get a GenericJDBCException retrieving data from an object that is mapped to a table that has a foreign key to another table the has a composite primary key.
I am working with hibernate3.1.3 and Hibernate Tools 3.1.0.beta4 for eclipse 3.1.2. I did also same test with Hibernate Tools 3.2.0.beta6 with eclipse 3.2.
Here the example done using MySQL:
Code:
create table stato (
id int,
nome varchar(255)
);
alter table stato
add constraint stato_pk primary key (id);
create table provincia (
stato_id int,
id varchar(5),
nome varchar(255)
);
alter table provincia
add constraint provincia_pk primary key (id, stato_id);
alter table provincia
add constraint prov_stato_fk foreign key (stato_id) references stato (id);
create table comune (
stato_id int,
prov_id varchar(5),
id varchar(5),
nome varchar(255)
);
alter table comune
add constraint comune_pk primary key (id, prov_id, stato_id);
alter table comune
add constraint com_stato_fk foreign key (stato_id) references stato (id);
alter table comune
add constraint com_prov_fk foreign key (stato_id, prov_id) references provincia (stato_id, id);
- Stato is the first table with a simple PK that is STATO.ID
- Provincia is another table which has a composite PK (PROVINCIA.STATO_ID, PROVINCIA.ID) where STATO_ID has a FK to table STATO.
- Comune is the third table which has a composite PK (COMUNE.STATO_ID, COMUNE.PROVINCIA_ID, COMUNE.ID). Here I have a FK to (PROVINCIA.STATO_ID, PROVINCIA.ID) that causes the problem.
I used Hibernate tools to create the hibernate.cfg.xml and hibernate.reveng.xml and generated the POJO and ID classes mapping the three tables.
Hibernate Tools created classes Comune.java, ComuneId.java, Provincia.java, ProvinciaId.java, Stato.java
I did not touch anything in that classes.
When I write a test like this:
ComuneId comId = new ComuneId("CM_01", "PR_01", 13);
Comune comune = (Comune) session.load(Comune.class, comId);
Provincia provincia = comune.getProvincia();
Doing instruction comune.getProvincia() I get the GenericJDBCException
SQL statement issued by Hibernate is:
Hibernate: select comune0_.id as id2_0_, comune0_.prov_id as prov2_2_0_, comune0_.stato_id as stato3_2_0_, comune0_.nome as nome2_0_ from test.comune comune0_ where comune0_.id=? and comune0_.prov_id=? and comune0_.stato_id=?
could not load an entity: [cosm.example.component.stradario.ent.pom.plc.Comune#component[id,provId,statoId]{statoId=13, provId=PR_01, id=CM_01}]
Next exception is:
Caused by: java.sql.SQLException: Invalid value for getInt() - 'PR_01' in column 2
in column 2 "prov2_2_0_" there is a string and not an integer
Here the complete test class:
-----------------------------------------------------------------------------------------
Code:
package test;
import org.hibernate.ObjectNotFoundException;
import org.hibernate.Transaction;
import org.hibernate.classic.Session;
import cosm.example.component.stradario.ent.pom.plc.Comune;
import cosm.example.component.stradario.ent.pom.plc.ComuneId;
import cosm.example.component.stradario.ent.pom.plc.Provincia;
import cosm.example.component.stradario.ent.pom.plc.ProvinciaId;
import cosm.example.component.stradario.ent.pom.plc.Stato;
public class TestPersistence {
public static void main(String[] args) {
Session session = InitSessionFactory.getInstance().openSession();
System.out.println("Test start");
Transaction transaction = session.getTransaction();
transaction.begin();
Stato stato = null;
Provincia provincia = null;
Comune comune = null;
//Search or create a new Stato
int statoId = 13;
try {
stato = (Stato) session.load(Stato.class, new Integer(statoId));
System.out.println("stato: ("
+ stato.getId()
+", "+stato.getNome()
+")");
}
catch (ObjectNotFoundException e) {
stato = new Stato(statoId, "STATO 13", null, null);
session.save(stato);
System.out.println("STATO CREATED: "+stato);
}
//Search or create a new Provincia
ProvinciaId provId = new ProvinciaId("PR_01", statoId);
try {
provincia = (Provincia) session.load(Provincia.class, provId);
stato = provincia.getStato();
System.out.println("provincia: ("
+ stato.getId()
+", "+stato.getNome()
+", "+provincia.getId().getId()
+", "+provincia.getNome()
+")");
}
catch (ObjectNotFoundException e) {
provincia = new Provincia(provId, stato, "PROVINCIA 01", null);
session.save(provincia);
System.out.println("PROVINCIA CREATED: "+provincia);
}
//Search or create a new Comune
ComuneId comId = new ComuneId("CM_01", "PR_01", statoId);
try {
comune = (Comune) session.load(Comune.class, comId);
/*
* Doing next instruction comune.getProvincia() I get the GenericJDBCException
* SQL statement issued by Hibernate is:
* Hibernate: select comune0_.id as id2_0_, comune0_.prov_id as prov2_2_0_, comune0_.stato_id as stato3_2_0_, comune0_.nome as nome2_0_ from test.comune comune0_ where comune0_.id=? and comune0_.prov_id=? and comune0_.stato_id=?
* could not load an entity: [cosm.example.component.stradario.ent.pom.plc.Comune#component[id,provId,statoId]{statoId=13, provId=PR_01, id=CM_01}]
* Next exception is:
* Caused by: java.sql.SQLException: Invalid value for getInt() - 'PR_01' in column 2
* in column 2 "prov2_2_0_" there is a string and not an integer
*/
provincia = comune.getProvincia();
stato = comune.getStato();
System.out.println("comune: ("
+ stato.getId()
+", "+stato.getNome()
+", "+provincia.getId()
+", "+provincia.getNome()
+", "+comune.getId().getId()
+", "+comune.getNome()
+")");
}
catch (ObjectNotFoundException e) {
comune = new Comune(comId, stato, provincia, "COMUNE 01");
session.save(comune);
System.out.println("COMUNE CREATED: "+comune);
}
transaction.commit();
session.close();
System.out.println("Done");
}
}
hibernate.cfg.xml:
-----------------------------------------------------------------------------------------
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">org.gjt.mm.mysql.Driver</property>
<property name="hibernate.connection.password">root</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="show_sql">true</property>
<mapping resource="Stato.hbm.xml"/>
<mapping resource="Provincia.hbm.xml"/>
<mapping resource="Comune.hbm.xml"/>
</session-factory>
</hibernate-configuration>
Comune.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">
<!-- Generated 1-ago-2006 14.38.22 by Hibernate Tools 3.1.0.beta4 -->
<hibernate-mapping>
<class name="cosm.example.component.stradario.ent.pom.plc.Comune" table="comune" catalog="test">
<composite-id name="id" class="cosm.example.component.stradario.ent.pom.plc.ComuneId">
<key-property name="id" type="string">
<column name="id" length="5" />
</key-property>
<key-property name="provId" type="string">
<column name="prov_id" length="5" />
</key-property>
<key-property name="statoId" type="int">
<column name="stato_id" />
</key-property>
</composite-id>
<many-to-one name="stato" class="cosm.example.component.stradario.ent.pom.plc.Stato" update="false" insert="false" fetch="select">
<column name="stato_id" not-null="true" />
</many-to-one>
<many-to-one name="provincia" class="cosm.example.component.stradario.ent.pom.plc.Provincia" update="false" insert="false" fetch="select">
<column name="stato_id" not-null="true" />
<column name="prov_id" length="5" not-null="true" />
</many-to-one>
<property name="nome" type="string">
<column name="nome" />
</property>
</class>
</hibernate-mapping>
Provincia.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">
<!-- Generated 1-ago-2006 14.38.22 by Hibernate Tools 3.1.0.beta4 -->
<hibernate-mapping>
<class name="cosm.example.component.stradario.ent.pom.plc.Provincia" table="provincia" catalog="test">
<composite-id name="id" class="cosm.example.component.stradario.ent.pom.plc.ProvinciaId">
<key-property name="id" type="string">
<column name="id" length="5" />
</key-property>
<key-property name="statoId" type="int">
<column name="stato_id" />
</key-property>
</composite-id>
<many-to-one name="stato" class="cosm.example.component.stradario.ent.pom.plc.Stato" update="false" insert="false" fetch="select">
<column name="stato_id" not-null="true" />
</many-to-one>
<property name="nome" type="string">
<column name="nome" />
</property>
<set name="comunes" inverse="true">
<key>
<column name="stato_id" not-null="true" />
<column name="prov_id" length="5" not-null="true" />
</key>
<one-to-many class="cosm.example.component.stradario.ent.pom.plc.Comune" />
</set>
</class>
</hibernate-mapping>
Stato.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">
<!-- Generated 1-ago-2006 14.38.22 by Hibernate Tools 3.1.0.beta4 -->
<hibernate-mapping>
<class name="cosm.example.component.stradario.ent.pom.plc.Stato" table="stato" catalog="test">
<id name="id" type="int">
<column name="id" />
<generator class="assigned" />
</id>
<property name="nome" type="string">
<column name="nome" />
</property>
<set name="provincias" inverse="true">
<key>
<column name="stato_id" not-null="true" />
</key>
<one-to-many class="cosm.example.component.stradario.ent.pom.plc.Provincia" />
</set>
<set name="comunes" inverse="true">
<key>
<column name="stato_id" not-null="true" />
</key>
<one-to-many class="cosm.example.component.stradario.ent.pom.plc.Comune" />
</set>
</class>
</hibernate-mapping>
Here the complete log plus stack trace:
-----------------------------------------------------------------------------------------
Code:
Test start
Hibernate: select stato0_.id as id0_0_, stato0_.nome as nome0_0_ from test.stato stato0_ where stato0_.id=?
stato: (13, STATO 13)
Hibernate: select provincia0_.id as id1_0_, provincia0_.stato_id as stato2_1_0_, provincia0_.nome as nome1_0_ from test.provincia provincia0_ where provincia0_.id=? and provincia0_.stato_id=?
provincia: (13, STATO 13, PR_01, PROVINCIA 01)
Hibernate: select comune0_.id as id2_0_, comune0_.prov_id as prov2_2_0_, comune0_.stato_id as stato3_2_0_, comune0_.nome as nome2_0_ from test.comune comune0_ where comune0_.id=? and comune0_.prov_id=? and comune0_.stato_id=?
org.hibernate.exception.GenericJDBCException: could not load an entity: [cosm.example.component.stradario.ent.pom.plc.Comune#component[id,provId,statoId]{statoId=13, provId=PR_01, id=CM_01}]
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1799)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:47)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:41)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:2730)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:365)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:346)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:123)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:82)
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:862)
at org.hibernate.impl.SessionImpl.immediateLoad(SessionImpl.java:820)
at org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:62)
at org.hibernate.proxy.AbstractLazyInitializer.getImplementation(AbstractLazyInitializer.java:98)
at org.hibernate.proxy.CGLIBLazyInitializer.intercept(CGLIBLazyInitializer.java:158)
at cosm.example.component.stradario.ent.pom.plc.Comune$$EnhancerByCGLIB$$2cedec2e.getProvincia(<generated>)
at test.TestPersistence.main(TestPersistence.java:62)
Caused by: java.sql.SQLException: Invalid value for getInt() - 'PR_01' in column 2
at com.mysql.jdbc.ResultSet.getIntFromString(ResultSet.java:2419)
at com.mysql.jdbc.ResultSet.getNativeInt(ResultSet.java:3727)
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:2338)
at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:2353)
at org.hibernate.type.IntegerType.get(IntegerType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
at org.hibernate.type.ComponentType.hydrate(ComponentType.java:506)
at org.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:229)
at org.hibernate.type.ManyToOneType.hydrate(ManyToOneType.java:95)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:1899)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1372)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1300)
at org.hibernate.loader.Loader.getRow(Loader.java:1197)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:569)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
... 14 more
Exception in thread "main"
Any hint?
Is this a known bug?
Thanks a lot,
Marco.
Login
FAQ
Search
