druid连接池 + myBatis 多数据源管理

程序员文章站 2022-07-15 10:47:19

...

项目场景：druid连接池 + myBatis 多数据源管理

引入pom依赖：

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.3</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.1</version>
        </dependency>
        <dependency>
 			<groupId>org.springframework.boot</groupId>
 			<artifactId>spring-boot-configuration-processor</artifactId>
 			<optional>true</optional>
		</dependency>

创建mapper文件：

在项目中创建mapper文件夹
druid连接池 + myBatis 多数据源管理
在资源路径classpath下创建mapper文件夹，并创建mapper文件

添加mapper 接口内容

package cn.edu.bistu.cs.example.mapper;

import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
@Repository
public interface ExampleMapper {

    List<Map<String, Object>> getExampleData();

}

添加mapper xml内容
需要注意：此处的namespace需要指向项目中的Mapper接口文件

<?xml version="1.0" encoding="UTF-8"?>
<!--    遇到http://mybatis.org/dtd/mybatis-3-mapper.dtd报红的问题
        需要在File Settings -> Languages & Frameworks -> Schemas and DTDs 中Ignored Schemas and DTDs 添加该字符串即可->
        -->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<mapper namespace="cn.edu.bistu.cs.example.mapper.ExampleMapper">
    <select id="getExampleData" resultType="Map">
        select * from your_table_name;
    </select>
</mapper>

配置文件application.properties：

将需要管理的数据源配置到配置文件中

#数据库配置
spring.datasource.data1.url=jdbc:mysql://localhost:3306/databasename1?serverTimezone=GMT%2B8
spring.datasource.data1.username=root
spring.datasource.data1.password=123456
spring.datasource.data1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.data2.url=jdbc:mysql://localhost:3307/databasename2?serverTimezone=GMT%2B8
spring.datasource.data2.username=root
spring.datasource.data2.password=123456
spring.datasource.data2.driver-class-name=com.mysql.cj.jdbc.Driver

