使用h2作为单元测试数据库

application.properties

spring.profiles.active=test
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
logging.level.root=info
logging.level.org.hibernate=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicExtractor=DEBUG
logging.level.com.itmuch=DEBUG

web访问数据库

 @Bean(name = "h2WebServer", initMethod = "start", destroyMethod = "stop")
  //启动一个H2的web server， 调试时可以通过localhost:8082访问到H2的内容
  //JDBC URL: jdbc:h2:mem:testdb
  //User Name: sa
  //Password: 无
  //注意如果使用断点，断点类型(Suspend Type)一定要设置成Thread而不能是All,否则web server无法正常访问!
  public Server server() throws Exception {
    //在8082端口上启动一个web server
    Server server = Server.createWebServer("-web", "-webAllowOthers", "-webDaemon", "-webPort", "8082");
    return server;
  }

@Configuration
@Profile("test")
public class DataSourceTestConfig {

  @Bean(name = "tagDataSource")
  @Qualifier("tagDataSource")
  @Primary
  public DataSource tagDataSource() {
    EmbeddedDatabaseBuilder databaseBuilder = new EmbeddedDatabaseBuilder();

    EmbeddedDatabase db = databaseBuilder
        .setType(EmbeddedDatabaseType.H2)
        //启动时初始化建表语句
        .addScript("classpath:tag-schema.sql")
        .build();

    return db;
  }

  @Bean(name = "hbaseDataSource")
  @Qualifier("hbaseDataSource")
  @ConfigurationProperties(prefix = "hbase.datasource", locations = "classpath:datasource-dev.properties")
  public DataSource hbaseDataSource() {
    DruidDataSource druidDataSource = new DruidDataSource();
    try {
      druidDataSource.setFilters("stat");
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return druidDataSource;
  }
}

配置

driverClassName=org.h2.Driver
url=jdbc:h2:men:test
username=sa
password=

初始化脚本

/*
注意 不支持timestamp 自动更新  这里的sql也是有问题的
还有就是要开启spring.jpa.hibernate.ddl-auto=none
否则线上数据库会被清空
还有就是h2 严格区分sql里面的单引号和双引号 字符串必须使用单引号
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` varchar(64) NOT NULL,
  `org_id` varchar(32) DEFAULT NULL COMMENT '企业ID',
  `user_name` varchar(60) DEFAULT NULL COMMENT '用户姓名',
  `user_tag` varchar(64) DEFAULT NULL COMMENT '姓名标签',
  `user_sign` varchar(64) DEFAULT NULL COMMENT '人员唯一标志（学号，学籍号，工号。。。） 现在暂为 学生：学号，老师：工号',
  `photo_url` varchar(200) DEFAULT NULL COMMENT '头像地址 ../user_id.jpg',
  `born_date` varchar(50) DEFAULT NULL COMMENT '出生日期',
  `sex` varchar(32) DEFAULT NULL COMMENT '姓别  1:男; 2:女',
  `is_delete` varchar(1) DEFAULT NULL COMMENT '是否删除 1:删除；0：正常',
  `create_user` varchar(64) DEFAULT NULL,
  `gmt_create` datetime DEFAULT NULL,
  `modify_user` varchar(64) DEFAULT NULL,
  `gmt_modify` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `tag_project_tag_group`;
CREATE TABLE `tag_project_tag_group` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `tag_project_tag_user_backup`;
CREATE TABLE `tag_project_tag_user_backup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` varchar(255) DEFAULT NULL,
  `user_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `tag_project_user_tag_checked`;
CREATE TABLE `tag_project_user_tag_checked` (
  `id` varchar(40) NOT NULL DEFAULT '' ,
  `name` varchar(60) DEFAULT NULL ,
  `is_delete` tinyint(1) DEFAULT NULL ,
  `modify_time` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `is_checked` tinyint(1) DEFAULT NULL ,
  `is_group` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_group
-- ----------------------------
DROP TABLE IF EXISTS `user_group`;
CREATE TABLE `user_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) DEFAULT NULL,
  `application_id` varchar(11) DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_group_event_group
-- ----------------------------
DROP TABLE IF EXISTS `user_group_event_group`;
CREATE TABLE `user_group_event_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_group_id` int(11) DEFAULT NULL,
  `event_id` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_group_event_status
-- ----------------------------
DROP TABLE IF EXISTS `user_group_event_status`;
CREATE TABLE `user_group_event_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) DEFAULT NULL,
  `code` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_group_events
-- ----------------------------
DROP TABLE IF EXISTS `user_group_events`;
CREATE TABLE `user_group_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  `modify_time` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_group_groups
-- ----------------------------
DROP TABLE IF EXISTS `user_group_groups`;
CREATE TABLE `user_group_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  `modify_time` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `user_group` VALUES ('7', '12', '1', '0', '2017-08-28 17:29:51', '2017-08-28 17:29:51');
INSERT INTO `user_group` VALUES ('8', '13', '1', '0', '2017-08-28 17:37:45', '2017-08-28 17:37:45');