SpringBoot 中如何整合 JdbcTemplate 使用

在SpringBoot中持久化框架有很多，最常见的例如：JdbcTemplate、mybatis、，mybatisplus、hibernate、jpa等等。今天我们就来聊一聊Spring自带的持久化框架：JdbcTemplate。虽然JdbcTemplate功能相比较于mybatisplus等要简单的多，但是这已经比java原生的jdbc进步了很多啦。

1. 配置

1. pom.xml

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.27</version>
    <scope>runtime</scope>
</dependency>

2. application.properties

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.username=root
spring.datasource.password=qwe123
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/train?useUnicode=true&characterEncoding=UTF-8

2. 聊一聊Service

这里，我们就用前期工程中的User实体类，有兴趣的同学可以看看我发布的前几期SpringBoot的文章。

@Data //生成getter,setter等函数
@AllArgsConstructor //生成全参数构造函数
@NoArgsConstructor //生成无参构造函数
@ApiModel //在swagger中说明当前实体
public class User {
    @ApiModelProperty(value = "用户id")
    private Long id;

    @ApiModelProperty(value = "用户名")
    private String name;

    @ApiModelProperty(value = "用户地址")
    private String address;
}

然后，我们创建一个Service类

@Service
public class UserService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    public int addUser(User user) {
        return jdbcTemplate.update("insert into user (id,name,address) values (?,?,?);", user.getId(), user.getName(), user.getAddress());
    }

    public int deleteUserById(Long id) {
        return jdbcTemplate.update("delete from user where id=?", id);
    }

    public int updateUserById(User user) {
        return jdbcTemplate.update("update user set name=?,address=? where id=?", user.getName(), user.getAddress(),user.getId());
    }

    public List<User> getAllUsers() {
        return jdbcTemplate.query("select * from user", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                String username = resultSet.getString("name");
                String address = resultSet.getString("address");
                long id = resultSet.getLong("id");
                User user = new User();
                user.setAddress(address);
                user.setName(username);
                user.setId(id);
                return user;
            }
        });
    }

    public List<User> getAllUsers2() {
        return jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
    }

    public User getUserById(Long id){
        List<User> userList = jdbcTemplate.query("select * from user where id = "+id, new BeanPropertyRowMapper<>(User.class));
        User user = userList != null ? userList.get(0) : null;
        return user;
    }
}

在UserService中我们使用jdbcTemplate分别实现对User的增删改查。

然后我们再创建一个Controller类对外提供标准的RESTFUL接口。

@RestController
@Api(tags = "用户管理接口")
@RequestMapping("/user")
public class UserController {
    @Autowired
    UserService userService;

    @PostMapping("/")
    @ApiOperation("添加用户的接口")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "id", value = "用户id", defaultValue = "999"),
            @ApiImplicitParam(name = "username", value = "用户名", defaultValue = "李四"),
            @ApiImplicitParam(name = "address", value = "用户地址", defaultValue = "深圳", required = true)
    })
    public String addUser(Long id, String username, @RequestParam(required = true) String address) {
        User user = new User();
        user.setId(id);
        user.setName(username);
        user.setAddress(address);
        int i = userService.addUser(user);
        return "add " + i + " user success";
    }

    @GetMapping("/{id}")
    @ApiOperation("根据id查询用户的接口")
    @ApiImplicitParam(name = "id", value = "用户id", defaultValue = "99", required = true)
    public User getUserById(@PathVariable Long id) {
        List<User> allUsers2 = userService.getAllUsers2();
        return allUsers2.get(0);
    }

    @GetMapping("/")
    @ApiOperation("查询所有用户的接口")
    public List<User> getUsers() {
        List<User> allUsers2 = userService.getAllUsers2();
        return allUsers2;
    }

    @PutMapping("/")
    @ApiOperation("更新用户的接口")
    public String updateUserById(Long id, String username, String address) {
        User user = new User(id,username,address);
        int i = userService.updateUserById(user);
        return "update " + i + " user success";
    }

    @DeleteMapping("/{id}")
    @ApiOperation("删除用户的接口")
    public String deleteUserById(@PathVariable Long id) {
        int i = userService.deleteUserById(id);
        return "delete " + i + " user success";
    }
}

3. 测试

我们这里使用postman作为测试工具

1. 测试添加
   
2. 测试查询单个user
   
3. 测试查询所有user
   
4. 测试修改
   
5. 测试删除
   

4. 总结

这里我们列出了JdbcTemplate中最常用的增删改查，当然JdbcTemplate还有其他的用法，例如调用存储过程，都和jdbc类似，其他的各位看官可以自己去试一试哦

源码已上传至Github,有兴趣的同学可以下载下来看看

更多精彩，可到GitHub查看我的公开项目，关注码技术持续输出，O(∩_∩)O哈哈~