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单元测试，也是有不尽人意的地方：
| matthewcasperson.blogspot.com/2013/07/exp… 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.还有未发现待补充。。。 |
|---|
目前致力于寻求解决方案：
| h2database.com/html/links.… github.com/bgranvea/my… |
|---|
H2使用步骤：
test-tools项目已经封装了H2的依赖和默认设置，故只需引入pom即可
| ```html com.xueqiu.infra test-tools 1.0.3
| ------------------------------------------------------------------------------------------------------------------------------------------ |
version版本根据实时情况更新
H2使用示例：
只需指定schema和data的sql脚本文件地址，通过list形式传入即可，详情见如下示例地址
示例地址：
源码版：<http://git.snowballfinance.com/lib/test-tools>
| ```html
/** * h2 replace mysql unit test * * 使用H2代替mysqld进行单元测试，注意会出现SQL脚本的语法兼容问题 */
``````html
@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](http://git.snowballfinance.com/lib/xueqiu-push)
| ```html
test的resource目录下添加sql文件 src --main --test --java --包路径 --TestConfig.java --BaseTests.java --H2DatasourceTest.java --resources --sql --demo_schema.sql --demo_data.sql
``` |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
demo_schema.sql
| ```html
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
| ```html
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
| ```html
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
| ```html
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
| ```html
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); } }
``` |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
