网站导航
> 文章列表 > 使用JdbcTemplate和dynamic-datasource-spring-boot-starter动态切换数据源操作数据库

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

网友: 文章列表 2024-03-21 23:55:22

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

记录：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 {@Autowiredprivate 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 {@Autowiredprivate 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() {@Overridepublic 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"));}@Overridepublic int getBatchSize() {return data.size();}});}
}

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

@Repository
public class GetAndInsertDataDao {@Autowiredprivate 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() {@Overridepublic 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"));}@Overridepublic int getBatchSize() {return data.size();}});}
}

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

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

@Repository
public class GetAndInsertDataByHolderDao {@Autowiredprivate 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() {@Overridepublic 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"));}@Overridepublic int getBatchSize() {return data.size();}});}
}

4.测试类

4.1测试类

@Slf4j
@RestController
@RequestMapping("/hub/example/load01")
public class LoadController {@Autowiredprivate GetDataDao getDataDao;@Autowiredprivate InsertDataDao insertDataDao;@Autowiredprivate GetAndInsertDataDao getAndInsertDataDao;@Autowiredprivate 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+8datasource:dynamic:primary: hub_a_dbstrict: falsedatasource:hub_a_db:url: jdbc:mysql://127.0.0.1:3306/hub_a_dbusername: hub_apassword: 12345678driver-class-name: com.mysql.cj.jdbc.Driverhub_b_db:url: jdbc:mysql://127.0.0.1:3306/hub_b_dbusername: hub_bpassword: 12345678driver-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日

网络标签：

相关问题