springboot -- druid 多数据源(根据mapp位置区分业务数据库)

左手的ㄟ右手 2024-02-19 13:47 5阅读 0赞

1.**多数据源切换**：指项目所需要不止一个数据库的连接信息，eg：同一数据库地址下的不同库或者压根连地址都不同。

**项目中不同的业务需求保存在不同的数据库中。**

如 会员信息---会员数据库中

订单信息-- 订单数据库中。

*多数据源彼此切换，*要求不同的mybatis实例的mapper映射目录不能相同

如图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70][]

sql脚本:

zhw-demo:
    CREATE TABLE `cat_user` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `zhw-demo`.`cat_user`(`id`, `name`) VALUES (1, 'primary');
    
    
    xx:
    
    CREATE TABLE `cat_order` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO `wss`.`cat_order`(`id`, `name`) VALUES (1, 'second');

1.pom依赖

<?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.example.jdbc</groupId>
        <artifactId>demo</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <packaging>jar</packaging>
    
        <name>demo</name>
        <description>Demo project for Spring Boot</description>
    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.0.0.RELEASE</version>
            <relativePath/> 
        </parent>
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
            <java.version>1.8</java.version>
            
            <mybatis.version>1.1.1</mybatis.version>
        </properties>
    
        <dependencies>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>${mybatis.version}</version>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.3</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.16.22</version>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    
    </project>

2.application.properties配置

#Spring Boot 2.0后，一些配置及用法有了变化，如果不小心就会碰到“jdbcUrl is required with driverClassName.”的错误
    #使用spring.datasource.jdbc-url，而不是通常使用的spring.datasource.url
    #主库
    spring.datasource.one.jdbc-url=jdbc:mysql://xxxx:3306/zhw-demo?useUnicode=true&characterEncoding=utf-8
    spring.datasource.one.username=root
    spring.datasource.one.passwordxxxx
    spring.datasource.one.driver-class-name = com.mysql.jdbc.Driver
    #从库
    spring.datasource.two.jdbc-url=jdbc:mysql://xxxx:3306/wss?useUnicode=true&characterEncoding=utf-8
    spring.datasource.two.username=root
    spring.datasource.two.password=xxxxx
    spring.datasource.two.driver-class-name = com.mysql.jdbc.Driver
    
    
    
    #spring.datasource.type=com.example.jdbc.demo.config.MyDruidDataSource
    spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    spring.datasource.maxWait=60000
    spring.datasource.timeBetweenEvictionRunsMillis=60000
    spring.datasource.minEvictableIdleTimeMillis=30000
    spring.datasource.validationQuery=select 'x'
    spring.datasource.testWhileIdle=true
    spring.datasource.testOnBorrow=false
    spring.datasource.testOnReturn=false
    spring.datasource.poolPreparedStatements=true
    spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
    spring.datasource.filters=stat,wall,logback
    spring.datasource.initialSize=1
    spring.datasource.minIdle=5
    spring.datasource.maxActive=20

3.PrimaryDruidConfiguration

