openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gokhan Ergul <gokhan.er...@telenity.com>
Subject Re: Update by query and MySQL
Date Thu, 29 Nov 2007 00:42:54 GMT
You can workaround it by setting:

openjpa.jdbc.DBDictionary = mysql(supportsCorrelatedSubselect=false)

This will force openjpa to revert to in-memory bulk operation. Be warned 
that it'll be grossly inefficient for anything beyond a few rows, as 
openjpa will select the rows matching your where clause and then issue a 
separate delete statement for each row. The only alternative is to 
fallback to plain JDBC prepared statements.

That's actually a rather nasty issue, all bulk operations involving a 
where clause against mysql fail because of that. Since I'm writing a 
very performance-aware app myself, I've dug deep into fixing it but I 
can't find a sensible way to do it. DBDictionary.toBulkOperation() 
relies on the select statement passed to it to build the final bulk 
update/delete statement, and that select always has table aliases 
embedded to it, even if where clause spans a single table as in Ognjen's 
example.

If Patrick or any openjpa veteran can offer a strategy to fix it, I'll 
gladly put in the time to implement it.

Ognjen Blagojevic wrote:
> I try to do an update by query like this
>
>  em = emf.createEntityManager();
>  em.getTransaction().begin();
>  Query q = em.createQuery(
>    "UPDATE SifTipInstitucije o SET o.nazivEngleski='someText' WHERE 
> o.idTipInstitucije = 1");
>  int updated = q.executeUpdate();
>  em.getTransaction().commit();
>  em.close();
>
> But it seems that OpenJPA (1.0.0) translates this into
>
> UPDATE sif_tip_institucije
>    SET naziv_engleski = ?
>  WHERE id_tip_institucije IN
>        (SELECT DISTINCT t0.id_tip_institucije
>           FROM sif_tip_institucije t0
>          WHERE (t0.id_tip_institucije = ?))
>
> [params=(String) someText, (long) 1]
>
> Which is not legal MySQL query... It throws the exception:
>
> You can't specify target table 'sif_tip_institucije' for update in 
> FROM clause.
>
> Is this a bug? Should I log a JIRA?
>
> Regards,
> Ognjen


Mime
View raw message