MyBatis 初代目小样 分页查询+条件查询

数据库字段

UserMapper.xml配置文件

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.wn.mapper.UserMapper">
    <!--开启二级缓存-->
    <cache eviction="LRU" flushInterval="100000" readOnly="true" size="1024"/>
    
    <select id="queryAllUser" resultType="user">
        select * from user
        <where>
            <if test="id!=null and id!=0">
                and id=#{id}
            </if>
            <if test="userName!=null and userName!=''">
                and userName like "%" #{userName} "%"
            </if>
            <if test="userPhone!=null and userPhone!=''">
                and userPhone=#{userPhone}
            </if>
        </where>
    </select>

    <!--批量删除-->
    <delete id="deleteManyUser">
        delete from user
        <where>
            <foreach collection="array" open="id in(" close=")" separator="," item="id">
                #{id}
            </foreach>
        </where>
    </delete>

    <!--插入-->
    <insert id="addUser" parameterType="user">
        insert into user values (null ,#{userName},#{userPass},#{userPhone},0,#{userImg})
    </insert>

    <!--删除-->
    <delete id="deleteUser" parameterType="int">
        delete from user where id=#{id}
    </delete>

    <!--修改-->
    <update id="updateUser" parameterType="user">
        update user
        <set>
            <if test="userName!=null and userName!=''">
                userName=#{userName},
            </if>
            <if test="userPass!=null and userPass!=''">
                userPass=#{userPass},
            </if>
            <if test="userPhone!=null and userPhone!=''">
                userPhone=#{userPhone},
            </if>
            <if test="userImg!=null and userImg!=''">
                userImg=#{userImg},
            </if>
        </set>
        where id=#{id}
    </update>

    <!--批导-->
    <insert id="addManyUser">
        insert into user values
        <foreach collection="list" item="user" separator=",">
            (null ,#{user.userName},#{user.userPass},#{user.userPhone},0,#{user.userImg})
        </foreach>
    </insert>

    <!--分页+多条件查询-->
    <select id="pageList" resultType="user" parameterType="map">
        select * from user
        <where>
            <if test="user.userName!=null and user.userName!=''">
                and userName like "%" #{user.userName} "%"
            </if>
            <if test="user.userPhone!=null and user.userPhone!=''">
                and userPhone like "%" #{user.userPhone} "%"
            </if>
            <if test="user.id!=null and user.id!=0">
                and id=#{id}
            </if>
        </where>
        <foreach collection="pages" open="limit" separator="," item="page">
            #{page}
        </foreach>
    </select>

    <!--根据条件查询出来的总数-->
    <select id="getDataCount" resultType="int" parameterType="user">
        select count(*) from user
        <where>
            <if test="userName!=null and userName!=''">
                and userName like "%" #{userName} "%"
            </if>
            <if test="userPhone!=null and userPhone!=''">
                and userPhone like "%" #{userPhone} "%"
            </if>
            <if test="id!=null and id!=0">
                and id=#{id}
            </if>
        </where>
    </select>

</mapper>

UserMapper.xml配置文件结合页面的截图详解

✪✪✪
页面端功能选项:

分页+多条件查询

查询用户-admin.jsp页面代码

        function queryAllUser() {
            $.ajax({
                url:"queryAllUser.do",
                dataType:"json",
                data:$("#userFormSearchId").serialize(),
                type:"get",
                success:function (pb) {
                    let data = pb.dataList; //PageBean pb 里面的两个属性 maxPage/dataList
                    let maxPage = parseInt(pb.maxPage);

                    let pagNumStr = "<ul class='pagination'>";
                    pagNumStr += "<li οnclick='getPageList(-1)'><a href='javascript:void(0)'>&laquo;</a></li>";
                    for (let i = 1; i <= maxPage; i++) {
                        pagNumStr += "<li οnclick='getPageList("+i+")'><a href='javascript:void(0)'>"+i+"</a></li>";
                    }
                    pagNumStr += "<li οnclick='getPageList(-2)'><a href='javascript:void(0)'>&raquo;</a></li>";

                    pagNumStr += "</ul>";
                    $("#userPage").html(pagNumStr);

                    userJson = data;
                    // alert(data);
                    let str = "<table class='table-bordered col-md-12 text-center'>";
                    str += "<form id='userFormIds'>";
                    str += "<tr>";
                    str += "<td><input type='checkbox' οnchange='selAll(this.checked)' > 全选 </td>"
                    str += "<td>编号</td>";
                    str += "<td>用户名称</td>";
                    str += "<td>用户密码</td>";
                    str += "<td>手机号码</td>";
                    str += "<td>用户头像</td>";
                    str += "<td>用户类型</td>";
                    str += "<td colspan='2'>操作</td>";
                    str += "</tr>";
                    for (let i = 0; i < data.length; i++) {
                        str += "<tr>";
                        str += "<td><input type='checkbox' name='idName' class='sel' value='"+data[i].id+"'>选择</td>"
                        str += "<td>"+data[i].id+"</td>";
                        str += "<td>"+data[i].userName+"</td>";
                        str += "<td>"+data[i].userPass+"</td>";
                        str += "<td>"+data[i].userPhone+"</td>";
                        str += "<td><img src='"+data[i].userImg+"' class='img-circle' width='100px'></td>"
                        if(data[i].userType == 1){
                            str += "<td>管理员</td>";
                        }else {
                            str += "<td>普通用户</td>";
                        }
                        str += "<td><button class='btn btn-danger' type='button'  οnclick='deleteUser("+data[i].id+")'><span class='glyphicon glyphicon-remove'></span>删除</button></td>";
                        str += "<td><button class='btn btn-info' type='button' data-toggle='modal' data-target='#updateModal' οnclick='updateUser("+data[i].id+")'><span class='glyphicon glyphicon-pencil'></span>修改</button></td>"
                        str += "</tr>";
                    }
                    str += "</form>";
                    str += "</table>";
                    $("#userContainer").html(str);
                }
            });
        }

分页方法

入口函数里的条件查询

用户模块

            <div style="height:800px;" class="table-bordered text-center" >
                <!--用户管理模块-->
                <div class="container" id="usersId"  style="display: none">
                    <div class="row table-bordered" style="margin-top: 20px;line-height: 100px">
                        <div class="col-md-8 form-inline text-center">
                            <form id="userFormSearchId">
                                <input type="hidden" name="nowPage">
                                <input type="text" placeholder="请输入id" class="form-control" name="id">
                                <input type="text" placeholder="请输入手机号" class="form-control" name="userPhone">
                                <input type="text" placeholder="请输入关键字" class="form-control" name="userName"><input type="button" id="userSerachBtn" class="btn btn-primary" value="搜索">
                            </form>
                        </div>

                        <div class="col-md-4 navbar-right text-center">
                            <button class="btn btn-primary btn-lg" data-toggle="modal" data-target="#addModal">
                                <span class="glyphicon glyphicon-plus"></span>添加
                            </button>
                            <button class="btn btn-danger btn-lg" id="deleteManyUserBtn">
                                <span class="glyphicon glyphicon-remove"></span>批量删除
                            </button>
                            <button class="btn btn-warning btn-lg" data-toggle="modal" data-target="#addManyUserModal">
                                <span class="glyphicon glyphicon-plus"></span>批量导入
                            </button>
                        </div>
                    </div>

                    <div class="row" style="margin-top: 20px" id="userContainer" >
                        <table class="table-bordered col-md-12 text-center" >
                            <tr style="line-height: 50px">
                                <td>用户编号</td>
                                <td>用户姓名</td>
                                <td>用户密码</td>
                                <td>手机号码</td>
                                <td>用户头像</td>
                                <td>用户类型</td>
                                <td colspan="2">操作</td>
                            </tr>
                            <tr>
                                <td>1</td>
                                <td>张三</td>
                                <td>123456</td>
                                <td>18179160021</td>
                                <td>
                                    <img src="images/0.jpg" class="img-circle" width="100px" >
                                </td>
                                <td>
                                    普通用户
                                </td>
                                <td>
                                    <button class="btn btn-danger">
                                        <span class="glyphicon glyphicon-remove"></span>删除
                                    </button>
                                </td>
                                <td>
                                    <button class="btn btn-info" data-toggle="modal" data-target="#updateModal">
                                        <span class="glyphicon glyphicon-heart"></span>修改
                                    </button>
                                </td>
                            </tr>
                        </table>
                    </div>
                    <div id="userPage">
                        <ul class="pagination">
                            <li><a href="#">&laquo;</a></li>
                            <li><a href="#">1</a></li>
                            <li><a href="#">2</a></li>
                            <li><a href="#">3</a></li>
                            <li><a href="#">4</a></li>
                            <li><a href="#">&raquo;</a></li>
                        </ul>
                    </div>

                </div>

分页相关

UserController层

代码:

public class UesrController {

    @Autowired
    private UserService userService;
    private int nowPage = 1;
    private int pageCount = 5;

    @RequestMapping("/queryAllUser.do")
    public void queryAllUser(User user,HttpServletResponse resp,HttpServletRequest req) throws IOException {
        // -1 上一页   -2 下一页 nowPage
        String pageStr = req.getParameter("nowPage");
//        System.out.println(pageStr);
        int maxPage = getMaxPage(user);
        System.out.println("总页数:" + maxPage);
        if(pageStr != null && !"".equals(pageStr)){
            int temp = Integer.parseInt(pageStr);
            if(temp > 0){ // 点击页码
                nowPage = temp;
            }else { // 点击 上下一页
                if(temp == -1 && nowPage > 1){
                    nowPage--;
                }
                if(temp == -2 && nowPage < maxPage){
                    nowPage++;
                }
            }
        }
        int[] pages = {(nowPage-1)*pageCount,pageCount};
        Map<String,Object> map = new HashMap<>();
        map.put("user",user);
        map.put("pages",pages);
        List<User> list = userService.pageList(map);
        System.out.println(list);
        PageBean<User> pb = new PageBean<>(maxPage,list);
//        List<User> list = userService.queryAllUser(user);
//        System.out.println("后台:" + list);
//        resp.getWriter().write(JSON.toJSONString(list));
        resp.getWriter().write(JSON.toJSONString(pb));
    }
    //最大页数
    public int getMaxPage(User user){
        int dataCount = userService.getDataCount(user);
        int maxPage = dataCount%pageCount==0?dataCount/pageCount:dataCount/pageCount+1;
        return maxPage;
    }
  }

截图详解

条件查询+分页

补充

PageBean实体类

前端ajax

UserController层

以上截图不解的看发的源码

源码看不明白的看截图详解

都不解的评论见…

下一章 批量导入+批量删除+分页查询