javaweb｜分页查询

在javaweb中，分页查询可以通过jsp来调用java代码实现实时更新查询结果并且展示的功能。

数据控制层ManagerDao.java

import com.css.domain.ManagerBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 管理员数据操作类
 * @author noTalent
 * @since 2018/5/18
 */
public class ManagerDao {

    private final String url = "jdbc:mysql://localhost:3306/xxx?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    private final String username = "xxx";
    private final String password = "xxx";
    private Connection conn = null;
    private ResultSet rs = null;
    private PreparedStatement ps = null;
    private Statement stmt = null;

    /**
     * 连接数据库
     */
    public void getConn() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭数据库连接
     */
    public void closeConn() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 注册管理员账号
     * @param manager
     */
    public void addManager(ManagerBean manager) {
        try {
            getConn();
            String sql = "insert into manager values (?, ?, ?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, manager.getUsername());
            ps.setString(2, manager.getPassword());
            ps.setInt(3, manager.getPermission());
            ps.executeUpdate();
            if (ps != null) {
                ps.close();
            }
            closeConn();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 分页查询
     * @param page
     * @return
     */
    public List<ManagerBean> find(int page, String sql) {
        List<ManagerBean> list = new ArrayList<ManagerBean>();
        /* 分页查询sql语句
        String sql="select * from manager limit ?, ?";
        */
        try {
            getConn();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, (page - 1) * 8);
            ps.setInt(2, 8);
            rs = ps.executeQuery();
            while (rs.next()) {
                ManagerBean manager = new ManagerBean();
                manager.setUsername(rs.getString("username"));
                manager.setPassword(rs.getString("password"));
                manager.setPermission(rs.getInt("permission"));
                list.add(manager);
            }
            rs.close();
            ps.close();
            closeConn();
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询管理员总记录数目
     * @return
     */
    public int findCount(String sql) {
        int count = 0;
        /*
        String sql = "select count(*) from manager";
        */
        try {
            getConn();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                count = rs.getInt(1);
            }
            rs.close();
            closeConn();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 删除管理员操作
     * @param username
     */
    public void delete(String username) {
        String sql = "delete from manager where username=?";
        try {
            getConn();
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.executeUpdate();
            ps.close();
            closeConn();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

在查询代码中，可以通过mysql内置的limit来实现获取某一段数据。limit ?, ? 要放在sql语句的末尾（可以再加查询条件），limit第一个参数是从第几个数据开始获取，第二个参数是获取的数据项数目。所谓分页，就是通过jsp传参获取到具体的页面数来调用数据控制层的查询方法。

例如：https://mp.csdn.net/postedit?page=x

上面的url中，page假设是第几页的参数名，xxx是获取到的第几页对应的参数值，即request.getParameter("condition");

所以我们可以根据给页面跳转传递跳转到的页面来实现分页查询。看下面代码：

<div class="manager-list">
        <table class="tableDecoration">
            <thead>
                <tr>
                    <td>用 户 名</td>
                    <td>密 码</td>
                    <td>管 理 权 限</td>
                    <td>操 作</td>
                </tr>
            </thead>
            <%
                /* 查询 */
                ManagerDao managerDao = new ManagerDao();
                int currPage = 1;
                String sql = null;
                String sqlCount = null;
                /* 获取页面参数 */
                if (request.getParameter("page") != null) {
                    currPage = Integer.parseInt(request.getParameter("page"));
                }
                /* 判断是否有条件参数而更改查询语句 */
                if (request.getParameter("condition") == null) {
                    sql = "select * from manager limit ?, ?";
                    sqlCount = "select count(*) from manager";
                }
                if (request.getParameter("condition") != null) {
                    sql = "select * from manager where username like \"%" + request.getParameter("condition") + "%\" limit ?, ?";
                    sqlCount = "select count(*) from manager where username like \"%" + request.getParameter("condition") + "%\"";
                }
                /* 删除操作 */
                if (request.getParameter("delete") != null) {
                    if (!request.getParameter("delete").equals("")) {
                        String delete = request.getParameter("delete");
                        if (delete != null) {
                            if (!delete.equals("")) {
                                managerDao.delete(delete);
                            }
                        }
                    }
                }
                /* 获取查询结果 */
                List<ManagerBean> list = managerDao.find(currPage, sql);
                int pages;
                int count = managerDao.findCount(sqlCount);
                if (count % 8 == 0) {
                    pages = count / 8;
                }
                else {
                    pages = count / 8 + 1;
                }
                StringBuffer sb = new StringBuffer();
                /* 通过循环构建分页条 */
                for (int i = 1; i <= pages; i++) {
                    if (i == currPage) {
                        sb.append("[" + i + "]");
                    }
                    else if (i != currPage && request.getParameter("condition") != null && !request.getParameter("condition").equals("")) {
                        sb.append("<a href='manager.jsp?condition=" + request.getParameter("condition") + "&page=" + i + "'>" + i + "</a>");
                    }
                    else {
                        sb.append("<a href='manager.jsp?page=" + i + "'>" + i + "</a>");
                    }
                    sb.append("  ");
                }
            %>
            <%
                for(ManagerBean m:list) {
            %>
            <tr class="differTr">
                <td><%=m.getUsername()%></td>
                <td><%=m.getPassword()%></td>
                <td><%=m.getPermission()%></td>
                <%-- jsp方法嵌入java代码参数必须加单引号 --%>
                <td><a id="delete" οnclick="deleteManager('<%=m.getUsername()%>');" href="javascript:void(0)">×</a></td>
            </tr>
            <%
                }
            %>
            <tr>
                <td></td>
                <td></td>
                <td></td>
                <td id="differPage"><%=sb.toString()%></td>
            </tr>
        </table>
    </div>

最后展示的页面如下图所示：