使用springboottest和h2来构建数据库测试的采坑记录

本文主要是介绍使用springboottest和h2来构建数据库测试的采坑记录，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

现状

因为项目关系和人力关系, 代码写的比较快而且质量不是很好. bug比较多 基本功能总是有问题(某些场景下) 所以现在想快速补齐测试短板.

为啥要做

想看看如何将spring boot test + db这套结合起来做测试… 因为我们是saas项目 所以更多的想法就是能不能采用内存数据库来方便UAT测试. 所以就有了下面的数据库对比和h2采坑记录

不同数据库对比:

	H2	Derby	HSQLDB	MySQL	PostgreSQL
Pure Java	Yes	Yes	Yes	No	No
Memory Mode	Yes	Yes	Yes	No	No
Encrypted Database	Yes	Yes	Yes	No	No
ODBC Driver	Yes	No	No	Yes	Yes
Fulltext Search	Yes	No	No	Yes	Yes
Multi Version Concurrency	Yes	No	Yes	Yes	Yes
Footprint (embedded)	~2 MB	~3 MB	~1.5 MB	—	—
Footprint (client)	~500 KB	~600 KB	~1.5 MB	~1 MB	~700 KB

我们的效果

依赖springboot test 可以很方便的对整个应用的各个层级的代码做测试而且不用担心有问题

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
//...
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;//@Ignore
@RunWith(SpringRunner.class)
@SpringBootTest
public class UseMemDbDerbyTest {Log LOG = LogFactory.getLog(UseMemDbDerbyTest.class);// 可以在这里做一些针对UAT测试的一些设置啥的static {Constants.COREDB = "testdb";System.setProperty("kb.config", "/Users/edward/projects/kb/config/kb-local-uat.config");try {LayeredConf.getInstance().load(System.getProperty("kb.config"));}catch (IOException e) {e.printStackTrace();}}// 自动注入的对象  可以很方便的拿来测试 只要是被springboot管理的@AutowiredAccountService accountService;@AutowiredRpcSessionController addSessionController;@Testpublic void test() throws Exception {LOG.info("Step 1: prepare account");Account a = prepareAccount();}Account prepareAccount() {AccountCatalog.initialize();accountService.delete(Account.getExternalId(Environment.ACCOUNT_ID_TEST));JSONObject o = new JSONObject();o.put("name", "PerfTest");return accountService.create(o.toString());}}

kb.config中的配置(可以理解为application.properties), 我们是自己写的 所以key 和 spring boot的不一样 但是不影响
database.url=jdbc:h2:mem:a1;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;
database.username=
database.password=
database.initDb = true
database.driver=org.h2.Driver

build.gradle中的依赖:

        testCompile 'org.apache.logging.log4j:log4j-api:2.8'testCompile 'org.apache.logging.log4j:log4j-core:2.8'testCompile 'org.apache.logging.log4j:log4j-slf4j-impl:2.8'testCompile 'org.springframework.boot:spring-boot-starter-log4j2:2.1.0.RELEASE'testCompile 'junit:junit:4.11'testCompile 'org.springframework.boot:spring-boot-starter-test:2.1.0.RELEASE'testCompile 'com.h2database:h2:1.4.197'

遇到的问题

table找不到

原因是内存表再创建后连接关闭了就没了. 而一般是多个连接, 所以在url中加入:

jdbc:h2:mem:;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;

DB_CLOSE_DELAY 指定等jvm退出后关闭. DATABASE_TO_UPPER 不让h2 自动转大写db名称.

insert into value 不支持

注意是insert into value 而不是 insert into values… mysql支持前者 而h2不支持. 修改下使用的sql即可. 标准的sql就是insert into xxx values (1, 2, 3)

不支持json列

换成varchar2 前提是没使用json相关功能.

create database 不支持

H2 使用的是create schema if not exists xxxx; 并且不支持设置字符集(像mysql方式).

判断表是否存在有问题

应该是mysql没有按方式来… 正确的java代码:

