使用JdbcTemplate和dynamic-datasource-spring-boot-starter动态切换数据源操作数据库

柔情只为你懂 2024-04-23 03:17 59阅读 0赞

**记录**：414

**场景**：使用JdbcTemplate和dynamic-datasource-spring-boot-starter动态切换数据源操作数据库。

**版本**：JDK 1.8,Spring Boot 2.6.3,dynamic-datasource-spring-boot-starter-3.3.2。

**源码**：https://github.com/baomidou/dynamic-datasource-spring-boot-starter

**dynamic-datasource-spring-boot-starter**：一个基于springboot的快速集成多数据源的启动器。

**1.动态数据源注解@DS作用在类上**

**1.1类GetDataDao**

@DS("hub_a_db")
    @Repository
    public class GetDataDao {
      @Autowired
      private JdbcTemplate jt;
      public List<Map<String, Object>> getData() {
          String selectSQL = "SELECT CITY_ID,CITY_NAME,LAND_AREA,POPULATION, " +
                  "  GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIME " +
                  "FROM t_city ";
          List<Map<String, Object>> data = jt.queryForList(selectSQL);
          return data;
      }
    }

**1.2类InsertDataDao**

@DS("hub_b_db")
    @Repository
    public class InsertDataDao {
      @Autowired
      private JdbcTemplate jt;
      public void insertData(List<Map<String, Object>> data) {
        String insertSQL = "INSERT INTO t_city (\n" +
              "  CITY_ID,CITY_NAME,LAND_AREA,POPULATION,\n" +
              "  GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIME)\n" +
              "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        jt.batchUpdate(insertSQL, new BatchPreparedStatementSetter() {
          @Override
          public void setValues(PreparedStatement ps, int i) throws SQLException {
              Map<String, Object> oneRow = data.get(i);
              ps.setObject(1, oneRow.get("CITY_ID"));
              ps.setObject(2, oneRow.get("CITY_NAME"));
              ps.setObject(3, oneRow.get("LAND_AREA"));
              ps.setObject(4, oneRow.get("POPULATION"));
              ps.setObject(5, oneRow.get("GROSS"));
              ps.setObject(6, oneRow.get("CITY_DESCRIBE"));
              ps.setObject(7, oneRow.get("DATA_YEAR"));
              ps.setObject(8, oneRow.get("UPDATE_TIME"));
          }
          @Override
          public int getBatchSize() {
              return data.size();
          }
        });
      }
    }

**2.动态数据源注解@DS作用在方法**

@Repository
    public class GetAndInsertDataDao {
      @Autowired
      private JdbcTemplate jt;
      @DS("hub_a_db")
      public List<Map<String, Object>> getData() {
          String selectSQL = "SELECT CITY_ID,CITY_NAME,LAND_AREA,POPULATION, " +
                  "  GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIME " +
                  "FROM t_city ";
          List<Map<String, Object>> data = jt.queryForList(selectSQL);
          return data;
      }
      @DS("hub_b_db")
      public void insertData(List<Map<String, Object>> data) {
          String insertSQL = "INSERT INTO t_city (\n" +
                  "  CITY_ID,CITY_NAME,LAND_AREA,POPULATION,\n" +
                  "  GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIME)\n" +
                  "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
          jt.batchUpdate(insertSQL, new BatchPreparedStatementSetter() {
              @Override
              public void setValues(PreparedStatement ps, int i) throws SQLException {
                  Map<String, Object> oneRow = data.get(i);
                  ps.setObject(1, oneRow.get("CITY_ID"));
                  ps.setObject(2, oneRow.get("CITY_NAME"));
                  ps.setObject(3, oneRow.get("LAND_AREA"));
                  ps.setObject(4, oneRow.get("POPULATION"));
                  ps.setObject(5, oneRow.get("GROSS"));
                  ps.setObject(6, oneRow.get("CITY_DESCRIBE"));
                  ps.setObject(7, oneRow.get("DATA_YEAR"));
                  ps.setObject(8, oneRow.get("UPDATE_TIME"));
              }
              @Override
              public int getBatchSize() {
                  return data.size();
              }
          });
      }
    }

**3.使用DynamicDataSourceContextHolder操作动态数据源**

无注解，在调用时，使用DynamicDataSourceContextHolder操作动态数据源。

