Springboot+Mybatis实现分页加条件查询功能

<? 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 >

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);

}

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;

    }

}

<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>