SpringBoot integrates MyBatis to configure multiple data sources to operate MySQL database

Keywords: Spring Database JDBC Mybatis

Taking several MySQL databases as an example, this paper adopts spring boot framework to integrate MyBatis to configure multiple data sources for database operation. In the actual project, in order to reduce the pressure on the database during the peak flow period, we can cache some database inert data (mainly for query, and infrequently updated data) into the JVM memory, which can respond quickly and reduce the pressure on the database.
Project source git address: https://github.com/piaoranyuji/muldb

1, MySQL table structure (double database)

Two databases, testmgmdb and testonldb, are shared in this project.

  1. The table creation statement of TABLE tbl? MGM? Menu in testmgdb database is as follows.
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 key',
  `menu_name` varchar(30) NOT NULL DEFAULT '' COMMENT 'Menu name',
  `menu_logo` varchar(64) NOT NULL DEFAULT '' COMMENT 'menu logo',
  `menu_url` varchar(64) NOT NULL DEFAULT '' COMMENT 'menu url',
  `menu_seq` int(2) NOT NULL DEFAULT '0' COMMENT 'Menu order',
  `rec_st` char(1) NOT NULL DEFAULT '1' COMMENT 'Record status, 0: invalid, 1: valid',
  `rec_crt_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `rec_upd_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  PRIMARY KEY (`menu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

INSERT INTO `tbl_mgm_menu` VALUES ('23', 'Broadcast today', 'https://www.baidu.com', 'https://www.baidu.com', '0', '1', '2020-01-21 14:44:03', '2020-01-21 14:44:03');
  1. The table creation statement of TABLE tbl? Onl? SP in testonldb database is as follows.
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 'Service provider ID',
  `call_dt` char(8) NOT NULL DEFAULT '' COMMENT 'Interface call time( yyyyMMdd)',
  `sp_name` char(30) NOT NULL DEFAULT '' COMMENT 'Name of service provider',
  `rec_crt_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `rec_upd_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
  PRIMARY KEY (`sp_id`,`call_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2, SpringBoot configuration file configuration database connection information
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

#Throw an exception directly when an error occurs
spring.mvc.throw-exception-if-no-handler-found=true
#Do not map the resource files in our project
spring.resources.add-mappings=false

spring.aop.proxy-target-class=true

conf.file.path=D:/testconf/muldb/svcConfig.properties
log_dir=D:/testlog
3, Configure multi database DataSource, SqlSessionFactory, SqlSessionTemplate, and DataSourceTransactionManager
  1. Mgm database configuration
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 //This annotation must be added. Otherwise, an error is reported. The next class does not need to be added
    @ConfigurationProperties(prefix = "spring.datasource.mgmdb") // The prefix value must be the prefix of the corresponding property in 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);
        //Add XML directory
        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);
    }
}
  1. Onl database configuration
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);
    }
}
4, Generator plug in configuration file description

Take the configuration file generatorMgmDb.xml of the Mgm database table as an example.

<?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>
    <!--Database driven-->
    <context id="mysql4" targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>
        <!--Database link address account password-->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/testmgmdb"
                        userId="root"
                        password="">
        </jdbcConnection>

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

        <!--generate Model Class storage location-->
        <javaModelGenerator targetPackage="com.test.svc.model.mgm" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <!--Build mapping file storage location-->
        <sqlMapGenerator targetPackage="sqlmapper.mgmMapper" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>

        <!--generate Dao Class storage location-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.test.svc.dao.mgm" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>

        <!--Generate corresponding table and class name-->
        <table tableName="tbl_mgm_menu" domainObjectName="Menu" enableCountByExample="false"
               enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false"
               selectByExampleQueryId="false">
        </table>
    </context>
</generatorConfiguration>
5, Configuration of the generator plug-in in the project

Click Run in the idea menu bar, click Edit Configurations, add Maven, configure the specific project path and command line (mybatis generator: generate), and click Apply and OK.

