定义多数据源springboot的starter

ゝ一纸荒年。 2022-12-11 13:32 93阅读 0赞

## 1：思路分析 ##

### 1.1：切换后的数据源信息的存储 ###

因为每个线程之间数据源信息需要线程隔离存储，因此存储工作由`java.lang.ThreadLocal`完成，当然如果你有多个变量需要线程隔离存储，那么定义多个ThreadLocal就可以了，本例中只会定义一个。

### 1.2：数据库连接池 ###

我们操作数据库使用的是`mybatis-spring-boot-starter`，该starter内部使用的是`HicariCp`数据库连接池。

### 1.3：如何让mybatis使用切换的数据源 ###

我们只需要继承`com.zaxxer.hikari.HikariDataSource`然后重写其获取数据库链接信息的方法`getConnection()`即可。

### 1.4：数据源种类 ###

按照业务代码传递的业务参数来进行区分，每个业务参数对应一个数据库。

## 2：正式开始 ##

### 2.1：pom ###

<dependencies>
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-configuration-processor</artifactId>
              <optional>true</optional>
          </dependency>
    
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter</artifactId>
          </dependency>
          
          <dependency>
              <groupId>org.mybatis.spring.boot</groupId>
              <artifactId>mybatis-spring-boot-starter</artifactId>
              <version>2.1.2</version>
          </dependency>
          
          <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
              <version>8.0.16</version>
          </dependency>
          
          <dependency>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-starter-jdbc</artifactId>
              
          </dependency>
      </dependencies>

### 2.2：封装用户配置的属性类 ###

@ConfigurationProperties(prefix = "dongshi.daddy.multidatasource")
    public class CustomDatasourceProperties { 
    
        // 11用户配置的多数据源信息会设置到该map中，可能如下配置：
        /* dongshi.daddy.multidatasource.source_1.routingKey=source_1 dongshi.daddy.multidatasource.source_1.host=23.23.23.23 dongshi.daddy.multidatasource.source_1.port=3232 dongshi.daddy.multidatasource.source_1.username=db_username dongshi.daddy.multidatasource.source_1.password=db_password dongshi.daddy.multidatasource.source_1.database=db */
        private Map<String, Map<String, Object>> datasoures;
    
        public Map<String, Map<String, Object>> getDatasoures() { 
            return datasoures;
        }
    
        public void setDatasoures(Map<String, Map<String, Object>> datasoures) { 
            this.datasoures = datasoures;
        }
    }

### 2.3：定义封装数据库信息的实体 ###

public class MyDbConnectionInfo { 
        private int time = 400;
        /** * 数据源路由键，该值对应于配置文件中的routeKey，用来标示具体的数据源 */
        private String routingKey;
    
        /** * 数据库地址 */
        private String host;
    
        /** * 数据库端口 */
        private String port;
    
        /** * 数据库用户名 */
        private String username;
    
        /** * 数据库密码 */
        private String password;
    
        /** * 数据库名称 */
        private String database;
    
        public String getRoutingKey() { 
            return routingKey;
        }
    
        public void setRoutingKey(String routingKey) { 
            this.routingKey = routingKey;
        }
    
        public String getHost() { 
            return host;
        }
    
        public void setHost(String host) { 
            this.host = host;
        }
    
        public String getPort() { 
            return port;
        }
    
        public void setPort(String port) { 
            this.port = port;
        }
    
        public String getUsername() { 
            return username;
        }
    
        public void setUsername(String username) { 
            this.username = username;
        }
    
        public String getPassword() { 
            return password;
        }
    
        public void setPassword(String password) { 
            this.password = password;
        }
    
        public String getDatabase() { 
            return database;
        }
    
        public void setDatabase(String database) { 
            this.database = database;
        }
    
        /** * 构造数据库链接URL * @return 数据库链接URL */
        public String buildJdbcUrl() { 
            return String
                    .format("jdbc:mysql://%s:%s/%s?serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=CONVERT_TO_NULL&characterEncoding=utf8&allowMultiQueries=true&useAffectedRows=true", this.host, this.port, this.database);
        }
    }

### 2.4：定义存储当前切换的数据源标识信息类 ###

public class MyCurrenetDatasourceHolder { 
        // 默认数据源routekey的名称，如果基于业务生成的routekey在，
        // 配置文件中不存在，则使用配置文件中配置的该routekey，所以如果有必要
        // ，需要配置该默认的
        private static final String DEFAULT_ROUTE_KEY_NAME = "default_route_key";
    
