JPA: foreign key violation during parent entity deletion
10,050 views
Skip to first unread message
Tex
Jan 25, 2011, 6:19:53 AM1/25/11
to play-framework
Hi,
I'm playing with play, yes it is a JOY to play with it !
I've a little JPA question, when I try to delete a parent entity an
exception has always been thrown.
Here my 2 models (parent: ProductSector / child:
ProductSectorTranslation)
ProductSector.java
package models;
import play.*;
import play.db.jpa.*;
import javax.persistence.*;
import java.util.*;
@Entity(name="product_sectors")
public class ProductSector extends Model {
@OneToMany(mappedBy="productSector", cascade=CascadeType.PERSIST)
public List<ProductSectorTranslation> productSectorTranslations =
new ArrayList<ProductSectorTranslation>();
public transient String name;
public String getName() {
return getName("it");
}
public void setName(String value) {
setName("it", value);
}
public String getName(String locale) {
String result = "";
ProductSectorTranslation pst = null;
try {pst = ProductSectorTranslation.find("product_sector_id
= ? and locale = ?", this.id, locale).first();}
catch (Exception e) {}
if (pst != null) {
result = pst.name;
}
return result;
}
public void setName(String locale, String value) {
boolean localeUpdated = false;
for (ProductSectorTranslation pst : productSectorTranslations)
{
if (pst.locale.equals(locale)) {
localeUpdated = true;
pst.name = value;
break;
}
}
if (!localeUpdated) {
ProductSectorTranslation pst = new
ProductSectorTranslation();
pst.locale = locale;
pst.name = value;
pst.productSector = this;
productSectorTranslations.add(pst);
}
}
}
ProductSectorTranslation.java
package models;
import play.*;
import play.db.jpa.*;
import javax.persistence.*;
import java.util.*;
@Entity(name="product_sector_translations")
public class ProductSectorTranslation extends Model {
@ManyToOne(cascade=CascadeType.PERSIST)
@JoinColumn(name="product_sector_id")
public ProductSector productSector;
public String locale;
public String name;
}
And here the test:
BasicTest.java
@Test
public void simpleTest() {
ProductSector.deleteAll();
ProductSector ps = new ProductSector();
ps.name = "Test";
ps.save();
assertEquals(ps.name, "Test");
}
When launched the 1st time the test is ok, when launched the 2nd time
the test throws an exception (it find some data into child relation
ProductSectorTranslation and stop the deletion on parent entity
ProductSector):
1st time:
Test ok, here the log:
12:05:30,391 DEBUG ~ delete from product_sectors
12:05:30,393 DEBUG ~ insert into product_sectors (id) values (null)
12:05:30,393 DEBUG ~ call identity()
12:05:30,394 DEBUG ~ insert into product_sector_translations (id,
locale, name, product_sector_id) values (null, ?, ?, ?)
12:05:30,394 DEBUG ~ call identity()
12:05:30,419 DEBUG ~ select top ? productsec0_.id as id5_,
productsec0_.locale as locale5_, productsec0_.name as name5_,
productsec0_.product_sector_id as product4_5_ from
product_sector_translations productsec0_ where product_sector_id=? and
productsec0_.locale=?
2nd time:
Test wrong: I see:
A javax.persistence.PersistenceException has been caught,
org.hibernate.exception.ConstraintViolationException: could not
execute update query
In /test/BasicTest.java, line 10 :
ProductSector.deleteAll();
and here the log:
12:06:20,354 DEBUG ~ delete from product_sectors
12:06:20,355 WARN ~ SQL Error: -8, SQLState: 23000
12:06:20,355 ERROR ~ Violazione del vincolo di integrità
FK5DE7B8CBD31775D5 tabella: PRODUCT_SECTOR_TRANSLATIONS in statement
[delete from product_sectors]
Translation: "violazione del vincolo di integrità" => "integrity
constraint violation"
How can I delete the parent entity with automatic deletion on child
relation ?
Many thanks in advance...
I'm playing with play, yes it is a JOY to play with it !
I've a little JPA question, when I try to delete a parent entity an
exception has always been thrown.
Here my 2 models (parent: ProductSector / child:
ProductSectorTranslation)
ProductSector.java
package models;
import play.*;
import play.db.jpa.*;
import javax.persistence.*;
import java.util.*;
@Entity(name="product_sectors")
public class ProductSector extends Model {
@OneToMany(mappedBy="productSector", cascade=CascadeType.PERSIST)
public List<ProductSectorTranslation> productSectorTranslations =
new ArrayList<ProductSectorTranslation>();
public transient String name;
public String getName() {
return getName("it");
}
public void setName(String value) {
setName("it", value);
}
public String getName(String locale) {
String result = "";
ProductSectorTranslation pst = null;
try {pst = ProductSectorTranslation.find("product_sector_id
= ? and locale = ?", this.id, locale).first();}
catch (Exception e) {}
if (pst != null) {
result = pst.name;
}
return result;
}
public void setName(String locale, String value) {
boolean localeUpdated = false;
for (ProductSectorTranslation pst : productSectorTranslations)
{
if (pst.locale.equals(locale)) {
localeUpdated = true;
pst.name = value;
break;
}
}
if (!localeUpdated) {
ProductSectorTranslation pst = new
ProductSectorTranslation();
pst.locale = locale;
pst.name = value;
pst.productSector = this;
productSectorTranslations.add(pst);
}
}
}
ProductSectorTranslation.java
package models;
import play.*;
import play.db.jpa.*;
import javax.persistence.*;
import java.util.*;
@Entity(name="product_sector_translations")
public class ProductSectorTranslation extends Model {
@ManyToOne(cascade=CascadeType.PERSIST)
@JoinColumn(name="product_sector_id")
public ProductSector productSector;
public String locale;
public String name;
}
And here the test:
BasicTest.java
@Test
public void simpleTest() {
ProductSector.deleteAll();
ProductSector ps = new ProductSector();
ps.name = "Test";
ps.save();
assertEquals(ps.name, "Test");
}
When launched the 1st time the test is ok, when launched the 2nd time
the test throws an exception (it find some data into child relation
ProductSectorTranslation and stop the deletion on parent entity
ProductSector):
1st time:
Test ok, here the log:
12:05:30,391 DEBUG ~ delete from product_sectors
12:05:30,393 DEBUG ~ insert into product_sectors (id) values (null)
12:05:30,393 DEBUG ~ call identity()
12:05:30,394 DEBUG ~ insert into product_sector_translations (id,
locale, name, product_sector_id) values (null, ?, ?, ?)
12:05:30,394 DEBUG ~ call identity()
12:05:30,419 DEBUG ~ select top ? productsec0_.id as id5_,
productsec0_.locale as locale5_, productsec0_.name as name5_,
productsec0_.product_sector_id as product4_5_ from
product_sector_translations productsec0_ where product_sector_id=? and
productsec0_.locale=?
2nd time:
Test wrong: I see:
A javax.persistence.PersistenceException has been caught,
org.hibernate.exception.ConstraintViolationException: could not
execute update query
In /test/BasicTest.java, line 10 :
ProductSector.deleteAll();
and here the log:
12:06:20,354 DEBUG ~ delete from product_sectors
12:06:20,355 WARN ~ SQL Error: -8, SQLState: 23000
12:06:20,355 ERROR ~ Violazione del vincolo di integrità
FK5DE7B8CBD31775D5 tabella: PRODUCT_SECTOR_TRANSLATIONS in statement
[delete from product_sectors]
Translation: "violazione del vincolo di integrità" => "integrity
constraint violation"
How can I delete the parent entity with automatic deletion on child
relation ?
Many thanks in advance...
encod*ng
Jan 25, 2011, 7:05:09 AM1/25/11
to play-framework
Take a look at the different cascading types:
http://download.oracle.com/javaee/6/api/javax/persistence/CascadeType.html
CascadeType.REMOVE or CascadeType.ALL (perhaps with orphanRemoval) may
be your friend.
http://download.oracle.com/javaee/6/api/javax/persistence/CascadeType.html
CascadeType.REMOVE or CascadeType.ALL (perhaps with orphanRemoval) may
be your friend.
Tex
Jan 25, 2011, 8:52:24 AM1/25/11
to play-framework
Hi, thanks for your response...
I just tried the following cascading options:
cascade=CascadeType.ALL => same error
cascade=CascadeType.ALL, orphanRemoval = true => same error
cascade={CascadeType.PERSIST, CascadeType.REMOVE}, orphanRemoval =
true => same error
cascade=CascadeType.REMOVE => cannot save child relation on parent
saving operation (seems that PERSIST or ALL is mandatory)
Seems that it is impossible to delete a parent with children automatic
deletion...
Where is my fault ?
On 25 Gen, 13:05, "encod*ng" <bsv1...@yahoo.de> wrote:
> Take a look at the different cascading types:
>
> http://download.oracle.com/javaee/6/api/javax/persistence/CascadeType...
I just tried the following cascading options:
cascade=CascadeType.ALL => same error
cascade=CascadeType.ALL, orphanRemoval = true => same error
cascade={CascadeType.PERSIST, CascadeType.REMOVE}, orphanRemoval =
true => same error
cascade=CascadeType.REMOVE => cannot save child relation on parent
saving operation (seems that PERSIST or ALL is mandatory)
Seems that it is impossible to delete a parent with children automatic
deletion...
Where is my fault ?
On 25 Gen, 13:05, "encod*ng" <bsv1...@yahoo.de> wrote:
> Take a look at the different cascading types:
>
Dirk
Jan 25, 2011, 9:44:54 AM1/25/11
to play-fr...@googlegroups.com
You probably want to add something like this to your test class:
@Before
public void setUp() {
Fixtures.deleteAll();
}
@Before
public void setUp() {
Fixtures.deleteAll();
}
--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To post to this group, send email to play-fr...@googlegroups.com.
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
Tex
Jan 25, 2011, 10:30:06 AM1/25/11
to play-framework
Many thanks Dirk, now it works but it works only in test...
If I put this code into Application.index controller's method:
ProductSector.deleteAll();
...and run play in dev mode I cannot delete parents that have
associated children:
PersistenceException occured :
16:17:03,714 ERROR ~ Violazione del vincolo di integrità
referential constraint (it is correct but I think that JPA should
delete the children automatically...):
create table product_sector_translations (id bigint generated by
default as identity (start with 1), locale varchar(255), name
varchar(255), product_sector_id bigint, primary key (id))
create table product_sectors (id bigint generated by default as
identity (start with 1), primary key (id))
alter table product_sector_translations add constraint
FK5DE7B8CBD31775D5 foreign key (product_sector_id) references
product_sectors
You can copy & paste my models code and test code (in my first post)
in an empty play framework, they work without dependencies, put the
above code into Application.index controller's method and see what
happens...
Some ideas on how can I cascade delete the children on parent
deletion ?
> > play-framewor...@googlegroups.com<play-framework%2Bunsubscribe@go oglegroups.com>
> > .
If I put this code into Application.index controller's method:
ProductSector.deleteAll();
...and run play in dev mode I cannot delete parents that have
associated children:
PersistenceException occured :
org.hibernate.exception.ConstraintViolationException: could not
execute update query
The log:
execute update query
16:17:03,714 ERROR ~ Violazione del vincolo di integrità
FK5DE7B8CBD31775D5 tabella: PRODUCT_SECTOR_TRANSLATIONS in statement
[delete from product_sectors]
I take a look at DDL created from my models and I see that there is a
[delete from product_sectors]
referential constraint (it is correct but I think that JPA should
delete the children automatically...):
create table product_sector_translations (id bigint generated by
default as identity (start with 1), locale varchar(255), name
varchar(255), product_sector_id bigint, primary key (id))
create table product_sectors (id bigint generated by default as
identity (start with 1), primary key (id))
alter table product_sector_translations add constraint
FK5DE7B8CBD31775D5 foreign key (product_sector_id) references
product_sectors
You can copy & paste my models code and test code (in my first post)
in an empty play framework, they work without dependencies, put the
above code into Application.index controller's method and see what
happens...
Some ideas on how can I cascade delete the children on parent
deletion ?
> > .
Dirk
Jan 25, 2011, 10:51:08 AM1/25/11
to play-fr...@googlegroups.com
I think you need to replace CascadeType.PERSIST with CascadeType.ALL on the OneToMany, and you need to remove CascadeType.PERSIST from the ManyToOne:
public class ProductSector extends Model {
@OneToMany(mappedBy="productSector")
@Cascade(CascadeType.ALL)
// ...
public class ProductSectorTranslation extends Model {
@ManyToOne
public class ProductSector extends Model {
@Cascade(CascadeType.ALL)
public List<ProductSectorTranslation> productSectorTranslations =
new ArrayList<ProductSectorTranslation>();
new ArrayList<ProductSectorTranslation>();
public class ProductSectorTranslation extends Model {
@ManyToOne
@JoinColumn(name="product_sector_id")
public ProductSector productSector;
public ProductSector productSector;
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
Tex
Jan 25, 2011, 11:18:53 AM1/25/11
to play-framework
Many thanks Dirk but seems that this won't works.
Now my models have:
@Entity(name="product_sectors")
public class ProductSector extends Model {
@OneToMany(mappedBy="productSector", cascade=CascadeType.ALL)
constraint violation exception, I can't understand why...
Can you copy and paste my code into an empty play project and try to
understand what's wrong ? (just lauch play run and go to localhost:
9000 the code insert the record in parent and children and when it try
to remove all parent records throws the exception...)
ProductSectorTranslation.java
package models;
import play.*;
import play.db.jpa.*;
import javax.persistence.*;
import java.util.*;
@Entity(name="product_sector_translations")
public class ProductSectorTranslation extends Model {
Controller
Application.java
public class Application extends Controller {
public static void index() {
render();
}
}
Many thanks in advance Dirk...
Now my models have:
@Entity(name="product_sectors")
public class ProductSector extends Model {
public List<ProductSectorTranslation>
productSectorTranslations = new ArrayList<ProductSectorTranslation>();
productSectorTranslations = new ArrayList<ProductSectorTranslation>();
@Entity(name="product_sector_translations")
public class ProductSectorTranslation extends Model {
public class ProductSectorTranslation extends Model {
@ManyToOne
@JoinColumn(name="product_sector_id")
public ProductSector productSector;
But when I try to delete parent (ParentSector) it throws the same
@JoinColumn(name="product_sector_id")
public ProductSector productSector;
constraint violation exception, I can't understand why...
Can you copy and paste my code into an empty play project and try to
understand what's wrong ? (just lauch play run and go to localhost:
9000 the code insert the record in parent and children and when it try
to remove all parent records throws the exception...)
ProductSectorTranslation.java
package models;
import play.*;
import play.db.jpa.*;
import javax.persistence.*;
import java.util.*;
@Entity(name="product_sector_translations")
public class ProductSectorTranslation extends Model {
@ManyToOne
@JoinColumn(name="product_sector_id")
public ProductSector productSector;
@JoinColumn(name="product_sector_id")
public ProductSector productSector;
public String locale;
public String name;
}
public String name;
}
ProductSector.java
package models;
import play.*;
import play.db.jpa.*;
import javax.persistence.*;
import java.util.*;
@Entity(name="product_sectors")
public class ProductSector extends Model {
@OneToMany(mappedBy="productSector", cascade=CascadeType.ALL)
package models;
import play.*;
import play.db.jpa.*;
import javax.persistence.*;
import java.util.*;
@Entity(name="product_sectors")
public class ProductSector extends Model {
Application.java
public class Application extends Controller {
public static void index() {
render();
}
}
Many thanks in advance Dirk...
Dirk
Jan 25, 2011, 11:55:40 AM1/25/11
to play-fr...@googlegroups.com
Try ps.delete() instead of ProductSector.deleteAll()
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
Tex
Jan 25, 2011, 12:05:30 PM1/25/11
to play-framework
Yes Dirk, you're right, it works (many thanks !)
But why if I have a dozen parent entities I cannot remove all together
with a single deleteAll() statement ?
> ...
>
> leggi tutto
But why if I have a dozen parent entities I cannot remove all together
with a single deleteAll() statement ?
>
> leggi tutto
Dirk
Jan 25, 2011, 12:16:53 PM1/25/11
to play-fr...@googlegroups.com
I'm not sure why that would be, hibernate is pretty confusing for me too a lot of the time :)
--
Tex
Jan 25, 2011, 12:31:04 PM1/25/11
to play-framework
Ok Dirk, many thanks for your support, have a nice day !
> ...
>
> leggi tutto
>
> leggi tutto
Guillaume Bort
Jan 25, 2011, 4:06:03 PM1/25/11
to play-fr...@googlegroups.com
No cascade delete is only triggered when you delete an entity using
the entity manager (so when you delete a single entity using delete()
). When you use deleteAll() a batch statement is sent to the database,
and cascade delete is not propagated. I know it sucks, but it's the
standard JPA behavior.
the entity manager (so when you delete a single entity using delete()
). When you use deleteAll() a batch statement is sent to the database,
and cascade delete is not propagated. I know it sucks, but it's the
standard JPA behavior.
> --
> You received this message because you are subscribed to the Google Groups "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
>
>
--
Guillaume Bort, http://guillaume.bort.fr
For anything work-related, use g...@zenexity.fr; for everything else,
write guillau...@gmail.com
Tex
Jan 26, 2011, 4:48:07 PM1/26/11
to play-framework
Now is all clear !!!
I just watched the sources for deleteAll method and you are right... !
Many thanks Guillame and keep up the good work !
> ...
>
> leggi tutto
I just watched the sources for deleteAll method and you are right... !
Many thanks Guillame and keep up the good work !
>
> leggi tutto
Reply all
Reply to author
Forward
0 new messages