<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="warehousePU" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>jdbc/MySQLDataSource</jta-data-source>
<class>com.mycompany.entity.Customer</class>
<class>com.mycompany.entity.Item</class>
<class>com.mycompany.entity.Order1</class>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties/>
</persistence-unit>
</persistence>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//GlassFish.org//DTD GlassFish Application Server 3.1 Resource Definitions//EN"
"http://glassfish.org/dtds/glassfish-resources_1_5.dtd">
<resources>
</resources>
CREATE TABLE `warehouse`.`customer` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`address` VARCHAR(1024) ,
PRIMARY KEY (`id`) );
CREATE TABLE `warehouse`.`item` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`price` BIGINT ,
`stock` BIGINT ,
PRIMARY KEY (`id`) );
CREATE TABLE `warehouse`.`order` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`customer_id` BIGINT NOT NULL ,
`item_id` BIGINT NOT NULL ,
`amount` BIGINT NOT NULL,
`shipdate` DATETIME NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`customer_id`) references customer(`id`),
FOREIGN KEY (`item_id`) references item(`id`) );
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.mycompany.entity;
import java.io.Serializable;
import java.util.Collection;
import javax.persistence.Basic;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;
/**
* Customer Table
* @author atsushi
*/
@Entity
@Table(name = "customer")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Customer.findAll", query = "SELECT c FROM Customer c"),
@NamedQuery(name = "Customer.findById", query = "SELECT c FROM Customer c WHERE c.id = :id"),
@NamedQuery(name = "Customer.findByName", query = "SELECT c FROM Customer c WHERE c.name = :name"),
@NamedQuery(name = "Customer.findByAddress", query = "SELECT c FROM Customer c WHERE c.address = :address")})
public class Customer implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Long id;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 255)
@Column(name = "name")
private String name;
@Size(max = 1024)
@Column(name = "address")
private String address;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "customerId")
private Collection<Order1> order1Collection;
public Customer() {
}
public Customer(Long id) {
this.id = id;
}
public Customer(Long id, String name) {
this.id = id;
this.name = name;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@XmlTransient
public Collection<Order1> getOrder1Collection() {
return order1Collection;
}
public void setOrder1Collection(Collection<Order1> order1Collection) {
this.order1Collection = order1Collection;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Customer)) {
return false;
}
Customer other = (Customer) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "com.mycompany.entity.Customer[ id=" + id + " ]";
}
}
@Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Basic(optional = false) @Column(name = "id") private Long id;
CREATE TABLE xxx_tbl (id long AUTO_INCREMENT, col_name2 data_type2, ..., INDEX(id));
CREATE TABLE xxx_tbl (id bigserial PRIMARY KEY, col-name2 data_type2, ...)
@Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="シーケンステーブル名") @SequenceGenerator(name="シーケンステーブル名", sequenceName="シーケンステーブル名", allocationSize=1) @Column(name="id", nullable = false, insertable = false, updatable = false) private Long id;http://otn.oracle.co.jp/forum/thread.jspa?threadID=35001797
@OneToMany(cascade = CascadeType.ALL, mappedBy = "customerId") private Collection<Order1> order1Collection;
| CascadeType?.ALL | 全て |
| CascadeType?.PERSIST | 親Entity が em.persist(customer) で永続化されるときに、子Entity にもそれを波及させる |
| CascadeType?.REMOVE | 親Entity が em.remove(customer) で削除されるときに、子Entity にもそれを波及させる |
| CascadeType?.MERGE | 親Entity が em.merge(customer) でマージされるときに、子Entity にもそれを波及させる |
| CascadeType?.REFRESH | 親Entity が em.refresh(cusotmer) でリフレッシュされるときに、子Entity にもそれを波及させる |
| CascadeType?.DETACH | 親Entity が em.detach(cusotmer), em.clerar() で分離されるときに、子Entity にもそれを波及させる |
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.mycompany.entity;
import java.io.Serializable;
import java.math.BigInteger;
import java.util.Collection;
import javax.persistence.Basic;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;
/**
*
* @author atsushi
*/
@Entity
@Table(name = "item")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Item.findAll", query = "SELECT i FROM Item i"),
@NamedQuery(name = "Item.findById", query = "SELECT i FROM Item i WHERE i.id = :id"),
@NamedQuery(name = "Item.findByName", query = "SELECT i FROM Item i WHERE i.name = :name"),
@NamedQuery(name = "Item.findByPrice", query = "SELECT i FROM Item i WHERE i.price = :price"),
@NamedQuery(name = "Item.findByStock", query = "SELECT i FROM Item i WHERE i.stock = :stock")})
public class Item implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Long id;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 255)
@Column(name = "name")
private String name;
@Column(name = "price")
private BigInteger price;
@Column(name = "stock")
private BigInteger stock;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "itemId")
private Collection<Order1> order1Collection;
public Item() {
}
public Item(Long id) {
this.id = id;
}
public Item(Long id, String name) {
this.id = id;
this.name = name;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public BigInteger getPrice() {
return price;
}
public void setPrice(BigInteger price) {
this.price = price;
}
public BigInteger getStock() {
return stock;
}
public void setStock(BigInteger stock) {
this.stock = stock;
}
@XmlTransient
public Collection<Order1> getOrder1Collection() {
return order1Collection;
}
public void setOrder1Collection(Collection<Order1> order1Collection) {
this.order1Collection = order1Collection;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Item)) {
return false;
}
Item other = (Item) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "com.mycompany.entity.Item[ id=" + id + " ]";
}
}
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.mycompany.entity;
import java.io.Serializable;
import java.util.Date;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.validation.constraints.NotNull;
import javax.xml.bind.annotation.XmlRootElement;
/**
*
* @author atsushi
*/
@Entity
@Table(name = "order")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Order1.findAll", query = "SELECT o FROM Order1 o"),
@NamedQuery(name = "Order1.findById", query = "SELECT o FROM Order1 o WHERE o.id = :id"),
@NamedQuery(name = "Order1.findByAmount", query = "SELECT o FROM Order1 o WHERE o.amount = :amount"),
@NamedQuery(name = "Order1.findByShipdate", query = "SELECT o FROM Order1 o WHERE o.shipdate = :shipdate")})
public class Order1 implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Long id;
@Basic(optional = false)
@NotNull
@Column(name = "amount")
private long amount;
@Basic(optional = false)
@NotNull
@Column(name = "shipdate")
@Temporal(TemporalType.TIMESTAMP)
private Date shipdate;
@JoinColumn(name = "item_id", referencedColumnName = "id")
@ManyToOne(optional = false)
private Item itemId;
@JoinColumn(name = "customer_id", referencedColumnName = "id")
@ManyToOne(optional = false)
private Customer customerId;
public Order1() {
}
public Order1(Long id) {
this.id = id;
}
public Order1(Long id, long amount, Date shipdate) {
this.id = id;
this.amount = amount;
this.shipdate = shipdate;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public long getAmount() {
return amount;
}
public void setAmount(long amount) {
this.amount = amount;
}
public Date getShipdate() {
return shipdate;
}
public void setShipdate(Date shipdate) {
this.shipdate = shipdate;
}
public Item getItemId() {
return itemId;
}
public void setItemId(Item itemId) {
this.itemId = itemId;
}
public Customer getCustomerId() {
return customerId;
}
public void setCustomerId(Customer customerId) {
this.customerId = customerId;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Order1)) {
return false;
}
Order1 other = (Order1) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "com.mycompany.entity.Order1[ id=" + id + " ]";
}
}
public class ComplrexId {
private String name;
private String age;
//… getter, setter, queals, hashcode …
}
@Entity
public class Account {
@EmbeddedId
private ComprexId id;
//… getter, setter, queals, hashcode …
}
public class ComplrexId {
private String name;
private String age;
//… getter, setter, queals, hashcode …
}
@Entity
@IdClass(ComprexId.class)
public class Account {
@Id
private String name;
@Id
private String age;
//… getter, setter, queals, hashcode …
}
public enum STATE {
ON,
OFF,
ACTIVE,
PASSIVE
}
@Entity
public class Sensor {
@Id
private long id;
@Enumerated
priavte STATE state;
}
@Entity
@Cacheable(true)
public class Item {
...
}
で、Entity が EntityManager? 内にキャッシュされるようになる
EntityManagerFactory emf = Persistence.createEntityManagerFactory("warehousePU");
Cache cache = emf.getCache();
if (cache.contains(Item.class, 1234L)) {
cache.evict(Item.class, 1234L);
}
Item 全部なら
cache.evict(Item.class)全部なら
cache.evict()
| ALL | 全ての Entity をキャッシュ |
| DISABLE_SELECTIVE | @Cacheable(false) 指定した Entity 以外の全ての Entity をキャッシュ |
| ENABLE_SELECTIVE | @Cacheable(true) 指定した全ての Entity をキャッシュ |
| NONE | キャッシュしない |
| UNSPECIFIED | コンテナ依存(拡張用の予備フラグ) |

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="appPU" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>jdbc/sample</jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<shared-cache-mode>NONE</shared-cache-mode>
<properties>
<!-- OFF,SEVERE,WARNING,INFO,CONFIG,FINE,FINER,FINEST,ALL -->
<property name="eclipselink.logging.level" value="FINE" />
<property name="eclipselink.logging.timestamp" value="false" />
<property name="eclipselink.logging.session" value="false" />
<property name="eclipselink.logging.thread" value="false" />
</properties>
</persistence-unit>
</persistence>
<dependencies>
...
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.9.1.0</version>
<scope>test</scope>
</dependency>
</dependencies>
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="warehouseTestPU" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<class>com.mycompany.entity.Customer</class>
<class>com.mycompany.entity.Item</class>
<class>com.mycompany.entity.Order1</class>
<properties>
<property name="eclipselink.target-database" value="Derby"/>
<property name="javax.persistence.jdbc.driber" value="org.apache.derby.jdbc.ClientDriver"/>
<property name="javax.persistence.jdbc.url" value="jdbc:derby:/tmp/testdb;create=true"/>
<property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
<!-- OFF,SEVERE,WARNING,INFO,CONFIG,FINE,FINER,FINEST,ALL -->
<property name="eclipselink.logging.level" value="FINE" />
<property name="eclipselink.logging.timestamp" value="false" />
<property name="eclipselink.logging.session" value="false" />
<property name="eclipselink.logging.thread" value="false" />
</properties>
</persistence-unit>
</persistence>
Customer Item Order1
<?xml version="1.0" encoding="UTF-8"?>
<item>
<name>こんぼう</name>
<stock>255</stock>
<price>100</price>
</item>
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.mycompany.test.entity;
import com.mycompany.entity.Item;
import java.io.File;
import java.math.BigInteger;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Unmarshaller;
import org.junit.BeforeClass;
import org.junit.Test;
import static org.junit.Assert.assertThat;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.equalTo;
/**
*
* @author atsushi
*/
public class ItemTest {
private static JAXBContext jaxbContext;
@BeforeClass
public static void setUpClass() throws JAXBException {
jaxbContext = JAXBContext.newInstance("com.mycompany.entity");
}
@Test
public void testCreateItem() throws JAXBException {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("warehouseTestPU");
EntityManager em = emf.createEntityManager();
EntityTransaction tx = em.getTransaction();
// JAXBでXMLを読み込んでItemを作成し、RDBに登録
Unmarshaller unmarshaller = jaxbContext.createUnmarshaller();
Item item1 = (Item) unmarshaller.unmarshal(new File("src/test/resources/item1.xml"));
tx.begin();
em.persist(item1);
tx.commit();
// 全件検索
Query query = em.createNamedQuery("Item.findAll");
List<Item> items = query.getResultList();
// 検索結果の検証
assertThat(items.size(), is(equalTo(1)));
assertThat(items.get(0).getId(), is(equalTo(1L))); // Auto Incremet
assertThat(items.get(0).getName(), is(equalTo("こんぼう")));
assertThat(items.get(0).getPrice(), is(equalTo(new BigInteger("100"))));
assertThat(items.get(0).getStock(), is(equalTo(new BigInteger("255"))));
em.close();
emf.close();
}
}

