关于Springboot 配置多数据源 Druid 德鲁伊

admin 管理员组

文章数量: 1087131

关于Springboot 配置多数据源 Druid 德鲁伊

关于Springboot 配置多数据源 Druid 德鲁伊

• • yml
  • config

yml

server:port: 8080servlet:encoding:charset: UTF-8
spring:resources:static-locations: classpath:/staticapplication:name:springboot_listaop:proxy-target-class: trueauto: truemvc:static-path-pattern: /**thymeleaf:check-template-location: true  # 检查模板位置是否正确（默认值 :true ）check-template: true  # 检查模板是否存在，然后再呈现content-type: text/htmlenabled: trueencoding: UTF-8cache: false  # 开启模板缓存（默认值： true ）mode: HTML5prefix: classpath:/templates/suffix: .htmldatasource:#使用druid连接池type: com.alibaba.druid.pool.DruidDataSourceprimary:datasource:#druid相关配置druid:#监控统计拦截的filtersfilters: statdriverClassName: com.mysql.cj.jdbc.Driver#配置基本属性url: username: password: #配置初始化大小/最小/最大initialSize: 1minIdle: 1maxActive: 20#获取连接等待超时时间maxWait: 60000#间隔多久进行一次检测，检测需要关闭的空闲连接timeBetweenEvictionRunsMillis: 60000#一个连接在池中最小生存的时间minEvictableIdleTimeMillis: 300000validationQuery: SELECT 'x'testWhileIdle: truetestOnBorrow: falsetestOnReturn: false#打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为falsepoolPreparedStatements: falsemaxPoolPreparedStatementPerConnectionSize: 20# 自定义的从数据源配置信息
time:datasource:#druid相关配置druid:#监控统计拦截的filtersfilters: statdriverClassName: com.mysql.cj.jdbc.Driver#配置基本属性url: username: password: #配置初始化大小/最小/最大initialSize: 1minIdle: 1maxActive: 20#获取连接等待超时时间maxWait: 60000#间隔多久进行一次检测，检测需要关闭的空闲连接timeBetweenEvictionRunsMillis: 60000#一个连接在池中最小生存的时间minEvictableIdleTimeMillis: 300000validationQuery: SELECT 'x'testWhileIdle: truetestOnBorrow: falsetestOnReturn: false#打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为falsepoolPreparedStatements: falsemaxPoolPreparedStatementPerConnectionSize: 20sqlser:datasource:druid:#监控统计拦截的filtersfilters: statusername: password: url: driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriverinitialSize: 1minIdle: 1maxActive: 20#获取连接等待超时时间maxWait: 60000#间隔多久进行一次检测，检测需要关闭的空闲连接timeBetweenEvictionRunsMillis: 60000#一个连接在池中最小生存的时间minEvictableIdleTimeMillis: 300000validationQuery: SELECT 'x'testWhileIdle: truetestOnBorrow: falsetestOnReturn: false#打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为falsepoolPreparedStatements: falsemaxPoolPreparedStatementPerConnectionSize: 20#      url: jdbc:mysql://192.168.1.66:3306/hnks_qzj?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT&useSSL=false
#      driver-class-name: com.mysql.cj.jdbc.Driver
#      username: hnks
#      password: hnksadmin
#      initial-size: 1
#      min-idle: 1
#      max-active: 20mybatis-plus:mapper-locations: classpath:mapper/*/*.xmltype-aliases-package: com.hnks.springboot_list.entity.*configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplmap-underscore-to-camel-case: true #驼峰logging:level:com.hnks.springboot_list.mapper.*: debug

