Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Message-ID: <23273809.1183052386680.JavaMail.jira@brutus>
Date: Thu, 28 Jun 2007 10:39:46 -0700 (PDT)
From: "Michael Dick (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Subject: [jira] Issue Commented: (OPENJPA-270) Delete Query transformed into
 a Select Query
In-Reply-To: <17891438.1183020265902.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/OPENJPA-270?page=3Dcom.atlassia=
n.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12508870 ]=20

Michael Dick commented on OPENJPA-270:
--------------------------------------

Hi Frederic,

Older versions of MySQL do not support subselect statements. As a result Op=
enJPA translates your delete into two SQL statements, a select and then a d=
elete based on the results of the select.=20

I'm assuming since you opened a JIRA report you don't see a delete. Have yo=
u gone out to the database and verified that there is an Employee with=20
t0.SSID =3D 1 AND t0.PERS_NOM =3D nom1 AND t0.PERS_PRENOM =3D prenom1? If t=
here isn't a matching row in the database we'll never issue the delete stat=
ement.=20

If there is a matching row then you should see the delete statement just af=
ter the select. I ran on Informix and the sql trace looked like this :=20

5689  persistence-tests  TRACE  [main] openjpa.jdbc.SQL - <t 754724092, con=
n 573710898> executing prepstmnt 437787160 SELECT t0.SSID, t0.PERS_NOM, t1.=
NUM, t1.SERV_NOM FROM MDDEMPL t0 INNER JOIN MDDSERV t1 ON t0.affectation_NU=
M =3D t1.NUM WHERE (t0.SSID =3D ? AND t0.PERS_NOM =3D ?) [params=3D(int) 10=
, (String) DePlume]
5847  persistence-tests  TRACE  [main] openjpa.jdbc.SQL - <t 754724092, con=
n 573710898> [158 ms] spent
6128  persistence-tests  TRACE  [main] openjpa.jdbc.SQL - <t 754724092, con=
n 573710898> executing prepstmnt 1529371432 DELETE FROM MDDEMPL WHERE SSID =
=3D ? AND PERS_NOM =3D ? [params=3D(int) 10, (String) DePlume]

I took your entities and modified the table names - so it doesn't quite mat=
ch up, but you can see the delete statement follows the select.=20

Hope this helps,
-Mike