@Repository
    public class GetAndInsertDataByHolderDao {
      @Autowired
      private JdbcTemplate jt;
      public List<Map<String, Object>> getData() {
          String selectSQL = "SELECT CITY_ID,CITY_NAME,LAND_AREA,POPULATION, " +
                  "  GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIME " +
                  "FROM t_city ";
          List<Map<String, Object>> data = jt.queryForList(selectSQL);
          return data;
      }
      public void insertData(List<Map<String, Object>> data) {
          String insertSQL = "INSERT INTO t_city (\n" +
                  "  CITY_ID,CITY_NAME,LAND_AREA,POPULATION,\n" +
                  "  GROSS,CITY_DESCRIBE,DATA_YEAR,UPDATE_TIME)\n" +
                  "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
          jt.batchUpdate(insertSQL, new BatchPreparedStatementSetter() {
              @Override
              public void setValues(PreparedStatement ps, int i) throws SQLException {
                  Map<String, Object> oneRow = data.get(i);
                  ps.setObject(1, oneRow.get("CITY_ID"));
                  ps.setObject(2, oneRow.get("CITY_NAME"));
                  ps.setObject(3, oneRow.get("LAND_AREA"));
                  ps.setObject(4, oneRow.get("POPULATION"));
                  ps.setObject(5, oneRow.get("GROSS"));
                  ps.setObject(6, oneRow.get("CITY_DESCRIBE"));
                  ps.setObject(7, oneRow.get("DATA_YEAR"));
                  ps.setObject(8, oneRow.get("UPDATE_TIME"));
              }
              @Override
              public int getBatchSize() {
                  return data.size();
              }
          });
      }
    }

**4.测试类**

**4.1测试类**

@Slf4j
    @RestController
    @RequestMapping("/hub/example/load01")
    public class LoadController {
      @Autowired
      private GetDataDao getDataDao;
      @Autowired
      private InsertDataDao insertDataDao;
      @Autowired
      private GetAndInsertDataDao getAndInsertDataDao;
      @Autowired
      private GetAndInsertDataByHolderDao getAndInsertDataByHolderDao;
      /**
       * 1.动态数据源注解@DS作用在类上
       * */
      @GetMapping("/load01")
      public Object load01() {
          log.info("测试开始...");
          List<Map<String, Object>> data = getDataDao.getData();
          insertDataDao.insertData(data);
          log.info("测试结束...");
          return "执行成功";
      }
      /**
       * 2.动态数据源注解@DS作用在方法上
       * */
      @GetMapping("/load02")
      public Object load02() {
          log.info("测试开始...");
          List<Map<String, Object>> data = getAndInsertDataDao.getData();
          getAndInsertDataDao.insertData(data);
          log.info("测试结束...");
          return "执行成功";
      }
      /**
       * 3.使用DynamicDataSourceContextHolder操作动态数据源
       * */
      @GetMapping("/load03")
      public Object load03() {
          log.info("测试开始...");
          //1.使用hub_a_db数据源读数据
          DynamicDataSourceContextHolder.push("hub_a_db");
          List<Map<String, Object>> data = getAndInsertDataByHolderDao.getData();
          //2.使用hub_b_db数据源写数据
          DynamicDataSourceContextHolder.poll();
          DynamicDataSourceContextHolder.push("hub_b_db");
          getAndInsertDataByHolderDao.insertData(data);
          log.info("测试结束...");
          return "执行成功";
      }
    }

**4.2测试URL**

URL01: http://127.0.0.1:18204/hub-example/hub/example/load01/load01

URL02: http://127.0.0.1:18204/hub-example/hub/example/load01/load02

URL03: http://127.0.0.1:18204/hub-example/hub/example/load01/load03

**5.基础配置**

**5.1配置动态数据源**

spring:
      jackson:
        time-zone: GMT+8
      datasource:
        dynamic:
          primary: hub_a_db
          strict: false
          datasource:
            hub_a_db:
              url: jdbc:mysql://127.0.0.1:3306/hub_a_db
              username: hub_a
              password: 12345678
              driver-class-name: com.mysql.cj.jdbc.Driver
            hub_b_db:
              url: jdbc:mysql://127.0.0.1:3306/hub_b_db
              username: hub_b
              password: 12345678
              driver-class-name: com.mysql.cj.jdbc.Driver

**5.2动态数据源依赖包**

<dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
      <version>3.3.2</version>
    </dependency>

**5.3建表语句**

CREATE TABLE t_city (
      CITY_ID BIGINT(16) NOT NULL COMMENT '唯一标识',
      CITY_NAME VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '城市名',
      LAND_AREA DOUBLE DEFAULT NULL COMMENT '城市面积',
      POPULATION BIGINT(16) DEFAULT NULL COMMENT '城市人口',
      GROSS DOUBLE DEFAULT NULL COMMENT '生产总值',
      CITY_DESCRIBE VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述',
      DATA_YEAR VARCHAR(16) COLLATE utf8_bin DEFAULT NULL COMMENT '数据年份',
      UPDATE_TIME DATETIME DEFAULT NULL COMMENT '更新时间'
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表';

以上，感谢。

2023年4月17日