单元测试接入H2

单元测试接入h2隔离真实库

1.maven增加依赖

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.200</version>
    <scope>test</scope>
</dependency>

2.数据库配置

db.mysql.driverClassName=org.h2.Driver
db.url=jdbc:h2:mem:testdb;MODE=MYSQL;DB_CLOSE_DELAY=-1
db.user=root
db.password=123456

3. 各xml配置

spring-dao-test.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
             http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"
       default-autowire="byName">

    <aop:aspectj-autoproxy/>
    <import resource="classpath:spring/persistence-test.xml"/>
</beans>

persistence-test.xml

• 此示例使用的是ibatis

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
	 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
	 http://www.springframework.org/schema/jdbc
     http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">

  <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="locations">
      <list>
        <value>classpath:dao/important.properties</value>
      </list>
    </property>
  </bean>
  <bean id="mysqlDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
    <property name="driverClassName" value="${db.mysql.driverClassName}"/>
    <property name="url" value="${db.url}"/>
    <property name="username" value="root"/>
    <property name="password" value="123456"/>
    <property name="timeBetweenEvictionRunsMillis" value="120000"/>  
    <property name="minEvictableIdleTimeMillis" value="180000"/>  
    <property name="numTestsPerEvictionRun" value="20"/>  
    <property name="maxActive" value="20"/>  
    <property name="maxIdle" value="5"/>  
    <property name="minIdle" value="5"/>  
    <property name="maxWait" value="1500"/>  
    <property name="removeAbandoned" value="true"/>  
    <property name="removeAbandonedTimeout" value="20"/>  
    <property name="validationQuery" value="select 1"/> 
  </bean>  
  <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> 
    <property name="dataSource" ref="mysqlDataSource"/> 
  </bean>  
  <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate"> 
    <property name="transactionManager" ref="transactionManager" /> 
  </bean>  
  <bean id="transactionCustomTemplate" class="org.springframework.transaction.support.TransactionTemplate"> 
    <property name="transactionManager" ref="transactionManager"/> 
    <property name="propagationBehaviorName" value="PROPAGATION_REQUIRES_NEW"/> 
  </bean>
  <bean id="sqlSessionFactory" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
    <property name="configLocation" value="classpath:sqlmap-config.xml"/>
  </bean>
  
 
  <bean id="useDao" class="com.user.dao.impl.UserDaoImpl">
    <property name="dataSource" ref="mysqlDataSource"></property>
    <property name="sqlMapClient" ref="sqlSessionFactory"></property>
  </bean>
  
   <jdbc:initialize-database data-source="mysqlDataSource" ignore-failures="NONE">
       <jdbc:script location="classpath:database/user.sql"/>
   </jdbc:initialize-database>
  
</beans>

• 如果是mybatis使用此配置

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
	 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
	 http://www.springframework.org/schema/jdbc
     http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">
	 
  <bean id="mysqlDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
    <property name="driverClassName" value="${db.mysql.driverClassName}"/>  
    <property name="url" value="${db.url}"/>  
    <property name="username" value="${db.user}"/>  
    <property name="password" value="${db.password}"/>  
    <property name="timeBetweenEvictionRunsMillis" value="120000"/>  
    <property name="minEvictableIdleTimeMillis" value="180000"/>  
    <property name="numTestsPerEvictionRun" value="20"/>  
    <property name="maxActive" value="20"/>  
    <property name="maxIdle" value="5"/>  
    <property name="minIdle" value="5"/>  
    <property name="maxWait" value="1500"/>  
    <property name="removeAbandoned" value="true"/>  
    <property name="removeAbandonedTimeout" value="20"/>  
    <property name="validationQuery" value="select 1"/> 
  </bean>  
  <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> 
    <property name="dataSource" ref="mysqlDataSource"/> 
  </bean>  
  <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate"> 
    <property name="transactionManager" ref="transactionManager" /> 
  </bean>  
  <bean id="transactionCustomTemplate" class="org.springframework.transaction.support.TransactionTemplate"> 
    <property name="transactionManager" ref="transactionManager"/> 
    <property name="propagationBehaviorName" value="PROPAGATION_REQUIRES_NEW"/> 
  </bean>  
  <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 
    <property name="dataSource" ref="mysqlDataSource"/>  
    <property name="configLocation" value="classpath:mybatis-config.xml"/> 
  </bean>  
  <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate"> 
    <constructor-arg index="0" ref="sqlSessionFactory"/> 
  </bean>  
  <bean id="useDao" class="com.user.dao.impl.UserDaoImpl">
    <property name="dataSource" ref="mysqlDataSource"></property>
    <property name="sqlMapClient" ref="sqlSessionFactory"></property>
  </bean>
  
   <jdbc:initialize-database data-source="mysqlDataSource" ignore-failures="NONE">
       <jdbc:script location="classpath:database/user.sql"/>
   </jdbc:initialize-database>
  
</beans>

建表sql

• 注意：h2数据库不支持utf8mb4_bin，utf8_bin 字符集，用这些字符会建表失败，报语法错误
• utf8mb4_bin，utf8_bin 与utf8 自行百度释义

 `userId` VARCHAR(64) COLLATE UTF8_BIN NOT NULL COMMENT '用户唯一标识'
 需要改成
  `userId` VARCHAR(64)  NOT NULL COMMENT '用户唯一标识',

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for ledger_config_solution
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `act_fail_info` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `userId` VARCHAR(64)  NOT NULL COMMENT '用户唯一标识',
    `createTime` DATETIME(3) DEFAULT NULL COMMENT '创建时间',
    `updateTime` DATETIME(3) DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (`id`),
)  ENGINE=INNODB AUTO_INCREMENT=22191623 DEFAULT CHARSET=UTF8 COMMENT='用户信息表';