        // 存储切换的routekey,设置默认数据源
        private static ThreadLocal<String> DATASOURCE_HOLDER = ThreadLocal.withInitial(() -> DEFAULT_ROUTE_KEY_NAME);
    
        /* 切换数据源 */
        public static void switchCurrentDataSourceConfig(String routeKey) { 
            DATASOURCE_HOLDER.set(routeKey);
        }
    
        /* 获取当前数据源 */
        public static String queryCurrentDataSourceConfig() { 
            return DATASOURCE_HOLDER.get();
        }
    }

### 2.5：自定义`DataSource` ###

public class MyCustomDatasource extends HikariDataSource { 
    
        @Resource
        private CustomDatasourceProperties customDatasourceProperties;
    
        @Override
        public Connection getConnection() throws SQLException { 
            // 获取当前的routekey
            String routeKey = MyCurrenetDatasourceHolder.queryCurrentDataSourceConfig();
            // 根据routkey从配置文件类中获取数据库链接信息
            Map<String, Object> datasouresMap = customDatasourceProperties
                    .getDatasoures()
                    .get(routeKey);
            if (datasouresMap == null) { 
                throw new IllegalArgumentException(
                        "routeKey: " + routeKey + " 是不是一个合法的key，根据您提供的配置文件，合法的key有:"
                                + customDatasourceProperties.getDatasoures().keySet()
                );
            }
            MyDbConnectionInfo myDbConnectionInfo = new MyDbConnectionInfo();
            myDbConnectionInfo.setHost(datasouresMap.get("host").toString());
            myDbConnectionInfo.setPort(datasouresMap.get("port").toString());
            String username = datasouresMap.get("username").toString();
            myDbConnectionInfo.setUsername(username);
            String password = datasouresMap.get("password").toString();
            myDbConnectionInfo.setPassword(password);
            myDbConnectionInfo.setDatabase(datasouresMap.get("database").toString());
            String jdbcUrl = myDbConnectionInfo.buildJdbcUrl();
    
            HikariDataSource hikariDataSource = new HikariDataSource();
            hikariDataSource.setJdbcUrl(jdbcUrl);
            hikariDataSource.setUsername(username);
            hikariDataSource.setPassword(password);
            hikariDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
            return hikariDataSource.getConnection();
        }
    }

### 2.6：定义`auto configure bean` ###

@EnableConfigurationProperties(value = CustomDatasourceProperties.class)
    @Configuration
    public class MultiDatasourceBeanConfiguration { 
    
        @Autowired
        private CustomDatasourceProperties customDatasourceProperties;
    
        // 设置使用的Datasource的bean,猜测会从容器中获取该自定义数据源对象
        @Bean
        public DataSource dataSource() { 
            DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
            dataSourceBuilder.type(MyCustomDatasource.class);
            return dataSourceBuilder.build();
        }
    }

### 2.7：定义`META-INF/spring.factories` ###

org.springframework.boot.autoconfigure.EnableAutoConfiguration=dongshi.daddy.dongsrmutitidatasoucespringbootstarter.configuration.MultiDatasourceBeanConfiguration

### 2.8：安装到本地仓库 ###

`mvn clean install`。

## 3：测试 ##

### 3.1：pom ###

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>dongshi.daddy</groupId>
            <artifactId>dongsrmutitidatasouce-spring-boot-starter</artifactId>
            <version>0.0.1-SNAPSHOT</version>
        </dependency>
    </dependencies>
    
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

### 3.2：`application.properties` ###

server.port=9876
    dongshi.daddy.multidatasource.datasoures.client1.routingKey=client1
    dongshi.daddy.multidatasource.datasoures.client1.host=127.0.0.1
    dongshi.daddy.multidatasource.datasoures.client1.port=3306
    dongshi.daddy.multidatasource.datasoures.client1.username=root
    dongshi.daddy.multidatasource.datasoures.client1.password=12345678
    dongshi.daddy.multidatasource.datasoures.client1.database=db1
    
    dongshi.daddy.multidatasource.datasoures.client2.routingKey=client2
    dongshi.daddy.multidatasource.datasoures.client2.host=127.0.0.1
    dongshi.daddy.multidatasource.datasoures.client2.port=3306
    dongshi.daddy.multidatasource.datasoures.client2.username=root
    dongshi.daddy.multidatasource.datasoures.client2.password=12345678
    dongshi.daddy.multidatasource.datasoures.client2.database=db2
    
    mybatis.type-aliases-package=dongshi.dadday.**.entity
    mybatis.mapper-locations=classpath*:sqlmapper/**/*.xml