Modify the generator configuration file in pom.xml in turn, and generate the corresponding Model, Dao and Mapper files for each table in turn.

6, Create memory data refresh and read methods

In memory, double data are stored, i.e. A and B caches are opened and refreshed regularly. Which data should be refreshed or read depends on the tag quantity. When refreshing area A data, read area B data; when refreshing area B data, read area A data.

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 {

    /**
     * Memory block access and refresh status flag
     * 0: Unavailable (initial), refresh A cache block for next scheduled task
     * 1: A cache Block available, refresh B cache block for next scheduled task
     * 2: B cache Block available, refresh A cache block for next scheduled task
     */
    public static int indicator = 0;

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

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

    // Cache single thread
    private static ScheduledExecutorService executorService = Executors.newSingleThreadScheduledExecutor();

    // Cache instance
    private static DualBlockCache cache = new DualBlockCache();

    private DualBlockCache() {
    }

    /**
     * Find Object according to key value
     *
     * @param key key value in memory
     * @return Return Object 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() {

        // Refresh memory data regularly
        executorService.scheduleWithFixedDelay(new Runnable() {
            @Override
            public void run() {
                // Refresh memory parameters
                flushData();

                // According to the reloadCache parameter switch, judge whether to perform the next refresh task. 0: close, do not refresh memory. 1: open, refresh memory regularly, and cannot be recovered after the process 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); // The project starts to execute 1 second after startup, and refreshes once every 10 minutes
    }

    /**
     * Refresh memory data
     */
    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("Refresh of memory data completed, indicator = {}", indicator);
    }
}

Reading method of menu table data list

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 Update memory data
 */
@Component
public class QueryCacheManageService {

    @Resource
    private MenuMapper menuMapper;

    private static QueryCacheManageService dbCacheMapper;

    @PostConstruct
    public void init() {

        dbCacheMapper = this;
        // The following five lines of code can be annotated or retained
        dbCacheMapper.menuMapper = this.menuMapper;
    }

    public List<Menu> listMenu() {
        Menu menu = new Menu();
        menu.setRecSt(RecSt._1.getCode());
        return dbCacheMapper.menuMapper.selectSelective(menu);
    }
}
7, Write test methods
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 Refresh memory data background entry
 */
@RestController
@Slf4j
public class TestController {

    @Resource
    private QueryService queryService;

    // Refresh memory business parameters manually
    @RequestMapping("/flushData")
    public void flushData() {
        DualBlockCache.getInstance().flushData();
        log.info("Manual cache refresh complete");
    }

    // Manually adjust the memory refresh flag to the initial state
    @RequestMapping("/setFlag")
    public int setFlag(String indicator) {
        DualBlockCache.indicator = Integer.parseInt(indicator);
        return DualBlockCache.indicator;
    }

    // Get memory flags
    @RequestMapping("/getFlag")
    public int getFlag() {
        return DualBlockCache.indicator;
    }

    // Get menu details according to menuId (memory search)
    @RequestMapping("/getMenu")
    public Menu getMenu(String menuId) {

        log.info("Memory query menu details, receive menu primary key:[{}]", menuId);
        Menu menu = queryService.getMenu(menuId);
        log.info("Memory query menu details, answer data:[{}]", JSON.toJSONString(menu));
        return menu;
    }

    // Get menu details according to menuId (database search)
    @RequestMapping("/getMenu1")
    public Menu getMenu1(String menuId) {

        log.info("Database query menu details, receive menu primary key:[{}]", menuId);
        Menu menu = queryService.getMenu1(menuId);
        log.info("Database query menu details, response data:[{}]", JSON.toJSONString(menu));
        return menu;
    }
}

After testing, the database operation is normal, and the memory data can be refreshed and read regularly.

Published 30 original articles, won praise 11, visited 8685
Private letter follow

Posted by blues on Tue, 21 Jan 2020 03:03:57 -0800

Hot Keywords

©2024 Programmer Group