    public static boolean tableExists(Connection conn, String dbName, String tableName) throws SQLException {String[] types = {"TABLE"};ResultSet rs = null;try {//  注意这里的参数顺序rs = conn.getMetaData().getTables(null, dbName, tableName, types);if(rs.next()) {return true;}else {return false;}}finally {closeQuietly(rs);}}

但是mysql也支持如下这样的, h2不支持:注意这里的参数顺序

rs = conn.getMetaData().getTables(dbName, null, tableName, types);  

获取最后insert 的id

对于有主键的, mysql支持 你手动输入某个表的id 字段, 然后返回, 但是 h2不支持这种case. 比如:

create table tttt (id bigint not null primary key auto_increment, a int);

        PreparedStatement ps2 = mysqlConn.prepareStatement("insert into uat.tttt values(1, 2)", Statement.RETURN_GENERATED_KEYS);ps2.executeUpdate();ResultSet psRs2 = ps2.getGeneratedKeys();while (psRs2.next()) {System.out.println("Found one record");System.out.println(psRs2.getLong(1));}

mysql能够查询返回即便你手动指定的id=1. 但是h2 不支持. 参考: 这里

insert into 的字符串 不能使用双引号

insert into testdb.kbgroups values (1, 'abc', '')

mysql支持使用双引号:

insert into testdb.kbgroups values (1, "abc", "")

参考这里: http://www.h2database.com/html/grammar.html#string

不同connection创建的database无法被看见

        Class.forName("org.h2.Driver");Connection connection = DriverManager.getConnection("jdbc:h2:mem:;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;");Statement s = connection.createStatement();s.execute("create schema testdb");s.execute("create table testdb.testt(id bigint not null primary key auto_increment, a int)");PreparedStatement ps = connection.prepareStatement("insert into testdb.testt values (111, 33)", Statement.RETURN_GENERATED_KEYS);int in = ps.executeUpdate();connection.commit();Connection connection2 = DriverManager.getConnection("jdbc:h2:mem:;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;");System.out.println("use connection1 test:" + tableExists(connection, "testdb", "testt"));System.out.println("use connection2 test:" + tableExists(connection2, "testdb", "testt"));public static boolean tableExists(Connection conn, String dbName, String tableName) throws SQLException {String[] types = {"TABLE"};ResultSet rs = null;try {rs = conn.getMetaData().getTables(null, dbName, tableName, types);if(rs.next()) {return true;}else {return false;}}finally {if (rs != null) {rs.close();}}}

输出:

use connection1 test:true
use connection2 test:false

可以看见确实无法看见另外连接创建的:

​ In-Memory

jdbc:h2:mem:test multiple connections in one process
jdbc:h2:mem: unnamed private; one connection

or certain use cases (for example: rapid prototyping, testing, high performance operations, read-only databases), it may not be required to persist data, or persist changes to the data. This database supports the in-memory mode, where the data is not persisted.In some cases, only one connection to a in-memory database is required. This means the database to be opened is private. In this case, the database URL is jdbc:h2:mem: Opening two connections within the same virtual machine means opening two different (private) databases.Sometimes multiple connections to the same in-memory database are required. In this case, the database URL must include a name. Example: jdbc:h2:mem:db1. Accessing the same database using this URL only works within the same virtual machine and class loader environment.To access an in-memory database from another process or from another computer, you need to start a TCP server in the same process as the in-memory database was created. The other processes then need to access the database over TCP/IP or TLS, using a database URL such as: jdbc:h2:tcp://localhost/mem:db1.By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use 

这条还是比较奇怪的~. 上面也说清楚了内存模式的一些限制/规则:

1. 内存模式, 数据不会持久化

2. 如果想要one connection one database, 使用:jdbc:h2:mem 数据库在连接关闭后关闭. 这样的话即便是同一个jvm的2个连接看到的也是不同的数据库.

3. 如果想在jvm内部共享, 就必须:jdbc:h2:mem:db1 这样. (在jvm级别的classloader共享)

4. 关于连接关闭数据库消失可以设置: ;DB_CLOSE_DELAY=-1 到url

   我创建了issue

这篇关于使用springboottest和h2来构建数据库测试的采坑记录的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---