#mybatis配置mapper_locations
mybatis.type-aliases-package=cn.edu.bistu.cs.example.poi.model
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath:mapper/*.xml

#连接池配置
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=50
spring.datasource.druid.max-wait=60000
spring.datasource.druid.connection-error-retry-attempts=10
spring.datasource.druid.break-after-acquire-failure=true
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.stat-view-servlet.login-username=root
spring.datasource.druid.stat-view-servlet.login-password=123456
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.stat-view-servlet.enabled=true

# 数据源配置类

需要管理几个数据源，需要创建几个管理类，此处以两个为例

DruidDataSource1Config.java

package cn.edu.bistu.cs.example.druid;

import com.alibaba.druid.pool.DruidDataSource;
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.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.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@ConfigurationProperties(prefix = "spring.datasource.druid")
//此处配置mybatis的mapper接口位置，需要指向项目中mapper接口的文件
@MapperScan(basePackages = "cn.edu.bistu.cs.example.mapper", sqlSessionTemplateRef = "db1SqlSessionTemplate")
public class DruidDataSource1Config {
    //mybatis 配置文件路径，xml文件路径
    @Value("${mybatis.mapper-locations}")
    private String mapper_locations;

    private int maxActive;
    private int initialSize;
    private int minIdle;
    private int maxWait;
    private int connectionErrorRetryAttempts;
    private boolean breakAfterAcquireFailure;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public void setConnectionErrorRetryAttempts(int connectionErrorRetryAttempts) {
        this.connectionErrorRetryAttempts = connectionErrorRetryAttempts;
    }

    public void setBreakAfterAcquireFailure(boolean breakAfterAcquireFailure) {
        this.breakAfterAcquireFailure = breakAfterAcquireFailure;
    }

    public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }

    @Bean
    //ConfigurationProperties读取并且设置我们在application.properties配置的内容.
    @ConfigurationProperties(prefix = "spring.datasource.data1")
    //@Primary这个注解用来标识当存在多个相同的类型的bean时,优先选用哪个bean注入,
    // 需要注意的是,配置多数据源的时候,必须有一个且只能有一个@Primary注解
    @Primary
    public DataSource db1DataSource() {
        //之前使用的DataSourceBuilder.create().build()
        //导致spring.datasource.druid.*配置的属性都没有生效
        DruidDataSource db1DataSource = new DruidDataSource();
        db1DataSource.setMaxActive(maxActive);
        db1DataSource.setInitialSize(initialSize);
        db1DataSource.setMinIdle(minIdle);
        db1DataSource.setMaxWait(maxWait);
        db1DataSource.setConnectionErrorRetryAttempts(connectionErrorRetryAttempts);
        db1DataSource.setBreakAfterAcquireFailure(breakAfterAcquireFailure);
        db1DataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        db1DataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        db1DataSource.setValidationQuery(validationQuery);
        db1DataSource.setTestWhileIdle(testWhileIdle);
        db1DataSource.setTestOnBorrow(testOnBorrow);
        return db1DataSource;
    }

    @Bean
    @Primary
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapper_locations));
        return bean.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager db1TransactionManager(@Qualifier("db1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    @Primary
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

DruidDataSource2Config.java

package cn.edu.bistu.cs.example.druid;

import com.alibaba.druid.pool.DruidDataSource;
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.boot.context.properties.ConfigurationProperties;
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
@ConfigurationProperties(prefix = "spring.datasource.druid")
@MapperScan(basePackages = "cn.edu.bistu.cs.example.mapper", sqlSessionTemplateRef = "db2SqlSessionTemplate")
public class DruidDataSource2Config {
    //mybatis 配置文件路径
    @Value("${mybatis.mapper-locations}")
    private String mapper_locations;

    private int maxActive;
    private int initialSize;
    private int minIdle;
    private int maxWait;
    private int connectionErrorRetryAttempts;
    private boolean breakAfterAcquireFailure;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public void setConnectionErrorRetryAttempts(int connectionErrorRetryAttempts) {
        this.connectionErrorRetryAttempts = connectionErrorRetryAttempts;
    }

    public void setBreakAfterAcquireFailure(boolean breakAfterAcquireFailure) {
        this.breakAfterAcquireFailure = breakAfterAcquireFailure;
    }

    public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }


    @Bean
    //ConfigurationProperties读取并且设置我们在application.properties配置的内容.
    @ConfigurationProperties(prefix = "spring.datasource.data2")
    public DataSource db2DataSource() {
        //之前使用的DataSourceBuilder.create().build()
        //导致spring.datasource.druid.*配置的属性都没有生效
        DruidDataSource db2DataSource = new DruidDataSource();
        db2DataSource.setMaxActive(maxActive);
        db2DataSource.setInitialSize(initialSize);
        db2DataSource.setMinIdle(minIdle);
        db2DataSource.setMaxWait(maxWait);
        db2DataSource.setConnectionErrorRetryAttempts(connectionErrorRetryAttempts);
        db2DataSource.setBreakAfterAcquireFailure(breakAfterAcquireFailure);
        db2DataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        db2DataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        db2DataSource.setValidationQuery(validationQuery);
        db2DataSource.setTestWhileIdle(testWhileIdle);
        db2DataSource.setTestOnBorrow(testOnBorrow);
        return db2DataSource;
    }

    @Bean
    public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapper_locations));
        return bean.getObject();
    }

    @Bean
    public DataSourceTransactionManager db2TransactionManager(@Qualifier("db2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

相关标签：笔记 java spring boot 开发语言

上一篇：闭包——循环中的定时器

下一篇：用c语言实现简单三子棋游戏

druid连接池 + myBatis 多数据源管理

项目场景：druid连接池 + myBatis 多数据源管理

引入pom依赖：

创建mapper文件：

配置文件application.properties：

DruidDataSource1Config.java

DruidDataSource2Config.java

Springboot mybatis plus druid多数据源解决方案 dynamic-datasource的使用详解

SpringBoot + MyBatis + Druid连接池配置多数据源

Springboot集成通用Mapper与Pagehelper，实现mybatis+Druid的多数据源配置

springboot整合mybatis采用druid连接池对mysql，hive双数据源整合并打包

springboot使用Druid连接池并集成mybatis

SpringBoot集成mybatis(配置通用mapper)并且使用druid作为数据库连接池

springboot多数据源配置mybatis采用druid连接池对mysql，hive双数据源整合

druid数据库连接池双数据源

druid连接池 + myBatis 多数据源管理

springboot 整合mybatis和druid数据源连接池