【十八】Spring Boot之多数据源(指定每个数据源对应哪些DAO，配置每个数据源的事务管理器、sqlsessionfactory)

最新推荐文章于 2024-08-22 11:13:11 发布

jy02268879

最新推荐文章于 2024-08-22 11:13:11 发布

阅读量5.3k

点赞数 1

分类专栏： spring boot 文章标签：多数据源多套DAO Spring Boot

本文链接：https://blog.csdn.net/jy02268879/article/details/84370428

版权

spring boot 专栏收录该内容

26 篇文章 6 订阅

订阅专栏

目录结构

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.sid</groupId>
    <artifactId>multi-datasource</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.8.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- spring-boot的web启动的jar包 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- spring-boot 测试包 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <!-- mysql数据库连接包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

        <!-- alibaba的druid数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>

            <!-- mybatis generator 自动生成代码插件 -->
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
                <configuration>
                    <configurationFile>${basedir}/src/main/resources/generator/generatorConfig.xml</configurationFile>
                    <overwrite>true</overwrite>
                    <verbose>true</verbose>
                </configuration>
            </plugin>

        </plugins>
    </build>

</project>

application.yml

server:
  port: 8088
  context-path: /sid

spring:
  datasource:
    # 使用druid数据源
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    druid:
      one:  #数据源1
        url: jdbc:mysql://localhost:3306/sid
        username: root
        password: root
      two: #数据源2
        url: jdbc:mysql://localhost:3306/test
        username: root
        password: root
      #初始化时建立物理连接的个数
      initialSize: 1
      #池中最大连接数
      maxActive: 20
      #最小空闲连接
      minIdle: 1
      #获取连接时最大等待时间，单位毫秒
      maxWait: 60000
      #有两个含义：
      #1) Destroy线程会检测连接的间隔时间，如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。
      #2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明
      timeBetweenEvictionRunsMillis: 60000
      #连接保持空闲而不被驱逐的最小时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      #使用该SQL语句检查链接是否可用。如果validationQuery=null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
      validationQuery: SELECT 1 FROM DUAL
      #建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
      testWhileIdle: true
      #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
      testOnBorrow: false
      #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
      testOnReturn: false
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,slf4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      #connectionProperties.druid.stat.mergeSql: true
      #connectionProperties.druid.stat.slowSqlMillis: 5000
      # 合并多个DruidDataSource的监控数据
      #useGlobalDataSourceStat: true
      #default-auto-commit: true 默认
      #default-auto-commit: false

## 该配置节点为独立的节点，不是在在spring的节点下
mybatis:
  mapper-locations: classpath:mapping/*/*.xml  #注意：一定要对应mapper映射xml文件的所在路径
  type-aliases-package: com.sid.model  # 注意：对应实体类的路径
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #控制台打印sql

主数据源配置类

要为每个数据源写配置类，

配置类中定义各自的数据源、事务管理器、SqlSessionFactory，如果用到JdbcTemplate还需要定义JdbcTemplate。

指定哪些mapper用这个数据源。

package com.sid.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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.boot.context.properties.ConfigurationProperties;
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.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @program: multidatasource
 * @description:
 * @author: Sid
 * @date: 2018-11-22 19:26
 * @since: 1.0
 **/
@Configuration
@MapperScan(basePackages = OneDataSourcesConfig.PACKAGES, sqlSessionFactoryRef = "oneSqlSessionFactory")
public class OneDataSourcesConfig {
    static final String PACKAGES = "com.sid.mapper.pen";

    private static final String MAPPER_LOCAL = "classpath:mapping/pen/*.xml";

    @ConfigurationProperties("spring.datasource.druid.one")
    @Primary
    @Bean(name = "oneDataSource")
    public DruidDataSource druidDataSource() {
        DruidDataSource build = DruidDataSourceBuilder.create().build();
        return build;
        //不要用下面这个，不然application.yml中配置的druid的maxActive之类的属性加载不进去
        //return new DruidDataSource();
    }


    @Bean(name = "oneTransactionManager")
    @Primary
    public DataSourceTransactionManager oneTransactionManager(@Qualifier("oneDataSource")DataSource oneDataSource) {
        //return new DataSourceTransactionManager(druidDataSource());
        return new DataSourceTransactionManager(oneDataSource);
    }




