- 首先创建2个用于测试的数据库,每个数据库中都创建一张user表,建表语句如下:
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- 创建一个spring boot项目,项目结构如下:
- 导入相关maven依赖:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
4.编写数据源配置文件,datasourceconfig.properties:
# 第一个数据源
spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/bg-learnsp?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.primary.username:root
spring.datasource.primary.password:root
spring.datasource.primary.driver-class-name:com.mysql.cj.jdbc.Driver
# 第二个数据源
spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/bg-learnsp-second?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root
spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
注意:多数据源配置的时候,与单数据源不同点在于url属性名称需要替换成jdbc-url,同时,spring.datasource之后多配置一个数据源名称primary和secondary来区分不同的数据源配置,后期初始化数据源时,也是用名称来区分不同数据源,分别初始化。
- 初始化数据源和JdbcTemplate对象:
@Configuration
@PropertySource("classpath:datasourceconfig.properties")
public class DBConfig {
@Primary
@Bean
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource primaryDataSource) {
return new JdbcTemplate(primaryDataSource);
}
@Bean
public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
return new JdbcTemplate(secondaryDataSource);
}
}
说明:
- 使用@PropertySource注解读取datasourceconfig.properties文件
- 使用@ConfigurationProperties注解,分别注入前缀为spring.datasource.primary和spring.datasource.secondary的配置,初始化数据源
- @Primary注解指定了主数据源,就是当我们不特别指定哪个数据源的时候,就会使用这个数据源
- 创建不同的JdbcTemplate对象,注入不同的数据源
- 编写数据库User实体类对象:
- primary数据源:
@Data
public class MasterUserEntity {
private Long id;
private String name;
private Integer age;
}
- secondary数据源:
@Data
public class SecondUserEntity {
private Long id;
private String name;
private Integer age;
}
- 创建UserService接口:
- primary数据源:
public interface MasterUserService {
/**
* 保存用户信息
*
* @param masterUserEntity
* @return
*/
int saveMasterUser(MasterUserEntity masterUserEntity);
/**
* 查询所有用户信息
*
* @return
*/
List<MasterUserEntity> listUsers();
}
- secondary数据源:
public interface SecondUserService {
/**
* 保存用户信息
*
* @param secondUserEntity
* @return
*/
int saveSecondUser(SecondUserEntity secondUserEntity);
/**
* 查询所有用户信息
*
* @return
*/
List<SecondUserEntity> listUsers();
}
- 创建UserService接口实现类,使用JdbcTemplate实现业务逻辑:
- primary数据源:
@Service
public class MasterUserServiceImpl implements MasterUserService {
@Autowired
private JdbcTemplate primaryJdbcTemplate;
@Override
public int saveMasterUser(MasterUserEntity masterUserEntity) {
return primaryJdbcTemplate.update("insert user (id,name,age) values (?,?,?);", masterUserEntity.getId(), masterUserEntity.getName(), masterUserEntity.getAge());
}
@Override
public List<MasterUserEntity> listUsers() {
return primaryJdbcTemplate.query("select id, name, age from user", new RowMapper<MasterUserEntity>() {
@Override
public MasterUserEntity mapRow(ResultSet resultSet, int i) throws SQLException {
MasterUserEntity user = new MasterUserEntity();
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("name"));
user.setAge(resultSet.getInt("age"));
return user;
}
});
}
}
- secondary数据源:
@Service
public class SecondUserServiceImpl implements SecondUserService {
@Autowired
private JdbcTemplate secondaryJdbcTemplate;
@Override
public int saveSecondUser(SecondUserEntity masterUserEntity) {
return secondaryJdbcTemplate.update("insert user (id,name,age) values (?,?,?);", masterUserEntity.getId(), masterUserEntity.getName(), masterUserEntity.getAge());
}
@Override
public List<SecondUserEntity> listUsers() {
return secondaryJdbcTemplate.query("select id, name, age from user", new RowMapper<SecondUserEntity>() {
@Override
public SecondUserEntity mapRow(ResultSet resultSet, int i) throws SQLException {
SecondUserEntity user = new SecondUserEntity();
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("name"));
user.setAge(resultSet.getInt("age"));
return user;
}
});
}
}
- 编写测试用例:
@SpringBootTest
class SpLearnJdbcTemplateMultiDataSourceApplicationTests {
@Autowired
private MasterUserService masterUserService;
@Autowired
private SecondUserService secondUserService;
/**
* 测试保存用户信息
*/
@Test
void testSave() {
//保存Primary数据源用户
MasterUserEntity masterUserEntity = new MasterUserEntity();
masterUserEntity.setId(111L);
masterUserEntity.setName("我是用Primary数据源存储的");
masterUserEntity.setAge(23);
masterUserService.saveMasterUser(masterUserEntity);
//保存Second数据源用户
SecondUserEntity secondUserEntity = new SecondUserEntity();
secondUserEntity.setId(222L);
secondUserEntity.setName("我是用Secondary数据源存储的");
secondUserEntity.setAge(52);
secondUserService.saveSecondUser(secondUserEntity);
System.out.println("save done!");
}
/**
* 测试查询用户信息
*/
@Test
void testQuery() {
//查询Primary数据源用户
System.out.println(masterUserService.listUsers());
//查询Second数据源用户
System.out.println(secondUserService.listUsers());
}
}
测试结果:
到这里,JdbcTemplate简单使用多数据源配置就完成了,通过截图可以看到,不同的数据成功插入到了不同的数据库中。