Mysql 前置知识:https://www.bilibili.com/video/BV19M4y1W7Uh
增删改查数据
user 表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户名',
`password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '密码',
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名',
`phone` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '电话',
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '邮箱',
`address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '地址',
`avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '头像',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
在 Navicat 里面创建数据库 honey2024
增
插入数据
UserMapper
@Insert("insert into `user` (username, password, name, phone, email, address, avatar) " +
"values (#{username}, #{password}, #{name}, #{phone}, #{email}, #{address}, #{avatar})")
void insert(User user);
UserService
@Autowired
UserMapper userMapper;
public void insertUser(User user) {
userMapper.insert(user);
}
UserController
@Autowired
UserService userService;
/**
* 新增用户信息
*/
@PostMapping("/add")
public Result add(@RequestBody User user) {
userService.insertUser(user);
return Result.success();
}
遇到了一个小错误,
需要配置 username 和 password
数据重复插入会出现错误
学会看控制台的错误信息:
Duplicate entry ‘qing’ for key ‘username’
更加优雅的方式处理接口数据
改
根据 id 修改
UserMapper
@Update("update `user` set username = #{username}, password = #{password}, name= #{name}, phone = #{phone}, " +
"email = #{email}, address = #{address}, avatar = #{avatar} where id = #{id}")
void updateUser(User user);
UserService
public void updateUser(User user) {
userMapper.updateUser(user);
}
UserController
/**
* 修改用户信息
*/
@PutMapping("/update")
public Result update(@RequestBody User user) {
userService.updateUser(user);
return Result.success();
}
删
- 单个删除
UserMapper
@Delete("delete from `user` where id = #{id}")
void deleteUser(Integer id);
UserService
public void deleteUser(Integer id) {
userMapper.deleteUser(id);
}
UserController
/**
* 删除用户信息
*/
@DeleteMapping("/delete/{id}")
public Result delete(@PathVariable Integer id) {
userService.deleteUser(id);
return Result.success();
}
- 批量删除
UserService
public void batchDeleteUser(List<Integer> ids) {
for (Integer id : ids) {
userMapper.deleteUser(id); // 7 - 8
}
}
UserController
/**
* 批量删除用户信息
*/
@DeleteMapping("/delete/batch")
public Result batchDelete(@RequestBody List<Integer> ids) { // [7, 8]
userService.batchDeleteUser(ids);
return Result.success();
}
接口类型错误
查
- 全部查询
请求成功了,但是没返回数据?
非常简单,因为你没设置数据!
- 排序查询
asc desc
UserMapper
@Select("select * from `user` order by id desc")
List<User> selectAll();
UserService
public List<User> selectAll() {
return userMapper.selectAll();
}
UserController
/**
* 查询全部用户信息
*/
@GetMapping("/selectAll")
public Result selectAll() {
List<User> userList = userService.selectAll();
return Result.success(userList);
}
- 单条件
/**
* 根据ID查询用户信息
*/
@GetMapping("/selectById/{id}")
public Result selectById(@PathVariable Integer id) {
User user = userService.selectById(id);
return Result.success(user);
}
public User selectById(Integer id) {
return userMapper.selectById(id);
}
@Select("select * from `user` where id = #{id} order by id desc")
User selectById(Integer id);
- 多条件
为什么会发生 500 错误??
因为在数据库返回的时候,是返回了 6 条数据,但是你期待的查询结果是一个对象,这跟他的结果是违背的,所以报错了
一个很典型的错误,sql 写错了,但是自己没发现
如果看到这个 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException
那肯定是你的 sql 错了,要么是字段不合法,要么是 sql 语句写错了
注意:参数加上**@Param **注解
@Select("select * from `user` where username = #{username} and name = #{name} order by id desc")
List<User> selectByMore(@Param("username") String username, @Param("name") String name);
- 模糊查询
@Select("select * from `user` where username like concat('%', #{username}, '%') and name like concat('%', #{name}, '%') order by id desc")
List<User> selectByMo(String username, String name);
- 分页多条件模糊查询
计算 limit:(pageNum - 1) * pageSize
UserMapper
@Select("select * from `user` where username like concat('%', #{username}, '%') and name like concat('%', #{name}, '%') order by id desc limit #{skipNum}, #{pageSize}")
List<User> selectByPage(@Param("skipNum") Integer skipNum, @Param("pageSize")Integer pageSize, @Param("username") String username, @Param("name") String name);
@Select("select count(id) from `user` where username like concat('%', #{username}, '%') and name like concat('%', #{name}, '%') order by id desc")
int selectCountByPage(@Param("username") String username, @Param("name") String name);
UserService
public Page<User> selectByPage(Integer pageNum, Integer pageSize, String username, String name) {
Integer skipNum = (pageNum - 1) * pageSize; // 计算出来 1 -> 0,5 2 -> 5,5 3 -> 10,5
Page<User> page = new Page<>();
List<User> userList = userMapper.selectByPage(skipNum, pageSize, username, name);
Integer total = userMapper.selectCountByPage(username, name);
page.setTotal(total);
page.setList(userList);
return page;
}
UserController
/**
* 多条件模糊查询用户信息
* pageNum 当前的页码
* pageSize 每页查询的个数
*/
@GetMapping("/selectByPage")
public Result selectByPage(@RequestParam Integer pageNum,
@RequestParam Integer pageSize,
@RequestParam String username,
@RequestParam String name) {
Page<User> page = userService.selectByPage(pageNum, pageSize, username, name);
return Result.success(page);
}