根據上一篇的
在 Windows 環境使用 Docker 指令控制 WSL2 中的 Docker (不是 Docker Desktop)
的設定方式，
我們設定好讓 Windows 可以使用 Docker 指令控制 WSL2 的 Docker
之後，
就可以使用 TestContainers 來進行測試，
在這篇文裡我會展示一個使用
TestContainers 進行 Database 的 Unit Test 範例。
模擬環境：
- TestContainers version 我使用 2.0.3 版。
- 以使用 Maven 的 Spring MVC 專案為例 (這裡使用了 No-xml 的配置方式，可以參考 No XML for Java EE Spring Application，不過使用了較新的 JDK, Spring 版本，所以有部份修改 ) 。
- 使用 JDK 20。
- 在 Unit Test 中使用 TestContainers 測試 MsSql (SqlServer) DAO method，MsSql 有設定 full-text search 環境，可以測試如 CONTAINS, FREETEXT 等語法。
- 在測試中模擬了兩個 Database， database1 和 database2。
- 使用了 HikariCP connection pool。
首先是在 pom.xml 裡引入需要的 LIbrary ：
/pom.xml (主要是 <dependencyManagement> 和 <dependency> 的部份) :<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>my.test</groupId>
<artifactId>testcontainers-test</artifactId>
<version>0.1</version>
<packaging>war</packaging>
<name>testcontainers-test Maven Webapp</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>20</maven.compiler.source>
<maven.compiler.target>20</maven.compiler.target>
</properties>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-framework-bom</artifactId>
<version>7.0.5</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<!-- Source: https://mvnrepository.com/artifact/org.testcontainers/testcontainers-bom -->
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>testcontainers-bom</artifactId>
<version>2.0.3</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<!-- Source: https://mvnrepository.com/artifact/org.junit/junit-bom -->
<dependency>
<groupId>org.junit</groupId>
<artifactId>junit-bom</artifactId>
<version>6.0.3</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<!-- Source: https://mvnrepository.com/artifact/org.junit.jupiter/junit-jupiter -->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/jakarta.servlet/jakarta.servlet-api -->
<dependency>
<groupId>jakarta.servlet</groupId>
<artifactId>jakarta.servlet-api</artifactId>
<version>6.1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.9.0</version>
</dependency>
<!-- Source: https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>7.0.2</version>
</dependency>
<!-- Source: https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>13.2.1.jre11</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.testcontainers/testcontainers -->
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>testcontainers</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.testcontainers/junit-jupiter -->
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>testcontainers-junit-jupiter</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.testcontainers/testcontainers-postgresql -->
<!-- 如果是使用 Postgresql， testcontainers 也有相應配合的 dependency 可用 -->
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>testcontainers-postgresql</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.testcontainers/testcontainers-mssqlserver -->
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>testcontainers-mssqlserver</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<finalName>testcontainers-test</finalName>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
再來先來設定一下 production 真實環境 Database 的 properties，例如 driver, url,
username, password 等，我們可以用 Spring 的 @Value 將值讀進來，不過這裡我只是想要展示 UnitTest 的部份，
不用管實際的環境情況，所以值可以隨便設定，
在 UnitTest 時，我們可以用 @DynamicPropertySource 在 Spring Bean 被裝配之前覆蓋掉 Spring properties 的值，改變 @Value 讀進來的值。
/src/main/java/com/properties/db.properties :
#mssql db properties db.mssql.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver db.mssql.port=xxx-port db.mssql.username=xxx-user db.mssql.password=xxx-password db.mssql.database1.url=jdbc:sqlserver:///xxxUrl:xxxPort;databaseName=database1 db.mssql.database2.url=jdbc:sqlserver:///xxxUrl:xxxPort;databaseName=database2
在 db.properties 中設定了兩個 Database，database1 和 database2。
接下來我要設定 DataSource 給 Spring 去裝配，
/src/main/java/com/config/DBConfig.java :
package com.config;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
@Configuration
public class DBConfig {
// database1 的設定
@Bean
public DataSource database1DataSource(@Value("${db.mssql.driver}") String dbDriver,
@Value("${db.mssql.port}") String dbPort,
@Value("${db.mssql.username}") String dbUsername,
@Value("${db.mssql.password}") String dbPassword,
@Value("${db.mssql.database1.url}") String dbUrl) {
//這裡練習使用 HikariCP 做 connection pool
HikariConfig dataSourceConfig = new HikariConfig();
dataSourceConfig.setDriverClassName(dbDriver);
dataSourceConfig.setJdbcUrl(dbUrl);
dataSourceConfig.setUsername(dbUsername);
dataSourceConfig.setPassword(dbPassword);
dataSourceConfig.setConnectionTestQuery("SELECT 1");
DataSource dataSource = new HikariDataSource(dataSourceConfig);
//如果沒有要使用其他 Connection pool 的話，也可以直接使用 BasicDataSource
//DataSource dataSource = new BasicDataSource();
return dataSource;
}
@Bean
public NamedParameterJdbcTemplate database1JdbcTemplate(@Qualifier("database1DataSource") DataSource datasource) {
return new NamedParameterJdbcTemplate(datasource);
}
@Bean
public DataSourceTransactionManager database1TxManager(@Qualifier("database1DataSource") DataSource datasource) {
return new DataSourceTransactionManager(datasource);
}
// database2 的設定
@Bean
public DataSource database2DataSource(@Value("${db.mssql.driver}") String dbDriver,
@Value("${db.mssql.port}") String dbPort,
@Value("${db.mssql.username}") String dbUsername,
@Value("${db.mssql.password}") String dbPassword,
@Value("${db.mssql.database2.url}") String dbUrl) {
HikariConfig dataSourceConfig = new HikariConfig();
dataSourceConfig.setDriverClassName(dbDriver);
dataSourceConfig.setJdbcUrl(dbUrl);
dataSourceConfig.setUsername(dbUsername);
dataSourceConfig.setPassword(dbPassword);
dataSourceConfig.setConnectionTestQuery("SELECT 1");
DataSource dataSource = new HikariDataSource(dataSourceConfig);
return dataSource;
}
@Bean
public NamedParameterJdbcTemplate database2JdbcTemplate(@Qualifier("database2DataSource") DataSource datasource) {
return new NamedParameterJdbcTemplate(datasource);
}
@Bean
public DataSourceTransactionManager database2TxManager(@Qualifier("database2DataSource") DataSource datasource) {
return new DataSourceTransactionManager(datasource);
}
}
做一下 代表 Database Table Data 的 Bean 的設定 :
/src/main/java/com/bean/MemberBean.java :
package com.bean;
public class MemberBean {
private int id;
private String name;
private String email;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
/src/main/java/com/bean/PurchaseOrderBean.java :
package com.bean;
import java.time.Instant;
import java.time.OffsetDateTime;
import java.time.format.DateTimeFormatter;
public class PurchaseOrderBean {
private int id;
private Instant createdDate;
private int memberId;
private String detail;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Instant getCreatedDate() {
return createdDate;
}
public void setCreatedDate(Instant createdDate) {
this.createdDate = createdDate;
}
public void setCreatedDate(String offsetDatetimeStr) {
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSSS xxx");
Instant instantDate = OffsetDateTime.parse(offsetDatetimeStr, dtf).toInstant();
this.createdDate = instantDate;
}
public int getMemberId() {
return memberId;
}
public void setMemberId(int memberId) {
this.memberId = memberId;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail;
}
}
設定 DAO 的部份 :
/src/main/java/com/dao/MemberDAO.java :
package com.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import com.bean.MemberBean;
@Repository
public class MemberDAO {
private NamedParameterJdbcTemplate database1JdbcTemplate;
public MemberDAO(@Qualifier("database1JdbcTemplate") NamedParameterJdbcTemplate database1JdbcTemplate) {
this.database1JdbcTemplate = database1JdbcTemplate;
}
public MemberBean queryMemberByName(String name) {
String sql = "SELECT * FROM member WHERE name = :name";
MapSqlParameterSource sqlParams = new MapSqlParameterSource()
.addValue("name", name);
try {
return database1JdbcTemplate.queryForObject(sql, sqlParams, new RowMapper<MemberBean>() {
@Override
public MemberBean mapRow(ResultSet rs, int rowNum) throws SQLException {
MemberBean member = new MemberBean();
member.setId(rs.getInt("id"));
member.setName(rs.getString("name"));
member.setEmail(rs.getString("email"));
return member;
}
});
} catch (IncorrectResultSizeDataAccessException e) {
return null;
}
}
}
/src/main/java/com/dao/PurchaseOrderDAO.java :
package dao;
import java.time.Instant;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoField;
import java.util.List;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import com.bean.PurchaseOrderBean;
import com.dao.PurchaseOrderDAO;
public class PurchaseOrderDAOTest extends BaseDBTest {
private PurchaseOrderDAO purchaseOrderDAO;
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss xxx").withZone(ZoneId.of("+0000"));
@Autowired
public PurchaseOrderDAOTest(PurchaseOrderDAO purchaseOrderDAO) {
this.purchaseOrderDAO = purchaseOrderDAO;
}
@Test
void testQueryPurchaseOrderListByMemberId() {
JdbcTemplate database2JdbcTemplate = jdbcTemplateMap.get(DATABASE.database2);
//捨棄毫秒部分以避免毫秒部份的精確度從 SQL 查詢回來的時間與測試用的時間不相等的問題
Instant testCreatedDate = Instant.now().with(ChronoField.NANO_OF_SECOND, 0);
int testMemberId = 3;
List<PurchaseOrderBean> purchaseOrderList = purchaseOrderDAO.queryPurchaseOrderListByMemberId(testMemberId);
Assertions.assertNotNull(purchaseOrderList);
Assertions.assertTrue(purchaseOrderList.isEmpty());
//
//執行 SQL Update 時，直接以 String 的方式傳入避免時區可能錯誤的問題
database2JdbcTemplate.update("INSERT INTO purchase_order(created_date, member_id) VALUES(?, ?)", dtf.format(testCreatedDate), testMemberId);
purchaseOrderList = purchaseOrderDAO.queryPurchaseOrderListByMemberId(testMemberId);
Assertions.assertNotNull(purchaseOrderList);
Assertions.assertEquals(1, purchaseOrderList.size());
PurchaseOrderBean purchaseOrder = purchaseOrderList.get(0);
Assertions.assertEquals(testMemberId, purchaseOrder.getMemberId());
Assertions.assertEquals(testCreatedDate, purchaseOrder.getCreatedDate());
}
@Test
void testQueryPurchaseOrderListByDetailKeyword() {
JdbcTemplate database2JdbcTemplate = jdbcTemplateMap.get(DATABASE.database2);
Instant testCreatedDate = Instant.now().with(ChronoField.NANO_OF_SECOND, 0);
int testMemberId = 111;
String testDetail = "Hi, how are you?";
String keyword = "hi";
String sql = "INSERT INTO purchase_order(created_date, member_id, detail) VALUES(?, ?, ?)";
database2JdbcTemplate.update(sql, dtf.format(testCreatedDate), testMemberId, testDetail);
List<PurchaseOrderBean> purchaseOrderList = purchaseOrderDAO.queryPurchaseOrderListByDetailKeyword(keyword);
Assertions.assertNotNull(purchaseOrderList);
Assertions.assertTrue(purchaseOrderList.size() == 1);
PurchaseOrderBean purchaseOrder = purchaseOrderList.get(0);
Assertions.assertEquals(testMemberId, purchaseOrder.getMemberId());
Assertions.assertEquals(testCreatedDate, purchaseOrder.getCreatedDate());
Assertions.assertEquals(testDetail, purchaseOrder.getDetail());
}
}
基本的專案內容都做好了以後，就可以來進行 Unit Test 的部份了，
為了方便建立測試用的
Database 環境，
例如建立要測試用的 Database, Table, View, Stored
Procedure, Index, Full-Text Search 之類的，
我先把建立測試環境用的
SQL先寫好並以下面的結構放好：
/src/test/resources/sql/databases_create.sql (建立好所需的 Database) :
CREATE DATABASE database1; CREATE DATABASE database2;
/src/test/resources/sql/tables_drop.sql (移除所有的 Database) :
--刪除目前 Database 下的所有 Table
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'DROP TABLE ' + QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) + ';' + CHAR(13)
FROM information_schema.tables
WHERE table_type = 'base table'
AND table_schema = 'dbo';
EXEC sp_executesql @sql;
-- 刪除 Database 下的所有 Full-Text Index
DECLARE @tableName NVARCHAR(MAX);
DECLARE @sqlCommand NVARCHAR(MAX);
DECLARE index_cursor CURSOR FOR
SELECT QUOTENAME(t.name) AS TableName
FROM sys.tables t
INNER JOIN sys.fulltext_indexes fti ON t.object_id = fti.object_id;
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'DROP FULLTEXT INDEX ON ' + @tableName + ';';
EXEC sp_executesql @sqlCommand;
FETCH NEXT FROM index_cursor INTO @tableName;
END
CLOSE index_cursor;
DEALLOCATE index_cursor;
-- 刪除 Database 下的所有 Full-Text Index Catelogs
DECLARE @CatalogName NVARCHAR(MAX);
DECLARE @CatalogCommand NVARCHAR(MAX);
DECLARE FullTextCatalogsCursor CURSOR FOR
SELECT QUOTENAME(name) AS CatalogName
FROM sys.fulltext_catalogs;
OPEN FullTextCatalogsCursor;
FETCH NEXT FROM FullTextCatalogsCursor INTO @CatalogName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CatalogCommand = 'DROP FULLTEXT CATALOG ' + @CatalogName;
EXEC sp_executesql @CatalogCommand;
FETCH NEXT FROM FullTextCatalogsCursor INTO @CatalogName;
END
CLOSE FullTextCatalogsCursor;
DEALLOCATE FullTextCatalogsCursor;
/src/test/resources/sql/databases/database1/tables/member.sql (建立 member 這個 Table，包括要的 Index, Full Text Index) :
CREATE TABLE member ( id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(200) NOT NULL, email NVARCHAR(200) NOT NULL );
/src/test/resources/sql/databases/database2/tables/purchase_order.sql (建立 purchase_order 這個 Table) :
CREATE TABLE purchase_order (
id INT IDENTITY(1,1) PRIMARY KEY,
created_date DATETIMEOFFSET NOT NULL,
member_id INT NOT NULL,
detail NVARCHAR(1000) NULL
);
-- 設定 Full-Text Index
-- 先把 Primary Key Index name 查出來
DECLARE @primaryKeyIndex NVARCHAR(100)
SELECT @primaryKeyIndex = i.name
FROM sys.indexes i
WHERE i.[object_id] = OBJECT_ID('purchase_order')
AND i.is_primary_key = 1;
-- 建立 Full Text Catalog
CREATE FULLTEXT CATALOG [full_text_catalog_purchase_order] WITH ACCENT_SENSITIVITY = ON
-- 建立 Full Text Index
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'CREATE FULLTEXT INDEX ON purchase_order KEY INDEX ' + @primaryKeyIndex + N' ON (full_text_catalog_purchase_order) WITH (CHANGE_TRACKING AUTO)'
EXEC sp_executesql @sql
-- 把 column (可設定多個) 加到 Full Text Index 裡
ALTER FULLTEXT INDEX ON purchase_order ADD ([detail])
ALTER FULLTEXT INDEX ON purchase_order ENABLE
建立一個 BaseDBTest.java 把 Unit Test 的基礎設定先寫好，
包括用
TestContainers 啟動 MSSQL Docker container, 建立 Database, Table, Index
等，
詳細的註解都寫在程式碼中。
/src/test/java/dao/BaseDBTest.java :
package dao;
import java.io.IOException;
import java.net.URISyntaxException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import org.junit.jupiter.api.
;
import org.junit.jupiter.api.AfterEach;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.DynamicPropertyRegistry;
import org.springframework.test.context.DynamicPropertySource;
import org.springframework.test.context.junit.jupiter.web.SpringJUnitWebConfig;
import org.testcontainers.images.builder.ImageFromDockerfile;
import org.testcontainers.mssqlserver.MSSQLServerContainer;
import org.testcontainers.utility.DockerImageName;
import com.config.SpringApplicationConfig;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
@SpringJUnitWebConfig(SpringApplicationConfig.class)
public class BaseDBTest {
//用一個 enum 來定義需要的 Database，這樣在程式碼中要使用 Database 的地方就可以直接用 enum 的方式來使用，會比直接用 String 來得更有彈性和可讀性
enum DATABASE {
database1("database1"), database2("database2");
private String databaseName;
private DATABASE(String databaseName) {
this.databaseName = databaseName;
}
public static DATABASE fromDatabaseName(String databaseName) {
for (DATABASE db : DATABASE.values()) {
if (db.getDatabaseName().equalsIgnoreCase(databaseName)) {
return db;
}
}
return null;
}
public String getDatabaseName() {
return databaseName;
}
}
// TestContainers 啟動的 MSSQL Docker container
private static MSSQLServerContainer mssqlServerDockerContainer;
//MSSQL Docker container 一開始建立好我們取得的 jdbcTemplate，
//用來建立其他 Unit Test 所需要的 Database
private static JdbcTemplate mssqlJdbcTemplate;
//用來存放對應各 Database 的 jdbcTemplate，用 Database enum 當 key 增加可讀性
static Map<DATABASE, JdbcTemplate> jdbcTemplateMap = new HashMap<>();
//@DynamicPropertSource 會在 Spring Context 啟動前執行，
//這樣就可以在 Spring Context 啟動前先啟動 TestContainers 的 MSSQL Docker container
//並將實際的參數覆蓋掉 db.properties 中的參數，
//確保在測試時用 @Value 取得的值是正確的 TestContainers Docker container 的參數，
//例如 username, password, port, url 等等
@DynamicPropertySource
static void setUpMssqlEnvironment(DynamicPropertyRegistry registry) throws IOException, URISyntaxException {
//使用 TestContainers docker pull image 下來並啟動 Container
//如果需要 full-text search 功能，則需要自己 build image 並在 image 中安裝 full-text search 的套件，範例如下：
ImageFromDockerfile mssqlFtsImage = new ImageFromDockerfile()
.withDockerfileFromBuilder(builder ->
builder.from("mcr.microsoft.com/mssql/server:2022-latest")
.user("root")
//# Install dependencies - these are required to make changes to apt-get below
.run("apt-get update")
.run("apt-get install -yq gnupg gnupg2 gnupg1 curl apt-transport-https")
//# Install SQL Server package links
.run("curl https://packages.microsoft.com/keys/microsoft.asc -o /var/opt/mssql/ms-key.cer")
.run("apt-key add /var/opt/mssql/ms-key.cer")
.run("curl https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list -o /etc/apt/sources.list.d/mssql-server.list")
.run("apt-get update")
//# Install SQL Server full-text-search - this only works if you add the packages references into apt-get above
.run("apt-get install -y mssql-server-fts")
//# Cleanup
.run("apt-get clean")
.run("rm -rf /var/lib/apt/lists")
//# Run SQL Server process
.entryPoint("/opt/mssql/bin/sqlservr")
.build()
);
String builtImageName = mssqlFtsImage.get();
DockerImageName dockerImageName = DockerImageName.parse(builtImageName)
.asCompatibleSubstituteFor("mcr.microsoft.com/mssql/server");
mssqlServerDockerContainer = new MSSQLServerContainer(dockerImageName)
//如果沒有需要 full-text search 的功能，則可以直接使用官方 image，範例如下：
//new MSSQLServerContainer("mcr.microsoft.com/mssql/server:2019-CU14-ubuntu-20.04")
.acceptLicense()
.withUrlParam("trustServerCertificate", "true")
.withPassword("testPassword123#")
.withEnv(Map.of("MSSQL_PID", "Standard"))
.withEnv(Map.of("MSSQL_AGENT_ENABLED", "true"))
.withEnv(Map.of("TZ", "America/Los_Angeles"));
mssqlServerDockerContainer.start();
//將 TestContainers 建立的 Mssql Docker container 的各項實際參數 (username, passowrd, url, port 等)
//覆蓋 db.properties 中設定的值，這樣在測試時用 @Valve 取得的值就會是被覆蓋掉的值
registry.add("db.mssql.driver", mssqlServerDockerContainer::getDriverClassName);
registry.add("db.mssql.username", mssqlServerDockerContainer::getUsername);
registry.add("db.mssql.password", mssqlServerDockerContainer::getPassword);
registry.add("db.mssql.port", () -> mssqlServerDockerContainer.getMappedPort(1433));
registry.add("db.mssql.database1.url", () -> mssqlServerDockerContainer.getJdbcUrl() + ";databaseName=database1");
registry.add("db.mssql.database2.url", () -> mssqlServerDockerContainer.getJdbcUrl() + ";databaseName=database2");
//建立一開始 Database 的 Datasource 和 jdbcTemplate
HikariConfig dataSourceConfig = new HikariConfig();
dataSourceConfig.setDriverClassName(mssqlServerDockerContainer.getDriverClassName());
dataSourceConfig.setJdbcUrl(mssqlServerDockerContainer.getJdbcUrl());
dataSourceConfig.setUsername(mssqlServerDockerContainer.getUsername());
dataSourceConfig.setPassword(mssqlServerDockerContainer.getPassword());
dataSourceConfig.setConnectionTestQuery("SELECT 1");
DataSource mssqlDataSource = new HikariDataSource(dataSourceConfig);
mssqlJdbcTemplate = new JdbcTemplate(mssqlDataSource);
//建立需要的 Database
String dbCreateSql = Files.readString(Paths.get(BaseDBTest.class.getClassLoader().getResource("sql/databases_create.sql").toURI()), StandardCharsets.UTF_8);
mssqlJdbcTemplate.update(dbCreateSql);
//設定各 Database 的 jdbcTemplate 到 JdbcTemplate 中方便之後取用
setJdbcTemplateForDatabases();
//為各 Database 建立需要的 Table
createTables();
}
//在每個測試方法之後都執行一次，確保每個測試方法執行時 Database 中的 Table 都是乾淨的狀態
@AfterEach
void refreshTables() throws IOException, URISyntaxException {
dropDbTables();
createTables();
}
@AfterAll
static void closeDockerContainers() {
//將 TestContainer 開啟的 Container 停掉，
//不過官方有說 TestContainers 預設會使用 Ryuk Container (除非你因例如權限原因等不能使用 Ryuk 而設定了環境變數 TESTCONTAINERS_RYUK_DISABLED=true)
//自動進行 Container 的清理，所以也可以不用手動執行 stop
mssqlServerDockerContainer.stop();
}
//設定各 Database 的 jdbcTemplate 到 JdbcTemplate 中方便之後取用
static void setJdbcTemplateForDatabases() {
for (DATABASE db : DATABASE.values()) {
HikariConfig dataSourceConfig = new HikariConfig();
dataSourceConfig.setDriverClassName(mssqlServerDockerContainer.getDriverClassName());
dataSourceConfig.setJdbcUrl(mssqlServerDockerContainer.getJdbcUrl() + ";databaseName=" + db.getDatabaseName());
dataSourceConfig.setUsername(mssqlServerDockerContainer.getUsername());
dataSourceConfig.setPassword(mssqlServerDockerContainer.getPassword());
dataSourceConfig.setConnectionTestQuery("SELECT 1");
DataSource dataSource = new HikariDataSource(dataSourceConfig);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplateMap.put(db, jdbcTemplate);
}
}
//為各 Database 建立需要的 Table
static void createTables() throws IOException, URISyntaxException {
List<Path> databasesResourcePathList = Files.list(Paths.get(BaseDBTest.class.getClassLoader().getResource("sql/databases").toURI()))
.collect(Collectors.toList());
for (Path databasesResourcePath : databasesResourcePathList) {
String databaseName = databasesResourcePath.getFileName().toString();
JdbcTemplate jdbcTemplate = jdbcTemplateMap.get(DATABASE.fromDatabaseName(databaseName));
Path tablesFolderPath = databasesResourcePath.resolve("tables");
if (!Files.exists(tablesFolderPath)) {
continue;
}
List<Path> tableCreateSqlPathList = Files.list(tablesFolderPath)
.collect(Collectors.toList());
for (Path tableCreateSqlPath : tableCreateSqlPathList) {
String databasesCreateSql = Files.readString(tableCreateSqlPath, StandardCharsets.UTF_8);
jdbcTemplate.update(databasesCreateSql);
}
}
}
//Drop 所有 Database 下的所有 Table
//包括 Drop 所有的 Index, Full Text Index, Full Text Catalog 等
void dropDbTables() throws IOException, URISyntaxException {
Path tablesDropSqlPath = Paths.get(BaseDBTest.class.getClassLoader().getResource("sql/tables_drop.sql").toURI());
List<Path> databasesResourcePathList = Files.list(Paths.get(BaseDBTest.class.getClassLoader().getResource("sql/databases").toURI()))
.collect(Collectors.toList());
for (Path databasesResourcePath : databasesResourcePathList) {
String databaseName = databasesResourcePath.getFileName().toString();
JdbcTemplate jdbcTemplate = jdbcTemplateMap.get(DATABASE.fromDatabaseName(databaseName));
String tablesDropSql = Files.readString(tablesDropSqlPath, StandardCharsets.UTF_8);
jdbcTemplate.update(tablesDropSql);
}
}
}
/src/test/java/dao/MemberDAOTest.java :
package dao;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import com.bean.MemberBean;
import com.dao.MemberDAO;
public class MemberDAOTest extends BaseDBTest {
private MemberDAO memberDAO;
@Autowired
public MemberDAOTest(MemberDAO memberDAO) {
this.memberDAO = memberDAO;
}
@Test
void testQueryMemberByName() {
JdbcTemplate database1JdbcTemplate = jdbcTemplateMap.get(DATABASE.database1);
String testName = "testName";
String testEmail = "xxx@xxx.com";
MemberBean member = memberDAO.queryMemberByName(testName);
Assertions.assertNull(member);
database1JdbcTemplate.update("INSERT INTO member(name, email) VALUES(?, ?)", testName, testEmail);
member = memberDAO.queryMemberByName(testName);
Assertions.assertNotNull(member);
Assertions.assertEquals(testName, member.getName());
Assertions.assertEquals(testEmail, member.getEmail());
}
}
/src/test/java/dao/PurchaseOrderDAOTest.java :
package dao;
import java.time.Instant;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoField;
import java.util.List;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import com.bean.PurchaseOrderBean;
import com.dao.PurchaseOrderDAO;
public class PurchaseOrderDAOTest extends BaseDBTest {
private PurchaseOrderDAO purchaseOrderDAO;
DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss xxx").withZone(ZoneId.of("+0000"));
@Autowired
public PurchaseOrderDAOTest(PurchaseOrderDAO purchaseOrderDAO) {
this.purchaseOrderDAO = purchaseOrderDAO;
}
@Test
void testQueryPurchaseOrderListByMemberId() {
JdbcTemplate database2JdbcTemplate = jdbcTemplateMap.get(DATABASE.database2);
//捨棄毫秒部分以避免毫秒部份的精確度從 SQL 查詢回來的時間與測試用的時間不相等的問題
Instant testCreatedDate = Instant.now().with(ChronoField.NANO_OF_SECOND, 0);
int testMemberId = 3;
List<PurchaseOrderBean> purchaseOrderList = purchaseOrderDAO.queryPurchaseOrderListByMemberId(testMemberId);
Assertions.assertNotNull(purchaseOrderList);
Assertions.assertTrue(purchaseOrderList.isEmpty());
//
//執行 SQL Update 時，直接以 String 的方式傳入避免時區可能錯誤的問題
database2JdbcTemplate.update("INSERT INTO purchase_order(created_date, member_id) VALUES(?, ?)", dtf.format(testCreatedDate), testMemberId);
purchaseOrderList = purchaseOrderDAO.queryPurchaseOrderListByMemberId(testMemberId);
Assertions.assertNotNull(purchaseOrderList);
Assertions.assertEquals(1, purchaseOrderList.size());
PurchaseOrderBean purchaseOrder = purchaseOrderList.get(0);
Assertions.assertEquals(testMemberId, purchaseOrder.getMemberId());
Assertions.assertEquals(testCreatedDate, purchaseOrder.getCreatedDate());
}
@Test
void testQueryPurchaseOrderListByDetailKeyword() {
JdbcTemplate database2JdbcTemplate = jdbcTemplateMap.get(DATABASE.database2);
Instant testCreatedDate = Instant.now().with(ChronoField.NANO_OF_SECOND, 0);
int testMemberId = 111;
String testDetail = "Hi, how are you?";
String keyword = "hi";
String sql = "INSERT INTO purchase_order(created_date, member_id, detail) VALUES(?, ?, ?)";
database2JdbcTemplate.update(sql, dtf.format(testCreatedDate), testMemberId, testDetail);
List<PurchaseOrderBean> purchaseOrderList = purchaseOrderDAO.queryPurchaseOrderListByDetailKeyword(keyword);
Assertions.assertNotNull(purchaseOrderList);
Assertions.assertTrue(purchaseOrderList.size() == 1);
PurchaseOrderBean purchaseOrder = purchaseOrderList.get(0);
Assertions.assertEquals(testMemberId, purchaseOrder.getMemberId());
Assertions.assertEquals(testCreatedDate, purchaseOrder.getCreatedDate());
Assertions.assertEquals(testDetail, purchaseOrder.getDetail());
}
}
源碼下載分享：
參考資料：
- HikariCP
- 在 Linux 上安裝 SQL Server 全文檢索搜尋
- Installing MSSQL server using docker with full text search support
