springboot mysql8 数据源 springboot多数据源mybatis

转载

云端筑梦大师 2023-12-07 22:22:58

文章标签 spring boot java mybatis spring sql 文章分类 MySQL 数据库

https://zhuanlan.zhihu.com/p/563949762

mybatis 配置多数据源

使用mybatis配置多数据源我接触过的有两种方式，一种是通过java config的方式手动配置两个数据源，另一种方式便是使用mybatis-plus-dynamic。*

总体来说，配置主要包括，产生DataSource，然后是mybatis所需要的SqlSessionFactory，以及配置相应的事务管理器

<!--MyBatis整合SpringBoot框架的起步依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!-- Mysql驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
        <!-- jdbc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.7.8</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

配置文件 yml

server:
  port: 8080

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave:
      jdbc-url: jdbc:mysql://43.143.217.124:3306/hongbei?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver

java 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.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;

/**
 * @Classname DB1DataSourceConfig
 * @Description DB1DataSourceConfig
 * @Date 2023-02-24 15:14
 * @Created by lihw
 */
@Configuration
@MapperScan(
        basePackages = "com.example.demo.mapper.master",
        sqlSessionFactoryRef = "masterSqlSessionFactory")
public class DB1DataSourceConfig {

    String MAPPER_LOCATION = "classpath*:abc/*.xml";

    @Primary
    @Bean("masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource getMasterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean("masterSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        // 使用 mybatis plus  配置
        //MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        //mybatisSqlSessionFactoryBean.setDataSource(dataSource);
        //mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
        //        .getResources(MAPPER_LOCATION)); // "classpath:mapping/*Mapper.xml"
        //mybatisSqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity");
        //
        //return mybatisSqlSessionFactoryBean.getObject();

        // mybatis 配置
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
              .getResources(MAPPER_LOCATION));
        sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity.master");

        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setMapUnderscoreToCamelCase(true);

        sqlSessionFactoryBean.setConfiguration(configuration);

        return sqlSessionFactoryBean.getObject();
    }

    @Primary
    @Bean("masterSqlSessionTemplate")
    public SqlSessionTemplate  sqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
        return sqlSessionTemplate;
    }

    @Bean("masterTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager(dataSource);
        return dataSourceTransactionManager;
    }


}

配置数据源二

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;

/**
 * @Classname DB1DataSourceConfig
 * @Description DB1DataSourceConfig
 * @Date 2023-02-24 15:14
 * @Created by lihw
 */
@Configuration
@MapperScan(
        basePackages = "com.example.demo.mapper.slave",
        sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class DB2DataSourceConfig {

    String MAPPER_LOCATION = "classpath*:slave/*.xml";

    //@Primary
    @Bean("slaveDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource getSlaveDataSource() {
        return DataSourceBuilder.create().build();
    }

    //@Primary
    @Bean("slaveSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        // mybatis plus配置
        //MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        //mybatisSqlSessionFactoryBean.setDataSource(dataSource);
        //mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
        //        .getResources(MAPPER_LOCATION)); // "classpath:mapping/*Mapper.xml"
        //mybatisSqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity");
        //
        //return mybatisSqlSessionFactoryBean.getObject();


        // mybatis 配置
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MAPPER_LOCATION));
        sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity.slave");

        // 设置mybatis配置
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        // 下划线转驼峰
        configuration.setMapUnderscoreToCamelCase(true);

        sqlSessionFactoryBean.setConfiguration(configuration);

        return sqlSessionFactoryBean.getObject();
    }

    //@Primary
    @Bean("slaveSqlSessionTemplate")
    public SqlSessionTemplate  sqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
        return sqlSessionTemplate;
    }

    @Bean("slaveTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager(dataSource);
        return dataSourceTransactionManager;
    }


}

