1. 配置基础依赖

mysql 驱动版本看情况【我的mysql版本为8.0.26】

org.springframework.bootspring-boot-starter-jdbc


mysqlmysql-connector-java8.0.26

配置数据库的连接信息，在 application.yaml 配置文件中配置

官方配置：Common Application Properties (spring.io)

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverusername: rootpassword: rooturl: jdbc:mysql://localhost:3306/[数据库表]

2.新建实体类

新建实体类 User ,实现 RowMapper 接口 ，该接口重写的 mapRow 方法为了实体字段和数据表字段映射（对应）。

package com.example.demo.entity;import lombok.*;
import org.springframework.jdbc.core.RowMapper;import java.sql.ResultSet;
import java.sql.SQLException;/*** @author: fly* @Date: 2023-03-19 20:14* @Description: 用户实体类*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@ToString
public class User implements RowMapper {private int id;private String username;private String password;@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {User user = new User();user.setId(rs.getInt("id"));user.setUsername(rs.getString("username"));user.setPassword(rs.getString("password"));return user;}
}

3.操作数据

JDBCTemplate 提供3个操作数据的方法：

1. execute 直接执行 sql 语句
2. update 进行新增、修改、删除操作
3. query 查询操作

创建数据表 

CREATE TABLE `t_user` (`id` int NOT NULL AUTO_INCREMENT COMMENT '用户id',`username` varchar(100) DEFAULT NULL,`password` varchar(100) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表'

插入一条数据

INSERT INTO t_user VALUES(NULL,"admin","123456");

 添加控制器 UserController 【均在浏览器测试通过！】

1. 添加用户 /user/save
2. 查询用户 /user/get/1 
3. 查询所有用户 /user/get 或者 /user/getAllUsers
4. 修改用户 /user/update/1
5. 删除用户 /user/delete/1

package com.fly.demo.controller;import com.fly.demo.entity.User;
import lombok.extern.log4j.Log4j2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;/*** @author: fly* @Date: 2023-03-20 17:05* @Description: 用户控制器*/
@RestController
@RequestMapping("/user")
@Log4j2
public class UserController {private JdbcTemplate jdbcTemplate;@Autowiredpublic void setJdbcTemplate(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}/*** 添加用户*/@GetMapping("/save")public void saveUser() {String sql = "INSERT INTO t_user VALUES(NULL,?,?)";User user = new User(null,"209110407","fengluyu");int row = jdbcTemplate.update(sql,user.getUsername(),user.getPassword());log.info("保存用户成功!保存个数: " + row);}/*** 获取单个用户* @param id 用户id* @return 返回用户*/@GetMapping("/get/{id}")public User getUser(@PathVariable int id) {String sql = "SELECT id,username,password FROM t_user WHERE id = ?";List userList = jdbcTemplate.query(sql, new User(), id);User user = null;if (userList.size() > 0) {user = userList.get(0);}return user;}/*** 返回所有用户* @return 所有用户列表*/@GetMapping("/get")public List getUsers() {String sql = "SELECT id,username,password FROM t_user";return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));}/*** 获取所有用户* @return 所有用户列表*/@GetMapping("/getAllUsers")public List getAllUser() {String sql = "SELECT id,username,password FROM t_user";return jdbcTemplate.query(sql,new User());}@GetMapping("/update/{id}")public void updateUser(@PathVariable int id) {String sql = "UPDATE t_user SET password = ? WHERE id = ?";int row = jdbcTemplate.update(sql,"87am23",id);log.info("修改用户(id:" +id + ") 操作: " + row);}@GetMapping("/delete/{id}")public void deleteUser(@PathVariable int id) {String sql = "DELETE FROM t_user WHERE id = ?";int row = jdbcTemplate.update(sql,id);log.info("删除用户(id: " + id + ") 操作: " + row);}
}

 4.浏览器访问