Hi, I’m a noob using Hibernate 3.1, MySQL 5 and Java 5


I have 2 tables (cars and passengers) and 2 pojos (car and passenger).
car has a 1-to-many association with passenger

the car table has the following columns:
id –PK not null
vin -a legacy PK, unique (vehicle identification number)

the passenger table has the following columns:
id —pk not null
passenger_name –a legacy PK, unique
vin -foreign key to the car table

I’d like to fetch a specific car from the database based on its vin and also fetch the associated passengers.
To do so, my HQL statement is:
“from car vehicle inner join fetch vehicle.passengers where vehicle.vin = :ms"
I also restrict the query by a particular vin : q.setString("ms", "12345678");

I *know* this query should give me 1 car with 2 passengers.
However, when I execute the query I get a null result.

If I debug and look at the generated SQL statement it is as follows (I’ve removed a lot of superfluous stuff like column aliases):

select
car.id,
p.id,
car.vin,
p.passenger_name,
p.vin,
p.vin,
p.id
from cars car
inner join passengers p on car.id=p.vin where car.vin=?

You can see that the ‘join portion’ of the SQL statement is “incorrect” in that it is trying to find equality between car.id and p.vin.

This gives a null value since vin is the legacy PK and id is “my” assigned PK value.

The statement should really be “…inner join passengers on car.vin = p.vin….”
Note that when I execute the “correct” sql statement directly I get the correct result set (i.e. it’s not the database that has bad data)

I assume that this is an issue with my hbm.xml files for car and for passenger, but I can’t figure out what is wrong with them (e.g. I’m specifying a 1-many association and specifying the key as being “vin”)

The xml files are shown below (with superfluous data removed):

Any idea as to what I’m doing wrong?
TIA!!!!

////////////////////////////////////////////////begin passenger.hbm.xml///////////////////////////
<?xml version="1.0" encoding="UTF-8"?>

<hibernate-mapping>
<class name="hibernatetest.passenger" table="passengers" >

<id name="id" column="id" type="java.lang.Long" >
<generator class="native" />
</id>

<property
name="passenger_name"
type="java.lang.String"
update="true"
insert="true"
column="passenger_name"
/>

<property
name="vin"
type="java.lang.Integer"
update="false"
insert="false"
column="vin"
/>

<many-to-one
name="car"
class="hibernatetest.car"
cascade="all"
outer-join="true"
update="true"
insert="true"
column="vin"
not-null="true"
/>
</class>
</hibernate-mapping>


//////////////////////////////////////begin car.hbm.xml file//////////////////////////////////////////////////////////
<?xml version="1.0" encoding="UTF-8"?>

<hibernate-mapping >
<class name="hibernatetest.car" table="cars" >

<id name="id" column="id" type="java.lang.Long" >
<generator class="native" />
</id>

<set
name="passengers"
lazy="false"
inverse="true"
cascade="all"
sort="unsorted"
fetch="select"
>
<key column="vin" not-null="true"/>
<one-to-many class="hibernatetest.passenger" />
</set>

<property
name="vin"
type="java.lang.Integer"
update="true"
insert="true"
column="vin" />
</class>
</hibernate-mapping>

You need to specify a property-ref on the many-to-one and key of the set in order for it to look at something other than the PK. i.e.

passenger.hbm.xml



car.hbm.xml


Note also that with a bi-directional association like you've used you need to specify inverse="true" on the set.