目录
在日常编码过程中，单元测试作为代码的质量校验最重要的手段之一，必须做好数据隔离和代码充分的测试。数据侵入会造成测试困扰。为规范日常单元测试，通常使用H2内存数据库来实现单元测试数据库数据持久化校验。h2分为三种使用场景：内嵌模式、服务器模式和混合模式。本篇讲述的是内嵌模式。
H2支持数据库模型
h2支持的数据库模型如下
public static enum ModeEnum {
REGULAR,
DB2,
Derby,
MSSQLServer,
HSQLDB,
MySQL,
Oracle,
PostgreSQL,
Ignite;
private ModeEnum() {
}
}URL编写构造器
下面是本地化的文件和内存数据库实现的Builder
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Data
public class H2URLBuilder {
final String start="jdbc:h2:";
Mode.ModeEnum mode;
String scriptPath;
boolean isInit;
boolean isDbCloseDelay;
public String getURL(){
StringBuilder sb = new StringBuilder();
sb.append(start).append(isInit?"~/test":"mem:test");
if(null == mode){
throw new UnsupportedOperationException("mode can not be null");
}
sb.append(";MODE=").append(mode);
if(isInit){
if(StringUtils.isNullOrEmpty(scriptPath)){
throw new UnsupportedOperationException("scriptPath is null you need set " +
" like ./src/test/resources/createTable.sql");
}
sb.append(";INIT=runscript from '").append(scriptPath).append("'");
}
if(isDbCloseDelay){
sb.append(";DB_CLOSE_DELAY=-1");
}
return sb.toString();
}
}H2Helper用于建表、插入和查询操作
@Slf4j
public class H2Helper {
/**
* getJdbcTemplate
* @param dataSource
* @return
*/
public static JdbcTemplate getJdbcTemplate(DataSource dataSource){
Assert.notNull(dataSource,"dataSource is null");
return new JdbcTemplate(dataSource);
}
/**
* getJdbcTemplate
* @param builder
* @return
*/
public static JdbcTemplate getJdbcTemplate(H2URLBuilder builder){
DataSource dataSource = H2Helper.getDataSource(builder);
Assert.notNull(dataSource,"dataSource is null");
return new JdbcTemplate(dataSource);
}
/**
* getDataSource
* @return
*/
public static DataSource getDataSource(H2URLBuilder builder){
JdbcDataSource ds = new JdbcDataSource();
String url = builder.getURL();
log.debug("URL--->{}",url);
ds.setURL(url);
ds.setUser("sa");
ds.setPassword("");
return ds;
}
/**
* createTable
* @param jdbcTemplate
* @param sql
*/
public static void createOrDropTable(JdbcTemplate jdbcTemplate,String sql){
try (Connection connection = jdbcTemplate.getDataSource().getConnection()){
TransactionSynchronizationManager.initSynchronization();
connection.setAutoCommit(false);
jdbcTemplate.execute(sql);
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}finally {
TransactionSynchronizationManager.clearSynchronization();
}
}
/**
* save
* @param jdbcTemplate
* @param sql
* @param args
*/
public static void save(JdbcTemplate jdbcTemplate,String sql,Object[] args){
try (Connection connection = jdbcTemplate.getDataSource().getConnection()){
TransactionSynchronizationManager.initSynchronization();
connection.setAutoCommit(false);
jdbcTemplate.update(sql,args);
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}finally {
TransactionSynchronizationManager.clearSynchronization();
}
}
/**
* query
* @param jdbcTemplate
* @param sql
* @param args
* @return
*/
public static List<Map<String,Object>> select(JdbcTemplate jdbcTemplate,String sql,Object[] args){
List<Map<String,Object>> queryForList =jdbcTemplate.queryForList(sql,args);
Assert.notNull(queryForList,"queryForList is null");
return queryForList;
}
}单元测试示例
此处测试脚本文件和内存建库操作
@Slf4j
@SpringBootTest
public class H2HelperTest {
String insert = "insert into `user`(id,name,mobile) values(?,?,?) ";
String select = "select * from `user`";
Object [] args = new Object[]{"id_test","name_test","mobile_test"};
@Test
public void initMode(){
H2URLBuilder builder = H2URLBuilder.builder()
.isInit(true)
.mode(Mode.ModeEnum.MySQL)
.scriptPath("./src/test/resources/createTable.sql")
.build();
DataSource dataSource =H2Helper.getDataSource(builder);
Assert.notNull(dataSource,"dataSource is null");
JdbcTemplate jdbcTemplate = H2Helper.getJdbcTemplate(builder);
// save
H2Helper.save(jdbcTemplate,insert,args);
// query
H2Helper.select(jdbcTemplate,select,null);
}
@Test
public void memMode(){
H2URLBuilder builder = H2URLBuilder.builder()
.isInit(false)
.mode(Mode.ModeEnum.MySQL)
.build();
DataSource dataSource =H2Helper.getDataSource(builder);
Assert.notNull(dataSource,"dataSource is null");
JdbcTemplate jdbcTemplate = H2Helper.getJdbcTemplate(builder);
String createTable = "CREATE TABLE `user` (\n" +
" `id` char(32) NOT NULL,\n" +
" `name` varchar(255) NOT NULL,\n" +
" `mobile` varchar(64) DEFAULT NULL\n" +
")";
// createTable
H2Helper.createOrDropTable(jdbcTemplate,"DROP TABLE IF EXISTS `user`");
H2Helper.createOrDropTable(jdbcTemplate,createTable);
// save
H2Helper.save(jdbcTemplate,insert,args);
// query
H2Helper.select(jdbcTemplate,select,null);
}
}测试结果：
参考文章：运行模式 · leaning-h2
