embedded-db-junit：JUnit Rule for providing an embedded in-memory database for your tests
mysql-connector-mxj：MySQL Connector/MXJ is a Java Utility package for deploying and managing a MySQL database.
h2：H2 is an embeddable RDBMS written in Java.
使用H2做内存数据库进行MySQL单元测试，也是有不尽人意的地方：
| http://matthewcasperson.blogspot.com/2013/07/exporting-from-mysql-to-h2.html
1.mysql单引号用'\'转义，H2用 ''两个单引号转义
2.msyql十六进制导入H2失败（这个没试验出来，没搞懂应用场景）
3.mysql一位default值是b'0'，H2用0
4.H2 key键设置不能使用范围，例如： KEY "ATextFieldKey" ("ATextField"(255))
5.清除mysql脚本中的character字符集设置
6.清除mysql脚本中的COLLATE字段设置
7.清除mysql脚本中BLOBS，CLOBS和TEXT字段上的索引
8.H2要保证整个database的索引名字唯一
9.对于外键，H2要求关联的表在key创建前已经存在
10.mysql的字段类型enum、json在H2里面不支持
11.还有未发现待补充。。。 |
目前致力于寻求解决方案：
H2使用步骤：
test-tools项目已经封装了H2的依赖和默认设置，故只需引入pom即可
<dependency>
<groupId>com.xueqiu.infra</groupId>
<artifactId>test-tools</artifactId>
<version>1.0.3</version>
</dependency>
|
version版本根据实时情况更新
H2使用示例：
只需指定schema和data的sql脚本文件地址，通过list形式传入即可，详情见如下示例地址
示例地址：
源码版：http://git.snowballfinance.com/lib/test-tools
/**
* h2 replace mysql unit test
*
* 使用H2代替mysqld进行单元测试，注意会出现SQL脚本的语法兼容问题
*/
@Test
public void testH2(){
TestEnvironment env = new TestEnvironmentBuilder()
.withH2Database(Arrays.asList("h2/demo_schema.sql", "h2/demo_data.sql"))
.build();
try {
QueryRunner queryRunner = new QueryRunner();
Connection conn = TestEnvironment.jdbcDataSource().get().getConnection();
String query = "select * from test.t_demo";
List<Map<String, Object>> listOfMaps = queryRunner.query(conn, query, new MapListHandler());
System.out.println(listOfMaps);
Assert.assertEquals(listOfMaps.size(), 2);
} catch (SQLException se) {
Assert.assertTrue(false);
throw new RuntimeException("Couldn't query the h2.", se);
}
}
|
示例地址：
spring版：http://git.snowballfinance.com/hekuangsheng/xueqiu-push
test的resource目录下添加sql文件
src
--main
--test
--java
--包路径
--TestConfig.java
--BaseTests.java
--H2DatasourceTest.java
--resources
--sql
--demo_schema.sql
--demo_data.sql
|
demo_schema.sql
DROP SCHEMA IF EXISTS `test`;
CREATE SCHEMA `test`;
DROP TABLE IF EXISTS `test`.`t_demo`;
CREATE TABLE `test`.`t_demo` (
`id` BIGINT (20) NOT NULL DEFAULT '0' COMMENT '主键',
`demo_key` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '',
`demo_value` VARCHAR (255) NOT NULL DEFAULT '0' COMMENT '',
PRIMARY KEY (`id`)
);
|
demo_data.sql
insert into `test`.t_demo(id, demo_key, demo_value) values (1, 'key','value');
insert into `test`.t_demo(id, demo_key, demo_value) values (2, 'key2','value2');
|
TestConfig.java
import com.xueqiu.infra.test.tools.TestEnvironment;
import com.xueqiu.infra.test.tools.TestEnvironmentBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.List;
@ComponentScan(
basePackages = {
"com.xueqiu.infra.push.server.entity",
},
excludeFilters = {
@ComponentScan.Filter(Controller.class)
}
)
@Configuration
public class TestConfig {
private TestEnvironment env = new TestEnvironmentBuilder()
.withH2Database(Arrays.asList("sql/demo_schema.sql", "sql/demo_data.sql"))
.build();
@Bean(name = {"dataSource", "userDataSource", "snowballDataSource", "recommendBaseDataSource"})
public DataSource dataSource() {
return TestEnvironment.jdbcDataSource()
.orElseThrow(() -> new RuntimeException("init test data source fail"));
}
@Bean(name = "xueqiupushTemplate")
public JdbcTemplate jdbcTemplate() {
return new JdbcTemplate(dataSource());
}
@Bean
public PlatformTransactionManager transactionManager(DataSource dataSource) {
DataSourceTransactionManager tm = new DataSourceTransactionManager();
tm.setDataSource(dataSource);
return tm;
}
}
|
BaseTests.java
import org.junit.Ignore;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;
import org.springframework.transaction.annotation.Transactional;
@Ignore
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = TestConfig.class)
@TestExecutionListeners(TransactionalTestExecutionListener.class)
@Transactional
public class BaseTests extends AbstractJUnit4SpringContextTests {
}
|
H2DatasourceTest.java
import org.junit.Assert;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
import java.util.Map;
public class H2DatasourceTest extends BaseTests {
@Autowired
private JdbcTemplate xueqiupushTemplate;
@Test
public void initToken_test() {
String query = "select * from test.t_demo";
List<Map<String, Object>> result = xueqiupushTemplate.queryForList(query);
Assert.assertEquals(result.size(),2);
}
}
|