    @Bean(name = "oneSqlSessionFactory")
    @Primary
    public SqlSessionFactory oneSqlSessionFactory(@Qualifier("oneDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCAL));
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "oneJdbcTemplate")
    public JdbcTemplate primaryJdbcTemplate(@Qualifier("oneDataSource")DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }

}

第二个数据源配置类

package com.sid.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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.boot.context.properties.ConfigurationProperties;
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.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @program: multidatasource
 * @description:
 * @author: Sid
 * @date: 2018-11-22 19:29
 * @since: 1.0
 **/
@Configuration
@MapperScan(basePackages = TwoDataSourcesConfig.PACKAGES, sqlSessionFactoryRef = "twoSqlSessionFactory")
public class TwoDataSourcesConfig {
    static final String PACKAGES = "com.sid.mapper.test";

    private static final String MAPPER_LOCAL = "classpath:mapping/test/*.xml";

    @ConfigurationProperties("spring.datasource.druid.two")
    @Bean(name = "twoDataSource")
    public DruidDataSource druidDataSource() {
        DruidDataSource build = DruidDataSourceBuilder.create().build();
        return build;
        //不要用下面这个，不然application.yml中配置的druid的maxActive之类的属性加载不进去
        //return new DruidDataSource();

    }


    @Bean(name = "twoTransactionManager")
    public DataSourceTransactionManager twoTransactionManager() {
        return new DataSourceTransactionManager(druidDataSource());
    }


    @Bean(name = "twoSqlSessionFactory")
    public SqlSessionFactory twoSqlSessionFactory(@Qualifier("twoDataSource") DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCAL));
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "twoJdbcTemplate")
    public JdbcTemplate primaryJdbcTemplate(@Qualifier("twoDataSource")DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
}

启动类

package com.sid;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @program: springboot
 * @description: springboot启动类
 * @author: liyijie
 * @create: 2018-10-16 08:58
 **/

@SpringBootApplication
@MapperScan("com.sid.mapper.*")//将项目中对应的mapper类的路径加进来就可以了
public class App {
    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }
}

model

package com.sid.model;

public class User {
    private Long id;

    private String mobilePhone;

    private String name;

    private String password;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getMobilePhone() {
        return mobilePhone;
    }

    public void setMobilePhone(String mobilePhone) {
        this.mobilePhone = mobilePhone == null ? null : mobilePhone.trim();
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password == null ? null : password.trim();
    }
}

主数据源DAO

package com.sid.mapper.pen;

import com.sid.model.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;


@Mapper
public interface UserMapperPen {
    @Insert("insert into user(mobile_phone, name,password) values(#{mobilePhone},#{name},#{password})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertSelective(User user);

}

第二数据源DAO

package com.sid.mapper.test;

import com.sid.model.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;

@Mapper
public interface UserMapperTest {

    @Insert("insert into user(mobile_phone, name,password) values(#{mobilePhone},#{name},#{password})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertSelective(User user);

}

service

使用事务的时候要指明是使用哪个事务

package com.sid.service.impl;

import com.sid.mapper.pen.UserMapperPen;
import com.sid.mapper.test.UserMapperTest;
import com.sid.model.User;
import com.sid.service.MultiService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

/**
 * @program: multi datasource
 * @description:
 * @author: Sid
 * @date: 2018-11-22 19:11
 * @since: 1.0
 **/
@Service
public class MultiServiceImpl implements MultiService {
    @Autowired
    @Qualifier("oneJdbcTemplate")
    private JdbcTemplate jt;
    @Autowired
    @Qualifier("twoJdbcTemplate")
    private JdbcTemplate jt2;

    @Resource
    private UserMapperPen userMapperPen;

    @Resource
    private UserMapperTest userMapperTest;

    @Override
    public User addUser(User user) {
        return addUserTest(user);
    }

    @Override
    @Transactional(value="oneTransactionManager")
    public User addUserPen(User user) {
        userMapperPen.insertSelective(user);
        //int i = 1/0;
        return user;
    }

    @Override
    @Transactional(value="twoTransactionManager")
    public User addUserTest(User user) {
        userMapperTest.insertSelective(user);
        //int i = 1/0;
        return user;
    }
}