分页模糊查询示例代码

//分页模糊查询工具类Page
import java.util.List;

/**
 * 分页
 *
 */
public class Page<T> {

    /**
     * currentPage 当前页
     */
    private int currentPage = 1;
    /**
     * pageSize 每页大小
     */
    private int pageSize = 3;
    /**
     * pageTotal 总页数
     */
    private int pageTotal;
    /**
     * recordTotal 总条数
     */
    private int recordTotal = 0;

    /**
     * content 每页的内容
     */
    private List<T> content;


    public List<T> getContent() {
        return content;
    }

    public void setContent(List<T> content) {
        this.content = content;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

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

    public int getPageTotal() {

        pageTotal = ((recordTotal%pageSize)==0)?(recordTotal/pageSize):((recordTotal/pageSize)+1) ;
        if(pageTotal==0){
            pageTotal=1;
        }
        return pageTotal;
    }

//    public void setPageTotal(int pageTotal) {
//        this.pageTotal = pageTotal;
//    }

    public int getRecordTotal() {
        return recordTotal;
    }

    public void setRecordTotal(int recordTotal) {
        this.recordTotal = recordTotal;
    }



    @Override
    public String toString() {
        return "Page{" +
                "currentPage=" + currentPage +
                ", pageSize=" + pageSize +
                ", pageTotal=" + pageTotal +
                ", recordTotal=" + recordTotal +
                ", content=" + content +
                '}';
    }
}

//分页模糊查询JSP表单搜索
<form class="left-top" action="/login" method="post">
    <input value="getuser" name="method" hidden>
    姓&nbsp;&nbsp;&nbsp;名：<input type="text" id="name" name="uname" value="${name}">
    用户名：<input type="text" id="username" name="uusername" value="${username}">
    性&nbsp;&nbsp;&nbsp;别：<input type="radio" class="sex" name="usex" value="男"
           <c:if test="${sex=='男'}">checked</c:if>>男
    <input type="radio" class="sex" name="usex" value="女"
           <c:if test="${sex=='女'}">checked</c:if>>女&nbsp;&nbsp;&nbsp;
    角&nbsp;&nbsp;&nbsp;色：<select id="rid" name="urid">
    <option value="0">--请选择--</option>
    <c:forEach var="role" items="${rlist}">
    <option value="${role.id}"
            <c:if test="${rid==role.id}">selected</c:if>
    >--${role.name}--</option>
    </c:forEach>
    <input type="submit" value="查询">
</form>

//分页模糊查询显示列表部分
<table>
    <tr>
        <th>编号</th>
        <th>姓名</th>
        <th>用户名</th>
        <th>密码</th>
        <th>性别</th>
        <th>年龄</th>
        <th>爱好</th>
        <th>角色</th>
        <th>操作</th>
    </tr>
    <tbody>
    <c:forEach var="user" items="${page.content}">
        <tr>
            <td>${user.id}</td>
            <td>${user.name}</td>
            <td>${user.username}</td>
            <td>${user.password}</td>
            <td>${user.sex}</td>
            <td>${user.age}</td>
            <td>${user.hobby}</td>
            <td>${user.role.name}</td>
            <td>
                <button onclick="upuser(${user.id})" class="fancy-button">修改</button>
                <button onclick="deluser(${user.id})" class="fancy-button">删除</button>
            </td>
        </tr>
    </c:forEach>
    </tbody>
</table>
<span class="bottom">
<button onclick="sw(1)">首页</button>
<button onclick="left(${page.currentPage})">上一页</button>
第${page.currentPage}页/共${page.pageTotal}页
<button onclick="right(${page.currentPage},${page.pageTotal})">下一页</button>
<button onclick="sw(${page.pageTotal})">尾页</button>
共${page.recordTotal}条
</span>

//分页模糊查询换页部分
var uname=document.getElementById("name").value;
var uusername=document.getElementById("username").value;
var urid=document.getElementById("rid").value;
var sex=document.getElementsByClassName("sex");
var usex="";
for(var i=0;i<sex.length;i++){
    if (sex[i].checked==true){
        usex=sex[i].value;
    }
}


function left(pagesize) {
    if (pagesize=="1"){
        alert("已经到达第一页！");
    }else{
        location.href = "/login?method=getuser&pagesize="+(pagesize-1)+"&uname="+uname
            +"&uusername="+uusername+"&usex="+usex+"&urid="+urid;
    }
}
function right(pagesize,size) {
    if (pagesize==size){
        alert("已经到达最后一页！");
    }else{
        location.href = "/login?method=getuser&pagesize="+(pagesize+1)+"&uname="+uname
        +"&uusername="+uusername+"&usex="+usex+"&urid="+urid;
    }
}
function sw(pagesize) {
    location.href = "/login?method=getuser&pagesize="+pagesize+"&uname="+uname
        +"&uusername="+uusername+"&usex="+usex+"&urid="+urid;
}

//分页模糊查询servlet处理数据
private void getuser(HttpServletRequest req, HttpServletResponse resp) {
    Page page = new Page();
    String pagesize = req.getParameter("pagesize")==null?"1":req.getParameter("pagesize");
    page.setCurrentPage(Integer.parseInt(pagesize));
    String name = req.getParameter("uname")==null?"":req.getParameter("uname");
    String username = req.getParameter("uusername")==null?"":req.getParameter("uusername");
    String sex = req.getParameter("usex")==null?"":req.getParameter("usex");
    String rid = req.getParameter("urid")==null?"0":req.getParameter("urid");
    List<User> ulist=userService.getusers(name,username,sex,rid,page);
    page.setContent(ulist);
    int usum=userService.getusersum(name,username,sex,rid);
    page.setRecordTotal(usum);
    List<Role> rlist=userService.getrole();
    req.setAttribute("page",page);
    req.setAttribute("rlist",rlist);
    req.setAttribute("name",name);
    req.setAttribute("username",username);
    req.setAttribute("sex",sex);
    req.setAttribute("rid",rid);
    try {
        req.getRequestDispatcher("/jsp/user.jsp").forward(req,resp);
    } catch (ServletException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

//分页模糊查询DAO层查询各页数据
public List<User> getusers(String name, String username, String sex, String rid, Page page) {
    Connection conn = null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    List<User> list = new ArrayList<>();
    try {
        conn = JDBCUtil.getConnection();
        String sql = "select u.*,r.id rid,r.name rname from user u left join user_role ur on u.id=ur.uid left join role r on ur.rid=r.id where 1=1";

        if(name!=null && !name.equals("")){
            sql+=" and u.name like '%"+name+"%'";
        }
        if(username!=null && !username.equals("")){
            sql+=" and u.username like '%"+username+"%'";
        }
        if(sex!=null && !sex.equals("")){
            sql+=" and u.sex like '%"+sex+"%'";
        }
        if(!rid.equals("0")){
            sql+=" and r.id="+rid;
        }
        sql+=" limit ?,?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1,(page.getCurrentPage()-1)*page.getPageSize());
        ps.setInt(2,page.getPageSize());
        rs = ps.executeQuery();
        while (rs.next()){
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setSex(rs.getString("sex"));
            user.setAge(rs.getInt("age"));
            user.setHobby(rs.getString("hobby"));
            Role role = new Role();
            role.setId(rs.getInt("rid"));
            role.setName(rs.getString("rname"));
            user.setRole(role);
            list.add(user);
        }
    }  catch (SQLException e) {
        e.printStackTrace();
    }finally {
        //关流
        JDBCUtil.close(conn,ps,rs);
    }
    return list;
}

//分页模糊查询DAO层查询符合条件数
public int getusersum(String name, String username, String sex, String rid) {
    Connection conn = null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    List<User> list = new ArrayList<>();
    try {
        conn = JDBCUtil.getConnection();
        String sql = "select count(*) usum from user u left join user_role ur on u.id=ur.uid left join role r on ur.rid=r.id where 1=1";

        if(name!=null && !name.equals("")){
            sql+=" and u.name like '%"+name+"%'";
        }
        if(username!=null && !username.equals("")){
            sql+=" and u.username like '%"+username+"%'";
        }
        if(sex!=null && !sex.equals("")){
            sql+=" and u.sex like '%"+sex+"%'";
        }
        if(!rid.equals("0")){
            sql+=" and r.id="+rid;
        }
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()){
           return rs.getInt("usum");
        }
    }  catch (SQLException e) {
        e.printStackTrace();
    }finally {
        //关流
        JDBCUtil.close(conn,ps,rs);
    }
    return 0;
}