SpringBoot 整合 MyBatis 配置多数据源操作MySQL数据库_sping boot2.1.3下整合mybatis进行mysql数据库操作是-CSDN博客

本文链接：https://blog.csdn.net/piaoranyuji/article/details/104063149

本文以多个 MySQL 数据库为例，采用 SpringBoot 框架，整合 MyBatis 配置多数据源进行数据库操作。在实际项目中，为了减少流量高峰期间对数据库的压力，可对一些数据库惰性数据（以查询为主，且不经常更新的数据）缓存到 JVM 内存中，可快速响应，且减少数据库压力。
项目源码 git 地址：https://github.com/piaoranyuji/muldb

一、MySQL 表结构（双数据库）

本项目中共用到了 2 个数据库，分别为 testmgmdb 和 testonldb。

testmgmdb 数据库中表 tbl_mgm_menu 的建表语句如下。

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `tbl_mgm_menu`;
CREATE TABLE `tbl_mgm_menu` (
  `menu_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '菜单主键',
  `menu_name` varchar(30) NOT NULL DEFAULT '' COMMENT '菜单名称',
  `menu_logo` varchar(64) NOT NULL DEFAULT '' COMMENT '菜单logo',
  `menu_url` varchar(64) NOT NULL DEFAULT '' COMMENT '菜单url',
  `menu_seq` int(2) NOT NULL DEFAULT '0' COMMENT '菜单顺序',
  `rec_st` char(1) NOT NULL DEFAULT '1' COMMENT '记录状态，0：无效；1：有效',
  `rec_crt_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `rec_upd_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`menu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

INSERT INTO `tbl_mgm_menu` VALUES ('23', '今日播报', 'https://www.baidu.com', 'https://www.baidu.com', '0', '1', '2020-01-21 14:44:03', '2020-01-21 14:44:03');

testonldb 数据库中表 tbl_onl_sp 的建表语句如下。

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `tbl_onl_sp`;
CREATE TABLE `tbl_onl_sp` (
  `sp_id` char(16) NOT NULL DEFAULT '' COMMENT '服务方ID',
  `call_dt` char(8) NOT NULL DEFAULT '' COMMENT '接口调用时间（yyyyMMdd）',
  `sp_name` char(30) NOT NULL DEFAULT '' COMMENT '服务方名称',
  `rec_crt_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `rec_upd_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`sp_id`,`call_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、SpringBoot 配置文件配置数据库连接信息

spring.datasource.mgmdb.jdbc-url=jdbc:mysql://localhost:3306/testmgmdb?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.datasource.mgmdb.username=root
spring.datasource.mgmdb.password=
spring.datasource.mgmdb.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.mgmdb.max-idle=10
spring.datasource.mgmdb.max-wait=10000
spring.datasource.mgmdb.min-idle=5
spring.datasource.mgmdb.initial-size=5
spring.datasource.mgmdb.maximum-pool-size=200
spring.datasource.mgmdb.validation-query=SELECT 1
spring.datasource.mgmdb.test-on-borrow=false
spring.datasource.mgmdb.test-while-idle=true
spring.datasource.mgmdb.time-between-eviction-runs-millis=18800

spring.datasource.onldb.jdbc-url=jdbc:mysql://localhost:3306/testonldb?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.datasource.onldb.username=root
spring.datasource.onldb.password=
spring.datasource.onldb.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.onldb.max-idle=10
spring.datasource.onldb.max-wait=10000
spring.datasource.onldb.min-idle=5
spring.datasource.onldb.initial-size=5
spring.datasource.onldb.maximum-pool-size=200
spring.datasource.onldb.validation-query=SELECT 1
spring.datasource.onldb.test-on-borrow=false
spring.datasource.onldb.test-while-idle=true
spring.datasource.onldb.time-between-eviction-runs-millis=18800

mapper.not-empty=false
mapper.identity=MYSQL

server.port=8899

#出现错误时, 直接抛出异常
spring.mvc.throw-exception-if-no-handler-found=true
#不要为我们工程中的资源文件建立映射
spring.resources.add-mappings=false

spring.aop.proxy-target-class=true

conf.file.path=D:/testconf/muldb/svcConfig.properties
log_dir=D:/testlog

三、配置多数据库DataSource、SqlSessionFactory、SqlSessionTemplate 和 DataSourceTransactionManager

Mgm 数据库配置

package com.test.svc.conf;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.test.svc.dao.mgm"}, sqlSessionTemplateRef = "mgmSqlSessionTemplate")
public class MgmConfig {

    @Bean(name = "mgmDataSource")
    @Primary //必须加此注解，不然报错，下一个类则不需要添加
    @ConfigurationProperties(prefix = "spring.datasource.mgmdb") // prefix值必须是application.properteis中对应属性的前缀
    public DataSource mgmDataSource() {
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        return dataSourceBuilder.build();
    }

    @Bean(name = "mgmSqlSessionFactory")
    @Primary
    public SqlSessionFactory mgmSqlSessionFactory(@Qualifier("mgmDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        //添加XML目录
        try {
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:sqlmapper/mgmMapper/*.xml"));
            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    @Bean(name = "mgmSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate mgmSqlSessionTemplate(@Qualifier("mgmSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "mgmTransactionManager")
    @Primary
    public DataSourceTransactionManager mgmTransactionManager(@Qualifier("mgmDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

Onl 数据库配置

package com.test.svc.conf;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"com.test.svc.dao.onl"}, sqlSessionTemplateRef = "onlSqlSessionTemplate")
public class OnlConfig {

    @Bean(name = "onlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.onldb")
    public DataSource onlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "onlSqlSessionTemplate")
    public SqlSessionTemplate onlSqlSessionTemplate(@Qualifier("onlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "onlSqlSessionFactory")
    public SqlSessionFactory onlSqlSessionFactory(@Qualifier("onlDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);

        try {
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:sqlmapper/onlMapper/*.xml"));
            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    @Bean(name = "onlTransactionManager")
    public DataSourceTransactionManager onlTransactionManager(@Qualifier("onlDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

四、generater 插件配置文件说明

以 Mgm 数据库表的配置文件 generatorMgmDb.xml 为例。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <!--数据库驱动-->
    <context id="mysql4" targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!--数据库链接地址账号密码-->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/testmgmdb"
                        userId="root"
                        password="">
        </jdbcConnection>

        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>

        <!--生成Model类存放位置-->
        <javaModelGenerator targetPackage="com.test.svc.model.mgm" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <!--生成映射文件存放位置-->
        <sqlMapGenerator targetPackage="sqlmapper.mgmMapper" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>

        <!--生成Dao类存放位置-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.test.svc.dao.mgm" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>

        <!--生成对应表及类名-->
        <table tableName="tbl_mgm_menu" domainObjectName="Menu" enableCountByExample="false"
               enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false"
               selectByExampleQueryId="false">
        </table>
    </context>
</generatorConfiguration>

五、项目中 generater 插件配置

点击 idea 菜单栏 Run，单击 Edit Configurations，添加 Maven，配置具体的项目路径和命令行（mybatis-generator:generate），点击 Apply 和 OK。
在这里插入图片描述
依次修改 pom.xml 中的 generater 配置文件，依次为每个表生成对应的 Model、Dao 和 Mapper 文件。

六、创建内存数据刷新和读取方法

在内存中，存储双份数据，即开辟 A、B Cache，并定时刷新。具体应刷新和读取哪块数据，依赖标记量来区分。当刷新 A 区数据时，读取 B 区数据；当刷新 B 区数据时，读取 A 区数据。

package com.test.svc.utils;

import com.test.svc.ApplicationContextHelper;
import com.test.svc.constants.SvcConstant;
import com.test.svc.model.mgm.Menu;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.env.Environment;

import java.util.HashMap;
import java.util.List;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

@Slf4j
public class DualBlockCache {

    /**
     * 内存块取用与刷新状态标志
     * 0：不可用（初态），下次定时任务刷新A cache块
     * 1：A cache块可用，下次定时任务刷新B cache块
     * 2：B cache块可用，下次定时任务刷新A cache块
     */
    public static int indicator = 0;

    // A cache缓存Object
    private static HashMap<String, Object> blockObjectA = new HashMap<>();

    // B cache缓存Object
    private static HashMap<String, Object> blockObjectB = new HashMap<>();

    // 缓存单线程
    private static ScheduledExecutorService executorService = Executors.newSingleThreadScheduledExecutor();

    // 缓存实例
    private static DualBlockCache cache = new DualBlockCache();

    private DualBlockCache() {
    }

    /**
     * 根据key值查找Object
     *
     * @param key 内存中的key值
     * @return 返回Object对象
     */
    public Object getObject(String key) {
        switch (indicator) {
            case 1:
                return blockObjectA.get(key);
            case 2:
                return blockObjectB.get(key);
            default:
                return null;
        }
    }

    public static DualBlockCache getInstance() {
        return cache;
    }

    public void start() {

        // 定时刷新内存数据
        executorService.scheduleWithFixedDelay(new Runnable() {
            @Override
            public void run() {
                // 刷新内存参数
                flushData();

                // 根据reloadCache参数开关判断是否执行下次刷新任务，0：关闭，不刷新内存；1：开启，定时刷新内存,进程shutdown后不能恢复
                Environment environment = ApplicationContextHelper.applicationContext.getEnvironment();
                String reloadCache = PropertyUtil.getProperties(environment.getProperty("conf.file.path"), "reloadCache");
                if ("0".equals(reloadCache)) {
                    executorService.shutdown();
                }
            }
        }, 1L, 600L, TimeUnit.SECONDS); // 项目启动后1秒开始执行，此后10分钟刷新一次
    }

    /**
     * 刷新内存数据
     */
    public void flushData() {

        log.info(DateUtils.now() + " start to reload A-B Cache task ===");

        HashMap<String, Object> blockObject = (indicator == 1) ? blockObjectB : blockObjectA;

        blockObject.clear();

        QueryCacheManageService queryCacheManageService = new QueryCacheManageService();

        List<Menu> listMenu = queryCacheManageService.listMenu();
        for (Menu menu : listMenu) {
            blockObject.put(SvcConstant.MENUINFO + menu.getMenuId(), menu);
        }

        indicator = (indicator == 1) ? 2 : 1;
        log.info("blockObject size : " + blockObject.size());
        log.info("内存数据刷新完毕，indicator = {}", indicator);
    }
}

菜单表数据列表读取方法

package com.test.svc.utils;

import com.test.svc.constants.RecSt;
import com.test.svc.dao.mgm.MenuMapper;
import com.test.svc.model.mgm.Menu;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.util.List;

/**
 * @description 更新内存数据
 */
@Component
public class QueryCacheManageService {

    @Resource
    private MenuMapper menuMapper;

    private static QueryCacheManageService dbCacheMapper;

    @PostConstruct
    public void init() {

        dbCacheMapper = this;
        // 以下5行代码可以注释，也可以保留
        dbCacheMapper.menuMapper = this.menuMapper;
    }

    public List<Menu> listMenu() {
        Menu menu = new Menu();
        menu.setRecSt(RecSt._1.getCode());
        return dbCacheMapper.menuMapper.selectSelective(menu);
    }
}

七、编写测试方法

package com.test.svc.controller;

import com.alibaba.fastjson.JSON;
import com.test.svc.model.mgm.Menu;
import com.test.svc.service.QueryService;
import com.test.svc.utils.DualBlockCache;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

/**
 * @description 刷新内存数据后台入口
 */
@RestController
@Slf4j
public class TestController {

    @Resource
    private QueryService queryService;

    // 手动刷新内存业务参数
    @RequestMapping("/flushData")
    public void flushData() {
        DualBlockCache.getInstance().flushData();
        log.info("手动刷新缓存完成");
    }

    // 手动调整内存刷新标志位为初始态
    @RequestMapping("/setFlag")
    public int setFlag(String indicator) {
        DualBlockCache.indicator = Integer.parseInt(indicator);
        return DualBlockCache.indicator;
    }

    // 获取内存标志位
    @RequestMapping("/getFlag")
    public int getFlag() {
        return DualBlockCache.indicator;
    }

    // 根据 menuId 获取菜单详情（内存查找）
    @RequestMapping("/getMenu")
    public Menu getMenu(String menuId) {

        log.info("内存查询菜单详情，收到菜单主键:[{}]", menuId);
        Menu menu = queryService.getMenu(menuId);
        log.info("内存查询菜单详情，应答数据:[{}]", JSON.toJSONString(menu));
        return menu;
    }

    // 根据 menuId 获取菜单详情（数据库查找）
    @RequestMapping("/getMenu1")
    public Menu getMenu1(String menuId) {

        log.info("数据库查询菜单详情，收到菜单主键:[{}]", menuId);
        Menu menu = queryService.getMenu1(menuId);
        log.info("数据库查询菜单详情，应答数据:[{}]", JSON.toJSONString(menu));
        return menu;
    }
}