### 3.3：表 ###

*  表描述

+-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | id    | int(11) | NO   | PRI | NULL    |       |
    | a     | int(11) | YES  | MUL | NULL    |       |
    | b     | int(11) | YES  |     | NULL    |       |
    +-------+---------+------+-----+---------+-------+

*  建表语句

CREATE TABLE `t` (
      `id` int(11) NOT NULL,
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `a` (`a`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

### 3.4：entity ###

public class T { 
        private int id;
        private int a;
        private int b;
    
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public int getA() { 
            return a;
        }
    
        public void setA(int a) { 
            this.a = a;
        }
    
        public int getB() { 
            return b;
        }
    
        public void setB(int b) { 
            this.b = b;
        }
    }

### 3.5：Mapper ###

@Repository
    public interface TMapper { 
        T selectByPrimaryKey(int id);
    }

### 3.6：Mapper.xml ###

<mapper namespace="dongshi.dadday.testdongsirmultidatasourcestarter.mapper.TMapper">
        <resultMap id="BaseResultMap" type="dongshi.dadday.testdongsirmultidatasourcestarter.entity.T">
            <id column="id" jdbcType="INTEGER" property="id" />
            <result column="a" jdbcType="INTEGER" property="a" />
            <result column="b" jdbcType="INTEGER" property="b" />
        </resultMap>
        <sql id="Base_Column_List">
        id, a, b
      </sql>
    
        <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List" />
            from t
            where id = #{id,jdbcType=INTEGER}
        </select>
    
    </mapper>

### 3.7：dao ###

@Repository
    public class TDao { 
        @Resource
        private TMapper tMapper;
    
        public T selectByPrimaryKey(int id) { 
            return tMapper.selectByPrimaryKey(id);
        }
    }

### 3.8：service ###

@Service
    public class TService { 
    
        @Resource
        private TDao tDao;
    
        public T queryById(int id) { 
            return tDao.selectByPrimaryKey(id);
        }
    }

### 3.9：controller ###

@RestController
    @RequestMapping("/multidatasource")
    public class MultiDatasourceController { 
    
        @Autowired
        private TService tService;
    
        @RequestMapping(value = "/hello")
        public void hello(
                HttpServletResponse response,
                String routeKey,
                int id
        ) throws Exception { 
            System.out.println(">>>>>routekey is: " + routeKey + "<<<<<<");
            System.out.println(">>>>>id is: " + id + "<<<<<<");
            // 设置客户端传递的routekey，从而实现数据源的动态切换
            MyCurrenetDatasourceHolder.switchCurrentDataSourceConfig(routeKey);
            T t = tService.queryById(id);
            String result = t.getA() + "-" + t.getB();
            response.getWriter().write("welcome use multi datasource, " + result);
        }
    }

### 3.10：查询id为1的数据 ###

*  查看db1和db2中id为1的数据

mysql> select * from db1.t where id=1;
    +----+------+------+
    | id | a    | b    |
    +----+------+------+
    |  1 |    1 |    1 |
    +----+------+------+
    1 row in set (0.03 sec)
    
    mysql> select * from db2.t where id=1;
    +----+------+------+
    | id | a    | b    |
    +----+------+------+
    |  1 |    2 |    3 |
    +----+------+------+
    1 row in set (0.00 sec)

*  使用`routeKey=client1`从db1中查询  
    ![在这里插入图片描述][20201001183510872.png_pic_center]
 *  使用`routeKey=client2`从db2中查询  
    ![在这里插入图片描述][2020100118361077.png_pic_center]

## 4：源码 ##

[多数据源starter][starter]，[测试多数据源starter][starter 1]。

[20201001183510872.png_pic_center]: /images/20221123/b850e6336e594fca82f77ec4ba802889.png
[2020100118361077.png_pic_center]: /images/20221123/c075a4a585784b738b60cf89032ca65a.png
[starter]: https://gitee.com/dongsir2020/dongsrmutitidatasouce-spring-boot-starter/repository/archive/master-remote.zip
[starter 1]: https://gitee.com/dongsir2020/testdongsirmultidatasources-tarter/repository/archive/master-remote.zip