多数据源创建sqlsession mybaits多数据源

转载

落花流水人家 2024-03-26 10:16:52

文章标签 多数据源创建sqlsession mybtis 配置多数据源 spring sql 文章分类 架构后端开发

1、去掉application.yml文件里面的mybtis配置

mybatis:
  configuration:
    map-underscore-to-camel-case: true
  mapper-locations: /mybatis/**/*Mapper.xml
  typeAliasesPackage: com.tfkj.**.domain
  log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

2、application-dev.yml添加多个数据源配置

spring:
  datasource:
      zczp:
          type: com.alibaba.druid.pool.DruidDataSource
          driverClassName: com.mysql.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3308/test1?useUnicode=true&characterEncoding=utf8
          username: root
          password: root
          initialSize: 1
          minIdle: 3
          maxActive: 20
          # 配置获取连接等待超时的时间
          maxWait: 60000
          # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
          timeBetweenEvictionRunsMillis: 60000
          # 配置一个连接在池中最小生存的时间，单位是毫秒
          minEvictableIdleTimeMillis: 30000
          validationQuery: select 'x'
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          # 打开PSCache，并且指定每个连接上PSCache的大小
          poolPreparedStatements: true
          maxPoolPreparedStatementPerConnectionSize: 20
          # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
          filters: stat,wall,slf4j
          # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
          connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
          # 合并多个DruidDataSource的监控数据
          #useGlobalDataSourceStat: true
      smry:
          type: com.alibaba.druid.pool.DruidDataSource
          driverClassName: com.mysql.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3308/test2?useUnicode=true&characterEncoding=utf8
          username: root
          password:123456
          initialSize: 1
          minIdle: 3
          maxActive: 20
          # 配置获取连接等待超时的时间
          maxWait: 60000
          # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
          timeBetweenEvictionRunsMillis: 60000
          # 配置一个连接在池中最小生存的时间，单位是毫秒
          minEvictableIdleTimeMillis: 30000
          validationQuery: select 'x'
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          # 打开PSCache，并且指定每个连接上PSCache的大小
          poolPreparedStatements: true
          maxPoolPreparedStatementPerConnectionSize: 20
          # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
          filters: stat,wall,slf4j
          # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
          connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
          # 合并多个DruidDataSource的监控数据
          #useGlobalDataSourceStat: true

3、配置阿里Druid
DruidDBConfig

package com.tfkj.common.config.dbconfig;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * Created by PrimaryKey on 17/2/4.
 */
@SuppressWarnings("AlibabaRemoveCommentedCode")
@Configuration
public class DruidDBConfig {
    private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);


    @Bean
    public ServletRegistrationBean druidServlet() {
        //新建servlet
        ServletRegistrationBean reg = new ServletRegistrationBean();
        //Druid内置提供了一个StatViewServlet用于展示Druid的统计信息
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("allow", ""); //白名单
        return reg;
    }

    @Bean public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        //排除过滤的路径
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        //druid 0.2.7版本开始支持profile，配置profileEnable能够监控单个url调用的sql列表。
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        //principalCookieName配置。如果你的user信息保存在cookie中，你可以配置principalCookieName，使得druid知道当前的user是谁
        filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
        //principalSessionName配置。你可以配置principalSessionName，使得druid能够知道当前的session的用户是谁
        filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");
        //拦截所有请求
        filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");
        return filterRegistrationBean;
    }
}

4、配置扫描dao，xml文件
MultiDataSourceConfig

package com.tfkj.common.config.dbconfig;

public class MultiDataSourceConfig {

    public static final String ZCZP_NAME = "zczp";
    public static final String ZCZP_BASE_PACKAGE = "com.tfkj.*.dao";
    //多个路径需要处理
    public static final String ZCZP_MAPPER_LOCATION = "/mybatis/**/*Mapper.xml,/mapper/**/*Mapper.xml";

    public static final String SMRY_NAME = "smry";
    public static final String SMRY_BASE_PACKAGE = "com.tfkj.smry.smrytj.dao";
    public static final String SMRY_MAPPER_LOCATION = "/mapperSmry/**/*Mapper.xml"
}

5、数据库配置文件
ZczpDBConfig
有一个配置文件添加@Primary注解作为主要经常使用的库，如果不需要配置主从库可以去掉

/**
 * Created by PrimaryKey on 17/2/4.
 */
