1. 配置基础依赖
mysql 驱动版本看情况【我的mysql版本为8.0.26】
<!--JDBCTemplate 依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- MySql 依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
配置数据库的连接信息,在 application.yaml 配置文件中配置
官方配置:Common Application Properties (spring.io)
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: root
url: 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<User> {
private int id;
private String username;
private String password;
@Override
public 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个操作数据的方法:
- execute 直接执行 sql 语句
- update 进行新增、修改、删除操作
- 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 【均在浏览器测试通过!】
- 添加用户 /user/save
- 查询用户 /user/get/1
- 查询所有用户 /user/get 或者 /user/getAllUsers
- 修改用户 /user/update/1
- 删除用户 /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;
@Autowired
public 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<User> 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<User> getUsers() {
String sql = "SELECT id,username,password FROM t_user";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
}
/**
* 获取所有用户
* @return 所有用户列表
*/
@GetMapping("/getAllUsers")
public List<User> 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.浏览器访问