MyBatis和elementui中的高级查询和分页

前台

我们需要发送请求和带入参数

 

 有了这两个属性过后需要在data中添加

这个属性是和方法平级的

 

 

 整个页面

<template>
    <section>
        <!--工具条-->
        <el-col :span="24" class="toolbar" style="padding-bottom: 0px;">
            <el-form :inline="true" :model="filters">
                <el-form-item>
                    <el-input v-model="filters.keywords" placeholder="关键字"></el-input>
                </el-form-item>
                <el-form-item>
                    <el-button type="primary" v-on:click="getEmployees">查询</el-button>
                </el-form-item>
                <el-form-item>
                    <el-button type="primary" @click="handleAdd">新增</el-button>
                </el-form-item>
            </el-form>
        </el-col>

        <!--列表-->
        <el-table :data="employees" highlight-current-row v-loading="listLoading" @selection-change="selsChange" style=" 100%;">
            <el-table-column type="selection" width="55">
            </el-table-column>
            <el-table-column prop="id" width="60" label="ID">
            </el-table-column>
            <el-table-column prop="username" label="姓名" width="120" sortable>
            </el-table-column>
            <el-table-column prop="sn" label="员工编号" width="80" sortable>
            </el-table-column>
            <el-table-column prop="password" label="密码" width="280" sortable>
            </el-table-column>
            <el-table-column prop="headimg" label="头像" width="180" sortable>
            </el-table-column>
            <el-table-column prop="phone" label="电话" width="180" sortable>
            </el-table-column>
            <el-table-column prop="department" label="部门" width="180" sortable>
            </el-table-column>
            <el-table-column label="操作" width="150">
                <template scope="scope">
                    <el-button size="small" @click="handleEdit(scope.$index, scope.row)">编辑</el-button>
                    <el-button type="danger" size="small" @click="handleDel(scope.$index, scope.row)">删除</el-button>
                </template>
            </el-table-column>
        </el-table>

        <!--工具条
        page-size为每页显示的条数-->
        <el-col :span="24" class="toolbar">
            <el-button type="danger" @click="batchRemove" :disabled="this.sels.length===0">批量删除</el-button>
            <el-pagination layout="prev, pager, next" @current-change="handleCurrentChange" :page-size="pageSize" :total="total" style="float:right;">
            </el-pagination>
        </el-col>

        <!--编辑界面-->
        <el-dialog title="编辑" v-model="editFormVisible" :close-on-click-modal="false">
            <el-form :model="editForm" label-width="80px" :rules="editFormRules" ref="editForm">
                <el-form-item label="姓名" prop="username">
                    <el-input v-model="editForm.username" auto-complete="off"></el-input>
                </el-form-item>
            </el-form>
            <div slot="footer" class="dialog-footer">
                <el-button @click.native="editFormVisible = false">取消</el-button>
                <el-button type="primary" @click.native="editSubmit" :loading="editLoading">提交</el-button>
            </div>
        </el-dialog>

        <!--新增界面-->
        <el-dialog title="新增" v-model="addFormVisible" :close-on-click-modal="false">
            <el-form :model="addForm" label-width="80px" :rules="addFormRules" ref="addForm">
                <el-form-item label="姓名" prop="username">
                    <el-input v-model="addForm.username" auto-complete="off"></el-input>
                </el-form-item>
            </el-form>
            <div slot="footer" class="dialog-footer">
                <el-button @click.native="addFormVisible = false">取消</el-button>
                <el-button type="primary" @click.native="addSubmit" :loading="addLoading">提交</el-button>
            </div>
        </el-dialog>
    </section>
</template>

