Springboot+Mybatis实现分页加条件查询功能
本文实例为大家分享了Springboot+Mybatis实现分页加条件查询的具体代码,供大家参考,具体内容如下
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>
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。
赞 (0)