目录结构
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K95yKTJe-1677467535741)(https://note.youdao.com/yws/res/4010/WEBRESOURCE3a7424410d156d5dc0d6bd00cd7bf638 “image.png”)]

测试接口

@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    StudentMapper studentMapper;

    @Autowired
    SysUserMapper sysUserMapper;
	// 数据源一 查询
    @GetMapping("list")
    public List<Student> getUserList(){

        List<Student> userList = studentMapper.getUserList();
        System.out.println(userList);

        return userList;
    }
	// 数据源二 查询
    @GetMapping("msg2")
    public String getmsg2(){

        List<SysUser> user = sysUserMapper.getUserList();
        System.out.println(user);

        return "msg22";
    }

}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZSCJmOj9-1677467535743)(https://note.youdao.com/yws/res/4016/WEBRESOURCE0cc80d06ca0c483d00bb9e34f16ef10d)]

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.1.1</version>
</dependency>

# mybatis-plus-dynamic 配置多数据源
spring:
  datasource:
    dynamic:
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave:
          url: jdbc:mysql://43.143.217.124:3306/hongbei?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
      # 指定主数据库
      primary: master

#mybatis:
#  mapper-locations: classpath*:abc/*.xml,classpath*:slave/*.xml

mybatis-plus:
  mapper-locations: classpath*:abc/*.xml,classpath*:slave/*.xml

使用案例

@Service
@DS("slave-1")
public class TbServiceImpl extends ServiceImpl<TbDao, TbBean> implements TbService {
    @Override
    public String save1() {
        TbBean tbBean = new TbBean();
        tbBean.setName("王五");
        tbBean.setSubject("英语");
        tbBean.setScore(113);
        this.save(tbBean);
        return "success";
    }
}

此处是模拟的一个新增操作，注意类上面的@DS注解，该注解可以标注在类或方法上面；也可以标注在Mapper接口上面，但是不建议同时在Mapper和service上同时标注，可能会出现问题。该注解的value属性便是对应于在yaml中配置的数据源名称，如果没有给值，默认就是使用数据源名为master的数据源。

踩坑日记：

如下代码：

@Service
@DS("master")
public class UserServiceImpl extends ServiceImpl<UserDao, UserBean> implements UserService {
 
    @Autowired
    private TbService tbService;
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void add() {
        UserBean userBean = new UserBean();
        userBean.setId(3);
        userBean.setLoginName("zhangsan");
        userBean.setName("张三");
        userBean.setPassword("123456");
        this.save(userBean);
		// 第二个数据源
        tbService.save1();
    }
}

意思就是我想在保存userBean时同时调用一下tbService的save1方法，注意tbService被@DS(“slave-1”)注解标注，它对应于sqlServer数据库的操作。当直接调用上面的add方法时，会报如下的错误：

springboot mysql8 数据源 springboot多数据源mybatis_java

反正就是死活找不到tb这张表，实际上tb这张表是确实存在于sqlServer数据库中的，之所以报错是由于加事务的原因@Transactional(rollbackFor = Exception.class)，由于spring事务默认的传播级别是：

Propagation.REQUIRED

这个事务的特性就是如果上级方法调用时已经获取了事务，则该方法内调用的其它事务方法将复用同一个事务，结果就是对userBean的操作是对应于mysql的，由于加了事务，所以tbService.save1()方法还是在该事务内，造成的结果就是会在mysql数据库中找tb这张表，肯定找不到，结果就报错了，解决方式如下：

@Service
@DS("slave-1")
public class TbServiceImpl extends ServiceImpl<TbDao, TbBean> implements TbService {
    @Override
    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW)
    public String save1() {
        TbBean tbBean = new TbBean();
        tbBean.setName("王五");
        tbBean.setSubject("英语");
        tbBean.setScore(113);
        this.save(tbBean);
        return "success";
    }
}

给save1()方法加上 @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW) ，使其在每次获取事务时都是重新产生一个，不再复用上级方法的事务。

<!-- Druid 数据连接池依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.9</version>
        </dependency>
       <!--MyBatis整合SpringBoot框架的起步依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <!-- jdbc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.7.8</version>
        </dependency>
        <!-- Mysql驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>

# druid 多数据源配置
master:
  datasource:
    url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

slave:
  datasource:
    url: jdbc:mysql://43.143.217.124:3306/hongbei?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

数据源一配置

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;



/**
 * @Classname DruidConfig
 * @Description DruidConfig
 * @Date 2023-02-27 10:45
 * @Created by lihw
 */
@Configuration
@MapperScan(basePackages = {DruidConfig.PACKAGE},
        sqlSessionFactoryRef = "masterSqlSessionFactory")
public class DruidConfig {

    // 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.example.demo.mapper.master";
    static final String MAPPER_LOCATION = "classpath:abc/**/*.xml";

    @Value("${master.datasource.url}")
    private String url;

    @Value("${master.datasource.username}")
    private String user;

    @Value("${master.datasource.password}")
    private String password;

    @Value("${master.datasource.driver-class-name}")
    private String driverClass;

    @Bean("masterDataSource")
    @Primary
    public DataSource masterDataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(driverClass);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(user);
        druidDataSource.setPassword(password);
        return druidDataSource;
    }

    @Bean("masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager(){
        return new DataSourceTransactionManager(masterDataSource());
    }

    @Bean("masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MAPPER_LOCATION));
        // mybatis 配置
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        // 驼峰下划线
        configuration.setMapUnderscoreToCamelCase(true);
        sqlSessionFactoryBean.setConfiguration(configuration);

        return sqlSessionFactoryBean.getObject();
    }

}

数据源二配置

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


/**
 * @Classname DruidConfig
 * @Description DruidConfig
 * @Date 2023-02-27 10:45
 * @Created by lihw
 */
@Configuration
@MapperScan(basePackages = {DruidConfig2.PACKAGE},
        sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class DruidConfig2 {

    // 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.example.demo.mapper.slave";
    static final String MAPPER_LOCATION = "classpath:slave/**/*.xml";

    @Value("${slave.datasource.url}")
    private String url;

    @Value("${slave.datasource.username}")
    private String user;

    @Value("${slave.datasource.password}")
    private String password;

    @Value("${slave.datasource.driver-class-name}")
    private String driverClass;

    @Bean("slaveDataSource")
    @Primary
    public DataSource slaveDataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(driverClass);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(user);
        druidDataSource.setPassword(password);
        return druidDataSource;
    }

    @Bean("slaveTransactionManager")
    @Primary
    public DataSourceTransactionManager slaveTransactionManager(){
        return new DataSourceTransactionManager(slaveDataSource());
    }

    @Bean("slaveSqlSessionFactory")
    @Primary
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MAPPER_LOCATION));
        // mybatis 配置
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        // 驼峰下划线
        configuration.setMapUnderscoreToCamelCase(true);
        sqlSessionFactoryBean.setConfiguration(configuration);

        return sqlSessionFactoryBean.getObject();
    }

}

测试代码

@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    StudentMapper studentMapper;

    @Autowired
    SysUserMapper sysUserMapper;

    @GetMapping("list")
    public List<Student> getUserList(){

        List<Student> userList = studentMapper.getUserList();
        System.out.println(userList);

        return userList;
    }

    @GetMapping("msg2")
    public String getmsg2(){

        List<SysUser> user = sysUserMapper.getUserList();
        System.out.println(user);

        return "msg22";
    }

}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VnOmBExy-1677467535745)(https://note.youdao.com/yws/res/4051/WEBRESOURCE1a8c5b356140fb73fc973a659243a302)]

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。