Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
hib 2.1.8
jdk 1.5
oracle 9.2.0.4
Hello!
I am currently implementing a bidirectional one-to-one FK association based on the many-to-one (unique) + one-to-one (property-ref) technique described in "Hib in action" (p.221-223).
Everything worked well until i stumbled over a legacy hql that failed in unit testing. Changing the hql a bit solved the problem but failing to understand why the hql failed in the first place is the reason for this letter.
The junit test case faild due to not finding any result from the generated sql so do not expect any stacktrace here... :)
Mappings for the two involved classes:
Code:
<hibernate-mapping>
<subclass
name="com.fleet.model.hibernate.VehicleUnitClassicImpl"
proxy="com.fleet.model.hibernate.VehicleUnitClassic"
extends="com.fleet.model.hibernate.VehicleUnitImpl"
discriminator-value="0">
<meta attribute="scope-class" inherit="false">public abstract</meta>
<!-- bi-directional one-to-one association to FleetGprsSetting -->
<many-to-one
name="fleetGprsSetting"
class="com.fleet.model.hibernate.FleetGprsSetting"
column="FLEET_GPRS_SETTINGS__ID"
cascade="all"
unique="true"
outer-join="true"
/>
....
</subclass>
</hibernate-mapping>
<class
name="com.fleet.model.hibernate.FleetGprsSetting"
table="FLEET_GPRS_SETTINGS"
proxy="com.fleet.model.hibernate.FleetGprsSetting"
batch-size="20">
<id
name="fleetGprsSettingId"
type="java.lang.Long"
column="FLEET_GPRS_SETTINGS__ID"
>
<generator class="sequence">
<param name="sequence">seq_fleet_gprs_settings</param>
</generator>
</id>
....
<!-- bi-directional one-to-one association to VehicleUnit -->
<one-to-one
name="vehicleUnit"
class="com.fleet.model.hibernate.VehicleUnitClassicImpl"
property-ref="fleetGprsSetting"
/>
....
</class>
</hibernate-mapping>
The modified hql and the generated sql (for the one that works):
Code:
Query q = HibernateSession.currentSession().createQuery(
" select new " + VehicleTO.class.getName() +
" (vi.vehicleInformationId," +
" vi.displayname," +
" dv.versionName," +
" gprs.reportedSwVersion," +
" gprs.reportedFwVersion," +
" gprs.dotaStatus.constantname," +
" gprs.dotaRequired)" +
" from VehicleInformation as vi," +
" FleetGprsSetting as gprs " +
" left join gprs.dotaVersion as dv," +
" VehicleUnitClassic as vu" +
" where vi.fleet = :fleetId" +
" and vu.vehicleInformation = vi" +
" and gprs = vu.fleetGprsSetting" +
" and vi.isDeleted = 0" +
" order by vi.displayname"
);
q.setParameter("fleetId", fleetId);
List<?> result = q.list();
return result.toArray(new VehicleTO[]{});
SELECT
vehicleinf0_.VEHICLE_INFORMATION__ID as x0_0_,
vehicleinf0_.DISPLAYNAME as x1_0_,
dotaversio2_.VERSION_NAME as x2_0_,
fleetg1_.REPORTED_SW_VERSION as x3_0_,
fleetg1_.REPORTED_FW_VERSION as x4_0_,
dotastatus4_.CONSTANTNAME as x5_0_,
fleetg1_.DOTA_REQUIRED as x6_0_
FROM
VEHICLE_INFORMATION vehicleinf0_,
FLEET_GPRS_SETTINGS fleetg1_ left outer join DOTA_VERSION dotaversio2_ on fleetg1_.DOTA_VERSION__ID=dotaversio2_.DOTA_VERSION__ID,
VEHICLE_UNIT vehicleuni3_,
DOTA_STATUS dotastatus4_
WHERE
vehicleuni3_.VEHICLE_UNIT_TYPE__ID in (3, 2, 0, 1) and
fleetg1_.DOTA_STATUS__ID=dotastatus4_.DOTA_STATUS__ID and
((vehicleinf0_.FLEET__ID=? )and
(vehicleuni3_.VEHICLE_INFORMATION__ID__CURRE=vehicleinf0_.VEHICLE_INFORMATION__ID )and
(fleetg1_.FLEET_GPRS_SETTINGS__ID=vehicleuni3_.FLEET_GPRS_SETTINGS__ID )and
(vehicleinf0_.IS_DELETED=0 ))
order by vehicleinf0_.DISPLAYNAME;
The original hql and its generated sql:
Code:
Query q = HibernateSession.currentSession().createQuery(
" select new " + VehicleTO.class.getName() +
" (vi.vehicleInformationId," +
" vi.displayname," +
" dv.versionName," +
" gprs.reportedSwVersion," +
" gprs.reportedFwVersion," +
" gprs.dotaStatus.constantname," +
" gprs.dotaRequired)" +
" from VehicleInformation as vi," +
" FleetGprsSetting as gprs " +
" left join gprs.dotaVersion as dv," +
" VehicleUnitClassic as vu" +
" where vi.fleet = :fleetId" +
" and vu.vehicleInformation = vi" +
" and gprs.vehicleUnit = vu" +
" and vi.isDeleted = 0" +
" order by vi.displayname"
);
q.setParameter("fleetId", fleetId);
List<?> result = q.list();
return result.toArray(new VehicleTO[]{});
SELECT
vehicleinf0_.VEHICLE_INFORMATION__ID as x0_0_,
vehicleinf0_.DISPLAYNAME as x1_0_,
dotaversio2_.VERSION_NAME as x2_0_,
fleetg1_.REPORTED_SW_VERSION as x3_0_,
fleetg1_.REPORTED_FW_VERSION as x4_0_,
dotastatus4_.CONSTANTNAME as x5_0_,
fleetg1_.DOTA_REQUIRED as x6_0_
FROM
VEHICLE_INFORMATION vehicleinf0_,
FLEET_GPRS_SETTINGS fleetg1_ left outer join DOTA_VERSION dotaversio2_ on fleetg1_.DOTA_VERSION__ID=dotaversio2_.DOTA_VERSION__ID,
VEHICLE_UNIT vehicleuni3_,
DOTA_STATUS dotastatus4_
WHERE
vehicleuni3_.VEHICLE_UNIT_TYPE__ID in (3, 2, 0, 1) and
fleetg1_.DOTA_STATUS__ID=dotastatus4_.DOTA_STATUS__ID and
((vehicleinf0_.FLEET__ID=? )and
(vehicleuni3_.VEHICLE_INFORMATION__ID__CURRE=vehicleinf0_.VEHICLE_INFORMATION__ID )and
(fleetg1_.FLEET_GPRS_SETTINGS__ID=vehicleuni3_.VEHICLE_UNIT__ID )and
(vehicleinf0_.IS_DELETED=0 ))
order by vehicleinf0_.DISPLAYNAME;
So, basically:
...changing:
Code:
" and gprs = vu.fleetGprsSetting"
into:
Code:
" and gprs.vehicleUnit = vu"
changes the generated sql from:
Code:
(fleetg1_.FLEET_GPRS_SETTINGS__ID=vehicleuni3_.FLEET_GPRS_SETTINGS__ID )
to:
Code:
(fleetg1_.FLEET_GPRS_SETTINGS__ID=vehicleuni3_.VEHICLE_UNIT__ID )
...and clearly the id's in the last sql part will never be the same.
It feels like those two hql parts should produce the same sql.
I have no problems walking the association in java in any of the directions and fetching the associated object.
Code:
Long gprsId = vu.getFleetGprsSetting().getFleetGprsSettingId();
Long vuId = gprs.getVehicleUnit().getVehicleUnitId();
If anyone can see any problems with the mapping (it must be there), or any other reason of course, that can explain this behaviour, I would be glad, because then I can start sleeping ok at nights again...
I hope I have submitted enough information, otherwise, let me know...
Best regards,
Georg
Login
FAQ
Search