spring,mybatis,druid,多数据源配置

spring,mybatis,druid,多数据源配置

一、引入包

     <dependency>
         <groupId>com.alibaba</groupId>
         <artifactId>druid-spring-boot-starter</artifactId>
         <version>1.1.9</version>
     </dependency>

二、application-dev.yml

 spring:
   datasource:
     #使用druid连接池
     type: com.alibaba.druid.pool.DruidDataSource
 ​
 # 自定义的主数据源配置信息
 primary:
   datasource:
     #druid相关配置
     druid:
       #监控统计拦截的filters
       filters: stat
       driverClassName: com.mysql.jdbc.Driver
       #配置基本属性
       url: jdbc:mysql://127.0.0.1:3306/primary_database?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&autoReconnect=true&useSSL=false
       username: root
       password: password
       #配置初始化大小/最小/最大
       initialSize: 1
       minIdle: 1
       maxActive: 20
       #获取连接等待超时时间
       maxWait: 60000
       #间隔多久进行一次检测，检测需要关闭的空闲连接
       timeBetweenEvictionRunsMillis: 60000
       #一个连接在池中最小生存的时间
       minEvictableIdleTimeMillis: 300000
       validationQuery: SELECT 'x'
       testWhileIdle: true
       testOnBorrow: false
       testOnReturn: false
       #打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
       poolPreparedStatements: false
       maxPoolPreparedStatementPerConnectionSize: 20
 ​
 # 自定义的从数据源配置信息
 back:
   datasource:
     #druid相关配置
     druid:
       #监控统计拦截的filters
       filters: stat
       driverClassName: com.mysql.jdbc.Driver
       #配置基本属性
       url: jdbc:mysql://127.0.0.1:3306/back_database?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&autoReconnect=true&useSSL=false
       username: root
       password: password
       #配置初始化大小/最小/最大
       initialSize: 1
       minIdle: 1
       maxActive: 20
       #获取连接等待超时时间
       maxWait: 60000
       #间隔多久进行一次检测，检测需要关闭的空闲连接
       timeBetweenEvictionRunsMillis: 60000
       #一个连接在池中最小生存的时间
       minEvictableIdleTimeMillis: 300000
       validationQuery: SELECT 'x'
       testWhileIdle: true
       testOnBorrow: false
       testOnReturn: false
       #打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
       poolPreparedStatements: false
       maxPoolPreparedStatementPerConnectionSize: 20

三、配置

1. 主数据库

 package com.dabo.mini.game.zhaxinle.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.context.annotation.Primary;
 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
 import org.springframework.jdbc.datasource.DataSourceTransactionManager;
 ​
 import javax.sql.DataSource;
 import java.sql.SQLException;
 ​
 /**
  * @ProjectName zhaxinle
  * @Author: zeroJun
  * @Date: 2018/8/16 16:49
  * @Description: 主数据源配置类
  */
 @Data
 @Configuration
 // 前缀为primary.datasource.druid的配置信息
 @ConfigurationProperties(prefix = "primary.datasource.druid")
 @MapperScan(basePackages = PrimaryDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "primarySqlSessionFactory")
 public class PrimaryDataBaseConfig {
 ​
     /**
      * dao层的包路径
      */
     static final String PACKAGE = "com.dabo.mini.game.zhaxinle.dao.primary";
 ​
     /**
      * mapper文件的相对路径
      */
     private static final String MAPPER_LOCATION = "classpath:mappers/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();
         // 监控统计拦截的filters
         druid.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);
         //用来检测连接是否有效的sql
         druid.setValidationQuery(validationQuery);
         //建议配置为true，不影响性能，并且保证安全性。
         druid.setTestWhileIdle(testWhileIdle);
         //申请连接时执行validationQuery检测连接是否有效
         druid.setTestOnBorrow(testOnBorrow);
         druid.setTestOnReturn(testOnReturn);
         //是否缓存preparedStatement，也就是PSCache，oracle设为true，mysql设为false。分库分表较多推荐设置为false
         druid.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) throws Exception {
         final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
         sessionFactory.setDataSource(primaryDataSource);  // 设置数据源bean
         sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                 .getResources(PrimaryDataBaseConfig.MAPPER_LOCATION));  // 设置mapper文件路径
 ​
         return sessionFactory.getObject();
     }
 }

 

2. back数据库

 package com.dabo.mini.game.zhaxinle.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 javax.sql.DataSource;
 import java.sql.SQLException;
 ​
 /**
  * @ProjectName zhaxinle
  * @Author: zeroJun
  * @Date: 2018/8/16 16:49
  * @Description: 后台数据源配置类
  */
 ​
 @Data
 @Configuration
 @ConfigurationProperties(prefix = "back.datasource.druid")//选择的版本与现有库不兼容
 @MapperScan(basePackages = BackDataBaseConfig.PACKAGE, sqlSessionFactoryRef = "backSqlSessionFactory")
 public class BackDataBaseConfig {
 ​
     /**
      * dao层的包路径
      */
     static final String PACKAGE = "com.dabo.mini.game.zhaxinle.dao.back";
 ​
     /**
      * mapper文件的相对路径
      */
     private static final String MAPPER_LOCATION = "classpath:mappers/back/*.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();
         // 监控统计拦截的filters
         druid.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);
         //用来检测连接是否有效的sql
         druid.setValidationQuery(validationQuery);
         //建议配置为true，不影响性能，并且保证安全性。
         druid.setTestWhileIdle(testWhileIdle);
         //申请连接时执行validationQuery检测连接是否有效
         druid.setTestOnBorrow(testOnBorrow);
         druid.setTestOnReturn(testOnReturn);
         //是否缓存preparedStatement，也就是PSCache，oracle设为true，mysql设为false。分库分表较多推荐设置为false
         druid.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();
     }
 }