> Delete Query transformed into a Select Query
> --------------------------------------------
>
> common.concepts.key: OPENJPA-270
> common.concepts.url: https://issues.apache.org/jira/browse/OPENJPA-270
>common.concepts.project: OpenJPA
>common.concepts.issuetype: Bug
>common.concepts.components: jpa, query
>    Affects Versions: 0.9.7
>    common.words.env: Java JRE 1.5
> OpenJPA 0.9.7
> IDE Eclipse
> MySQL 4.1.9 DataBase (EasyPhp 1.8.0.1)
>issue.field.reporter: Frederic Jeanneau
>
> Hello.
> I'm french, so excuse my english.
> I have a problem while trying to delete (and so update) an object.
> The JQL syntax is correct (i think), but at the executeUpdate(), the quer=
y is transformed into a select.
> First, my object :
> I have classical - test objects : employees, persons, service.
> An employee is a person affected to a service
> A service as a number and a name
> A person has a number ID, a name and a surname.
> First, the Person :
> @Embeddable
> @Inheritance(strategy =3D InheritanceType.TABLE_PER_CLASS)
> @Table(name =3D "PERS")
> public class PersonneR implements comparable {
> =09@Id
> =09@Column(name =3D "SSID", nullable =3D false, length =3D 40)
> =09private int SSid;
> =09@Basic
> =09@Column(name =3D "PERS_NOM", nullable =3D false, length =3D 40)
> =09private String nom;
> =09@Basic
> =09@Column(name =3D "PERS_PRENOM", nullable =3D false, length =3D 40)
> ...
> }
> Then my Employee class :
> @Entity
> @Inheritance (strategy =3D InheritanceType.TABLE_PER_CLASS)
> @Table(name =3D "EMPL")
> public class EmployeR implements Comparable {
> =09@EmbeddedId
> =09private PersonneR pers;
> =09@ManyToOne (optional =3D false,cascade =3D {CascadeType.PERSIST }) @Jo=
inColumn(name =3D "NUM", unique =3D false, nullable =3D false, updatable =
=3D false, referencedColumnName =3D "NUM", table =3D "SERV")
> =09private ServiceR affectation;
> ...
> }
> The Service class is not important but i put it here :
> @Entity
> @Inheritance (strategy =3D InheritanceType.TABLE_PER_CLASS)
> @Table(name =3D "SERV")
> public class ServiceR implements Comparable {
> =09@Id
> =09@Column(name =3D "NUM", nullable =3D false, length =3D 5)
> =09private int numero;
> =09@Basic
> =09@Column(name =3D "SERV_NOM", nullable =3D false, length =3D 40)
> =09private String nom;
> ...
> }
> So, then I create an Entity manager, persist an employee and service.
> I have 2 tables :
> Table "empl" for the person/employee (SSID,PERS_NOM,PERS_PRENOM,affectati=
on_NUM)
> Primary key (SSID,PERS_NOM,PERS_PRENOM) (fields of the type Person)
> Table "serv" for the service (NUM, SERV_NOM)
> Primary Key : Num
> But when I try to delete an employee (num 1, name "prenom1", surname "nom=
1") who had been persisted,
> whith the following query :
> // PersonneR pers is the pers fiel of the EmployeR object I want to delet=
e.
> Query qp =3D this.em.createQuery("DELETE FROM EmployeR t0 where (t0.pers=
=3D?1)");
> qp.setParameter(1, pers);
> int deleted =3D qp.executeUpdate();
> I have the following trace :
> 2165  empjpa  TRACE  [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616>=
 executing prepstmnt 3157607 SELECT t0.SSID, t0.PERS_NOM, t0.PERS_PRENOM, t=
1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM =
=3D t1.NUM WHERE (t0.SSID =3D ? AND t0.PERS_NOM =3D ? AND t0.PERS_PRENOM =
=3D ?) [params=3D(int) 1, (String) nom1, (String) prenom1]
> 2165  empjpa  TRACE  [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616>=
 [0 ms] spent
> 0 lignes effac=C3=A9es.
> And there is a "select" where I wanted a "delete".
> The JPQL query is, in my opinion, correct, and the where clause is correc=
tly understood by OpenJPA.
> I also tried to do as in some examples "delete t0 from EmployeR to where =
...." but OpenJPA don't want "to" before "from"...
> I also tried a native query, but OpenJPA don't want to call a native sql =
query because "it is not supported"...
> i also tried in the where clause  : "(t0.pers.ssid=3D1) and (t0.pers.nom=
=3Dnom1) and (t0.pers.prenom=3Dprenom1)", via setparameter(int pos,Object p=
aram), but OpenJPA said "Argument Exception : null"
> I also tried in the where clause  : "('t0.pers.ssid'=3D1) and ('t0.pers.n=
om'=3Dnom1) and ('t0.pers.prenom'=3Dprenom1)", via setparameter(int pos,Obj=
ect param), but OpenJPA do a select.
> I also tried in the where clause "(t0.ssid=3D1) and (t0.nom=3Dnom1) and (=
t0.prenom=3Dprenom1)" , via setparameter(int pos,Object param), but OpenJPA=
 said that there is no ssid field in class EmployeR
> I also tried "('t0.ssid'=3D1) and ('t0.nom'=3Dnom1) and ('t0.prenom'=3Dpr=
enom1)" , via setparameter(int pos,Object param),but OpenJPA do a select
> So, if someone know how to delete by query, or why OpenJPA launch a selec=
t query instead of a delete, I'm waiting for their ideas, and i'm still try=
ing to solve the problem.
> thanks for your help

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.