Hi,
Is there a way to tell JPA or TopLink essentials to translate a
particular query in a particular way for a particular database server?
In my specific case, I have the following query:
"SELECT NEW gov.bnl.irmis.service.data.Queries.QComponent(c.cmpntId,
c.serialNo, c.fieldName, c.cmpntTypeId.cmpntTypeId,
c.cmpntTypeId.cmpntTypeName) FROM Cmpnt c ORDER BY c.cmpntId"
For Mysql this gets transalated to:
SELECT t0.cmpnt_id, t0.serial_no, t0.field_name, t1.cmpnt_type_id,
t1.cmpnt_type_name FROM cmpnt t0, cmpnt_type t1 WHERE (t1.cmpnt_type_id
= t0.cmpnt_type_id) ORDER BY t0.cmpnt_id ASC
The problem is that Mysql generates a horrible query plan, and I need to
substitute it with:
SELECT t0.cmpnt_id, t0.serial_no, t0.field_name, t1.cmpnt_type_id,
t1.cmpnt_type_name FROM cmpnt t0 FORCE INDEX (PRIMARY), cmpnt_type t1
WHERE (t1.cmpnt_type_id = t0.cmpnt_type_id) ORDER BY t0.cmpnt_id ASC
which forces the use of the correct index, but I'd like to do that only
for mysql
What's the best way to achieve this?
Thanks!
Gabriele