@Configuration
@MapperScan(basePackages = MultiDataSourceConfig.ZCZP_BASE_PACKAGE, sqlSessionFactoryRef = "zczpSqlSessionFactory")
public class ZczpDBConfig {
    private Logger logger = LoggerFactory.getLogger(ZczpDBConfig.class);
    @Value("${spring.datasource.zczp.url}")
    private String dbUrl;

    @Value("${spring.datasource.zczp.username}")
    private String username;

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

    @Value("${spring.datasource.zczp.driverClassName}")
    private String driverClassName;

    @Value("${spring.datasource.zczp.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.zczp.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.zczp.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.zczp.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.zczp.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.zczp.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.zczp.validationQuery}")
    private String validationQuery;

    @Value("${spring.datasource.zczp.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.zczp.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.zczp.testOnReturn}")
    private boolean testOnReturn;

    @Value("${spring.datasource.zczp.poolPreparedStatements}")
    private boolean poolPreparedStatements;

    @Value("${spring.datasource.zczp.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;

    @Value("${spring.datasource.zczp.filters}")
    private String  filters;

    @Value("{spring.datasource.zczp.connectionProperties}")
    private String connectionProperties;

    @Bean(name="zczp" ,initMethod = "init", destroyMethod = "close")   //声明其为Bean实例
    @Primary
    public DataSource zczpDataSource() {
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);

        //configuration
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("druid configuration initialization filter", e);
        }
        datasource.setConnectionProperties(connectionProperties);

        return datasource;
    }


    //配置数据源事务管理
    @Bean(name="zczpDataSourceTransactionManager")
    @Primary
    public DataSourceTransactionManager zczpDataSourceTransactionManager(){
        DataSourceTransactionManager dataSourceTransactionManager=new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(zczpDataSource());
        return dataSourceTransactionManager;
    }

    //创建session
    @Bean(name="zczpSqlSessionFactory")
    @Primary
    public SqlSessionFactory zczpSqlSessionFactory(@Qualifier("zczp") DataSource dataSource) throws Exception {
        //工程上默认使用的是Mybatis的DefaultVFS进行扫描，但是在springboot的环境下，Mybatis的DefaultVFS这个扫包会出现问题，所以只能修改VFS
        VFS.addImplClass(SpringBootVFS.class);
        //创建sessionFactoryBean
        SqlSessionFactoryBean sqlSessionFactory=new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource);
        //扫描mapper资源，处理多个路径问题
        List<Resource> listResource=new ArrayList<>();
        String[] paths=MultiDataSourceConfig.ZCZP_MAPPER_LOCATION.split(",");
        for (int i=0;i<paths.length;i++){
            listResource.addAll(Arrays.asList(new PathMatchingResourcePatternResolver().getResources(paths[i])));
        }
        Resource[] temp=new Resource[listResource.size()];
        Resource[] mapper=listResource.toArray(temp);
        sqlSessionFactory.setMapperLocations(mapper);
        // 开启驼峰功能
        org.apache.ibatis.session.Configuration ibatisConfiguration = new org.apache.ibatis.session.Configuration();
        ibatisConfiguration.setMapUnderscoreToCamelCase(true);
        sqlSessionFactory.setConfiguration(ibatisConfiguration);

        return sqlSessionFactory.getObject();




    }
    @Bean
    public SqlSessionTemplate mysqlSession() throws Exception{
        SqlSessionTemplate sqlSessionTemplate=new SqlSessionTemplate(zczpSqlSessionFactory(zczpDataSource()));

        return sqlSessionTemplate;
    }
}

SmryDBConfig

/**
 * Created by PrimaryKey on 17/2/4.
 */
@Configuration
//扫描包
@MapperScan(basePackages = MultiDataSourceConfig.SMRY_BASE_PACKAGE, sqlSessionFactoryRef = "smrySqlSessionFactory")

public class SmryDBConfig {
    private Logger logger = LoggerFactory.getLogger(SmryDBConfig.class);
    @Value("${spring.datasource.smry.url}")
    private String dbUrl;

    @Value("${spring.datasource.smry.username}")
    private String username;

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

    @Value("${spring.datasource.smry.driverClassName}")
    private String driverClassName;

    @Value("${spring.datasource.smry.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.smry.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.smry.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.smry.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.smry.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.smry.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.smry.validationQuery}")
    private String validationQuery;

    @Value("${spring.datasource.smry.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.smry.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.smry.testOnReturn}")
    private boolean testOnReturn;

    @Value("${spring.datasource.smry.poolPreparedStatements}")
    private boolean poolPreparedStatements;