config

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;import javax.sql.DataSource;
import java.sql.SQLException;/*** @Description: 后台数据源配置类*/@Data
@Configuration
@Component
@ConfigurationProperties(prefix = "time.datasource.druid")
@MapperScan(basePackages = BackDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "backSqlSessionFactory")
public class BackDataBaseConfig {/*** dao层的包路径*/static final String PACKAGE = "com.hnks.springboot_list.mapper.time";/*** mapper文件的相对路径*/private static final String MAPPER_LOCATION = "classpath:mapper/time/*.xml";private String filters;private String url;private String username;private String password;private String driverClassName;private int initialSize;private int minIdle;private int maxActive;private long maxWait;private long timeBetweenEvictionRunsMillis;private long minEvictableIdleTimeMillis;private String validationQuery;private boolean testWhileIdle;private boolean testOnBorrow;private boolean testOnReturn;private boolean poolPreparedStatements;private int maxPoolPreparedStatementPerConnectionSize;@Bean(name = "backDataSource")public DataSource backDataSource() throws SQLException {DruidDataSource druid = new DruidDataSource();// 监控统计拦截的filtersdruid.setFilters(filters);// 配置基本属性druid.setDriverClassName(driverClassName);druid.setUsername(username);druid.setPassword(password);druid.setUrl(url);//初始化时建立物理连接的个数druid.setInitialSize(initialSize);//最大连接池数量druid.setMaxActive(maxActive);//最小连接池数量druid.setMinIdle(minIdle);//获取连接时最大等待时间，单位毫秒。druid.setMaxWait(maxWait);//间隔多久进行一次检测，检测需要关闭的空闲连接druid.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);//一个连接在池中最小生存的时间druid.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);//用来检测连接是否有效的sqldruid.setValidationQuery(validationQuery);//建议配置为true，不影响性能，并且保证安全性。druid.setTestWhileIdle(testWhileIdle);//申请连接时执行validationQuery检测连接是否有效druid.setTestOnBorrow(testOnBorrow);druid.setTestOnReturn(testOnReturn);//是否缓存preparedStatement，也就是PSCache，oracle设为true，mysql设为false。分库分表较多推荐设置为falsedruid.setPoolPreparedStatements(poolPreparedStatements);// 打开PSCache时，指定每个连接上PSCache的大小druid.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);return druid;}@Bean(name = "backTransactionManager")public DataSourceTransactionManager backTransactionManager() throws SQLException {return new DataSourceTransactionManager(backDataSource());}@Bean(name = "backSqlSessionFactory")public SqlSessionFactory backSqlSessionFactory(@Qualifier("backDataSource") DataSource backDataSource) throws Exception {final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();sessionFactory.setDataSource(backDataSource);sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(BackDataBaseConfig.MAPPER_LOCATION));return sessionFactory.getObject();}
}

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import lombok.Data;
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.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;import javax.sql.DataSource;
import java.sql.SQLException;@Data
@Configuration
// 前缀为primary.datasource.druid的配置信息
@Component
@ConfigurationProperties(prefix = "primary.datasource.druid")
@MapperScan(basePackages = PrimaryDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataBaseConfig {/*** dao层的包路径*/static final String PACKAGE = "com.hnks.springboot_list.mapper.primary";/*** mapper文件的相对路径*/private static final String MAPPER_LOCATION = "classpath:mapper/primary/*.xml";private String filters;private String url;private String username;private String password;private String driverClassName;private int initialSize;private int minIdle;private int maxActive;private long maxWait;private long timeBetweenEvictionRunsMillis;private long minEvictableIdleTimeMillis;private String validationQuery;private boolean testWhileIdle;private boolean testOnBorrow;private boolean testOnReturn;private boolean poolPreparedStatements;private int maxPoolPreparedStatementPerConnectionSize;// 主数据源使用@Primary注解进行标识@Primary@Bean(name = "primaryDataSource")public DataSource primaryDataSource() throws SQLException {DruidDataSource druid = new DruidDataSource();// 监控统计拦截的filtersdruid.setFilters(filters);// 配置基本属性druid.setDriverClassName(driverClassName);druid.setUsername(username);druid.setPassword(password);druid.setUrl(url);//初始化时建立物理连接的个数druid.setInitialSize(initialSize);//最大连接池数量druid.setMaxActive(maxActive);//最小连接池数量druid.setMinIdle(minIdle);//获取连接时最大等待时间，单位毫秒。druid.setMaxWait(maxWait);//间隔多久进行一次检测，检测需要关闭的空闲连接druid.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);//一个连接在池中最小生存的时间druid.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);//用来检测连接是否有效的sqldruid.setValidationQuery(validationQuery);//建议配置为true，不影响性能，并且保证安全性。druid.setTestWhileIdle(testWhileIdle);//申请连接时执行validationQuery检测连接是否有效druid.setTestOnBorrow(testOnBorrow);druid.setTestOnReturn(testOnReturn);//是否缓存preparedStatement，也就是PSCache，oracle设为true，mysql设为false。分库分表较多推荐设置为falsedruid.setPoolPreparedStatements(poolPreparedStatements);// 打开PSCache时，指定每个连接上PSCache的大小druid.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);return druid;}// 创建该数据源的事务管理@Primary@Bean(name = "primaryTransactionManager")public DataSourceTransactionManager primaryTransactionManager() throws SQLException {return new DataSourceTransactionManager(primaryDataSource());}// 创建Mybatis的连接会话工厂实例@Primary@Bean(name = "primarySqlSessionFactory")public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource primaryDataSource, MybatisPlusProperties mybatisPlusProperties) throws Exception {final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();sessionFactory.setDataSource(primaryDataSource);  // 设置数据源beansessionFactory.setConfiguration(mybatisPlusProperties.getConfiguration());//配置mybatisPlus sql打印日志
//        sessionFactory.setConfiguration(mybatisProperties.getConfiguration());// mybatis 配置sql打印日志
//        MybatisProperties mybatisPropertiessessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(PrimaryDataBaseConfig.MAPPER_LOCATION));  // 设置mapper文件路径return sessionFactory.getObject();}
}

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import lombok.Data;
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.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;import javax.sql.DataSource;
import java.sql.SQLException;/*** @Description: 后台数据源配置类*/@Data
@Configuration
@Component
@ConfigurationProperties(prefix = "sqlser.datasource.druid")
@MapperScan(basePackages = SqlServerDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "sqlServerDataBase")
public class SqlServerDataBaseConfig {/*** dao层的包路径*/static final String PACKAGE = "com.hnks.springboot_list.mapper.sqlser";/*** mapper文件的相对路径*/private static final String MAPPER_LOCATION = "classpath:mapper/sqlser/*.xml";private String filters;private String url;private String username;private String password;private String driverClassName;private int initialSize;private int minIdle;private int maxActive;private long maxWait;private long timeBetweenEvictionRunsMillis;private long minEvictableIdleTimeMillis;private String validationQuery;private boolean testWhileIdle;private boolean testOnBorrow;private boolean testOnReturn;private boolean poolPreparedStatements;private int maxPoolPreparedStatementPerConnectionSize;@Bean(name = "sqlBaseConfig")public DataSource sqlBaseConfig() throws SQLException {DruidDataSource druid = new DruidDataSource();// 监控统计拦截的filtersdruid.setFilters(filters);// 配置基本属性druid.setDriverClassName(driverClassName);druid.setUsername(username);druid.setPassword(password);druid.setUrl(url);//初始化时建立物理连接的个数druid.setInitialSize(initialSize);//最大连接池数量druid.setMaxActive(maxActive);//最小连接池数量druid.setMinIdle(minIdle);//获取连接时最大等待时间，单位毫秒。druid.setMaxWait(maxWait);//间隔多久进行一次检测，检测需要关闭的空闲连接druid.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);//一个连接在池中最小生存的时间druid.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);//用来检测连接是否有效的sqldruid.setValidationQuery(validationQuery);//建议配置为true，不影响性能，并且保证安全性。druid.setTestWhileIdle(testWhileIdle);//申请连接时执行validationQuery检测连接是否有效druid.setTestOnBorrow(testOnBorrow);druid.setTestOnReturn(testOnReturn);//是否缓存preparedStatement，也就是PSCache，oracle设为true，mysql设为false。分库分表较多推荐设置为falsedruid.setPoolPreparedStatements(poolPreparedStatements);// 打开PSCache时，指定每个连接上PSCache的大小druid.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);return druid;}@Bean(name = "backManager")public DataSourceTransactionManager backManager() throws SQLException {return new DataSourceTransactionManager(sqlBaseConfig());}@Bean(name = "sqlServerDataBase")public SqlSessionFactory sqlServerDataBaseConfig(@Qualifier("sqlBaseConfig") DataSource backDataSource) throws Exception {final MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();sessionFactory.setDataSource(backDataSource);sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(SqlServerDataBaseConfig.MAPPER_LOCATION));return sessionFactory.getObject();}
}

特别注意

mybatis 和mybatisplus 是不一样的 mybatis->SqlSessionFactoryBean
mybatisplus->MybatisSqlSessionFactoryBean（曾经也是吃了我半天时间！！）

打印控制台sql：mybatis -》MybatisProperties sessionFactory.setConfiguration(mybatisProperties.getConfiguration());// mybatis 配置sql打印日志

打印控制台sql：mybatisplus-》MybatisPlusProperties
sessionFactory.setConfiguration(mybatisPlusProperties.getConfiguration());//配置mybatisPlus sql打印日志

本文标签： 关于Springboot 配置多数据源 Druid 德鲁伊