SSM 根据学号姓名性别模糊查询+实现分页的功能实现实现分页的sql语句

SSM 根据学号姓名性别模糊查询+实现分页的功能实现

实现：可以根据多个查询框中的输入值灵活查询。

查询返回所需内容的分页，点击分页跳转，并且无分页无数据bug

Mybatis语句

    <select id="findAll"  resultType="com.javen.model.Student" parameterType="com.javen.model.Student" >
        select student.student_id,student.student_name,student.student_sex,student.student_time,class_name,class_grade
        from student
        LEFT JOIN class on student.student_class = class.class_id
        <where>
            <if test="student_id != null and student_id != ''">
                <bind name="pattern" value="'%' + _parameter.student_id + '%'" />
                and student_id LIKE #{pattern}
            </if>
            <if test="student_name != null and student_name != ''">
                <bind name="pattern1" value="'%' + _parameter.student_name + '%'" />
                and student_name LIKE #{pattern1}
            </if>
            <if test="student_sex != null and student_sex != ''">
                <bind name="pattern2" value="'%' + _parameter.student_sex + '%'" />
                and student_sex LIKE #{pattern2}
            </if>
        </where>
        limit #{page},#{pagen}
    </select>

1、使用 判断获取的 student_id,student_name,student_sex是否为空，即输入框中的值，如果为空，则查询所有，进入页面默认所有输入框都为空，查询所有，输入模糊查询内容后，点击查询，获得想要的数据

2、使用 limit #{page},#{pagen}，page和pagen写入javabean中，类型为Integer，page是获取点击分页的value值。

3、分页生成需要，需要写 获取count语句，如下

    <select id="counts" resultType="java.lang.Integer" parameterType="com.javen.model.Student">
        select count(*) from student
        <where>
            <if test="student_id != null and student_id != ''">
                <bind name="pattern3" value="'%' + _parameter.student_id + '%'" />
                and student_id LIKE #{pattern3}
            </if>
            <if test="student_name != null and student_name != ''">
                <bind name="pattern4" value="'%' + _parameter.student_name + '%'" />
                and student_name LIKE #{pattern4}
            </if>
            <if test="student_sex != null and student_sex != ''">
                <bind name="pattern5" value="'%' + _parameter.student_sex + '%'" />
                and student_sex LIKE #{pattern5}
            </if>
        </where>
    </select>

点击查询后也要返回所需要内容的条数，所以也要写

4、将所有需要的值写入javabean，parameterType都为此javabean

5、html部分的js写法

    function get(page){
        var student_name=$("#student_name");
        var student_sex=$("#student_sex");
        var student_id=$("#student_id");
        if(page==null){
           var page=0;
        }else{
            var page=(page-1)*3
        }
        var pagen=3;
        console.log(page+"    "+pagen)
        $.ajax({
            method:"get",
            url:"http://localhost:8080/train0301zyk/student/findAll",
            data:{
                student_name:student_name.val(),
                student_sex:student_sex.val(),
                student_id:student_id.val(),
                page:page,
                pagen:pagen
            },
            success:function (data){
                console.log(data);
                $("#tbody").empty();
                for (let i = 0; i < data.length; i++) {
                    $('#tbody').append("<tr>"+
                        "<td><input οnchange='checkAllreverse()' class='checkbox1' value='"+data[i].student_id+"' type='checkbox'/></td>"+
                        "<td>"+data[i].student_id+"</td>"+
                        "<td>"+data[i].student_name+"</td>"+
                        "<td>"+data[i].student_sex+"</td>"+
                        "<td>"+data[i].student_time+"</td>"+
                        "<td>"+data[i].class_name+"</td>"+
                        "<td>"+data[i].class_grade+"</td>"+
                        "<td><button οnclick='del("+data[i].student_id+")'>删除</button> &nbsp;&nbsp; <button><a οnclick=openedit("+data[i].student_id+")>修改</a></button></td>"+
                        "</tr>");
                }
                loadPage()
            }
        })
    }


    function loadPage(){
        var student_name=$("#student_name");
        var student_sex=$("#student_sex");
        var student_id=$("#student_id");
        $.ajax({
            url:"http://localhost:8080/train0301zyk/student/counts",
            type:"get",
            data:{
                student_name:student_name.val(),
                student_sex:student_sex.val(),
                student_id:student_id.val()
            },
            success:function(data){
                var count = data
                $(".pagenation").empty();
                for (var i=0,j=1;i<Math.ceil(count/pagesize);i++,j++){
                    if(i==0){
                        $(".pagenation").append("<li class='current' οnclick='get("+j+")'>"+j+"</li>");
                    }else{
                        $(".pagenation").append("<li οnclick='get("+j+")'>"+j+"</li>");
                    }

                }

            }
        })
    }

6、最终可以实现：查询返回所需内容的分页，点击分页跳转，并且无分页无数据bug