<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@page import="model.Members"%> <%@page import="dao.MembersDao"%> <% int currentPage = 1; if (request.getParameter("page")!=null){ currentPage = Integer.parseInt(request.getParameter("page")); } MembersDao dao = new MembersDao(); List<Members> list = dao.getAllByPage(currentPage, ""); int totalPage = dao.getAllPage(""); %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Title</title> </head> <body> <table border="1"> <tr><td>id</td><td>username</td><td>password</td></tr> <% for (int i = 0; i < list.size(); i++) { Members m = list.get(i); %> <tr><td><%=m.getId()%></td><td><%=m.getUsername()%></td><td><%=m.getPassword()%></td></tr> <% } %> <tr><td colspan="3"> <% if (currentPage==1) { %> 上一页 <% }else {%> <a href="index.jsp?page=<%=currentPage-1%>">上一页</a> <% } %> | <% if (currentPage==totalPage) { %> 下一页 <% }else {%> <a href="index.jsp?page=<%=currentPage+1%>">下一页</a> <% } %> </td></tr> </table> </body> </html>
package dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DbFactory { private Connection conn = null; private void getConnection() { try { Class.forName("net.sourceforge.jtds.jdbc.Driver"); conn = DriverManager.getConnection( "jdbc:jtds:sqlserver://192.168.1.31:1433/test", "sa", ""); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } public void execSqlWithoutResult(String sql, Object[] params) { try { if (conn == null) getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql); for (int i = 0; i < params.length; i++) { pstmt.setObject(i + 1, params[i]); } pstmt.execute(); } catch (SQLException e) { e.printStackTrace(); } } public ResultSet execSqlWithResult(String sql, Object[] params) { ResultSet rs = null; try { if (conn == null) getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql); for (int i = 0; i < params.length; i++) { pstmt.setObject(i + 1, params[i]); } rs = pstmt.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return rs; } public void close() { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
package dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import model.Members; public class MembersDao { private DbFactory df = new DbFactory(); public void insert(Members member) { String sql = "insert into members values(?,?)"; Object[] params = new Object[] { member.getUsername(), member.getPassword() }; df.execSqlWithoutResult(sql, params); } public void delete(int id) { String sql = "delete from members where id=?"; Object[] params = new Object[] { id }; df.execSqlWithoutResult(sql, params); } public void update(Members member) { String sql = "update members set password=? where id=?"; Object[] params = new Object[] { member.getPassword(), member.getId() }; df.execSqlWithoutResult(sql, params); } public Members getById(int id) { Members member = null; try { String sql = "Select * from members where id=?"; Object[] params = new Object[] { id }; ResultSet rs = df.execSqlWithResult(sql, params); while (rs.next()) { member = new Members(); member.setId(rs.getInt("id")); member.setUsername(rs.getString("username")); member.setPassword(rs.getString("password")); } } catch (SQLException e) { e.printStackTrace(); } return member; } public List<Members> getAll() { List<Members> list = new ArrayList<Members>(); try { String sql = "Select * from members"; Object[] params = new Object[] {}; ResultSet rs = df.execSqlWithResult(sql, params); while (rs.next()) { Members m = new Members(); m.setId(rs.getInt("id")); m.setUsername(rs.getString("username")); m.setPassword(rs.getString("password")); list.add(m); } } catch (SQLException e) { e.printStackTrace(); } return list; } public List<Members> getAllByPage(int page, String condition) { int pageSize = 4; List<Members> list = new ArrayList<Members>(); try { int startId = (page - 1) * pageSize + 1, endId = page * pageSize; String sql = "select * from (select *,row_number() over(order by id) as rowid from members where username like ?) a " + "where rowid>=" + startId + " and rowid<=" + endId; Object[] params = new Object[] { "%" + condition + "%" }; ResultSet rs = df.execSqlWithResult(sql, params); while (rs.next()) { Members m = new Members(); m.setId(rs.getInt("id")); m.setUsername(rs.getString("username")); m.setPassword(rs.getString("password")); list.add(m); } } catch (SQLException e) { e.printStackTrace(); } return list; } public int getAllPage(String condition) { int totalPage = 0, pageSize = 4; try { String sql = "select (count(*)-1)/" + pageSize + "+1 totalPage from members where username like ?"; Object[] params = new Object[] { "%" + condition + "%" }; ResultSet rs = df.execSqlWithResult(sql, params); while (rs.next()) { totalPage = rs.getInt("totalPage"); } } catch (SQLException e) { e.printStackTrace(); } return totalPage; } }
package model; public class Members { private int id; private String username; private String password; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } }
856
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