package com.example.jdbc.demo.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.example.jdbc.demo.utils.AesUtils;
    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.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.example.jdbc.demo.mapper.UserMapper", sqlSessionTemplateRef  = "oneSqlSessionTemplate")
    public class PrimaryDruidConfiguration {
        @Value("${spring.datasource.one.jdbc-url}")
        private String dbUrl;
        @Value("${spring.datasource.one.username}")
        private String username;
        @Value("${spring.datasource.one.password}")
        private String password;
        @Value("${spring.datasource.one.driver-class-name}")
        private String driverClassName;
        @Value("${spring.datasource.initialSize}")
        private int initialSize;
        @Value("${spring.datasource.minIdle}")
        private int minIdle;
        @Value("${spring.datasource.maxActive}")
        private int maxActive;
        @Value("${spring.datasource.maxWait}")
        private int maxWait;
        @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
        private int timeBetweenEvictionRunsMillis;
        @Value("${spring.datasource.minEvictableIdleTimeMillis}")
        private int minEvictableIdleTimeMillis;
        @Value("${spring.datasource.validationQuery}")
        private String validationQuery;
        @Value("${spring.datasource.testWhileIdle}")
        private boolean testWhileIdle;
        @Value("${spring.datasource.testOnBorrow}")
        private boolean testOnBorrow;
        @Value("${spring.datasource.testOnReturn}")
        private boolean testOnReturn;
        @Value("${spring.datasource.poolPreparedStatements}")
        private boolean poolPreparedStatements;
        @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
        private int maxPoolPreparedStatementPerConnectionSize;
        @Value("${spring.datasource.filters}")
        private String filters;
        @Value("{spring.datasource.connectionProperties}")
        private String connectionProperties;
    
        @Bean(name = "oneDataSource")
        //@ConfigurationProperties(prefix = "spring.datasource.one")
        @Primary
        public DataSource customDataSource() {
            //return DataSourceBuilder.create().build();
            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) {
                System.err.println("druid configuration initialization filter: "+ e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }
    
        @Bean(name = "oneSqlSessionFactory")
        @Primary
        public SqlSessionFactory customSqlSessionFactory(@Qualifier("oneDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setTypeAliasesPackage("com.example.jdbc.demo.entity");
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "oneTransactionManager")
        @Primary
        public DataSourceTransactionManager customTransactionManager(@Qualifier("oneDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "oneSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate customSqlSessionTemplate(@Qualifier("oneSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    }

2.SecondDruidConfiguration

package com.example.jdbc.demo.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.example.jdbc.demo.utils.AesUtils;
    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.boot.jdbc.DataSourceBuilder;
    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;
    
    @Configuration
    @MapperScan(basePackages = "com.example.jdbc.demo.mapper.OrderMapper", sqlSessionTemplateRef  = "twoSqlSessionTemplate")
    public class SecondDruidConfiguration{
    
        @Value("${spring.datasource.two.jdbc-url}")
        private String dbUrl;
        @Value("${spring.datasource.two.username}")
        private String username;
        @Value("${spring.datasource.two.password}")
        private String password;
        @Value("${spring.datasource.two.driver-class-name}")
        private String driverClassName;
        @Value("${spring.datasource.initialSize}")
        private int initialSize;
        @Value("${spring.datasource.minIdle}")
        private int minIdle;
        @Value("${spring.datasource.maxActive}")
        private int maxActive;
        @Value("${spring.datasource.maxWait}")
        private int maxWait;
        @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
        private int timeBetweenEvictionRunsMillis;
        @Value("${spring.datasource.minEvictableIdleTimeMillis}")
        private int minEvictableIdleTimeMillis;
        @Value("${spring.datasource.validationQuery}")
        private String validationQuery;
        @Value("${spring.datasource.testWhileIdle}")
        private boolean testWhileIdle;
        @Value("${spring.datasource.testOnBorrow}")
        private boolean testOnBorrow;
        @Value("${spring.datasource.testOnReturn}")
        private boolean testOnReturn;
        @Value("${spring.datasource.poolPreparedStatements}")
        private boolean poolPreparedStatements;
        @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
        private int maxPoolPreparedStatementPerConnectionSize;
        @Value("${spring.datasource.filters}")
        private String filters;
        @Value("{spring.datasource.connectionProperties}")
        private String connectionProperties;
    
    
        @Bean(name = "twoDataSource")
        //@ConfigurationProperties(prefix = "spring.datasource.two")
        public DataSource customDataSource() {
           // return DataSourceBuilder.create().build();
            //return DataSourceBuilder.create().build();
            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) {
                System.err.println("druid configuration initialization filter: "+ e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }
    
        @Bean(name = "twoSqlSessionFactory")
        public SqlSessionFactory customSqlSessionFactory(@Qualifier("twoDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setTypeAliasesPackage("com.example.jdbc.demo.entity");
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "twoTransactionManager")
        public DataSourceTransactionManager customTransactionManager(@Qualifier("twoDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "twoSqlSessionTemplate")
        public SqlSessionTemplate customSqlSessionTemplate(@Qualifier("twoSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

4.OrderInfoMapper

package com.example.jdbc.demo.mapper.OrderMapper;
    
    import com.example.jdbc.demo.entity.Order;
    import org.apache.ibatis.annotations.Mapper;
    
    import java.util.List;
    
    @Mapper
    public interface OrderInfoMapper {
        List<Order> findOrderList();
    }

5.UserInfoMapper

package com.example.jdbc.demo.mapper.UserMapper;
    
    import com.example.jdbc.demo.entity.User;
    import org.apache.ibatis.annotations.Mapper;
    
    import java.util.List;
    
    @Mapper
    public interface UserInfoMapper {
    
        List<User> findUserList();
    
    }

6.OrderServiceImpl

import com.example.jdbc.demo.entity.Order;
    import com.example.jdbc.demo.mapper.OrderMapper.OrderInfoMapper;
    import com.example.jdbc.demo.service.OrderService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    @Service
    public class OrderServiceImpl implements OrderService {
    
        @Autowired
        private OrderInfoMapper orderMapper;
    
    
        @Override
        public List<Order> list() {
            return orderMapper.findOrderList();
        }
    }

结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 1][]

[代码][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/679d462ae24d495fa7f5d354b9f7c30e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzU0OTU3OA_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/7a77e8e25ecf45e09d77597e16b66547.png
[Link 1]: https://github.com/timeday/springbootdruid