<script>
    import util from '../../common/js/util'
    //import NProgress from 'nprogress'
    import { getUserListPage, removeUser, batchRemoveUser, editUser, addUser } from '../../api/api';

    export default {
        data() {
            return {
                filters: {
                    keywords: '',

                },
                employees: [],
                total: 0,
                page: 1,
                pageSize:5,
                listLoading: false,
                sels: [],//列表选中列

                editFormVisible: false,//编辑界面是否显示
                editLoading: false,
                editFormRules: {
                    name: [
                        { required: true, message: '请输入姓名', trigger: 'blur' }
                    ]
                },
                //编辑界面数据
                editForm: {
                    username: '',
                },

                addFormVisible: false,//新增界面是否显示
                addLoading: false,
                addFormRules: {
                    name: [
                        { required: true, message: '请输入姓名', trigger: 'blur' }
                    ]
                },
                //新增界面数据
                addForm: {
                    username: '',
                }

            }
        },
        methods: {
            //性别显示转换
            formatSex: function (row, column) {
                return row.sex == 1 ? '男' : row.sex == 0 ? '女' : '未知';
            },
            handleCurrentChange(val) {
                this.page = val;
                this.getEmployees();
            },
            //获取用户列表
            getEmployees() {
                let para = {
                    page: this.page,
                    pageSize:this.pageSize,
                    keywords: this.filters.keywords
                };
                this.listLoading = true;
                //NProgress.start();
                //getUserListPage(para)
                this.$http.patch("/employee/query",para).then((res) => {
                    this.total=res.data.total;
                    this.employees = res.data.rows;
                    this.listLoading=false;
                    //NProgress.done();
                });
            },
            //删除
            handleDel: function (index, row) {
                this.$confirm('确认删除该记录吗?', '提示', {
                    type: 'warning'
                }).then(() => {
                    this.listLoading = true;
                    //NProgress.start();
                    let para = { id: row.id };
                    //console.log(row.id);
                    this.$http.delete('/employee/delete/'+para.id).then((res) => {
                        this.listLoading = false;
                        //NProgress.done();
                        this.$message({
                            message: '删除成功',
                            type: 'success'
                        });
                        this.getEmployees();
                    });
                }).catch(() => {
                    this.$message({
                        message: '删除失败',
                        type: 'error'
                    });
                });
            },
            //显示编辑界面
            handleEdit: function (index, row) {
                this.editFormVisible = true;
                this.editForm = Object.assign({}, row);
            },
            //显示新增界面
            handleAdd: function () {
                this.addFormVisible = true;
                this.addForm = {
                    username:''
                };
            },
            //编辑
            editSubmit: function () {
                this.$refs.editForm.validate((valid) => {
                    if (valid) {
                        this.$confirm('确认提交吗？', '提示', {}).then(() => {
                            this.editLoading = true;
                            //NProgress.start();
                            let para = Object.assign({}, this.editForm);
                            // para.birth = (!para.birth || para.birth == '') ? '' : util.formatDate.format(new Date(para.birth), 'yyyy-MM-dd');
                            this.$http.put("/employee/update",para).then((res) => {
                                this.editLoading = false;
                                //NProgress.done();
                                this.$message({
                                    message: '提交成功',
                                    type: 'success'
                                });
                                this.$refs['editForm'].resetFields();
                                this.editFormVisible = false;
                                this.getEmployees();
                            });
                        });
                    }
                });
            },
            //新增
            addSubmit: function () {
                this.$refs.addForm.validate((valid) => {
                    if (valid) {
                        this.$confirm('确认提交吗？', '提示', {}).then(() => {
                            this.addLoading = true;
                            //NProgress.start();
                            let para = Object.assign({}, this.addForm);
                            console.log(para);
                            // para.birth = (!para.birth || para.birth == '') ? '' : util.formatDate.format(new Date(para.birth), 'yyyy-MM-dd');
                            this.$http.post("/employee/add",para).then((res) => {
                                this.addLoading = false;
                                //NProgress.done();
                                this.$message({
                                    message: '提交成功',
                                    type: 'success'
                                });
                                this.$refs['addForm'].resetFields();
                                this.addFormVisible = false;
                                this.getEmployees();
                            });
                        });
                    }
                });
            },
            selsChange: function (sels) {
                this.sels = sels;
            },
            //批量删除
            batchRemove: function () {
                var ids = this.sels.map(item => item.id).toString();
                this.$confirm('确认删除选中记录吗？', '提示', {
                    type: 'warning'
                }).then(() => {
                    this.listLoading = true;
                    //NProgress.start();
                    let para = { ids: ids };
                    batchRemoveUser(para).then((res) => {
                        this.listLoading = false;
                        //NProgress.done();
                        this.$message({
                            message: '删除成功',
                            type: 'success'
                        });
                        this.getEmployees();
                    });
                }).catch(() => {

                });
            }
        },
        mounted() {
            this.getEmployees();
        }
    }
    function pagination(index,size,list){
        return list.slice((index-1)*size,index*size);
    }
</script>

<style scoped>

</style>

后台web

首先需要一个返回值

PageList

package cn.jiedada;

import java.util.List;

public class PageList<T> {
    private Long total;
    private List<T> rows;

    public Long getTotal() {
        return total;
    }

    public void setTotal(Long total) {
        this.total = total;
    }

    public List<T> getRows() {
        return rows;
    }

