I think that my problem is a simple matter of me, as a newbie, getting some concepts reversed. It looks to me like I am missing something that is supposed to be very obvious.
The symptom I have is that when I attempt to retrieve an object that has a collection (relates to many "child" objects, for example), Hibernate generates funky SQL and the DBMS rejects it because it has unknown column names. The column names it is seeing are property names from my classes.
My properties are entity classes, referenced by a UUID.
Hibernate version:
2.1 (downloaded this week - I believe it is 2.1.7)
Mapping documents:
TechnicianVO.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping
package="com.affinitech.aviasoft.values"
>
<!-- use schema-"aviasoft2" eventually -->
<class name="TechnicianVO" table = "TECHNICIANS">
<id
name="OID"
type="com.affinitech.aviasoft.persist.UuidUserTypeForHibernate"
column="OID"
unsaved-value="null"
access="property">
<generator class="com.affinitech.aviasoft.persist.UUIDGeneratorForHibernate"/>
</id>
<version
column="SID"
name="SID"
type="integer"
access="property"
unsaved-value="null"
/>
<property
name="effectiveDT"
column="EFFECTIVE_TS"
type="timestamp"
update="false"
insert="true"
access="property"
/>
<property
name="endDT"
column="END_TS"
type="timestamp"
update="true"
insert="false"
access="property"
/>
<property
name="role"
column="USERROLE"
type="string"
update="false"
insert="true"
access="property"
/>
<property
name="userid"
column="USERID"
type="string"
update="false"
insert="true"
access="property"
/>
<property
name="password"
column="USERPASS"
type="string"
update="false"
insert="true"
access="property"
/>
<property
name="authority"
column="AUTHORITY"
type="integer"
update="false"
insert="true"
access="property"
/>
<!--
A one way one-to-many relationship is not possible under hibernate (yet)
It is on the FAQ so it may yet happen.
In the mean time, we will need to ensure that the classes perform the
necessary loading.
-->
<property
name="contactOID"
column="CONTACT_OID"
type="com.affinitech.aviasoft.persist.UuidUserTypeForHibernate"
update="false"
insert="true"
access="property"
/>
<bag
name="careContracts"
table="TECH_CARE_CONTRACT"
cascade="none"
outer-join="true"
inverse="false"
where="endDT is null">
<key column="technician"/>
<one-to-many
class="com.affinitech.aviasoft.values.CareContractVO"
/>
</bag>
</class>
</hibernate-mapping>
CareContractVO.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping
package="com.affinitech.aviasoft.values"
>
<!-- use schema-"aviasoft2" eventually -->
<class name="CareContractVO" table = "TECH_CARE_CONTRACT">
<id
name="OID"
type="com.affinitech.aviasoft.persist.UuidUserTypeForHibernate"
column="OID"
unsaved-value="null"
access="property">
<generator class="com.affinitech.aviasoft.persist.UUIDGeneratorForHibernate"/>
</id>
<version
column="SID"
name="SID"
type="integer"
access="property"
unsaved-value="null"
/>
<property
name="effectiveDT"
column="EFFECTIVE_TS"
type="timestamp"
update="false"
insert="true"
access="property"
/>
<property
name="endDT"
column="END_TS"
type="timestamp"
update="true"
insert="false"
access="property"
/>
<property
name="role"
column="USERROLE"
type="string"
update="false"
insert="true"
access="property"
/>
<many-to-one
name="technician"
class="com.affinitech.aviasoft.values.TechnicianVO"
cascade="none"
access="property"
/>
<many-to-one
name="flock"
class="com.affinitech.aviasoft.values.FlockVO"
cascade="none"
access="property"
/>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
There are several methods very similar to this one. The ResultSet is from a foreign database, not known to Hibernate.
/**
* <p>The flock should be the last of the business entities added to
* the database. In Aviasoft 1.x, it holds relationships to treatment
* program, veterinarian, producer, farm, and so on.</p>
*
* <p>Future transformations should be less sensitive to ordering because
* of the heavy denormalization in this database.</p>
*
* @param qry
* @return
* @throws HibernateException
* @throws SQLException
*/
protected FlockVO transferFlockQueryToFlockVO (ResultSet qry) throws HibernateException, SQLException
{
CareContractVO careContract = null;
ContactVO breeder = null;
TreatmentProgramInstanceVO treatmentPgm = null;
Timestamp timestamp = new Timestamp (System.currentTimeMillis());
ProducerVO producer = (ProducerVO) producerMap.get(qry.getString ("Producer"));
String vetName = qry.getString("Veterenarian");
log.info("Looking for veterinarian named "+vetName);
// this is the statement that fails
List techs = session.find(
"select tech from TechnicianVO tech, ContactVO cont where "+
"(cont.companyName = ?) or (cont.contactName = ?)"+
" and cont.OID = tech.contactOID and cont.endDT is null",
new String [] {vetName, vetName},
new Type [] {net.sf.hibernate.Hibernate.STRING, net.sf.hibernate.Hibernate.STRING});
TechnicianVO veterinarian = (techs != null) && (techs.size() > 0)
?(TechnicianVO)techs.get(0)
:null;
FlockVO result = new FlockVO (
null,
1,
timestamp,
null,
producer,
breeder,
treatmentPgm,
qry.getString("FlockID"),
qry.getString("Breed"),
qry.getDate("HatchDate")
);
session.save (result);
if (veterinarian != null)
{
careContract = new CareContractVO (
null,
1,
timestamp,
null,
veterinarian,
result,
Constants.VETERINARIAN
);
session.save (careContract);
}
if ((log != null) && (log.isDebugEnabled()))
{
String message =
this.getClass().getName()+".transferFarmQueryToFarmVO(<<resultSet>>) - "+
"Created flock "+result.getOID().toString()+ " as "+result.getName()+" for "+producer.getPrimaryContact().getCompanyName()+".";
log.debug(message);
}
return result;
}
Full stack trace of any exception that occurs:
See log below.
Name and version of the database you are using:
Firebird 1.5.1
The generated SQL (show_sql=true):
23:34:19,550 DEBUG SQL:229 - select carecontra0_.technician as technician__, carecontra0_.OID as OID__, carecontra0_.OID as OID1_, carecontra0_.SID as SID1_, carecontra0_.EFFECTIVE_TS as EFFECTIV3_1_, carecontra0_.END_TS as END_TS1_, carecontra0_.USERROLE as USERROLE1_, carecontra0_.technician as technician1_, carecontra0_.flock as flock1_, flockvo1_.OID as OID0_, flockvo1_.SID as SID0_, flockvo1_.EFFECTIVE_TS as EFFECTIV3_0_, flockvo1_.END_TS as END_TS0_, flockvo1_.BREED as BREED0_, flockvo1_.NAME as NAME0_, flockvo1_.HATCH_DATE as HATCH_DATE0_, flockvo1_.PRODUCER_OID as PRODUCER8_0_ from TECH_CARE_CONTRACT carecontra0_ left outer join FLOCKS flockvo1_ on carecontra0_.flock=flockvo1_.OID where carecontra0_.technician=? and carecontra0_.endDT is null
Debug level Hibernate log excerpt:
23:34:19,550 DEBUG SessionImpl:3149 - initializing non-lazy collections
23:34:19,550 DEBUG SessionImpl:3295 - initializing collection [com.affinitech.aviasoft.values.TechnicianVO.careContracts#2bdf1e3a-4b36-11d9-9d31-00400506faf5]
23:34:19,550 DEBUG SessionImpl:3296 - checking second-level cache
23:34:19,550 DEBUG SessionImpl:3302 - collection not cached
23:34:19,550 DEBUG BatcherImpl:203 - about to open: 0 open PreparedStatements, 0 open ResultSets
23:34:19,550 DEBUG SQL:229 - select carecontra0_.technician as technician__, carecontra0_.OID as OID__, carecontra0_.OID as OID1_, carecontra0_.SID as SID1_, carecontra0_.EFFECTIVE_TS as EFFECTIV3_1_, carecontra0_.END_TS as END_TS1_, carecontra0_.USERROLE as USERROLE1_, carecontra0_.technician as technician1_, carecontra0_.flock as flock1_, flockvo1_.OID as OID0_, flockvo1_.SID as SID0_, flockvo1_.EFFECTIVE_TS as EFFECTIV3_0_, flockvo1_.END_TS as END_TS0_, flockvo1_.BREED as BREED0_, flockvo1_.NAME as NAME0_, flockvo1_.HATCH_DATE as HATCH_DATE0_, flockvo1_.PRODUCER_OID as PRODUCER8_0_ from TECH_CARE_CONTRACT carecontra0_ left outer join FLOCKS flockvo1_ on carecontra0_.flock=flockvo1_.OID where carecontra0_.technician=? and carecontra0_.endDT is null
Hibernate: select carecontra0_.technician as technician__, carecontra0_.OID as OID__, carecontra0_.OID as OID1_, carecontra0_.SID as SID1_, carecontra0_.EFFECTIVE_TS as EFFECTIV3_1_, carecontra0_.END_TS as END_TS1_, carecontra0_.USERROLE as USERROLE1_, carecontra0_.technician as technician1_, carecontra0_.flock as flock1_, flockvo1_.OID as OID0_, flockvo1_.SID as SID0_, flockvo1_.EFFECTIVE_TS as EFFECTIV3_0_, flockvo1_.END_TS as END_TS0_, flockvo1_.BREED as BREED0_, flockvo1_.NAME as NAME0_, flockvo1_.HATCH_DATE as HATCH_DATE0_, flockvo1_.PRODUCER_OID as PRODUCER8_0_ from TECH_CARE_CONTRACT carecontra0_ left outer join FLOCKS flockvo1_ on carecontra0_.flock=flockvo1_.OID where carecontra0_.technician=? and carecontra0_.endDT is null
23:34:19,610 DEBUG BatcherImpl:252 - preparing statement
23:34:19,830 DEBUG JDBCExceptionReporter:49 - SQL Exception
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -206
Column unknown
CARECONTRA0_.FLOCK
At line 1, column 650.
at org.firebirdsql.jdbc.AbstractPreparedStatement.<init>(AbstractPreparedStatement.java:91)
at org.firebirdsql.jdbc.FBPreparedStatement.<init>(FBPreparedStatement.java:34)
at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:682)
at org.firebirdsql.jdbc.AbstractConnection.prepareStatement(AbstractConnection.java:232)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:260)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:235)
at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:66)
at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:779)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:265)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:990)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:965)
at net.sf.hibernate.loader.OneToManyLoader.initialize(OneToManyLoader.java:93)
at net.sf.hibernate.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:288)
at net.sf.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:3303)
at net.sf.hibernate.collection.PersistentCollection.forceInitialization(PersistentCollection.java:336)
at net.sf.hibernate.impl.SessionImpl.initializeNonLazyCollections(SessionImpl.java:3156)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1553)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1530)
at com.affinitech.aviasoft.persist.Aviasoft1XImporter.transferFlockQueryToFlockVO(Aviasoft1XImporter.java:394)
at com.affinitech.aviasoft.persist.Aviasoft1XImporter.importFlocks(Aviasoft1XImporter.java:620)
at com.affinitech.aviasoft.persist.Aviasoft1XImporter.run(Aviasoft1XImporter.java:665)
at java.lang.Thread.run(Unknown Source)
Login
FAQ
Search