| void persist(Object entity) | インスタンスを管理対象にする。flush() で RDB に挿入される |
| <T> find(Class<T> entityClass, Object primaryKey) | 主キーを指定して、RDB からデータを取得する |
| <T> getReference(Class<T> entityClass, Object primaryKey) | 主キーを指定して、RDB からデータを取得する。中身は遅延ロード |
| void remove(Object entity) | エンティティを RDB から削除する |
| <T> T merge(T entity) | 分離状態のエンティティを EM 管理状態にする。flush() で RDB が更新される |
| void refresh(Object entity) | エンティティの状態を RDB の内容で上書きする |
| void flush() | EM 管理下のエンティティの内容と RDB の状態を同期する。DBで採番された値(Postgresql の BIGSERIAL や Oracle の SEQUENCE)がトランザクション中で必要な場合に flush() すると、採番値が Entity Bean に反映される (cf.補足) |
| void clear() | EM 管理下の全てのエンティティを、管理外(分離状態)にする。flush() で RDB が更新されなくなる |
| void detach(Object entity) | EM 管理下の全ての entity を、管理外(分離状態)にする。flush() で RDB が更新されなくなる |
| boolean contains(Object entity) | entity が、EM 管理下であるかどうかを検証する |
UserEntity user = new UserEntity();
user.setId(null);
user.setName("Sae");
user.setSex("female");
user.setAge(28);
em.persist( user ); // INSERT 文発行で、RDB の USER_TABLE.ID は自動採番されたが、Java の user.id には反映されていない
em.flush(); // RDB の USER_TABLE の内容を Java の user オブジェクトに反映
return user.getId(); // トランザクションがコンテナ管理なので、return 後に COMMIT 発行基本的に、Entity クラスに名前付き Query として定義して、Entity クラスのテストクラスで単体テストする。
Query query = EntityManager#create***Query( … );
query.setParameter("city", req.getName());
query.setParameter("deliveryFrom", req.getFrom());
query.setParameter("deliveryTo", req.getTo());
query.setLockMode( … );
Item item = query.getSingleResult();
| オススメ | Entity Manager のメソッド | 概要 | |
| × | Query | em.createNamedQuery?(String name) | 名前付き Query を指定 |
| ○ | TypedQuery?<T> | em.createNamedQuery?(String name, Class<T> resultClass) | 名前付き Query を指定。返値の型指定版。JPQLはEntity の持ち物にして、業務ロジック内に書かない方が後々不幸を招かないと思う |
| × | Query | em.createNativeQuery?(String sql) | sql文指定 |
| △ | Query | em.createNativeQuery?(String sql, Class<T> resultClass) | sql文指定。返値の型指定版。PL/SQL 呼び出しなど |
| × | Query | em.createQuery(String jpql) | jpql直接指定 |
| △ | TypedQuery?<T> | em.createQuery(String jpql, Class<T> resultClass) | jpql直接指定。返値の型指定版。どうしても名前付き Query を使えないときに使う。業務ロジック内で WHERE 句に指定する項目を動的に変更しなければいけないときなど |
| × | TypedQuery?<T> | em.createQuery(CriterialQuery?<T> criterialQuery) | Criterial API を利用したクエリ。可読性悪し |
@Entity
@Table(name = "order")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Order1.findAll", query = "SELECT o FROM Order1 o"),
@NamedQuery(name = "Order1.findById", query = "SELECT o FROM Order1 o WHERE o.id = :id"),
@NamedQuery(name = "Order1.findByAmount", query = "SELECT o FROM Order1 o WHERE o.amount = :amount"),
@NamedQuery(name = "Order1.findByShipdate", query = "SELECT o FROM Order1 o WHERE o.shipdate = :shipdate")})
public class Order1 implements Serializable {
public static final String FIND_ALL = "Order1.findAll";
public static final String FIND_BY_ID = "Order1.findById";
public static final String FIND_BY_AMOUNT = "Order1.findByAmount";
public static final String FIND_BY_SHIPDATE = "Order1.findByShipdate";
...
SELECT o. FROM Order1 o WHERE o.customer.id = :id ORDER BY o.country DESC
SELECT o.customer.name, count(o) FROM Order1 o GROUP BY o.customer.id HAVING o.customer.country = :country
SELECT NEW com.foo.SomeBean(o.x, o.y, o.z) FROM OBJ o WHERE o.id = :id
SELECT CASE WHEN o.customer.vipflag = '1' THEN o.item.price * 0.8 ELSE o.item.price END FROM Order1 o得意先の場合は 20% 引き
SELECT c FROM Customer c WHERE c.creditLimit < (SELECT SUM(o.price) FROM Order1 o WHERE o.customer = c)
受注額が与信枠を超えている顧客の一覧。
JPQL副問い合わせの WHERE 句が Object 同士の比較なことに注意。発行される SQL 文は、order.customerId = customer.id とか FK,PK の比較に展開される。
DELETE FROM Order1 o WHERE o.shipdate < :date
ある日付以前の受注を削除
UPDATE Customer c SET c.creditLimit = 1000000 WHERE c.group = :group
指定グループの顧客の与信枠を 100万円にする
SELECT * FROM User u WHERE (:name = "" OR u.name = :name) AND (:sex = "" OR u.sex = :sex) AND (:age = "" OR u.age = :age)
| PROS | CONS | ||
| 対処案1 | 実行時に JPQL を動的に組み上げる | コードがわかりやすい | 実行速度が遅い(Named Query より 10倍遅い) |
| 対処案2 | Criteria を使う | 実行速度が早い | コードがわかりにくい |
| 対処案3 | 空文字か一致する時 True になる RDBのストアードプロシージャ を作る | コードわかりやすく、実行速度早い | FUNCTION句はJPA2.1(JavaEE7)から |
// 1. データ取得
SELECT flag, version FROM ORDER WHERE ID=...
var v0 = version;
:
: 2.業務処理
:
// 3. 他の人が更新していないか確認
SELECT version FROM ORDER WHERE ID=...
if (v0 < version) {
throw new OptimisticException();
}
// 4. 更新処理
UPDATE ORDER
SET flag = shipped, version = (v0+1)
WHERE ID = ...CREATE TABLE `warehouse`.`customer_table` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`address` VARCHAR(1024) ,
`version` BIGINT ,
PRIMARY KEY (`id`) );
CREATE TABLE `warehouse`.`item_table` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`price` BIGINT ,
`stock` BIGINT ,
`version` BIGINT ,
PRIMARY KEY (`id`) );
CREATE TABLE `warehouse`.`order_table` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`customer_id` BIGINT NOT NULL ,
`item_id` BIGINT NOT NULL,
`amount` BIGINT NOT NULL,
`shipdate` DATETIME NOT NULL,
`version` BIGINT,
PRIMARY KEY (`id`),
INDEX INDEX_ORDER (`shipdate`),
FOREIGN KEY (`customer_id`) references customer_table(`id`),
FOREIGN KEY (`item_id`) references item_table(`id`) );
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.mycompany.entity;
import java.io.Serializable;
import java.util.Collection;
import javax.persistence.Basic;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Version;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;
/**
*
* @author atsushi
*/
@Entity
@Table(name = "customer_table")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "CustomerTable.findAll", query = "SELECT c FROM CustomerTable c"),
@NamedQuery(name = "CustomerTable.findById", query = "SELECT c FROM CustomerTable c WHERE c.id = :id"),
@NamedQuery(name = "CustomerTable.findByName", query = "SELECT c FROM CustomerTable c WHERE c.name = :name"),
@NamedQuery(name = "CustomerTable.findByAddress", query = "SELECT c FROM CustomerTable c WHERE c.address = :address"),
@NamedQuery(name = "CustomerTable.findByVersion", query = "SELECT c FROM CustomerTable c WHERE c.version = :version")})
public class CustomerTable implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Long id;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 255)
@Column(name = "name")
private String name;
@Size(max = 1024)
@Column(name = "address")
private String address;
@Version
@Column(name = "version")
private long version;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "customerId")
private Collection<OrderTable> orderTableCollection;
public CustomerTable() {
}
public CustomerTable(Long id) {
this.id = id;
}
public CustomerTable(Long id, String name) {
this.id = id;
this.name = name;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public long getVersion() {
return version;
}
public void setVersion(long version) {
this.version = version;
}
@XmlTransient
public Collection<OrderTable> getOrderTableCollection() {
return orderTableCollection;
}
public void setOrderTableCollection(Collection<OrderTable> orderTableCollection) {
this.orderTableCollection = orderTableCollection;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof CustomerTable)) {
return false;
}
CustomerTable other = (CustomerTable) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "com.mycompany.entity.CustomerTable[ id=" + id + " ]";
}
}
package com.mycompany.test.entity;
import com.mycompany.entity.CustomerTable;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.LockModeType;
import javax.persistence.Persistence;
import javax.xml.bind.JAXBException;
import org.junit.Test;
import static org.junit.Assert.assertThat;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.equalTo;
public class CustomerTest {
@Test
public void testCreateItem() throws JAXBException {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("warehouseTestPU");
EntityManager em = emf.createEntityManager();
EntityTransaction tx = em.getTransaction();
// 1. 顧客を作成して DB に登録
CustomerTable cust = new CustomerTable();
cust.setName("文左衛門");
cust.setAddress("江戸日本橋");
// ID と Version は指定しない
tx.begin();
em.persist(cust);
tx.commit();
// 2. 1.で登録した顧客を読み出して更新
tx.begin();
CustomerTable cust2 = em.find(CustomerTable.class, 1L);
assertThat(cust2.getId(), is(equalTo(1L))); // 顧客番号は自動採番
assertThat(cust2.getName(), is(equalTo("文左衛門")));
assertThat(cust2.getAddress(), is(equalTo("江戸日本橋")));
assertThat(cust2.getVersion(), is(equalTo(1L))); // Versionの初期値
cust2.setAddress("東京日本橋");
tx.commit(); // ← ここで楽観ロック処理。i.バージョン確認 ii.カウントアップ
// 3. 2.で更新した顧客を読み出して Version がインクリメントされているか確認
tx.begin();
CustomerTable cust3 = em.find(CustomerTable.class, 1L);
assertThat(cust3.getId(), is(equalTo(1L))); // IDは変わらない
assertThat(cust3.getName(), is(equalTo("文左衛門")));
assertThat(cust3.getAddress(), is(equalTo("東京日本橋")));
assertThat(cust3.getVersion(), is(equalTo(2L))); // Versionはカウントアップ
tx.commit();
em.close();
emf.close();
}
}
[EL Fine]: Connection(510066109)--INSERT INTO customer_table (address, name, version) VALUES (?, ?, ?)
bind => [江戸日本橋, 文左衛門, 1]
[EL Fine]: Connection(510066109)--values IDENTITY_VAL_LOCAL()
[EL Fine]: Connection(510066109)--UPDATE customer_table SET address = ?, version = ? WHERE ((id = ?) AND (version = ?))
bind => [東京日本橋, 2, 1, 1]
| ISOLATION LEVEL | 概要 | PHANTOM READ | NON-REPEATABLE READ | DIRTY READ | SPEED |
| SERIALIZABLE | トランザクションを並列実行しない | なし | なし | なし | SLOWEST |
| REPEATABLE READ | 左記を保証 | あり | なし | なし | SLOWER |
| READ COMMITTED | 左記を保証 | あり | あり | なし | FASTER |
| READ UNCOMMITTED | トランザクション間の排他制御をしない | あり | あり | あり | FASTEST |
| OPTIMISTIC | 楽観ロック。(@Versionつきのエンティティは自動的に楽観ロックになるので指定不要) |
| OPTIMISTIC_FORCE_INCREMENT | 更新しなくても @Version をカウントアップする。OneToMany? で、One を変更したときに配下の Many 側も内容は変わっていないけど @Version をカウントアップしたいときなど |
| PESSIMISTIC_READ | 読み込み時に SELECT id FROM tbl WHERE id = :id FOR UPDATE を発行 |
| PESSIMISTIC_WRITE | 書き込み時に SELECT id FROM tbl WHERE id = :id FOR UPDATE を発行 |
| PESSIMISTIC_FORCE_INCREMENT | 悲観ロック + @Version をカウントアップ |
| NONE | ロックしない |
// 1. 顧客を作成して DB に登録
CustomerTable cust = new CustomerTable();
cust.setName("文左衛門");
cust.setAddress("江戸日本橋");
// ID と Version は指定しない
tx.begin();
em.persist(cust);
tx.commit();
// 2. 1.で登録した顧客を読み出して更新
tx.begin();
CustomerTable cust2 = em.find(CustomerTable.class, 1L, LockModeType.PESSIMISTIC_READ);
//em.lock(cust2, LockModeType.PESSIMISTIC_WRITE);
cust2.setAddress("東京日本橋");
tx.commit(); // ← ここで楽観ロック処理。i.バージョン確認 ii.カウントアップ
[EL Fine]: Connection(1764839676)--INSERT INTO customer_table (address, name, version) VALUES (?, ?, ?)
bind => [江戸日本橋, 文左衛門, 1]
[EL Fine]: Connection(1764839676)--values IDENTITY_VAL_LOCAL()
[EL Fine]: Connection(1764839676)--SELECT id, address, name, version FROM customer_table WHERE (id = ?) FOR UPDATE WITH RS
bind => [1]
[EL Fine]: Connection(1764839676)--SELECT id, amount, shipdate, version, customer_id, item_id FROM order_table
WHERE (customer_id = ?)
bind => [1]
[EL Fine]: Connection(1764839676)--UPDATE customer_table SET address = ?, version = ? WHERE ((id = ?) AND (version = ?))
bind => [東京日本橋, 2, 1, 1]
// 1. 顧客を作成して DB に登録
CustomerTable cust = new CustomerTable();
cust.setName("文左衛門");
cust.setAddress("江戸日本橋");
// ID と Version は指定しない
tx.begin();
em.persist(cust);
tx.commit();
// 2. 1.で登録した顧客を読み出して更新
tx.begin();
CustomerTable cust2 = em.find(CustomerTable.class, 1L, LockModeType.PESSIMISTIC_WRITE);
//em.lock(cust2, LockModeType.PESSIMISTIC_WRITE);
cust2.setAddress("東京日本橋");
tx.commit(); // ← ここで楽観ロック処理。i.バージョン確認 ii.カウントアップ
[EL Fine]: Connection(1879115578)--INSERT INTO customer_table (address, name, version) VALUES (?, ?, ?)
bind => [江戸日本橋, 文左衛門, 1]
[EL Fine]: Connection(1879115578)--values IDENTITY_VAL_LOCAL()
[EL Fine]: Connection(1879115578)--SELECT id, address, name, version FROM customer_table WHERE (id = ?) FOR UPDATE WITH RS
bind => [1]
[EL Fine]: Connection(1879115578)--SELECT id, amount, shipdate, version, customer_id, item_id FROM order_table
WHERE (customer_id = ?)
bind => [1]
[EL Fine]: Connection(1879115578)--UPDATE customer_table SET address = ?, version = ? WHERE ((id = ?) AND (version = ?))
bind => [東京日本橋, 2, 1, 1]
Eclipselink の実装は READ も WRITE も同じ SQL 操作がされるっぽい // 1. 顧客を作成して DB に登録
CustomerTable cust = new CustomerTable();
cust.setName("文左衛門");
cust.setAddress("江戸日本橋");
// ID と Version は指定しない
tx.begin();
em.persist(cust);
tx.commit();
// 2. 1.で登録した顧客を読み出して更新
tx.begin();
CustomerTable cust2 = em.find(CustomerTable.class, 1L, LockModeType.PESSIMISTIC_WRITE);
//em.lock(cust2, LockModeType.PESSIMISTIC_WRITE);
cust2.setAddress("東京日本橋");
tx.commit(); // ← ここで楽観ロック処理。i.バージョン確認 ii.カウントアップ
[EL Fine]: Connection(1356591055)--INSERT INTO customer_table (address, name, version) VALUES (?, ?, ?)
bind => [江戸日本橋, 文左衛門, 1]
[EL Fine]: Connection(1356591055)--values IDENTITY_VAL_LOCAL()
[EL Fine]: Connection(1356591055)--SELECT id, address, name, version FROM customer_table WHERE (id = ?) FOR UPDATE WITH RS
bind => [1]
[EL Fine]: Connection(1356591055)--SELECT id, amount, shipdate, version, customer_id, item_id FROM order_table
WHERE (customer_id = ?)
bind => [1]
[EL Fine]: Connection(1356591055)--UPDATE customer_table SET address = ?, version = ? WHERE ((id = ?) AND (version = ?))
bind => [東京日本橋, 2, 1, 1]
Eclipselink の実装では Version がインクリメントされないっぽい。READ や WRITE と同じ SQL 操作がされるっぽい
CREATE TABLE `warehouse`.`customer_table` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(255) NOT NULL ,
`address` VARCHAR(1024) ,
`version` BIGINT ,
PRIMARY KEY (`id`) );
CREATE TABLE `warehouse`.`item_table` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`jancode` INT NOT NULL ,
`name` VARCHAR(255) NOT NULL ,
`price` BIGINT ,
`stock` BIGINT ,
`version` BIGINT ,
INDEX INDEX_ORDER (`jancode`),
PRIMARY KEY (`id`) );
CREATE TABLE `warehouse`.`order_table` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`customer_id` BIGINT NOT NULL ,
`item_id` BIGINT NOT NULL,
`amount` BIGINT NOT NULL,
`shipdate` DATETIME NOT NULL,
`version` BIGINT,
PRIMARY KEY (`id`),
FOREIGN KEY (`customer_id`) references customer_table(`id`),
FOREIGN KEY (`item_id`) references item_table(`id`) );
概念設計+物理設計 → OK 物理設計のみ → なんとかなる 概念設計のみ → どうにもならん