    public void setRows(List<T> rows) {
        this.rows = rows;
    }

    public PageList(Long total, List<T> rows) {
        this.total = total;
        this.rows = rows;
    }

    public PageList() {
    }
}

和一个接收参数的类

EmployeeQuery

public class EmployeeQuery extends BasicQuery {
}

BasicQuery

package cn.jiedada.query;

public class BasicQuery {
    private Long page;
    //默认为每页10张数据
    private Long pageSize=10L;
    //高级查询的关键字
    private String keywords;

    public String getKeywords() {
        return keywords;
    }

    public void setKeywords(String keywords) {
        this.keywords = keywords;
    }

    public Long getPageSize() {
        return pageSize;
    }

    public void setPageSize(Long pageSize) {
        this.pageSize = pageSize;
    }

    public Long getPage() {
        return page;
    }

    public void setPage(Long page) {
        this.page = page;
    }
    //设置每页开始的页数，limit start,通过get可以直接获得#{start}
    public Long getStart(){
        return (this.page-1)*pageSize;
    }
}

web

@RequestMapping(value = "/query",method = RequestMethod.PATCH)
    @ResponseBody
    //@RequestBody才能把前台的json转为后台的数据
    public PageList<Employee> query(@RequestBody EmployeeQuery employeeQuery){
        return employeeService.query(employeeQuery);
    }

后台service

因为每个页面都需要使用分页和高级查询所以我们在父类中写入

IBasicService

package cn.jiedada.service;

import cn.jiedada.PageList;
import cn.jiedada.query.BasicQuery;

import java.io.Serializable;
import java.util.List;

public interface IBasicService<T> {
    void save(T t);
    void delete(Serializable id);
    void update(T t);
    List<T> findAll();
    T findOne(Serializable id);

    PageList<T> query(BasicQuery basicQuery);
}

BasicServiceImpl

package cn.jiedada.service.impl;

import cn.jiedada.PageList;
import cn.jiedada.mapper.BasicMapper;
import cn.jiedada.query.BasicQuery;
import cn.jiedada.service.IBasicService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.Serializable;
import java.util.List;
@Transactional(propagation = Propagation.SUPPORTS,readOnly = true)
public class BasicServiceImpl<T> implements IBasicService<T> {
    @Autowired
    private BasicMapper<T> basicMapper;
    @Override
    @Transactional
    public void save(T t) {
        basicMapper.save(t);
    }

    @Override
    @Transactional
    public void delete(Serializable id) {
        basicMapper.delete(id);
    }

    @Override
    @Transactional
    public void update(T t) {
        basicMapper.update(t);
    }

    @Override
    public List<T> findAll() {
        return basicMapper.findAll();
    }

    @Override
    public T findOne(Serializable id) {
        return basicMapper.findOne(id);
    }

    @Override
    public PageList<T> query(BasicQuery basicQuery) {
        //  通过Mapper查询总数
        Long total = basicMapper.querryCount(basicQuery);
        //查询当前页数据
        List<T> rows = basicMapper.queryData(basicQuery);
        //通过构造方法返回数据中的所有值
        return new PageList<T>(total,rows);
    }
}

后台mapper

BasicMapper

package cn.jiedada.mapper;

import cn.jiedada.query.BasicQuery;

import java.io.Serializable;
import java.util.List;

public interface BasicMapper<T> {
    void save(T t);
    void delete(Serializable id);
    void update(T t);
    List<T> findAll();
    T findOne(Serializable id);
    //查询总页数
    Long querryCount(BasicQuery basicQuery);
    //查询分页1的所有数据
    List<T> queryData(BasicQuery basicQuery);
}

后台mapper.xml

通过继承和实现EmployeeMapper和EmployeeService也有我们的东西了，只需要实现Mapper中的方法我们就可以完成了

在其中添加

<!--查询总页数
    记住需要添加where条件
    -->
    <select id="querryCount" parameterType="employeeQuery" resultType="long">
        select count(id) from t_employee
        <where>
            <include refid="sqlWhere"></include>
        </where>
    </select>
    <!--查询pageList-->
    <select id="queryData" parameterType="employeeQuery" resultType="employee">
        select * from t_employee
        <where>
            <include refid="sqlWhere"></include>
        </where>
        LIMIT #{start},#{pageSize}
    </select>
    <!--高级查询,判断是否传入来了keywords-->
    <sql id="sqlWhere">
        <if test="keywords!=null and keywords!=''">
            and username like concat('%',#{keywords},'%')
        </if>
    </sql>