第十四节:SpringBoot使用JdbcTemplate访问操作数据库基本用法

Spring对数据库的操作在jdbc上面做了深层次的封装,使用spring的注入功能,可以把DataSource注册到JdbcTemplate之中。

JdbcTemplate在Spring-jdbc包下面,还需要Spring-tx包支持,里面包含事务和异常控制.

建一个rumenz_springboot

  • 创建user表
  1. create table user(
  2. id int primary key auto_increment,
  3. name varchar(100) not null default '',
  4. domain varchar(100) not null default ''
  5. )engine=innodb default charset=utf8;

加入pom的依赖

  1. <dependency>
  2. <groupId>org.springframework.boot</groupId>
  3. <artifactId>spring-boot-starter-jdbc</artifactId>
  4. </dependency>
  5. <dependency>
  6. <groupId>mysql</groupId>
  7. <artifactId>mysql-connector-java</artifactId>
  8. <scope>runtime</scope>
  9. </dependency>

SpringBoot配置文件

  • application.properties
  1. spring.datasource.url=jdbc:mysql://localhost:3306/rumenz_springboot
  2. spring.datasource.username=root
  3. spring.datasource.password=root1234
  4. spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

创建User实体类

  1. @Builder
  2. @Data
  3. @AllArgsConstructor
  4. public class User implements RowMapper {
  5. private Integer id;
  6. private String name;
  7. private String domain;
  8. @Override
  9. public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
  10. User user=new User();
  11. user.setId(rs.getInt("id"));
  12. user.setName(rs.getString("name"));
  13. user.setDomain(rs.getString("domain"));
  14. return user;
  15. }
  16. }

Service接口

  • UserService.java
  1. package com.rumenz.lession14.controller.service;
  2. import com.rumenz.lession14.controller.entity.User;
  3. import java.util.List;
  4. /**
  5. * @className: UserService
  6. * @description: TODO 类描述
  7. * @author: 入门小站 rumenz.com
  8. * @date: 2021/12/13
  9. **/
  10. public interface UserService {
  11. Integer save(User user);
  12. List<User> list();
  13. Integer update(User user);
  14. Integer batchSave();
  15. }

Service接口实现类

  • UserServiceImpl.java
  1. package com.rumenz.lession14.controller.service.Impl;
  2. import com.fasterxml.jackson.core.JsonProcessingException;
  3. import com.fasterxml.jackson.databind.ObjectMapper;
  4. import com.fasterxml.jackson.databind.json.JsonMapper;
  5. import com.rumenz.lession14.controller.entity.User;
  6. import com.rumenz.lession14.controller.service.UserService;
  7. import org.springframework.beans.factory.annotation.Autowired;
  8. import org.springframework.jdbc.core.BeanPropertyRowMapper;
  9. import org.springframework.jdbc.core.JdbcTemplate;
  10. import org.springframework.stereotype.Service;
  11. import java.util.ArrayList;
  12. import java.util.List;
  13. /**
  14. * @className: UserServiceImpl
  15. * @description: TODO 类描述
  16. * @author: 入门小站 rumenz.com
  17. * @date: 2021/12/13
  18. **/
  19. @Service
  20. public class UserServiceImpl implements UserService {
  21. @Autowired
  22. private JdbcTemplate jdbcTemplate;
  23. @Override
  24. public Integer save(User user) {
  25. int reint = jdbcTemplate.update("insert into user(name,domain) values (?,?)", user.getName(), user.getDomain());
  26. return reint;
  27. }
  28. @Override
  29. public Integer batchSave() {
  30. String sql="insert into user(name,domain) values(?,?)";
  31. List<Object[]> par=new ArrayList<>();
  32. for (int i = 0; i < 10; i++) {
  33. String[] s=new String[2];
  34. s[0]="入门小站"+i;
  35. s[1]="https://rumenz.com/"+i;
  36. par.add(s);
  37. }
  38. int[] ints = jdbcTemplate.batchUpdate(sql, par);
  39. System.out.println(ints.toString());
  40. return 0;
  41. }
  42. @Override
  43. public List<User> list() {
  44. //User实现RowMapper接口,实现接口里的mapRow方法。
  45. List<User> list = jdbcTemplate.query("select * from user",new User());
  46. return list;
  47. }
  48. @Override
  49. public Integer update(User user) {
  50. Integer reint=jdbcTemplate.update("update user set name=?,domain=? where id=?", user.getName(),user.getDomain(),user.getId());
  51. //
  52. return reint;
  53. }
  54. }

Controller测试

  • RumenzController.java
  1. package com.rumenz.lession14.controller;
  2. import com.fasterxml.jackson.core.JsonProcessingException;
  3. import com.fasterxml.jackson.databind.ObjectMapper;
  4. import com.rumenz.lession14.controller.entity.User;
  5. import com.rumenz.lession14.controller.service.UserService;
  6. import org.springframework.beans.factory.annotation.Autowired;
  7. import org.springframework.web.bind.annotation.GetMapping;
  8. import org.springframework.web.bind.annotation.RequestMapping;
  9. import org.springframework.web.bind.annotation.RestController;
  10. import java.util.List;
  11. /**
  12. * @className: RumenzController
  13. * @description: TODO 类描述
  14. * @author: 入门小站 rumenz.com
  15. * @date: 2021/12/13
  16. **/
  17. @RestController
  18. @RequestMapping("/rumenz")
  19. public class RumenzController {
  20. @Autowired
  21. private UserService userService;
  22. //添加数据
  23. @GetMapping("/save")
  24. public String save(){
  25. User user=User.builder().name("入门小站").domain("https://rumenz.com").build();
  26. Integer reint = userService.save(user);
  27. return reint.toString();
  28. }
  29. //批量添加数据
  30. @GetMapping("/batchSave")
  31. public String batchSave(){
  32. Integer reint = userService.batchSave();
  33. return reint.toString();
  34. }
  35. //查询数据
  36. @GetMapping("/list")
  37. public String list() throws JsonProcessingException {
  38. List<User> list = userService.list();
  39. ObjectMapper objectMapper=new ObjectMapper();
  40. String val = objectMapper.writeValueAsString(list);
  41. return val;
  42. }
  43. //更新数据
  44. @GetMapping("/update")
  45. public String update() throws JsonProcessingException {
  46. User user=User.builder().id(1).name("入门小站-修改").domain("https://tooltt.com").build();
  47. Integer reint = userService.update(user);
  48. return reint.toString();
  49. }
  50. }

总结

常用CURD操作大致使用以下三个方法:

介绍

  • 关注【入门小站】回复【1001】获取 linux常用命令速查手册
  • 关注【入门小站】回复【1003】获取 LeetCode题解【java语言实现】
  • 关注【入门小站】回复【1004】获取 Java基础核心总结
  • 关注【入门小站】回复【1009】获取 阿里巴巴Java开发手册
返回笔记列表
入门小站