H2单元测试与业务数据隔离之内嵌模式

目录

H2支持数据库模型

URL编写构造器

H2Helper用于建表、插入和查询操作

单元测试示例

---

在日常编码过程中，单元测试作为代码的质量校验最重要的手段之一，必须做好数据隔离和代码充分的测试。数据侵入会造成测试困扰。为规范日常单元测试，通常使用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

 项目地址：daily-code-cache: Spring cache的相关使用。