首先我们准备好Mybatis项目和SpringBoot结合的项目
代码参考这三篇博文:
企业级智能软件开发(一)———Spring Boot
企业级智能软件开发(二)——Mybatis
企业智能软件开发(三)——SpringBoot 与Mybatis结合
数据库的操作内容在Spring Boot项目中的UserDao.java和UserController.java进行,给javaBean添加一个Result.java
-
数据库t_user表结构:
-
数据库数据:
查询数据库数据
- UserDao.java
@Select("select * from t_user where id=#{id}")
public User getUserById(Integer id);
@Select("select * from t_user where real_name like CONCAT('%',#{word},'%'); ")
public List<User> getUserByWord(String word);
@Select("select * from t_user")
public List<User> getAllUsers();
- UserController.java
@ResponseBody
@RequestMapping("/info")
public User getUserById(Integer id) {
//去数据中根据id查询用户信息
User user = userDao.getUserById(id);
return user;
}
@ResponseBody
@RequestMapping("/word")
public List<User> getUserByword(String word) {
//去数据中根据关键字查询用户信息
List<User> listUser = userDao.getUserByWord(word);
return listUser;
}
//列出所有的用户
@ResponseBody
@RequestMapping("/list")
public List<User> listUsers() {
//返回多个结果的,要用List接收
List<User> list = userDao.getAllUsers();
return list;
}
- 效果输出:
按id查找:
{
“id”: 1,
“username”: “zhangsan”,
“password”: “123”,
“realName”: “张三”,
“phone”: “13411112222”,
“email”: “zhangsan@qq.com”,
“status”: 1,
“tx”: “tx1.jpg”
}
按关键字查找:
[
{
“id”: 3,
“username”: “dulele”,
“password”: “123”,
“realName”: “杜乐乐”,
“phone”: “13455556666”,
“email”: “dulele@qq.com”,
“status”: 1,
“tx”: “tx3.jpg”
},
{
“id”: 4,
“username”: “zhonglele”,
“password”: “123”,
“realName”: “钟乐乐”,
“phone”: “13477778888”,
“email”: “zhonglele@qq.com”,
“status”: 1,
“tx”: “tx4.jpg”
}
]
全部信息:
[
{
“id”: 1,
“username”: “zhangsan”,
“password”: “123”,
“realName”: “张三”,
“phone”: “13411112222”,
“email”: “zhangsan@qq.com”,
“status”: 1,
“tx”: “tx1.jpg”
}
…
…
…
]
添加数据库数据
- UserDao.java
//插入用户
@Insert("insert into t_user(username, real_name, phone, email)"
+ " values (#{username}, #{realName}, #{phone}, #{email}) ")
public void insertUser(User user);
- UserController.java
@ResponseBody
@PostMapping("/add") //必须用post方式提交
public Result addUser(User user) {
userDao.insertUser(user); //插入数据库
Result r = new Result();
r.setCode(2000);
r.setMsg("新增用户成功!");
return r;
}
- 插入数据:
- 效果输出:
数据库插入效果:
删除数据库数据
- UserDao.java
@Delete("delete from t_user where id=#{id}")
public void deleteUserById(Integer id);
- UserController.java
@ResponseBody
@GetMapping("/delete") //使用Get方式
public Result delete(Integer id) {
userDao.deleteUserById(id);
Result r = new Result();
r.setCode(2000);
r.setMsg("");
return r;
}
- 删除数据:
- 效果输出:
数据库变化:
原:
后:
修改数据库数据
- UserDao.java
//更新用户
@Update("update t_user set real_name=#{realName},"
+ "phone=#{phone}, email=#{email} "
+ "where id=#{id}")
public void updateUser(User user);
- UserController.java
@ResponseBody
@PostMapping("/update") //必须用post方式提交
public Result update(User user) {
userDao.updateUser(user); //更新用户信息
Result r = new Result();
r.setCode(2000);
r.setMsg("");
return r;
}
- 更新内容:
- 效果输出:
数据库变化:
原:
后:
不显示值为null的字段
在调用新增用户接口的时候,响应的数据中有个data字段为null,在不删除这个字段的情况下实现响应时如果data为空就不带这个字段。
方法:
在bean类中加入
import com.fasterxml.jackson.annotation.JsonInclude;
@JsonInclude(JsonInclude.Include.NON_NULL)
效果:
添加修改的所有代码
- 添加的Result.java:
package com.test.bean;
import com.fasterxml.jackson.annotation.JsonInclude;
//封装返回数据
@JsonInclude(JsonInclude.Include.NON_NULL)
public class Result {
private Integer code;
private String msg;
private Object data;
public Integer getCode() {
return code;
}
public void setCode(Integer code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public Object getData() {
return data;
}
public void setData(Object data) {
this.data = data;
}
}
- UserDao.java总代码:
package com.test.dao;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.test.bean.User;
/**
* 注意这里是一个接口(interface),Mybatis会利用动态代理技术在运行过程中动态生成这个类的实现类
*/
public interface UserDao {
@Select("select * from t_user where id=#{id}")
public User getUserById(Integer id);
@Select("select * from t_user where real_name like CONCAT('%',#{word},'%'); ")
public List<User> getUserByWord(String word);
@Select("select * from t_user")
public List<User> getAllUsers();
//插入用户
@Insert("insert into t_user(username, real_name, phone, email)"
+ " values (#{username}, #{realName}, #{phone}, #{email}) ")
public void insertUser(User user);
//更新用户
@Update("update t_user set real_name=#{realName},"
+ "phone=#{phone}, email=#{email} "
+ "where id=#{id}")
public void updateUser(User user);
@Delete("delete from t_user where id=#{id}")
public void deleteUserById(Integer id);
}
- UserController.java
package com.test.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.test.bean.Result;
import com.test.bean.User;
import com.test.dao.UserDao;
@CrossOrigin
@Controller
@RequestMapping("/user") //写在类上面,该类所有的方法都以这个路径开头
public class UserController {
//自动注入UserDao的实现
@Autowired
private UserDao userDao;
@ResponseBody
@RequestMapping("/info")
public User getUserById(Integer id) {
//去数据中根据id查询用户信息
User user = userDao.getUserById(id);
return user;
}
@ResponseBody
@RequestMapping("/word")
public List<User> getUserByword(String word) {
//去数据中根据id查询用户信息
List<User> listUser = userDao.getUserByWord(word);
return listUser;
}
//列出所有的用户
@ResponseBody
@RequestMapping("/list")
public List<User> listUsers() {
//返回多个结果的,要用List接收
List<User> list = userDao.getAllUsers();
return list;
}
@ResponseBody
@PostMapping("/add") //必须用post方式提交
public Result addUser(User user) {
userDao.insertUser(user); //插入数据库
Result r = new Result();
r.setCode(2000);
r.setMsg("新增用户成功!");
return r;
}
@ResponseBody
@PostMapping("/update") //必须用post方式提交
public Result update(User user) {
userDao.updateUser(user); //更新用户信息
Result r = new Result();
r.setCode(2000);
r.setMsg("");
return r;
}
@ResponseBody
@GetMapping("/delete") //使用Get方式
public Result delete(Integer id) {
userDao.deleteUserById(id);
Result r = new Result();
r.setCode(2000);
r.setMsg("");
return r;
}
}