生活記事簿: 3月 2026

根據上一篇的
在 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.AfterAll;
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 停掉
		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());
	}
}

源碼下載分享：

testcontainers-test.zip

參考資料：

HikariCP

生活記事簿

2026年3月29日星期日

在 Windows 的環境下使用 Eclipse 在 Java Unit Test 中使用 TestContainers 做測試 - Docker 用 WSL2 的方式安裝 (不用 Docker Desktop)

2026年3月29日 星期日

在 Windows 的環境下使用 Eclipse 在 Java Unit Test 中使用 TestContainers 做測試 - Docker 用 WSL2 的方式安裝 (不用 Docker Desktop)

2026年3月29日星期日