携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第3天，点击查看活动详情
项目背景Type-3型的JDBC驱动。在集成测试中，使用HSQLDB 内存数据库模式，意在使得测试可以“到处运行”，并避免数据库操作的副作用。
项目结构
将测试模块单独作为一个模块进行编写。
Use Junit 5
Configuring JUnit Platform
To get started with JUnit Platform, you need to add at least a single TestEngine implementation to your project. For example, if you want to write tests with Jupiter, add the test artifact junit-jupiter-engine to the dependencies in POM:
<dependencies>
[...]
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<version>5.4.0</version>
<scope>test</scope>
</dependency>
[...]
</dependencies>
This will pull in all required dependencies. Among those dependencies is junit-jupiter-api which contains the classes and interfaces your test source requires to compile. junit-platform-engine is also resolved and added.
This is the only step that is required to get started - you can now create tests in your test source directory (src/test/java).
集成测试的细节
生命周期
In order to allow individual test methods to be executed in isolation and to avoid unexpected side effects due to mutable test instance state, JUnit creates a new instance of each test class before executing each test method (see Test Classes and Methods). This "per-method" test instance lifecycle is the default behavior in JUnit Jupiter and is analogous to all previous versions of JUnit.
If you would prefer that JUnit Jupiter execute all test methods on the same test instance, annotate your test class with
@TestInstance(Lifecycle.PER_CLASS). When using this mode, a new test instance will be created once per test class. Thus, if your test methods rely on state stored in instance variables, you may need to reset that state in@BeforeEachor@AfterEachmethods.
根据官方文档，Junit5的默认测试实例的生命周期为pre-method在集成测试时，应当选择pre_class，即一个测试类创建一个实例。
成员变量
在pre-class的生命周期下，整个测试过程中，测试方法有时需要共享变量。比如进行JDBC查询的时候，只需要共用一个connection，甚至是一个statement.
Method Order
Although true unit tests typically should not rely on the order in which they are executed, there are times when it is necessary to enforce a specific test method execution order — for example, when writing integration tests or functional tests where the sequence of the tests is important, especially in conjunction with
@TestInstance(Lifecycle.PER_CLASS)
除上述两点之外，在集成测试中，必不可少的一项是使用Order控制方法的执行顺序。
例子：
import org.junit.jupiter.api.MethodOrderer.OrderAnnotation;
import org.junit.jupiter.api.Order;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestMethodOrder;
@TestMethodOrder(OrderAnnotation.class)
class OrderedTestsDemo {
@Test
@Order(1)
void nullValues() {
// perform assertions against null values
}
@Test
@Order(2)
void emptyValues() {
// perform assertions against empty values
}
@Test
@Order(3)
void validValues() {
// perform assertions against valid values
}
}
使用 HSQL DB
运行模式
HSQL DB 是一款纯Java编写的免费数据库，并且支持Memory-Only模型，所以每个单测使用独立的数据库就有了可能，Memory-Only模型数据不会持久化跑完即销毁，从根源上解决了测试难以复现和测试对数据的污染问题。
版本问题
maven 依赖
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>2.5.2</version>
<scope>test</scope>
</dependency>
根据官网:
Latest version 2.7.0 works with JDK 8 and above. Version for JDK 6 is also available.
但是这不代表在测试的时候可以直接调用 2.7.0 版本的代码，因为该版本使用 jdk 11 编译，在使用 IDEA 测试时，直接引用会报错，所以版本应该选择 2.5x .
代码：
public class TestHSQL{
@BeforeAll
static void startHSQL(){
try {
Class.forName(DBProperties.HSQL_DTIVER_NAME);
Thread.sleep(1000);
} catch (Exception e) {
e.printStackTrace();
}
}
...
}
错误信息：
java.lang.UnsupportedClassVersionError: org/hsqldb/jdbcDriver has been compiled by a more recent version of the Java Runtime (class file version 55.0), this version of the Java Runtime only recognizes class file versions up to 52.0
语法问题
HSQL 与 MySQL 语法并不相同，HSQL语法，英文文档读起来稍微有点费劲。
CREATE TABLE student1 (
student_id int(32) PRIMARY KEY AUTO_INCREMENT NOT NULL,
student_name varchar(100) NOT NULL,
student_address varchar(100) NOT NULL
);
如上的sql语句在程序中要这么写，差别还是很多的，对于大部分在标准 SQL 语句之外的语句都需要先检查是否使用了正确的语法。
st.executeUpdate("CREATE TABLE student (" +
"student_id INTEGER GENERATED BY DEFAULT AS IDENTITY " +
"(START WITH 1, INCREMENT BY 1) NOT NULL," +
"student_name VARCHAR(100) NOT NULL," +
"student_address VARCHAR(100) NOT NULL," +
"PRIMARY KEY (student_id)" +
");");
后来我发现翻文档学习语法的时候，发现还有个取巧的方法，可以使用参数直接开启mysql语法支持。
"jdbc:hsqldb:mem:test;sql.syntax_mys=true"
使用示例
import com.fanruan.AgentStarter;
import com.fanruan.ServerStater;
import com.fanruan.service.jdbc.driver.ServiceDriver;
import com.fanruan.proxy.ProxyFactory;
import com.fanruan.utils.DBProperties;
import org.junit.jupiter.api.*;
import java.sql.*;
import java.util.Properties;
/**
* @author Yichen Dai
* @date 2022/8/18 15:27
*/
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class TestUtil {
static Connection conn = null;
static Statement st = null;
static PreparedStatement pst = null;
static ResultSet rs = null;
static void configService(){
// 启动socket服务器
String[][] DBs = new String[][]{
DBProperties.HSQL,
};
new ServerStater(DBs);
}
static void configAgent(){
// 启动socket客户端
String[][] DBs = new String[][]{
DBProperties.HSQL,
};
new AgentStarter(DBs);
}
@BeforeAll
static void autoConfig(){
configService();
configAgent();
try {
// 等待socket连接
Thread.sleep(2000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
@Test
@Order(1)
void testConnect() throws SQLException {
// 建立连接
Properties info = new Properties();
info.setProperty("user", "sa");
info.setProperty("password", "");
info.setProperty("agentID", "1001");
info.setProperty("agentDBName", DBProperties.HSQL[0]);
Driver driver = (ServiceDriver) ProxyFactory.getProxy(ServiceDriver.class, null);
conn = driver.connect("jdbc:hsqldb:mem:test;sql.syntax_mys=true", info);
}
@Test
@Order(2)
void testCreateTable1() throws SQLException {
// 创建 statement
st = conn.createStatement();
// 创建表
int num = st.executeUpdate("DROP TABLE student IF EXISTS;");
Assertions.assertEquals(0, num);
num = st.executeUpdate("CREATE TABLE student (" +
"student_id INTEGER GENERATED BY DEFAULT AS IDENTITY " +
"(START WITH 1, INCREMENT BY 1) NOT NULL," +
"student_name VARCHAR(100) NOT NULL," +
"student_address VARCHAR(100) NOT NULL," +
"PRIMARY KEY (student_id)" +
");");
Assertions.assertEquals(0, num);
}
@Test
@Order(3)
void testCreateTable2() throws SQLException {
// 创建表
int num = st.executeUpdate("DROP TABLE student_score IF EXISTS;");
Assertions.assertEquals(0, num);
num = st.executeUpdate("CREATE TABLE score (" +
"student_id int(10) PRIMARY KEY NOT NULL," +
"score int(10) NOT NULL" +
");"
);
Assertions.assertEquals(0, num);
}
@Test
@Order(4)
void testInsert1() throws SQLException {
// 插入数据
int num = st.executeUpdate("INSERT INTO student VALUES" +
"(1, '张三', '上海')," +
"(2, '李四', '北京')," +
"(3, '王五', '成都');");
Assertions.assertEquals(3, num);
}
@Test
@Order(5)
void testInsert2() throws SQLException {
// 插入数据
int num = st.executeUpdate("INSERT INTO score VALUES" +
"(1, 645)," +
"(2, 627)," +
"(3, 591);");
Assertions.assertEquals(3, num);
}
@Test
@Order(6)
void testUpdate() throws SQLException {
// 预查询语句 删除指定 ID
pst = conn.prepareStatement("UPDATE student" +
" SET student_name = '李华', student_address = '杭州'"+
"WHERE student_id = ?");
Assertions.assertNotNull(pst);
pst.setInt(1, 1);
int num = pst.executeUpdate();
Assertions.assertEquals(1, num);
}
@Test
@Order(7)
void testDelete() throws SQLException {
// 预查询语句 删除指定 ID
pst = conn.prepareStatement("delete from student where student_id = ?");
Assertions.assertNotNull(pst);
pst.setInt(1, 3);
int num = pst.executeUpdate();
Assertions.assertEquals(1, num);
}
@Test
@Order(8)
void testSelect() throws SQLException {
rs = st.executeQuery("select * from student;");
String[] nameStrings = new String[]{"李华", "李四", "王五"};
String[] addressString = new String[]{"杭州", "北京", "成都"};
// 结果集断言
int num = 1;
while(rs.next()) {
Assertions.assertEquals(num, rs.getInt("student_id"));
Assertions.assertEquals(nameStrings[num-1], rs.getString("student_name"));
Assertions.assertEquals(addressString[num-1], rs.getString("student_address"));
num++;
}
}
@Test
@Order(9)
void testSubSelect() throws SQLException {
// 插入数据
rs = st.executeQuery(
"SELECT student_name FROM student " +
"WHERE student_id IN " +
"(SELECT student_id " +
"FROM score " +
"WHERE score > 600);"
);
String[] nameStrings = new String[]{"李华", "李四", "王五"};
// 结果集断言
int num = 1;
while(rs.next()) {
Assertions.assertEquals(nameStrings[num-1], rs.getString("student_name"));
num++;
}
}
@Test
@Order(10)
void testJoin() throws SQLException {
// 插入数据
rs = st.executeQuery(
"SELECT A.student_name " +
"FROM student A JOIN score B " +
"ON A.student_id = B.student_id " +
"WHERE score > 600;"
);
String[] nameStrings = new String[]{"李华", "李四", "王五"};
// 结果集断言
int num = 1;
while (rs.next()) {
Assertions.assertEquals(rs.getString("student_name"), nameStrings[num - 1]);
num++;
}
}
}
