⇧ Oh, my gosh...
Oh～ Oh～ ともだち ずっと ともだち
ずっと ともだち いない
⇧ 友人のいない人にとっては、幸福感をどう高めたら良いのかしらね...
DBUnitでSQLファイルを実行したかったけど
⇧ 残念ながら、DBUnitはSQLファイルの実行をサポートしていないらしい...
DBUnit以外で実行するしかないのですが、Spring Frameworkを使っている場合は、spring-jdbcの依存関係を追加すれば、SQLファイルを実行できるAPIが利用できるみたいなので、試してみます。
試してみたのですが、
⇧ SQL文の中のスラッシュに対応してないっぽい...
しかも、Spring Frameworkの中の人の説明がかなり苦しい。
バグじゃないかもしれないけど、結局、スラッシュに対応してないんなら、対応してませんの一言で済むと思うんだけど、認めたくないのかね？
話が脱線しましたが、Oracle Database 19cのPDBにテーブルを作成しておきます。
そして、ビルドツールにMavenを選択し、Spring Bootプロジェクトを作成して、以下のようなファイルを配置。
ファイルの内容など。
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.7.8</version> <relativePath /> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>dbunit-example</artifactId> <version>0.0.1-SNAPSHOT</version> <name>dbunit-example</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> <optional>true</optional> </dependency> <dependency> <groupId>com.oracle.database.jdbc</groupId> <artifactId>ojdbc8</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc --> <!-- <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <scope>test</scope> </dependency> --> <!-- https://mvnrepository.com/artifact/org.dbunit/dbunit --> <dependency> <groupId>org.dbunit</groupId> <artifactId>dbunit</artifactId> <version>2.7.3</version> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.2.3</version> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.2.3</version> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
⇧ JUnitはDBUnitの依存関係に含まれるらしいので、DBUnitを依存関係に追加してれば自動的にJUnitが使えるっぽい。
あと、Spring Bootのバージョンが、3.0とかにしちゃうと、pom.xmlでJavaのバージョンを1.8（Java 8）とかにしても、実行時にはJava 17が利用されるっぽいので、実行時に、コンパイル時のJavaのバージョンと実行時のJavaのバージョン違うよってエラーになるんで要注意ですかね。
■/dbunit-example/src/test/resources/application.properties
# DB接続情報 spring.datasource.url=jdbc:oracle:thin:@//localhost:1519/orcldb_19c spring.datasource.username=ts0818 spring.datasource.password=password spring.datasource.driverClassName=oracle.jdbc.driver.OracleDriver server.address=localhost database.port=1519 pdb.service.name=orcldb_19c
■/dbunit-example/src/test/resources/db/backup_db.sql
-- PDBに接続する
ALTER session SET container=ORCLDB_19C;
/
-- バックアップテーブルが存在する場合は削除
--SET SERVEROUTPUT ON;
DECLARE
-- 削除対象のテーブル
CURSOR table_list IS
SELECT
a.table_name, a.owner
FROM
dba_tables a
WHERE
a.owner = 'TS0818' -- OWNERを指定
AND a.table_name IN ( -- テーブルを指定
'BK_ADDRESS_DETAIL'
,'BK_AUTH_INFOMATION'
,'BK_CATEGORY'
,'BK_CATEGORY_DETAIL'
,'BK_ORDER_DETAIL'
,'BK_ORDERS'
,'BK_ORDER_STATUS'
,'BK_PRODUCT'
,'BK_PRODUCT_DETAIL'
,'BK_PURCHASEORDER'
,'BK_USER_DETAIL'
,'BK_USER_LEAVE'
,'BK_USERS'
)
ORDER BY
a.table_name;
version VARCHAR2(2);
BEGIN
-- Oracleのバージョンを取得
DBMS_OUTPUT.PUT_LINE('Oracleのバージョンを確認...');
SELECT
REPLACE(SUBSTR(version ,0,2) ,'.' ,'') AS major_version
INTO version
FROM
product_component_version
WHERE
LOWER(product) LIKE 'oracle%';
DBMS_OUTPUT.PUT_LINE('バージョンは ' || version);
-- 削除処理開始
DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> START');
FOR vRec IN table_list LOOP
DBMS_OUTPUT.PUT_LINE('削除対象テーブル：' || vRec.owner || '.' || vRec.table_name);
-- DDL確認用：DROP
-- Oracleのバージョンが10g以降⇒PURGEでテーブルを完全削除
IF TO_NUMBER(version) >= 10 THEN
DBMS_OUTPUT.PUT_LINE('PURGE TABLE ' || vRec.owner || '.' || vRec.table_name);
-- DDL確認用：PURGE
EXECUTE IMMEDIATE 'DROP TABLE ' || vRec.owner || '.' || vRec.table_name || ' CASCADE CONSTRAINTS';
EXECUTE IMMEDIATE 'PURGE TABLE ' || vRec.owner || '.' || vRec.table_name;
ELSE
DBMS_OUTPUT.PUT_LINE('DROP TABLE ' || vRec.owner || '.' || vRec.table_name);
EXECUTE IMMEDIATE 'DROP TABLE ' || vRec.owner || '.' || vRec.table_name || ' CASCADE CONSTRAINTS';
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> END');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('----エラー発生！----------------');
DBMS_OUTPUT.PUT_LINE('--SQLERRM:'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('--SQLCODE:'||SQLCODE);
END;
/
-- バックアップテーブルの作成
CREATE TABLE BK_ADDRESS_DETAIL
AS
SELECT *
FROM ADDRESS_DETAIL
/
CREATE TABLE BK_AUTH_INFOMATION
AS
SELECT *
FROM AUTH_INFOMATION
/
CREATE TABLE BK_CATEGORY
AS
SELECT *
FROM CATEGORY
/
CREATE TABLE BK_CATEGORY_DETAIL
AS
SELECT *
FROM CATEGORY_DETAIL
/
CREATE TABLE BK_ORDER_DETAIL
AS
SELECT *
FROM ORDER_DETAIL
/
CREATE TABLE BK_ORDERS
AS
SELECT *
FROM ORDERS
/
CREATE TABLE BK_ORDER_STATUS
AS
SELECT *
FROM ORDER_STATUS
/
CREATE TABLE BK_PRODUCT
AS
SELECT *
FROM PRODUCT
/
CREATE TABLE BK_PRODUCT_DETAIL
AS
SELECT *
FROM PRODUCT_DETAIL
/
CREATE TABLE BK_PURCHASEORDER
AS
SELECT *
FROM PURCHASEORDER
/
CREATE TABLE BK_USER_DETAIL
AS
SELECT *
FROM USER_DETAIL
/
CREATE TABLE BK_USER_LEAVE
AS
SELECT *
FROM USER_LEAVE
/
CREATE TABLE BK_USERS
AS
SELECT *
FROM USERS
/
EXIT;
/
■/dbunit-example/src/test/java/com/example/demo/DbunitExampleApplicationTests.java
package com.example.demo;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.Properties;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dbunit.JdbcDatabaseTester;
import org.dbunit.database.AmbiguousTableNameException;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.DataSetException;
import org.dbunit.dataset.excel.XlsDataSetWriter;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInfo;
import org.springframework.jdbc.datasource.init.ScriptException;
class DbunitExampleApplicationTests {
private static Properties prop;
private static String url;
private static String user;
private static String pass;
private static String jdbcDriver;
private static Connection conn;
private static String host;
private static String dbPort;
private static String dbServiceName;
private TestInfo testInfo;
private static String EVIDENCE_DIR = Paths.get(System.getProperty("user.dir"), "evidence").toString();
private static String beforeOutputFileName = "before_output.xlsx";
private static String afterOutputFileName = "after_output.xlsx";
static {
InputStream is = null;
try {
prop = new Properties();
is = ClassLoader.class.getResourceAsStream("/application.properties");
prop.load(is);
url = getPropertyValue("spring.datasource.url");
user = getPropertyValue("spring.datasource.username");
pass = getPropertyValue("spring.datasource.password");
jdbcDriver = getPropertyValue("spring.datasource.driverClassName");
host = getPropertyValue("server.address");
dbPort = getPropertyValue("database.port");
dbServiceName = getPropertyValue("pdb.service.name");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
@BeforeEach
void init(TestInfo testInfo) {
this.testInfo = testInfo;
Path evidenceDirPath = Paths.get(EVIDENCE_DIR, this.testInfo.getDisplayName());
if (Files.notExists(evidenceDirPath)) {
try {
Files.createDirectories(evidenceDirPath);
} catch (IOException e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
}
}
EVIDENCE_DIR = evidenceDirPath.toString();
}
@BeforeEach
void backup () {
Process process = null;
try {
conn = DriverManager.getConnection(url, user, pass);
// ScriptUtils.executeSqlScript(conn, new ClassPathResource("/db/backup_db.sql"));
String sqlFilePath = ClassLoader.class.getResource("/db/backup_db.sql").getFile();
File file = new File(sqlFilePath);
StringBuilder sb = new StringBuilder();
sb.append("sqlplus ")
.append(user)
.append("/")
.append(pass)
.append("@")
.append(host)
.append(":")
.append(dbPort)
.append("/")
.append(dbServiceName)
.append(" ")
.append(" @")
.append(file.getAbsolutePath());
process = Runtime.getRuntime().exec(sb.toString());
int exitVal = process.waitFor();
if (exitVal == 0) {
System.out.println("正常終了");
}
} catch (ScriptException | SQLException | IOException | InterruptedException e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
}
}
@Test
void test() {
try {
// チェックしたいテーブル名のリスト
List<String> tableNameList = Arrays.asList(
"ADDRESS_DETAIL"
,"USERS"
);
// AmbiguousTableNameException対策
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_SCHEMA, user );
// DBUnitでデータベース接続
JdbcDatabaseTester jdbcDatabaseTester = new JdbcDatabaseTester(jdbcDriver, url, user, pass);
QueryDataSet dataSet = new QueryDataSet(jdbcDatabaseTester.getConnection());
setUpDataSet(dataSet, tableNameList);
// 処理実施前のテーブル
writeExcelFromDataSet(dataSet, Paths.get(EVIDENCE_DIR, beforeOutputFileName).toString());
// TODO: 何らかの処理実施
dataSet = new QueryDataSet(jdbcDatabaseTester.getConnection());
setUpDataSet(dataSet, tableNameList);
// 処理実施後のテーブル
writeExcelFromDataSet(dataSet, Paths.get(EVIDENCE_DIR, afterOutputFileName).toString());
} catch (Exception e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
}
}
public static String getPropertyValue(String key){
return prop.getProperty(key);
}
/**
* DBのテーブルのレコードを取得する
* @param dataSet DBのテーブルのデータ
* @param tableNameList テーブル名のリスト
* @return DBのテーブルのデータ
*/
private static QueryDataSet setUpDataSet(QueryDataSet dataSet, List<String> tableNameList) {
// 取得するレコード数の上限
// 取得するレコード数が多過ぎると、Excelの書き込みでOutOfMemoryErrorが起こる
// 実行するJVMの引数でメモリー増やせばOutOfMemoryError回避できるが、メモリー増やすにも限界はあるので、要注意
Integer rownum = 100;
try {
conn = DriverManager.getConnection(url, user, pass);
PreparedStatement preparedStatement = null;
StringBuilder sbSelectSql = new StringBuilder();
StringBuilder sbPrimaryKeySql = new StringBuilder();
StringBuilder sbPrimaryKey = new StringBuilder();
for (String tableName: tableNameList) {
// テーブルの主キーを取得するSQL
sbPrimaryKeySql.append("SELECT ")
.append("column_name ")
.append("FROM ")
.append("all_cons_columns ")
.append("WHERE ")
.append("constraint_name = (")
.append("SELECT ")
.append("constraint_name ")
.append("FROM ")
.append("user_constraints ")
.append("WHERE ")
.append("UPPER(table_name) = UPPER('")
.append(tableName)
.append("') ")
.append("AND ")
.append("CONSTRAINT_TYPE = 'P'")
.append(")");
preparedStatement = conn.prepareStatement(sbPrimaryKeySql.toString());
ResultSet resultSet = preparedStatement.executeQuery();
int index = 0;
while (resultSet.next()) {
if (index != 0) {
sbPrimaryKey.append(", ");
}
sbPrimaryKey.append(String.valueOf(resultSet.getObject(1)))
.append(" DESC"); // 降順
index++;
}
// テーブルからレコード取得するSQL
sbSelectSql.append("SELECT ")
.append("* ")
.append("FROM (")
.append("SELECT ")
.append("* ")
.append("FROM ")
.append(tableName)
.append(" ORDER BY ")
.append(sbPrimaryKey.toString())
.append(") WHERE ")
.append("rownum <= ")
.append(rownum);
dataSet.addTable(tableName, sbSelectSql.toString());
sbSelectSql.setLength(0);
sbPrimaryKeySql.setLength(0);
sbPrimaryKey.setLength(0);
}
} catch (SQLException | AmbiguousTableNameException e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
} finally {
//try {
// if (Objects.nonNull(conn)) {
// conn.close();
// }
//} catch (SQLException e) {
// // TODO 自動生成された catch ブロック
// e.printStackTrace();
//}
}
return dataSet;
}
/**
* DBのテーブルのレコードをExcelに書き込み
* @param dataSet DBのテーブルのレコード
* @param outputFilePath Excel出力先
*/
private static void writeExcelFromDataSet(QueryDataSet dataSet, String outputFilePath) {
try {
XlsDataSetWriter writer = new XlsDataSetWriter() {
@Override
public Workbook createWorkbook() {
return new XSSFWorkbook();
}
};
// Excelに書き込み
writer.write(dataSet, new FileOutputStream(outputFilePath));
} catch (DataSetException | IOException e) {
// TODO 自動生成された catch ブロック
e.printStackTrace();
}
}
}
で、実行すると、
SQLファイルが実行されてるのを確認。
DBUnitでデータベースの選択したテーブルのレコードがLibreOfficeのCalc（Microsoft Excelの代替として使ってます）に出力されてます。
SQLファイルの実行については、Spring Frameworkの対応がイケてないので、標準のJavaのAPIで頑張る感じになるんかね...
毎度モヤモヤ感が半端ない...
今回はこのへんで。
