Arquillian Persistence ExtensionでExcelデータを使ってみる
TweetPosted on Tuesday Jan 28, 2014 at 04:21PM in Technology
Excelで作ったデータを投入したり検証に使ったりしてみます
環境
- Arquillian Persistence Extension 1.0.0.Alpha6
- wildfly-arquillian-container-remote 8.0.0.CR1
- Arquillian 1.1.2.Final
- Hibernate 4.3.0.Final
- WildFly 8.0.0.CR1
- Eclipse Kepler SR1
- Oracle JDK7u51
- postgresql-9.3-1100.jdbc41.jar
- PostgreSQL 9.2.4
- Excel 2011
前提条件
- NativeQueryでSELECTしてみるで使った資源と環境を流用します
準備
資源の配置図
下図で選択した資源を作成または編集します
VariousTypes.java
package org.arquillian.example;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
@Entity
public class VariousTypes implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long bigint1;
@Column
private String varchar1;
@Column
@Temporal(TemporalType.DATE)
private Date date1;
@Column
@Temporal(TemporalType.TIME)
private Date time1;
@Column
@Temporal(TemporalType.TIMESTAMP)
private Date timestamp1;
@Column(precision = 7, scale = 4)
private BigDecimal numeric1;
@Column
private Double double1;
public Long getBigint1() {
return bigint1;
}
public void setBigint1(Long bigint1) {
this.bigint1 = bigint1;
}
public String getVarchar1() {
return varchar1;
}
public void setVarchar1(String varchar1) {
this.varchar1 = varchar1;
}
public Date getDate1() {
return date1;
}
public void setDate1(Date date1) {
this.date1 = date1;
}
public Date getTime1() {
return time1;
}
public void setTime1(Date time1) {
this.time1 = time1;
}
public Date getTimestamp1() {
return timestamp1;
}
public void setTimestamp1(Date timestamp1) {
this.timestamp1 = timestamp1;
}
public BigDecimal getNumeric1() {
return numeric1;
}
public void setNumeric1(BigDecimal numeric1) {
this.numeric1 = numeric1;
}
public Double getDouble1() {
return double1;
}
public void setDouble1(Double double1) {
this.double1 = double1;
}
@Override
public String toString() {
return "VariousTypes [bigint1=" + bigint1 + ", varchar1=" + varchar1 + ", date1=" + date1 + ", time1=" + time1
+ ", timestamp1=" + timestamp1 + ", numeric1=" + numeric1 + ", double1=" + double1 + "]";
}
}
orm.xml
以下のようなクエリを追加します
<named-query name="findVariousTypes">
<query><![CDATA[
SELECT
v
FROM
VariousTypes v
]]></query>
</named-query>
XlsDataSetTest.java
package org.arquillian.example;
import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;
import org.jboss.arquillian.container.test.api.Deployment;
import org.jboss.arquillian.junit.Arquillian;
import org.jboss.arquillian.persistence.ShouldMatchDataSet;
import org.jboss.arquillian.persistence.UsingDataSet;
import org.jboss.shrinkwrap.api.Archive;
import org.jboss.shrinkwrap.api.ShrinkWrap;
import org.jboss.shrinkwrap.api.asset.EmptyAsset;
import org.jboss.shrinkwrap.api.spec.WebArchive;
import org.junit.Test;
import org.junit.runner.RunWith;
@RunWith(Arquillian.class)
public class XlsDataSetTest {
@Deployment
public static Archive<?> createDeployment() {
Archive<?> a = ShrinkWrap.create(WebArchive.class, "test.war")
.addPackage(VariousTypes.class.getPackage())
.addAsResource("test-persistence.xml", "META-INF/persistence.xml")
.addAsResource("META-INF/orm.xml", "META-INF/orm.xml")
.addAsWebInfResource(EmptyAsset.INSTANCE, "beans.xml")
.addAsWebInfResource("jbossas-ds.xml");
return a;
}
@PersistenceContext
EntityManager em;
@Test
@Transactional
@UsingDataSet("datasets/xlsDataSet/variousTypes.xls")
@ShouldMatchDataSet(value = "datasets/xlsDataSet/variousTypesExpected.xls", orderBy = "bigint1")
public void select() throws Exception {
dumpEntityList(em.createNamedQuery("findVariousTypes", VariousTypes.class).getResultList());
Calendar cal = Calendar.getInstance();
cal.set(2014, 0, 2, 12, 13, 14);
cal.set(Calendar.MILLISECOND, 0);
Date newDate = cal.getTime();
VariousTypes toModify = em.find(VariousTypes.class, 1l);
toModify.setVarchar1("HogeHoge");
toModify.setDate1(newDate);
toModify.setTime1(newDate);
toModify.setTimestamp1(newDate);
toModify.setNumeric1(new BigDecimal("222.2222"));
toModify.setDouble1(1.0);
dumpEntityList(em.createNamedQuery("findVariousTypes", VariousTypes.class).getResultList());
}
protected void dumpEntityList(List<?> list){
for(Object o : list){
System.out.println(o + ", contains=" + em.contains(o));
}
}
}
variousTypes.xls
今回はMacのExcel2011で作りましたが、.xls形式（.xlsx形式でなく）で出力していれば、LibreOfficeやOpenOfficeで作ってもおそらく問題ありません。日付や時刻は文字列型で入れます。普通に入力するとシリアル値になって正常に認識してくれなくなります。こんな感じ。 
blobは少し調べた範囲ではExcel形式では対応してなさそうなのでスルーします。XML形式なら対応しているそうですが試してません[2]
variousTypesExpected.xls
こんな感じ。IDが1のデータをテストクラス内で更新してその結果を検証してみます 
テスト実行
投入データの確認
デバッガで45行目で止めて投入データを見てみます
テーブル
jpaprac=# \d varioustypes
Table "public.varioustypes"
Column | Type | Modifiers
------------+-----------------------------+-----------
bigint1 | bigint | not null
date1 | date |
double1 | double precision |
numeric1 | numeric(7,4) |
time1 | time without time zone |
timestamp1 | timestamp without time zone |
varchar1 | character varying(255) |
Indexes:
"varioustypes_pkey" PRIMARY KEY, btree (bigint1)
jpaprac=#
データ
jpaprac=# select bigint1, varchar1, date1, time1, timestamp1, numeric1, double1 from varioustypes ;
bigint1 | varchar1 | date1 | time1 | timestamp1 | numeric1 | double1
---------+----------+------------+----------+---------------------+----------+---------
1 | Hoge | 1980-01-01 | 12:00:00 | 1980-01-01 12:00:00 | 100.0001 | 0.5
2 | Hige | 1980-01-02 | 12:00:01 | 1980-01-02 12:00:01 | 100.0020 | 0.25
3 | Fuge | 1980-01-03 | 12:00:02 | 1980-01-03 12:00:02 | 100.0300 | 0.125
(3 rows)
jpaprac=#
ちゃんと入ってますね
実行結果
JUnit窓
コンソール出力
18:21:16,667 INFO [stdout] (pool-2-thread-20) Hibernate: select varioustyp0_.bigint1 as bigint1_2_, varioustyp0_.date1 as date2_2_, varioustyp0_.double1 as double3_2_, varioustyp0_.numeric1 as numeric4_2_, varioustyp0_.time1 as time5_2_, varioustyp0_.timestamp1 as timestam6_2_, varioustyp0_.varchar1 as varchar7_2_ from VariousTypes varioustyp0_ 18:21:16,670 INFO [stdout] (pool-2-thread-20) VariousTypes [bigint1=1, varchar1=Hoge, date1=1980-01-01, time1=12:00:00, timestamp1=1980-01-01 12:00:00.0, numeric1=100.0001, double1=0.5], contains=true 18:21:16,670 INFO [stdout] (pool-2-thread-20) VariousTypes [bigint1=2, varchar1=Hige, date1=1980-01-02, time1=12:00:01, timestamp1=1980-01-02 12:00:01.0, numeric1=100.0020, double1=0.25], contains=true 18:21:16,671 INFO [stdout] (pool-2-thread-20) VariousTypes [bigint1=3, varchar1=Fuge, date1=1980-01-03, time1=12:00:02, timestamp1=1980-01-03 12:00:02.0, numeric1=100.0300, double1=0.125], contains=true 18:21:16,676 INFO [stdout] (pool-2-thread-20) Hibernate: update VariousTypes set date1=?, double1=?, numeric1=?, time1=?, timestamp1=?, varchar1=? where bigint1=? 18:21:16,677 INFO [stdout] (pool-2-thread-20) Hibernate: select varioustyp0_.bigint1 as bigint1_2_, varioustyp0_.date1 as date2_2_, varioustyp0_.double1 as double3_2_, varioustyp0_.numeric1 as numeric4_2_, varioustyp0_.time1 as time5_2_, varioustyp0_.timestamp1 as timestam6_2_, varioustyp0_.varchar1 as varchar7_2_ from VariousTypes varioustyp0_ 18:21:16,678 INFO [stdout] (pool-2-thread-20) VariousTypes [bigint1=2, varchar1=Hige, date1=1980-01-02, time1=12:00:01, timestamp1=1980-01-02 12:00:01.0, numeric1=100.0020, double1=0.25], contains=true 18:21:16,678 INFO [stdout] (pool-2-thread-20) VariousTypes [bigint1=3, varchar1=Fuge, date1=1980-01-03, time1=12:00:02, timestamp1=1980-01-03 12:00:02.0, numeric1=100.0300, double1=0.125], contains=true 18:21:16,681 INFO [stdout] (pool-2-thread-20) VariousTypes [bigint1=1, varchar1=HogeHoge, date1=Thu Jan 02 12:13:14 JST 2014, time1=Thu Jan 02 12:13:14 JST 2014, timestamp1=Thu Jan 02 12:13:14 JST 2014, numeric1=222.2222, double1=1.0], contains=true
更新後のDBも覗いてみたいところですが、コンソールにJPQLから覗いた結果が出ているのでそれでよしとします
備考
- 浮動小数点の検証が気になる。丸め誤差とか。最悪テスト実行後に走らせるSQL文にUPDATEかまして端数を切り捨てるとかすれば出来そうだけど気持ち悪いなあ
- xlsのテストデータ修正後はEclipseでRefreshかけないとなぜか更新後のデータを読んでくれない
参考文献
Tags: test
