1 整合JdbcTemplate
1.1 引入需要的pom依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>2.2.5.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.25</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
1.2 application.properties中配置数据库连接信息
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=admin
1.3 创建数据库表及实体类
实体类User.java
public class User {
private Integer id;
private String username;
private Integer age;
private String address;
//省略getter和setter方法
}
1.4 注入JdbcTemplate编写增、删、改、查方法并测试
1.4.1 插入数据
@Service
public class UserService {
//springboot引入spring-boot-stater-jdbc依赖后可直接注入JdbcTemplate使用
@Autowired
JdbcTemplate jdbcTemplate;
//添加一条用户信息
public Integer addUser(User user){
return jdbcTemplate.update("insert into user (username,age,address) values (?,?,?)",
user.getUsername(),user.getAge(),user.getAddress());
}
}
编写测试方法并运行:
@SpringBootTest
class SpringbootdemoApplicationTests {
@Autowired
UserService userService;
@Test
void test1() {
User user = new User();
user.setUsername("张三");
user.setAge(17);
user.setAddress("北京市");
userService.addUser(user);
}
}
数据成功插入user表
1.4.2 查询数据
方式一:
public List<User> getAllUsers(){
//适用于数据库表字段与实体类字段一致
return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper(User.class));
}
测试方法及运行结果
@Test
void test2(){
List<User> users = userService.getAllUsers();
System.out.println(users);
}
方式二:
public List<User> getAllUsers(){
//适用于数据库表字段与实体类字段一致
//return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper(User.class));
//适用于数据库表字段与实体类字段不一致
return jdbcTemplate.query("select * from user", new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
int id= rs.getInt("id");
String username = rs.getString("username");
int age = rs.getInt("age");
String address = rs.getString("address");
User user = new User();
user.setId(id);
user.setUsername(username);
user.setAge(age);
user.setAddress(address);
return user;
}
});
}
运行结果:
1.4.3 修改用户信息
userService修改用户信息方法:
//通过id修改用户名
public Integer updateUserNameById(User user){
return jdbcTemplate.update("update user set username=? where id=?",user.getUsername(),user.getId());
}
测试方法:
@Test
void test3(){
User user = new User();
user.setId(1);
user.setUsername("李四");
userService.updateUserNameById(user);
test2();
}
运行结果:
1.4.4 删除用户信息
userService删除方法
public Integer deleteUserById(Integer id){
return jdbcTemplate.update("delete from user where id = ?",id);
}
测试方法:
@Test
void test4(){
userService.deleteUserById(1);
test2();
}
运行结果:
2 配置JdbcTemplate多数据源
2.1 准备
首先准备两个数据库,方便起见,数据库表及字段一致,数据库名称不一致,如下:
两张表数据分别如下:
test数据库user表
test01数据库user表
2.2 application.properties中配置两个数据源如下:
#test数据库数据源
spring.datasource.test.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.test.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.test.username=root
spring.datasource.test.password=admin
#test01数据库数据源
spring.datasource.test01.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.test01.url=jdbc:mysql://127.0.0.1:3306/test01
spring.datasource.test01.username=root
spring.datasource.test01.password=admin
2.3 编写配置类注入数据源到JdbcTemplate
@Configuration
public class WebMvcConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.test")
DataSource test(){
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.test01")
DataSource test01(){
return DruidDataSourceBuilder.create().build();
}
}
2.4 配置不同数据源JdbcTemplate
@Configuration
public class JdbcTemplateConfig {
@Bean//注入dsTest数据源到jdbcTemplateTest
JdbcTemplate jdbcTemplateTest(@Qualifier("dsTest") DataSource dsTest){
return new JdbcTemplate(dsTest);
}
@Bean//注入dsTest01数据源到jdbcTemplateTest01
JdbcTemplate jdbcTemplateTest01(@Qualifier("dsTest01") DataSource dsTest01){
return new JdbcTemplate(dsTest01);
}
}
2.5 Service层引入两个不同数据源JdbcTemplate并查询
@Service
public class UserService01 {
@Resource(name = "jdbcTemplateTest")
JdbcTemplate jdbcTemplateTest;
@Autowired
@Qualifier("jdbcTemplateTest01")
JdbcTemplate jdbcTemplateTest01;
public List<User> selectUsersByTest(){
return jdbcTemplateTest.query("select * from user", new BeanPropertyRowMapper(User.class));
}
public List<User> selectUsersByTest01(){
return jdbcTemplateTest01.query("select * from user", new BeanPropertyRowMapper(User.class));
}
}
2.6 查询测试
@Test
void test1(){
List<User> usersByTest = userService01.selectUsersByTest();
System.out.println("usersByTest:"+usersByTest);
List<User> usersByTest01 = userService01.selectUsersByTest01();
System.out.println("usersByTest01:"+usersByTest01);
}