    @Value("${spring.datasource.smry.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;

    @Value("${spring.datasource.smry.filters}")
    private String  filters;

    @Value("{spring.datasource.smry.connectionProperties}")
    private String connectionProperties;

    @Bean(name="smry" ,initMethod = "init", destroyMethod = "close")   //声明其为Bean实例
    public DataSource smryDataSource() {
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);

        //configuration
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("druid configuration initialization filter", e);
        }
        datasource.setConnectionProperties(connectionProperties);

        return datasource;
    }


    //配置数据源事务管理
    @Bean(name="smryDataSourceTransactionManager")
    public DataSourceTransactionManager smryDataSourceTransactionManager(){
        DataSourceTransactionManager dataSourceTransactionManager=new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(smryDataSource());
        return dataSourceTransactionManager;
    }

    //创建session
    @Bean(name="smrySqlSessionFactory")
    public SqlSessionFactory  smrySqlSessionFactory(@Qualifier("smry") DataSource dataSource) throws Exception {
        //工程上默认使用的是Mybatis的DefaultVFS进行扫描，但是在springboot的环境下，Mybatis的DefaultVFS这个扫包会出现问题，所以只能修改VFS
        VFS.addImplClass(SpringBootVFS.class);
        //创建sessionFactoryBean
        SqlSessionFactoryBean sqlSessionFactory=new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource);
        //扫描mapper资源
        Resource[] resourcesMapperPath= new PathMatchingResourcePatternResolver().getResources(MultiDataSourceConfig.SMRY_MAPPER_LOCATION);
        sqlSessionFactory.setMapperLocations(resourcesMapperPath);
        // 开启驼峰功能
        org.apache.ibatis.session.Configuration ibatisConfiguration = new org.apache.ibatis.session.Configuration();
        ibatisConfiguration.setMapUnderscoreToCamelCase(true);
        sqlSessionFactory.setConfiguration(ibatisConfiguration);

        return sqlSessionFactory.getObject();
    }
	@Bean
    public SqlSessionTemplate mysqlSession() throws Exception{
	        SqlSessionTemplate sqlSessionTemplate=new SqlSessionTemplate(smrySqlSessionFactory(smryDataSource()));
	        return sqlSessionTemplate;
	    }
}

6、删除TfkjApplication启动类的
@MapperScan(“com.tfkj.*.dao”)扫描包
7、启动就可以了

多数据源，mysql和sqlserver，配置分页插件

//mysql配置
         @Bean
        public SqlSessionFactory mysqlSessionFactory() throws Exception{
            SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(mysqlDataSource);
        
            PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            Resource[] resources = resolver.getResources("classpath*:mapper/mysql/**/*Mapper.xml");
            sqlSessionFactoryBean.setMapperLocations(resources);
        
            //分页插件
            Interceptor interceptor = new PageInterceptor();
            Properties properties = new Properties();
            properties.setProperty("helperDialect", "mysql");
            properties.setProperty("rowBoundsWithCount", "true");
            properties.setProperty("reasonable", "true");
            properties.setProperty("pageSizeZero", "true");
            properties.setProperty("supportMethodsArguments","true");
            properties.setProperty("params","count=countsql");
            properties.setProperty("offsetAsPageNum","true");
            interceptor.setProperties(properties);
            sqlSessionFactoryBean.setPlugins(new Interceptor[] {interceptor});
        
            return sqlSessionFactoryBean.getObject();
        }
    
       //sqlserverl配置
         @Bean
            public SqlSessionFactory sqlServerSessionFactory() throws Exception{
                SqlSessionFactoryBean sqlSessionFactoryBean=new SqlSessionFactoryBean();
                sqlSessionFactoryBean.setDataSource(mssqlDataSource);
        
                PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
                Resource[] resources = resolver.getResources("classpath*:mapper/sqlserver/**/*Mapper.xml");
                sqlSessionFactoryBean.setMapperLocations(resources);
        
        
                //分页插件
                Interceptor interceptor = new PageInterceptor();
                Properties properties = new Properties();
                properties.setProperty("helperDialect", "sqlserver2012");
                properties.setProperty("rowBoundsWithCount", "true");
                properties.setProperty("reasonable", "true");
                properties.setProperty("pageSizeZero", "true");
                properties.setProperty("supportMethodsArguments","true");
                properties.setProperty("params","count=countsql");
                properties.setProperty("offsetAsPageNum","true");
                interceptor.setProperties(properties);
                sqlSessionFactoryBean.setPlugins(new Interceptor[] {interceptor});
        
        
        
                return sqlSessionFactoryBean.getObject();
            }

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