User.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shelbourne.schooldelivery.mapper.UserMapper">
<!-- 用户更新-->
<update id="update">-- 这里的id为函数名
update user
<set>
<if test="username != null and username !=''">
username=#{username},
</if>
<if test="nickname != null and nickname !=''">
nickname=#{nickname},
</if>
<if test="email != null and email !=''">
email=#{email},
</if>
<if test="phone != null and phone !=''">
phone=#{phone},
</if>
<if test="address != null and address !=''">
address=#{address}
</if>
</set>
<where>
id = #{id}
</where>
</update>
<!-- 分页+条件查询-->
<select id="selectPageWithParam" resultType="com.shelbourne.schooldelivery.entity.User">
select * from user
<include refid="condition"></include>
limit #{startIdx},#{size}
</select>
<!-- 查询满足条件的用户总数-->
<select id="selectTotalWithParam" resultType="java.lang.Integer">
select count(*) from user
<include refid="condition"></include>
</select>
<!-- 查询条件-->
<sql id="condition">
<where>
1=1
<if test="username != null and username != ''">
and username like concat("%",#{username},"%")
</if>
<if test="email != null and email != ''">
and email like concat("%",#{email},"%")
</if>
<if test="address != null and address != ''">
and address like concat("%",#{address},"%")
</if>
</where>
</sql>
</mapper>UserMapper.java
package com.shelbourne.schooldelivery.mapper;
import com.shelbourne.schooldelivery.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserMapper {
//查询所有用户
@Select("select * from user")
//mybatis提供注解，注意SQL语句后不能加分号
List<User> findAll();
//新增用户
@Insert("insert into user(username,password,nickname,email,phone,address)" +
"values(#{username},#{password},#{nickname},#{email},#{phone},#{address})")
public Integer insert(User user);
//通过注解（静态）和xml里面（动态）两种方式编写SQL语句
int update(User user);
//删除单个用户
@Delete("delete from user where id=#{id}")
Integer deleteById(@Param("id") Integer id);//最后加上@Param参数，参数名和上面的#{}里面的一样
//查询记录条数
@Select("select count(*) from user")
Integer selectTotal();
//编写动态SQL实现分页查询+条件查询
//查询满足条件的某一页用户
List<User> selectPageWithParam(Integer startIdx, Integer size, String username, String email, String address);
//查询满足条件的所有用户数
int selectTotalWithParam(String username, String email, String address);
}UserController.java
package com.shelbourne.schooldelivery.controller;
import com.shelbourne.schooldelivery.entity.User;
import com.shelbourne.schooldelivery.mapper.UserMapper;
import com.shelbourne.schooldelivery.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RequestMapping("/user") //统一给接口加前缀，postman后台接口localhost:9090/user
@RestController
public class UserController {
@Autowired //注入其他类的注解
private UserMapper userMapper;
@Autowired
private UserService userService;
//查询所有用户
@GetMapping
public List<User> findAll(String username) {
return userMapper.findAll();
}
//通过POST请求进行新增和更新操作
@PostMapping
public Integer save(@RequestBody User user) {//一定要加上RequestBody，可以把前端传回的JSON对象转换为Java对象
return userService.save(user);
}
//删除请求接口
@DeleteMapping("/{id}")
public Integer delete(@PathVariable Integer id) {//这里的“id”必须和DeleteMapping里面的名字一样
return userMapper.deleteById(id);
}
@GetMapping("/page")
public Map<String, Object> findPage(@RequestParam Integer pageNum, @RequestParam Integer pageSize, @RequestParam String username,
@RequestParam String email, @RequestParam String address) {
int startIdx = (pageNum - 1) * pageSize, size = pageSize;
List<User> data = userMapper.selectPageWithParam(startIdx, size, username, email, address);//获取一页的数据
int total = userMapper.selectTotalWithParam(username, email, address);//查询总条数
Map<String, Object> res = new HashMap<>();
res.put("data", data);//表格数据
res.put("total", total);//分页使用
return res;
}
}Home.vue中：
<script>
export default {
data() {
return {
total: 0,//记录条数为0
pageNum: 1,//默认从第一条记录开始
pageSize: 10,//默认分页大小为10
username: "",//条件查询的姓名
email: "",//条件查询的邮箱
address: "",//条件查询的地址
}
},
created() {//页面渲染完成后的数据刷新
this.flushData()
},
methods: {
//获取数据
flushData() {
fetch("http://localhost:9090/user/page?pageNum=" +
this.pageNum + "&pageSize=" + this.pageSize + "&username=" +
this.username + "&email=" + this.email + "&address=" + this.address).then(res => res.json()).then(res => {
// console.log(res)
//跨域问题：前端端口8080，后端端口9090，导致跨域
this.tableData = res.data
this.total = res.total
})
}
}
}
</script>
