springboot+mybatis+mysql+yml配置多数据源

最新推荐文章于 2024-05-17 09:04:20 发布

tq_theSuperMan

最新推荐文章于 2024-05-17 09:04:20 发布

阅读量1.7k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： JAVA 积累

本文链接：https://blog.youkuaiyun.com/weixin_38689747/article/details/114590305

JAVA 同时被 2 个专栏收录

17 篇文章

订阅专栏

积累

12 篇文章

订阅专栏

本文介绍如何在SpringBoot项目中配置两个MySQL数据源，包括使用MyBatis和YML文件进行详细设置的方法。涉及数据源配置、连接池设置、Mapper扫描等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文通过最基本的设置，配置springboot 2个mysql数据源，搭配mybatis和yml

YML内容

server:
  port: 9150
spring:

  #mysql
  datasource:
    product:#第一个数据源
      url: jdbc:mysql://xx.xx.xx.xx:3306/xx?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=GMT%2B8&useSSL=false
      username: root
      password: xxx
      mapper-locations: classpath:/xmlmappers/pro/*.xml
      driver-class-name: com.mysql.cj.jdbc.Driver
    coordinate:#第二个数据源
      url: jdbc:mysql://xx.xx.xx.xx:3306/xx?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE&serverTimezone=GMT%2B8&useSSL=false
      username: root
      password: xxx
      mapper-locations: classpath:/xmlmappers/co/*.xml
      driver-class-name: com.mysql.cj.jdbc.Driver
    druid:#druid配置
      # 连接池的配置信息
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,log4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000

DataSource等BEAN配置

主（默认）数据源
请注意bean名称的对应 ,对应的mapperScan的位置以及@Primary标签标示默认
注意xml文件位置

package xx.config;

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.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

/**
 * @author tq
 * @version V1.0
 * @Package xxx
 * @date 2021-03-09 15:25
 * @Copyright © 2018-2019 *******
 */
@Configuration
@MapperScan(basePackages = "com.iptv.coordinator.mapper.coordinate", sqlSessionTemplateRef  = "coordinateSqlSessionTemplate")
public class CoordinateDatasourceConfig {

    @Value("${spring.datasource.coordinate.url}")
    private String url;
    @Value("${spring.datasource.coordinate.username}")
    private String username;
    @Value("${spring.datasource.coordinate.password}")
    private String password;
    @Value("${spring.datasource.coordinate.mapper-locations}")
    private String mapperLocations;
    @Value("${spring.datasource.coordinate.driver-class-name}")
    private String driverClassName;

    /**
     * 配置数据源
     */
    @Bean(name="coordinateDataSource")
    @Primary
    public DataSource setDataSource(){
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);
        return  dataSource;
    }

    @Bean(name = "coordinateSqlSessionFactory")
    @Primary
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("coordinateDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(mapperLocations));//配置xml文件位置
        return bean.getObject();
    }

    @Bean(name = "coordinateTransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("coordinateDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

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

}

副（第二个）数据源

package xx.config;

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.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

/**
 * @author tq
 * @version V1.0
 * @Package xx
 * @date 2021-03-09 15:25
 * @Copyright © 2018-2019 *******
 */
@Configuration
@MapperScan(basePackages = "com.iptv.coordinator.mapper.product", sqlSessionTemplateRef  = "productSqlSessionTemplate")
public class ProductDatasourceConfig {

    @Value("${spring.datasource.product.url}")
    private String url;
    @Value("${spring.datasource.product.username}")
    private String username;
    @Value("${spring.datasource.product.password}")
    private String password;
    @Value("${spring.datasource.coordinate.mapper-locations}")
    private String mapperLocations;
    @Value("${spring.datasource.product.driver-class-name}")
    private String driverClassName;


    /**
     * 配置数据源
     */
    @Bean(name="productDataSource")
    public DataSource setDataSource(){
        DriverManagerDataSource dataSource=new DriverManagerDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);
        return  dataSource;
    }


    @Bean(name = "productSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("productDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(mapperLocations));
        return bean.getObject();
    }

    @Bean(name = "productTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("productDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "productSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("productSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

Mapper配置

需要配置2个mapper，内容相同只是放在不同的文件夹下，就不全部举例了
请参考项目结构

package xx.mapper.coordinate;

import com.iptv.coordinator.model.Test;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Service;

/**
 * @author tq
 * @version V1.0
 * @Package xx.mapper.coordinate
 * @date 2021-03-09 16:28
 * @Copyright © 2018-2019 *******
 */
@Service
@Mapper
public interface TestMapperCo {

    @Insert("INSERT INTO test(s1,s2) VALUES(#{s1},#{s2})")
    void addOne(Test test);
}

模型

/**
 * @author tq
 * @version V1.0
 * @Package com.iptv.coordinator.model
 * @date 2021-03-09 16:38
 * @Copyright © 2018-2019 *******
 */
@Data
public class Test {

    private String s1;
    private String s2;
}

单元测试类

package com.iptv.coordinator;

import com.iptv.coordinator.mapper.coordinate.TestMapperCo;
import com.iptv.coordinator.mapper.product.TestMapperPro;
import com.iptv.coordinator.model.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import javax.annotation.Resource;

/**
 * @author tq
 * @version V1.0
 * @Package com.iptv.coordinator
 * @date 2021-03-09 15:27
 * @Copyright © 2018-2019 *******
 */
@SpringBootTest(classes = CoordinatorApp.class)
@RunWith(SpringRunner.class	)
@WebAppConfiguration
public class CTest {

    @Resource
    TestMapperCo co;
    @Resource
    TestMapperPro pro;

    @org.junit.Test
    public void testRabbitMQ() throws InterruptedException {

        Test t1=new Test();
        t1.setS1("pro");
        t1.setS2("pro");

        Test t2=new Test();
        t2.setS1("co");
        t2.setS2("co");

        co.addOne(t2);//分别写两个库
        pro.addOne(t1);

    }
}