spring项目内存数据库方案

零、为了项目的长治久安，需要单元测试。单元测试需要跑很多案例，原理上说我们不希望将单元测试的实例跑在我们的生产服务器上，因此一般采取内存数据库的方式。

一般有两种方式：H2简易内存数据库配置、Mysql内存数据库设配置

一、Myslq内存数据库设置

1、依赖

testImplementation 'com.wix:wix-embedded-mysql:4.6.1'

2、测试类启动类文件

路径 /test/java/com/longze/ApplicationTest.java

import com.mysql.cj.jdbc.MysqlDataSource;
import com.wix.mysql.ScriptResolver;
import com.wix.mysql.SqlScriptSource;
import com.wix.mysql.config.DownloadConfig;
import com.wix.mysql.config.MysqldConfig;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
import org.springframework.cloud.openfeign.EnableFeignClients;
import org.springframework.context.annotation.*;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.scheduling.annotation.EnableAsync;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.TimeZone;
import java.util.concurrent.TimeUnit;

import static com.wix.mysql.EmbeddedMysql.anEmbeddedMysql;
import static com.wix.mysql.config.Charset.UTF8;
import static com.wix.mysql.config.MysqldConfig.aMysqldConfig;
import static com.wix.mysql.distribution.Version.v5_7_10;

@SpringBootApplication(exclude= {DataSourceAutoConfiguration.class})
@EnableDiscoveryClient //开启服务注册与发现功能
@EnableFeignClients //开启Feign
@EnableAsync //开启多线程支持
//禁止加载 WebappApplication
@ComponentScan(value = {"com.longze"},excludeFilters = {
        @ComponentScan.Filter(type = FilterType.ASSIGNABLE_TYPE, classes = {MainApplication.class})})
//指定配置文件
@PropertySource(value = "classpath:application-test.properties")
public class ApplicationTest {

    @Bean(name = "data2SqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mysql2DataSource")DataSource mysql2DataSource) throws Exception{
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(mysql2DataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mappers
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for T_CAR
-- ----------------------------
DROp TABLE IF EXISTS `T_CAR`;
CREATE TABLE `T_CAR`  (
`id` bigint(100) NOT NULL AUTO_INCREMENT COMMENT '主键',
`carName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '汽车品种名',
`createTime` datetime(0) NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 114689 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '汽车表' ROW_FORMAT = Dynamic;

②视图初始化脚本

路径 db/view.sql

DROP VIEW IF EXISTS `CAR_V`;
CREATE VIEW `CAR_V` AS select `car`.`id` AS `carID`,`car`.`name` AS `carName`,`catecater`.`NAME` AS `carTypeName`
from (`T_CAR` `car`  left join `T_CATTYPE` `catecater` on(`car`.`typeId` = `catecater`.`ID`)));

③函数初始化脚本 db/function.sql

-- ----------------------------
-- Function structure for UUID_FACTORY
-- ----------------------------
DROP FUNCTION IF EXISTS `UUID_FACTORY`;
delimiter ;;
CREATE DEFINER=`user`@`%` FUNCTION `UUID_FACTORY`() RETURNS varchar(50) CHARSET utf8mb4
    COMMENT '生成UUID'
begin
    declare i varchar(55);
select replace(UUID(),'-','') into i;
RETURN i;
end
;;
delimiter ;

④初始化人员脚本 db/customer.sql

INSERT INTO `CC_USER` (`CREATED_ON`,`CREATED_BY`,`MODIFIED_ON`,`MODIFIED_BY`,`STATE_CODE`,`ID`,`USER_NAME`) VALUES (20220304152613,'zhangfei',20220402150718,'zhangfei','0','1','zhangfei');
INSERT INTO `CC_USER` (`CREATED_ON`,`CREATED_BY`,`MODIFIED_ON`,`MODIFIED_BY`,`STATE_CODE`,`ID`,`USER_NAME`) VALUES (20210706183949,'liubei',20210706183949,'liubei','0','2','liubei');

⑤也是和各个业务强行绑定关系的sql脚本，非通用型脚本

比如上面CarControllerTest.java 汽车专卖接口需要的初始化脚本 db/Cardata.sql

INSERT INTO `T_CAR` (`CREATED_ON`,`CREATED_BY`,`MODIFIED_ON`,`MODIFIED_BY`,`STATE_CODE`,`ID`,`CAR_NAME`) VALUES (20220304152613,'leijun',20220402150718,'leijun','0','001','小米汽车');
INSERT INTO `T_CAR` (`CREATED_ON`,`CREATED_BY`,`MODIFIED_ON`,`MODIFIED_BY`,`STATE_CODE`,`ID`,`CAR_NAME`) VALUES (20210706183949,'mayun',20210706183949,'mayun','0','002','码云汽车');

关于CarTest需要的初始化脚本，可以按照从业务的Mysql导出sql给内存数据库初始化好。

8、排查问题心得：这样就将业务代码需要的单测实例按照内存数据库来管理，后续接口有调整德华。内存数据库直接报错。在调试接口中，通过加断点

然后用Navicat来链接本地3306端口的内存数据库，可以查询到自己初始化的表和数据，来测试单测得执行情况。

二、H2内存数据库

1、依赖

compile(group: 'com.h2database', name: 'h2', version: '2.1.210')