类头的注解@ConfigurationProperties(prefix="primary.datasource.druid")，由于不兼容，依赖它的时候，报依赖的

 <dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-configuration-processor</artifactId>
     <optional>true</optional>
 </dependency>

 

XML不同步的时候，maven重新compile,install,deploy

src/application-dev.yml

bin/application-dev.yml

**mapper.xml

 

 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 private static Logger logger = null;
 public void WriteLog(String msg){
     if(loggger == null){
         logger = LoggerFactory.getLogger("bootdo");
     }
     logger.debug(msg);
 }

 import org.springframework.core.env.Environment;
 @AutoWired
 Environment env;
 env.getProperty("primary.datasource.druid.***");//原来env需要完整的路径，属性前缀配置并不起作用。遇到问题觉得自己没有想错算错，调试了也确定没有弄错，都做到了，需要再从细节查找思考处理。
 ​
 ​
 import org.springframework.boot.context.properties.ConfigurationProperties;
 @ConfigurationProperties(prefix="primary.datasource.druid")
 private String filters;
 ...;
 ​
     

 

数据库的属性值要在构造函数一次性赋值

 public class MapData{
     private SysEle system;
     private NodeEle node;
     private RoadEle road;
     public void MapData(SysEle sys,NodeEle node, RoadEle road){//一定要用这种方式,不用一个一个赋值，没有意义。
         this.system = sys;
         this.node = node;
         this.road = road;
     }
 }

 

服务的申明

@Transactional
@Service
public class service***Impl implements service***
@Override
private void fun1(){
    
}
public interface service***    
private void fun1();

 

java.lang.IllegalArgumentException: No converter found for return value of type

返回的对象没有get() and setter()

 

druid多数据库

• pom.xml

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.9.RELEASE</version>
    <relativePath/>
</parent>

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.4</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.1.1</version>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.28</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.9</version>
</dependency>

• application.yml

mybatis: 
  configuration:
    map-underscore-to-camel-case: true
  mapper-locations: classpath:mybatis/**/*Mapper.xml
  typeAliasesPackage: com.bootdo.**.model

• application-dev.yml

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    #connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
mds:
  datasource:
    druid:
      #type: com.alibaba.druid.pool.DruidDataSource
      #connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      filters: stat,wall,slf4j
      driverClassName: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/mds-0515?allowMultiQueries=true&characterEncoding=utf8&useSSL=false&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8
      username: root
      password: root
      initialSize: 30
      minIdle: 15
      maxActive: 50
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 30000
      validationQuery: select 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      #useGlobalDataSourceStat: true
gps:
  datasource:
    druid:
      type: com.alibaba.druid.pool.DruidDataSource
      #connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      filters: stat,wall,slf4j
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=GPS
      username: sa
      password: DFmc_123
      initialSize: 1
      minIdle: 1
      maxActive: 10
      #获取连接等待超时时间
      maxWait: 60000
      #间隔多久进行一次检测，检测需要关闭的空闲连接
      timeBetweenEvictionRunsMillis: 60000
      #一个连接在池中最小生存的时间
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 'x'
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      #打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
      poolPreparedStatements: false
      maxPoolPreparedStatementPerConnectionSize: 20

• mdsDataSourceConfig 主数据库配置

package com.bootdo.common.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.bootdo.common.utils.Common;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
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.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
@MapperScan(basePackages = "com.bootdo.mds.dao",
        sqlSessionFactoryRef = "mdsSqlSessionFactory")
public class mdsDataSourceConfig extends DataSourceConfig {

    @Primary
    @Bean(name = "mdsDataSource")
    @ConfigurationProperties(prefix = "mds.datasource.druid")
    public DataSource mdsDataSource() throws SQLException {
        this.setPrefix("mds.datasource.druid");
        return getDruidDataSource();
    }

    @Primary
    @Bean(name = "mdsTransactionManager")
    public DataSourceTransactionManager mdsTransactionManager() throws SQLException{
        return new DataSourceTransactionManager(mdsDataSource());
    }

    @Primary
    @Bean(name = "mdsSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mdsDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mybatis/mds/*.xml"));
        return sessionFactoryBean.getObject();
    }
}

从数据库配置

package com.bootdo.common.config;

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.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;
import java.sql.SQLException;

@Configuration
@MapperScan(basePackages = "com.bootdo.gps.dao",
        sqlSessionFactoryRef = "gpsSqlSessionFactory")
public class gpsDataSourceConfig extends DataSourceConfig{
    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 = "gpsDataSource")
    public DataSource gpsDataSource() throws SQLException {
        this.setPrefix("gps.datasource.druid");
        return this.getDruidDataSource();
    }

    @Bean(name = "gpsTransactionManager")
    public DataSourceTransactionManager gpsTransactionManager() throws SQLException{
        return new DataSourceTransactionManager(gpsDataSource());
    }

    @Bean(name = "gpsSqlSessionFactory")
    public SqlSessionFactory gpsSqlSessionFactory(@Qualifier("gpsDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mybatis/gps/*.xml"));
        return sessionFactoryBean.getObject();
    }
}