import com.util.Pager; import com.util.User; import java.sql.*; import java.util.ArrayList; import java.util.List; public class UserDAO { private Connection conn = null; private ResultSet rs = null; private PreparedStatement ps = null; // public static void main(String[] args) { // UserDAO dao = new UserDAO(); // dao.GetUsers("",15,1); // dao.close(); // } public UserDAO() { String driverName = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/fenyedemo"; String user = "root";String password = "123456"; try { Class.forName(driverName); conn = DriverManager.getConnection(url,user,password); } catch (ClassNotFoundException e) { System.out.println("没有发现驱动"); e.printStackTrace(); } catch (SQLException e) { System.out.println("获取连接失败"); e.printStackTrace(); } } /** * 具体分页实现代码 * @param name 查询条件 * @return */ public Pager GetUsers(String name){ //获取分页参数 int pagesize = SystemContext.getPageSize(); int pageIndex = SystemContext.getPageIndex(); //分页具体sql语句 String sql = "select * from user "; String sqlCount = "select count(*) from user "; if (name!=null && !name.trim().equals("")){ sql += "where username LIKE %"+name+"%"; sqlCount += "where username LIKE %"+name+"%"; } sql += " LIMIT ?,?"; //存放当前页的集合 List<User> datas = new ArrayList<>(); //存放当前分页的集合 Pager<User> pages = new Pager<>(); User userTemp = null; try { ps = conn.prepareStatement(sql); if(pageIndex<=0) pageIndex=1; //设置参数 ps.setInt(1,(pageIndex-1)*pagesize); ps.setInt(2,pagesize); rs = ps.executeQuery(); //循环取出,添加到datas中 while (rs.next()){ userTemp = new User(); userTemp.setUserId(rs.getString("id")); userTemp.setUsername(rs.getString("username")); userTemp.setPassword(rs.getString("password")); userTemp.setRegdate(rs.getTimestamp("regdate")); datas.add(userTemp); } //最后设置pages pages.setPageIndex(pageIndex); pages.setPageSize(pagesize); ps = conn.prepareStatement(sqlCount); rs = ps.executeQuery(); while(rs.next()){ pages.setTotalRecords(rs.getInt(1)); pages.setTotalPages((rs.getInt(1)-1)/pagesize+1); } pages.setDatas(datas); } catch (SQLException e) { System.out.println("获取出错"); e.printStackTrace(); } return pages; } public void close(){ try { if (rs!=null) rs.close(); rs = null; if (ps!=null) ps.close(); ps = null; if (conn!=null) conn.close(); conn = null; } catch (SQLException e) { System.out.println("关闭失败"); e.printStackTrace(); } } }
五.jsp测试页面
普通页面就是显示数据,这个很简单,代码如下
<%@ page import="com.dao.UserDAO" %> <%@ page import="com.util.Pager" %> <%@ page import="com.util.User" %> <%@ page import="java.util.Iterator" %> <%@ page contentType="text/html;charset=UTF-8" language="java" %> <% String condition = request.getParameter("condition"); UserDAO userDAO = new UserDAO(); Pager<User> pages = null; if (condition!=null && !condition.trim().equals("")){ pages = userDAO.GetUsers(condition); }else { pages = userDAO.GetUsers(null); } userDAO.close(); %> <html> <head> <title>测试用例</title> </head> <body> <h1>分页测试</h1> <table> <tr> <td colspan="100%"> <form method="get" action="index.jsp"> <input type="text"> <input type="submit" value="查询"> </form> </td> </tr> <tr> <th>ID</th> <th>USERNAME</th> <th>PASSWORD</th> <th>DATA</th> </tr> <% for (Iterator it = pages.getDatas().iterator(); it.hasNext() ; ) { User userTemp = (User) it.next(); %> <tr> <td><%=userTemp.getUserId()%></td> <td><%=userTemp.getUsername()%></td> <td><%=userTemp.getPassword()%></td> <td><%=userTemp.getRegdate()%></td> </tr> <% }%> </table> </body> </html>
此时已经有一些效果了
六.JSP页面添加控制选项
添加控制选项这里使用分页框架pager-taglib,也是为了更好的支持通用性.
首先在index.jsp页面查询之后静态引入一个新的页面,作为底部控制页面
使用方法,就是去下载相应的jar,然后引入到项目的lib中即可
