これはなに？ †
- JPA で、カスケード削除をやりたい
- EntityManager?#remove(obj) で、カスケード削除ができることは知っている
- JPQL の DELETE 文って、カスケード削除できないの? の検証をしてみる
- 実行環境 : JDK 7 / Postgre SQL 9.2 / Eclipse Link 2.5
テーブル作成 †
-- Table: mother_tbl
-- Table: child_tbl
ALTER TABLE child_tbl DROP CONSTRAINT child_tbl_motherid_fkey;
DROP TABLE IF EXISTS child_tbl;
DROP TABLE IF EXISTS mother_tbl;
CREATE TABLE mother_tbl
(
id bigserial NOT NULL,
version timestamp,
name character varying(255),
CONSTRAINT id_mother PRIMARY KEY (id)
);
CREATE TABLE child_tbl
(
id bigserial NOT NULL,
version timestamp without time zone,
name character varying(255),
motherid bigint,
CONSTRAINT id_child PRIMARY KEY (id),
CONSTRAINT child_tbl_motherid_fkey FOREIGN KEY (motherid)
REFERENCES mother_tbl (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);
ALTER TABLE mother_tbl
OWNER TO postgres;
ALTER TABLE child_tbl
OWNER TO postgres;
INSERT INTO mother_tbl (version, name) VALUES (current_timestamp, 'm1');
INSERT INTO mother_tbl (version, name) VALUES (current_timestamp, 'm2');
INSERT INTO child_tbl (version, name, motherid)
VALUES (current_timestamp, 'c1', (SELECT id FROM mother_tbl WHERE name = 'm1'));
INSERT INTO child_tbl (version, name, motherid)
VALUES (current_timestamp, 'c2', (SELECT id FROM mother_tbl WHERE name = 'm1'));
INSERT INTO child_tbl (version, name, motherid)
VALUES (current_timestamp, 'c3', (SELECT id FROM mother_tbl WHERE name = 'm1'));
INSERT INTO child_tbl (version, name, motherid)
VALUES (current_timestamp, 'c4', (SELECT id FROM mother_tbl WHERE name = 'm2'));
INSERT INTO child_tbl (version, name, motherid)
VALUES (current_timestamp, 'c5', (SELECT id FROM mother_tbl WHERE name = 'm2'));
INSERT INTO child_tbl (version, name, motherid)
VALUES (current_timestamp, 'c6', (SELECT id FROM mother_tbl WHERE name = 'm2'));
Entity 作成 †
- MotherTbl?.java
@Entity
@Table(name = "mother_tbl")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "MotherTbl.findAll", query = "SELECT m FROM MotherTbl m"),
@NamedQuery(name = "MotherTbl.findById", query = "SELECT m FROM MotherTbl m WHERE m.id = :id"),
@NamedQuery(name = "MotherTbl.findByVersion", query = "SELECT m FROM MotherTbl m WHERE m.version = :version"),
@NamedQuery(name = "MotherTbl.findByName", query = "SELECT m FROM MotherTbl m WHERE m.name = :name")})
public class MotherTbl implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Long id;
@Version
@Column(name = "version")
private Timestamp version;
@Column(name = "name")
private String name;
@OneToMany(mappedBy = "motherid", cascade = CascadeType.ALL)
private Collection<ChildTbl> childTblCollection;
public MotherTbl() {
}
public MotherTbl(Long id) {
this.id = id;
}
以下略
- ChildTbl?.java
@Entity
@Table(name = "child_tbl")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "ChildTbl.findAll", query = "SELECT c FROM ChildTbl c"),
@NamedQuery(name = "ChildTbl.findById", query = "SELECT c FROM ChildTbl c WHERE c.id = :id"),
@NamedQuery(name = "ChildTbl.findByVersion", query = "SELECT c FROM ChildTbl c WHERE c.version = :version"),
@NamedQuery(name = "ChildTbl.findByName", query = "SELECT c FROM ChildTbl c WHERE c.name = :name")})
public class ChildTbl implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Long id;
@Version
@Column(name = "version")
private Timestamp version;
@Column(name = "name")
private String name;
@JoinColumn(name = "motherid", referencedColumnName = "id")
@ManyToOne
private MotherTbl motherid;
public ChildTbl() {
}
public ChildTbl(Long id) {
this.id = id;
}
以下略
EntityManager?#remove(obj) でカスケード削除 †
public class App {
public static void main(String[] args) {
EntityManagerFactory emf
= Persistence.createEntityManagerFactory("com.mycompany_JPAExam_jar_1.0-SNAPSHOTPU");
EntityManager em = emf.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
TypedQuery<MotherTbl> query
= em.createNamedQuery("MotherTbl.findByName", MotherTbl.class);
query.setParameter("name", "m1");
MotherTbl mother = query.getSingleResult();
em.remove(mother);
tx.commit();
}
}
- 実行ログ
[EL Fine]: sql: Connection(565816949)--SELECT id, name, version FROM mother_tbl WHERE (name = ?)
bind => [m1]
[EL Fine]: sql: Connection(565816949)--SELECT id, name, version, motherid FROM child_tbl WHERE (motherid = ?)
bind => [1]
[EL Fine]: sql: Connection(565816949)--DELETE FROM child_tbl WHERE ((id = ?) AND (version = ?))
bind => [1, 2013-10-27 22:30:48.278485]
[EL Fine]: sql: Connection(565816949)--DELETE FROM child_tbl WHERE ((id = ?) AND (version = ?))
bind => [2, 2013-10-27 22:30:48.28811]
[EL Fine]: sql: Connection(565816949)--DELETE FROM child_tbl WHERE ((id = ?) AND (version = ?))
bind => [3, 2013-10-27 22:30:48.294036]
[EL Fine]: sql: Connection(565816949)--DELETE FROM mother_tbl WHERE ((id = ?) AND (version = ?))
bind => [1, 2013-10-27 22:30:48.263532]
まぁ、順当な SQL 文が発行されてる
JPQL の DELETE 文でカスケード削除 †
public class App {
public static void main(String[] args) {
EntityManagerFactory emf
= Persistence.createEntityManagerFactory("com.mycompany_JPAExam_jar_1.0-SNAPSHOTPU");
EntityManager em = emf.createEntityManager();
EntityTransaction tx = em.getTransaction();
tx.begin();
Query query = em.createQuery("DELETE FROM MotherTbl m WHERE m.name = :name");
query.setParameter("name", "m1");
int updateRows = query.executeUpdate();
System.out.println(updateRows);
tx.commit();
}
}
- 実行ログ
[EL Fine]: sql: Connection(1541737429)--DELETE FROM mother_tbl WHERE (name = ?)
bind => [m1]
1
- 実行結果
JPQL からは、m1 しか消していないけど、CREATE TABLE child_tbl の DELETE CASCADE 指定に従って、子レコードが消された
CONSTRAINT child_tbl_motherid_fkey FOREIGN KEY (motherid)
REFERENCES mother_tbl (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
子から親への FK に ON DELETE CASCADE を指定していない場合、親への DELET 文を発行したらどうなるか? †
子から親への FK に ON DELETE CASCADE をつけてんだから em.remove(m) で、child_tbl への delete 文を発行しないで欲しい †
- org.eclipse.persistence.annotations.CascadeOnDelete? をつけると、child_tbl への delete 文発行が抑止される
public class MotherTbl implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Long id;
@Version
@Column(name = "version")
private Timestamp version;
@Column(name = "name")
private String name;
@OneToMany(mappedBy = "motherid", cascade = CascadeType.ALL)
@CascadeOnDelete
private Collection<ChildTbl> childTblCollection;
(略)
Java#Glassfish
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
res2.png
res1.png
er.png
