You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Frederic Jeanneau (JIRA)" <ji...@apache.org> on 2007/06/28 10:44:25 UTC
[jira] Created: (OPENJPA-270) Delete Query transformed into a
Select Query
Delete Query transformed into a Select Query
--------------------------------------------
Key: OPENJPA-270
URL: https://issues.apache.org/jira/browse/OPENJPA-270
Project: OpenJPA
Issue Type: Bug
Components: jpa, query
Affects Versions: 0.9.7
Environment: Java JRE 1.5
OpenJPA 0.9.7
IDE Eclipse
MySQL 4.1.9 DataBase (EasyPhp 1.8.0.1)
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 query 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 = InheritanceType.TABLE_PER_CLASS)
@Table(name = "PERS")
public class PersonneR implements comparable {
@Id
@Column(name = "SSID", nullable = false, length = 40)
private int SSid;
@Basic
@Column(name = "PERS_NOM", nullable = false, length = 40)
private String nom;
@Basic
@Column(name = "PERS_PRENOM", nullable = false, length = 40)
...
}
Then my Employee class :
@Entity
@Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
@Table(name = "EMPL")
public class EmployeR implements Comparable {
@EmbeddedId
private PersonneR pers;
@ManyToOne (optional = false,cascade = {CascadeType.PERSIST }) @JoinColumn(name = "NUM", unique = false, nullable = false, updatable = false, referencedColumnName = "NUM", table = "SERV")
private ServiceR affectation;
...
}
The Service class is not important but i put it here :
@Entity
@Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
@Table(name = "SERV")
public class ServiceR implements Comparable {
@Id
@Column(name = "NUM", nullable = false, length = 5)
private int numero;
@Basic
@Column(name = "SERV_NOM", nullable = false, length = 40)
private 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,affectation_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 "nom1") who had been persisted,
whith the following query :
// PersonneR pers is the pers fiel of the EmployeR object I want to delete.
Query qp = this.em.createQuery("DELETE FROM EmployeR t0 where (t0.pers=?1)");
qp.setParameter(1, pers);
int deleted = 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, t1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ? AND t0.PERS_PRENOM = ?) [params=(int) 1, (String) nom1, (String) prenom1]
2165 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616> [0 ms] spent
0 lignes effacées.
And there is a "select" where I wanted a "delete".
The JPQL query is, in my opinion, correct, and the where clause is correctly 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"...
So, if someone know how to delete by query, or why OpenJPA launch a select query, I'm waiting for their ideas, and i'm still trying to solve the problem.
thanks for your help
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Issue Commented: (OPENJPA-270) Delete Query transformed into
a Select Query
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-270?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12508870 ]
Michael Dick commented on OPENJPA-270:
--------------------------------------
Hi Frederic,
Older versions of MySQL do not support subselect statements. As a result OpenJPA translates your delete into two SQL statements, a select and then a delete based on the results of the select.
I'm assuming since you opened a JIRA report you don't see a delete. Have you gone out to the database and verified that there is an Employee with
t0.SSID = 1 AND t0.PERS_NOM = nom1 AND t0.PERS_PRENOM = prenom1? If there isn't a matching row in the database we'll never issue the delete statement.
If there is a matching row then you should see the delete statement just after the select. I ran on Informix and the sql trace looked like this :
5689 persistence-tests TRACE [main] openjpa.jdbc.SQL - <t 754724092, conn 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_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ?) [params=(int) 10, (String) DePlume]
5847 persistence-tests TRACE [main] openjpa.jdbc.SQL - <t 754724092, conn 573710898> [158 ms] spent
6128 persistence-tests TRACE [main] openjpa.jdbc.SQL - <t 754724092, conn 573710898> executing prepstmnt 1529371432 DELETE FROM MDDEMPL WHERE SSID = ? AND PERS_NOM = ? [params=(int) 10, (String) DePlume]
I took your entities and modified the table names - so it doesn't quite match up, but you can see the delete statement follows the select.
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 query 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 = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "PERS")
> public class PersonneR implements comparable {
> @Id
> @Column(name = "SSID", nullable = false, length = 40)
> private int SSid;
> @Basic
> @Column(name = "PERS_NOM", nullable = false, length = 40)
> private String nom;
> @Basic
> @Column(name = "PERS_PRENOM", nullable = false, length = 40)
> ...
> }
> Then my Employee class :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "EMPL")
> public class EmployeR implements Comparable {
> @EmbeddedId
> private PersonneR pers;
> @ManyToOne (optional = false,cascade = {CascadeType.PERSIST }) @JoinColumn(name = "NUM", unique = false, nullable = false, updatable = false, referencedColumnName = "NUM", table = "SERV")
> private ServiceR affectation;
> ...
> }
> The Service class is not important but i put it here :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "SERV")
> public class ServiceR implements Comparable {
> @Id
> @Column(name = "NUM", nullable = false, length = 5)
> private int numero;
> @Basic
> @Column(name = "SERV_NOM", nullable = false, length = 40)
> private 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,affectation_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 "nom1") who had been persisted,
> whith the following query :
> // PersonneR pers is the pers fiel of the EmployeR object I want to delete.
> Query qp = this.em.createQuery("DELETE FROM EmployeR t0 where (t0.pers=?1)");
> qp.setParameter(1, pers);
> int deleted = 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, t1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ? AND t0.PERS_PRENOM = ?) [params=(int) 1, (String) nom1, (String) prenom1]
> 2165 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616> [0 ms] spent
> 0 lignes effacées.
> And there is a "select" where I wanted a "delete".
> The JPQL query is, in my opinion, correct, and the where clause is correctly 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=1) and (t0.pers.nom=nom1) and (t0.pers.prenom=prenom1)", via setparameter(int pos,Object param), but OpenJPA said "Argument Exception : null"
> I also tried in the where clause : "('t0.pers.ssid'=1) and ('t0.pers.nom'=nom1) and ('t0.pers.prenom'=prenom1)", via setparameter(int pos,Object param), but OpenJPA do a select.
> I also tried in the where clause "(t0.ssid=1) and (t0.nom=nom1) and (t0.prenom=prenom1)" , via setparameter(int pos,Object param), but OpenJPA said that there is no ssid field in class EmployeR
> I also tried "('t0.ssid'=1) and ('t0.nom'=nom1) and ('t0.prenom'=prenom1)" , 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 select query instead of a delete, I'm waiting for their ideas, and i'm still trying to solve the problem.
> thanks for your help
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (OPENJPA-270) Delete Query transformed into a
Select Query
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-270?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick resolved OPENJPA-270.
----------------------------------
Resolution: Invalid
Looks like this issue is resolved and is working for Frederic. Please reopen the issue if I've misunderstood your comments.
> Delete Query transformed into a Select Query
> --------------------------------------------
>
> Key: OPENJPA-270
> URL: https://issues.apache.org/jira/browse/OPENJPA-270
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa, query
> Affects Versions: 0.9.7
> Environment: Java JRE 1.5
> OpenJPA 0.9.7
> IDE Eclipse
> MySQL 4.1.9 DataBase (EasyPhp 1.8.0.1)
> 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 query 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 = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "PERS")
> public class PersonneR implements comparable {
> @Id
> @Column(name = "SSID", nullable = false, length = 40)
> private int SSid;
> @Basic
> @Column(name = "PERS_NOM", nullable = false, length = 40)
> private String nom;
> @Basic
> @Column(name = "PERS_PRENOM", nullable = false, length = 40)
> ...
> }
> Then my Employee class :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "EMPL")
> public class EmployeR implements Comparable {
> @EmbeddedId
> private PersonneR pers;
> @ManyToOne (optional = false,cascade = {CascadeType.PERSIST }) @JoinColumn(name = "NUM", unique = false, nullable = false, updatable = false, referencedColumnName = "NUM", table = "SERV")
> private ServiceR affectation;
> ...
> }
> The Service class is not important but i put it here :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "SERV")
> public class ServiceR implements Comparable {
> @Id
> @Column(name = "NUM", nullable = false, length = 5)
> private int numero;
> @Basic
> @Column(name = "SERV_NOM", nullable = false, length = 40)
> private 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,affectation_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 "nom1") who had been persisted,
> whith the following query :
> // PersonneR pers is the pers fiel of the EmployeR object I want to delete.
> Query qp = this.em.createQuery("DELETE FROM EmployeR t0 where (t0.pers=?1)");
> qp.setParameter(1, pers);
> int deleted = 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, t1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ? AND t0.PERS_PRENOM = ?) [params=(int) 1, (String) nom1, (String) prenom1]
> 2165 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616> [0 ms] spent
> 0 lignes effacées.
> And there is a "select" where I wanted a "delete".
> The JPQL query is, in my opinion, correct, and the where clause is correctly 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=1) and (t0.pers.nom=nom1) and (t0.pers.prenom=prenom1)", via setparameter(int pos,Object param), but OpenJPA said "Argument Exception : null"
> I also tried in the where clause : "('t0.pers.ssid'=1) and ('t0.pers.nom'=nom1) and ('t0.pers.prenom'=prenom1)", via setparameter(int pos,Object param), but OpenJPA do a select.
> I also tried in the where clause "(t0.ssid=1) and (t0.nom=nom1) and (t0.prenom=prenom1)" , via setparameter(int pos,Object param), but OpenJPA said that there is no ssid field in class EmployeR
> I also tried "('t0.ssid'=1) and ('t0.nom'=nom1) and ('t0.prenom'=prenom1)" , 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 select query instead of a delete, I'm waiting for their ideas, and i'm still trying to solve the problem.
> thanks for your help
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-270) Delete Query transformed into a
Select Query
Posted by "Frederic Jeanneau (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-270?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Frederic Jeanneau updated OPENJPA-270:
--------------------------------------
Description:
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 query 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 = InheritanceType.TABLE_PER_CLASS)
@Table(name = "PERS")
public class PersonneR implements comparable {
@Id
@Column(name = "SSID", nullable = false, length = 40)
private int SSid;
@Basic
@Column(name = "PERS_NOM", nullable = false, length = 40)
private String nom;
@Basic
@Column(name = "PERS_PRENOM", nullable = false, length = 40)
...
}
Then my Employee class :
@Entity
@Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
@Table(name = "EMPL")
public class EmployeR implements Comparable {
@EmbeddedId
private PersonneR pers;
@ManyToOne (optional = false,cascade = {CascadeType.PERSIST }) @JoinColumn(name = "NUM", unique = false, nullable = false, updatable = false, referencedColumnName = "NUM", table = "SERV")
private ServiceR affectation;
...
}
The Service class is not important but i put it here :
@Entity
@Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
@Table(name = "SERV")
public class ServiceR implements Comparable {
@Id
@Column(name = "NUM", nullable = false, length = 5)
private int numero;
@Basic
@Column(name = "SERV_NOM", nullable = false, length = 40)
private 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,affectation_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 "nom1") who had been persisted,
whith the following query :
// PersonneR pers is the pers fiel of the EmployeR object I want to delete.
Query qp = this.em.createQuery("DELETE FROM EmployeR t0 where (t0.pers=?1)");
qp.setParameter(1, pers);
int deleted = 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, t1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ? AND t0.PERS_PRENOM = ?) [params=(int) 1, (String) nom1, (String) prenom1]
2165 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616> [0 ms] spent
0 lignes effacées.
And there is a "select" where I wanted a "delete".
The JPQL query is, in my opinion, correct, and the where clause is correctly 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=1) and (t0.pers.nom=nom1) and (t0.pers.prenom=prenom1)", via setparameter(int pos,Object param), but OpenJPA said "Argument Exception : null"
I also tried in the where clause : "('t0.pers.ssid'=1) and ('t0.pers.nom'=nom1) and ('t0.pers.prenom'=prenom1)", via setparameter(int pos,Object param), but OpenJPA do a select.
I also tried in the where clause "(t0.ssid=1) and (t0.nom=nom1) and (t0.prenom=prenom1)" , via setparameter(int pos,Object param), but OpenJPA said that there is no ssid field in class EmployeR
I also tried "('t0.ssid'=1) and ('t0.nom'=nom1) and ('t0.prenom'=prenom1)" , 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 select query instead of a delete, I'm waiting for their ideas, and i'm still trying to solve the problem.
thanks for your help
was:
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 query 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 = InheritanceType.TABLE_PER_CLASS)
@Table(name = "PERS")
public class PersonneR implements comparable {
@Id
@Column(name = "SSID", nullable = false, length = 40)
private int SSid;
@Basic
@Column(name = "PERS_NOM", nullable = false, length = 40)
private String nom;
@Basic
@Column(name = "PERS_PRENOM", nullable = false, length = 40)
...
}
Then my Employee class :
@Entity
@Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
@Table(name = "EMPL")
public class EmployeR implements Comparable {
@EmbeddedId
private PersonneR pers;
@ManyToOne (optional = false,cascade = {CascadeType.PERSIST }) @JoinColumn(name = "NUM", unique = false, nullable = false, updatable = false, referencedColumnName = "NUM", table = "SERV")
private ServiceR affectation;
...
}
The Service class is not important but i put it here :
@Entity
@Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
@Table(name = "SERV")
public class ServiceR implements Comparable {
@Id
@Column(name = "NUM", nullable = false, length = 5)
private int numero;
@Basic
@Column(name = "SERV_NOM", nullable = false, length = 40)
private 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,affectation_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 "nom1") who had been persisted,
whith the following query :
// PersonneR pers is the pers fiel of the EmployeR object I want to delete.
Query qp = this.em.createQuery("DELETE FROM EmployeR t0 where (t0.pers=?1)");
qp.setParameter(1, pers);
int deleted = 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, t1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ? AND t0.PERS_PRENOM = ?) [params=(int) 1, (String) nom1, (String) prenom1]
2165 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616> [0 ms] spent
0 lignes effacées.
And there is a "select" where I wanted a "delete".
The JPQL query is, in my opinion, correct, and the where clause is correctly 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"...
So, if someone know how to delete by query, or why OpenJPA launch a select query, I'm waiting for their ideas, and i'm still trying to solve the problem.
thanks for your help
> Delete Query transformed into a Select Query
> --------------------------------------------
>
> Key: OPENJPA-270
> URL: https://issues.apache.org/jira/browse/OPENJPA-270
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa, query
> Affects Versions: 0.9.7
> Environment: Java JRE 1.5
> OpenJPA 0.9.7
> IDE Eclipse
> MySQL 4.1.9 DataBase (EasyPhp 1.8.0.1)
> 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 query 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 = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "PERS")
> public class PersonneR implements comparable {
> @Id
> @Column(name = "SSID", nullable = false, length = 40)
> private int SSid;
> @Basic
> @Column(name = "PERS_NOM", nullable = false, length = 40)
> private String nom;
> @Basic
> @Column(name = "PERS_PRENOM", nullable = false, length = 40)
> ...
> }
> Then my Employee class :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "EMPL")
> public class EmployeR implements Comparable {
> @EmbeddedId
> private PersonneR pers;
> @ManyToOne (optional = false,cascade = {CascadeType.PERSIST }) @JoinColumn(name = "NUM", unique = false, nullable = false, updatable = false, referencedColumnName = "NUM", table = "SERV")
> private ServiceR affectation;
> ...
> }
> The Service class is not important but i put it here :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "SERV")
> public class ServiceR implements Comparable {
> @Id
> @Column(name = "NUM", nullable = false, length = 5)
> private int numero;
> @Basic
> @Column(name = "SERV_NOM", nullable = false, length = 40)
> private 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,affectation_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 "nom1") who had been persisted,
> whith the following query :
> // PersonneR pers is the pers fiel of the EmployeR object I want to delete.
> Query qp = this.em.createQuery("DELETE FROM EmployeR t0 where (t0.pers=?1)");
> qp.setParameter(1, pers);
> int deleted = 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, t1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ? AND t0.PERS_PRENOM = ?) [params=(int) 1, (String) nom1, (String) prenom1]
> 2165 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616> [0 ms] spent
> 0 lignes effacées.
> And there is a "select" where I wanted a "delete".
> The JPQL query is, in my opinion, correct, and the where clause is correctly 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=1) and (t0.pers.nom=nom1) and (t0.pers.prenom=prenom1)", via setparameter(int pos,Object param), but OpenJPA said "Argument Exception : null"
> I also tried in the where clause : "('t0.pers.ssid'=1) and ('t0.pers.nom'=nom1) and ('t0.pers.prenom'=prenom1)", via setparameter(int pos,Object param), but OpenJPA do a select.
> I also tried in the where clause "(t0.ssid=1) and (t0.nom=nom1) and (t0.prenom=prenom1)" , via setparameter(int pos,Object param), but OpenJPA said that there is no ssid field in class EmployeR
> I also tried "('t0.ssid'=1) and ('t0.nom'=nom1) and ('t0.prenom'=prenom1)" , 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 select query instead of a delete, I'm waiting for their ideas, and i'm still trying to solve the problem.
> thanks for your help
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-270) Delete Query transformed into a
Select Query
Posted by "Frederic Jeanneau (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-270?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12509028 ]
Frederic Jeanneau commented on OPENJPA-270:
-------------------------------------------
Thanks for your answer.
In fact, I have a matching row in the table, but I don't see any delete statement.
I tried this morning, and it work.
Requete : delete from EmployeR t0 where (t0.pers=?1)
param 1 : 1 : prenom1 nom1
2218 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 9144903> executing prepstmnt 15605470 SELECT t0.SSID, t0.PERS_NOM, t0.PERS_PRENOM, t1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ? AND t0.PERS_PRENOM = ?) [params=(int) 1, (String) nom1, (String) prenom1]
2218 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 9144903> [0 ms] spent
1 lignes effacées.
2250 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 21443983> executing prepstmnt 18135083 DELETE FROM EMPL WHERE SSID = ? AND PERS_NOM = ? AND PERS_PRENOM = ? [params=(int) 1, (String) nom1, (String) prenom1]
2250 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 21443983> [0 ms] spent
It seems that the row was the same but different, because I added manually the row (problem of join persistance)
Thanks for your help.
> Delete Query transformed into a Select Query
> --------------------------------------------
>
> Key: OPENJPA-270
> URL: https://issues.apache.org/jira/browse/OPENJPA-270
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa, query
> Affects Versions: 0.9.7
> Environment: Java JRE 1.5
> OpenJPA 0.9.7
> IDE Eclipse
> MySQL 4.1.9 DataBase (EasyPhp 1.8.0.1)
> 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 query 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 = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "PERS")
> public class PersonneR implements comparable {
> @Id
> @Column(name = "SSID", nullable = false, length = 40)
> private int SSid;
> @Basic
> @Column(name = "PERS_NOM", nullable = false, length = 40)
> private String nom;
> @Basic
> @Column(name = "PERS_PRENOM", nullable = false, length = 40)
> ...
> }
> Then my Employee class :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "EMPL")
> public class EmployeR implements Comparable {
> @EmbeddedId
> private PersonneR pers;
> @ManyToOne (optional = false,cascade = {CascadeType.PERSIST }) @JoinColumn(name = "NUM", unique = false, nullable = false, updatable = false, referencedColumnName = "NUM", table = "SERV")
> private ServiceR affectation;
> ...
> }
> The Service class is not important but i put it here :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "SERV")
> public class ServiceR implements Comparable {
> @Id
> @Column(name = "NUM", nullable = false, length = 5)
> private int numero;
> @Basic
> @Column(name = "SERV_NOM", nullable = false, length = 40)
> private 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,affectation_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 "nom1") who had been persisted,
> whith the following query :
> // PersonneR pers is the pers fiel of the EmployeR object I want to delete.
> Query qp = this.em.createQuery("DELETE FROM EmployeR t0 where (t0.pers=?1)");
> qp.setParameter(1, pers);
> int deleted = 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, t1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ? AND t0.PERS_PRENOM = ?) [params=(int) 1, (String) nom1, (String) prenom1]
> 2165 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616> [0 ms] spent
> 0 lignes effacées.
> And there is a "select" where I wanted a "delete".
> The JPQL query is, in my opinion, correct, and the where clause is correctly 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=1) and (t0.pers.nom=nom1) and (t0.pers.prenom=prenom1)", via setparameter(int pos,Object param), but OpenJPA said "Argument Exception : null"
> I also tried in the where clause : "('t0.pers.ssid'=1) and ('t0.pers.nom'=nom1) and ('t0.pers.prenom'=prenom1)", via setparameter(int pos,Object param), but OpenJPA do a select.
> I also tried in the where clause "(t0.ssid=1) and (t0.nom=nom1) and (t0.prenom=prenom1)" , via setparameter(int pos,Object param), but OpenJPA said that there is no ssid field in class EmployeR
> I also tried "('t0.ssid'=1) and ('t0.nom'=nom1) and ('t0.prenom'=prenom1)" , 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 select query instead of a delete, I'm waiting for their ideas, and i'm still trying to solve the problem.
> thanks for your help
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (OPENJPA-270) Delete Query transformed into a Select
Query
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-270?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick closed OPENJPA-270.
--------------------------------
> Delete Query transformed into a Select Query
> --------------------------------------------
>
> Key: OPENJPA-270
> URL: https://issues.apache.org/jira/browse/OPENJPA-270
> Project: OpenJPA
> Issue Type: Bug
> Components: jpa, query
> Affects Versions: 0.9.7
> Environment: Java JRE 1.5
> OpenJPA 0.9.7
> IDE Eclipse
> MySQL 4.1.9 DataBase (EasyPhp 1.8.0.1)
> 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 query 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 = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "PERS")
> public class PersonneR implements comparable {
> @Id
> @Column(name = "SSID", nullable = false, length = 40)
> private int SSid;
> @Basic
> @Column(name = "PERS_NOM", nullable = false, length = 40)
> private String nom;
> @Basic
> @Column(name = "PERS_PRENOM", nullable = false, length = 40)
> ...
> }
> Then my Employee class :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "EMPL")
> public class EmployeR implements Comparable {
> @EmbeddedId
> private PersonneR pers;
> @ManyToOne (optional = false,cascade = {CascadeType.PERSIST }) @JoinColumn(name = "NUM", unique = false, nullable = false, updatable = false, referencedColumnName = "NUM", table = "SERV")
> private ServiceR affectation;
> ...
> }
> The Service class is not important but i put it here :
> @Entity
> @Inheritance (strategy = InheritanceType.TABLE_PER_CLASS)
> @Table(name = "SERV")
> public class ServiceR implements Comparable {
> @Id
> @Column(name = "NUM", nullable = false, length = 5)
> private int numero;
> @Basic
> @Column(name = "SERV_NOM", nullable = false, length = 40)
> private 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,affectation_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 "nom1") who had been persisted,
> whith the following query :
> // PersonneR pers is the pers fiel of the EmployeR object I want to delete.
> Query qp = this.em.createQuery("DELETE FROM EmployeR t0 where (t0.pers=?1)");
> qp.setParameter(1, pers);
> int deleted = 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, t1.NUM, t1.SERV_NOM FROM EMPL t0 INNER JOIN SERV t1 ON t0.affectation_NUM = t1.NUM WHERE (t0.SSID = ? AND t0.PERS_NOM = ? AND t0.PERS_PRENOM = ?) [params=(int) 1, (String) nom1, (String) prenom1]
> 2165 empjpa TRACE [main] openjpa.jdbc.SQL - <t 32519825, conn 6237616> [0 ms] spent
> 0 lignes effacées.
> And there is a "select" where I wanted a "delete".
> The JPQL query is, in my opinion, correct, and the where clause is correctly 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=1) and (t0.pers.nom=nom1) and (t0.pers.prenom=prenom1)", via setparameter(int pos,Object param), but OpenJPA said "Argument Exception : null"
> I also tried in the where clause : "('t0.pers.ssid'=1) and ('t0.pers.nom'=nom1) and ('t0.pers.prenom'=prenom1)", via setparameter(int pos,Object param), but OpenJPA do a select.
> I also tried in the where clause "(t0.ssid=1) and (t0.nom=nom1) and (t0.prenom=prenom1)" , via setparameter(int pos,Object param), but OpenJPA said that there is no ssid field in class EmployeR
> I also tried "('t0.ssid'=1) and ('t0.nom'=nom1) and ('t0.prenom'=prenom1)" , 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 select query instead of a delete, I'm waiting for their ideas, and i'm still trying to solve the problem.
> thanks for your help
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.