使用jsp与servlet作表格分页

package com.lxn.page; /** * @author ：lin xiangnan * @date ：2014年8月5日 下午5:12:12 * @function： */ import java.sql.*; import java.util.ArrayList; public class Mysql { private static String driver ="com.mysql.jdbc.Driver"; private static String url = "jdbc:mysql://localhost:3306/mydb"; private static String mysql_name = "root"; private static String mysql_password = "lxn1992"; private static Connection connect=null; private static Statement stmt=null; private static PreparedStatement pstmt=null; String tableName=""; int start=1; int end=0; int num=0; int page=0; public int getPage() { return page; } public void setPage(int page) { this.page = page; } public int getNum() { return num; } public void setNum(int num) { this.num = num; } public int getStart() { return start; } public void setStart(int start) { this.start = start; } public int getEnd() { return end; } public void setEnd(int end) { this.end = end; } String result=""; String result2=""; public Mysql() throws SQLException { // TODO Auto-generated constructor stub initSql(); } //加载MYSQL JDBC驱动程序 ------------------------------------------------------------------------------------------------ private static void initSql() throws SQLException { // TODO Auto-generated method stub try { Class.forName(driver); //加载MYSQL JDBC驱动程序 // Class.forName("org.gjt.mm.mysql.Driver"); // System.out.println("Success loading Mysql Driver!"); } catch (Exception e) { System.out.print("Error loading Mysql Driver!"); e.printStackTrace(); } } //连接数据库(自动方式)----------------------------------------------------------------------------------------------------------------- private void connectSql() throws SQLException{ try { connect = DriverManager.getConnection( url,mysql_name,mysql_password); //连接URL为 jdbc:mysql//服务器地址/数据库名 ，后面的2个参数分别是登陆用户名和密码 //System.out.println("连接数据库"); } catch (Exception e) { System.out.print("get data error!"); e.printStackTrace(); } } //断开数据库连接------------------------------------------------------------------------------------------------------------ private void notConnectSql(Connection connect) throws SQLException{ connect.close(); //System.out.println("关闭数据库连接"); } //获得数据库某一表的的用户数量 public int getUserNum(String table_name) throws SQLException { // TODO Auto-generated method stub this.connectSql(); stmt=connect.createStatement(); ResultSet rs= stmt.executeQuery("select username from "+table_name); int num=0; while(rs.next()){ num++; } rs.close(); stmt.close(); this.notConnectSql(connect); return num; } //输出输出数据库的某几行数据 public void useForDisplay(String table_name,int page_start,int page_end,int num,int page) throws SQLException{ UserBean user=new UserBean(); this.tableName=table_name; this.start=page_start; this.end=page_end; this.num=num; this.page=page; } /* //用于ArrayList返回 public ArrayList page_select() throws SQLException{ this.connectSql(); int page_num=end-start+1; stmt=connect.createStatement(); UserBean user=new UserBean(); ArrayList<Comparable> list1=new ArrayList(); //System.out.println("测试是否可以输出："+page_start+"与"+page_num); ResultSet rs=stmt.executeQuery("select * from "+tableName+" order by 'ID' asc limit "+(start-1)+","+page_num); while(rs.next()){ //userlist.add(0,rs.getInt("id")); list1.add(0,rs.getString("username")); list1.add(1,rs.getString("name")); list1.add(2,rs.getString("password")); list1.add(3,rs.getInt("age")); //System.out.println("测试是否可以输出："+rs.getString("username")+"与"+rs.getString("name")); } rs.close(); stmt.close(); this.notConnectSql(connect); return list1; } */ //获得列表名----------------------------------------------------------------------------------------------------------------- public String getTableName() { return tableName; } //创建列表名 public void setTableName(String tableName) { this.tableName = tableName; } //做成result表格 public String getResult() throws SQLException{ this.connectSql(); int page_num=end-start+1; stmt=connect.createStatement(); StringBuffer buffer=new StringBuffer(); //System.out.println("测试是否可以输出："+page_start+"与"+page_num); ResultSet rs=stmt.executeQuery("select * from "+tableName+" order by 'ID' asc limit "+(start-1)+","+page_num); /* while(rs.next()){ //userlist.add(0,rs.getInt("id")); list1.add(0,rs.getString("username")); list1.add(1,rs.getString("name")); list1.add(2,rs.getString("password")); list1.add(3,rs.getInt("age")); //System.out.println("测试是否可以输出："+rs.getString("username")+"与"+rs.getString("name")); //buffer.append(rs.getString("username")); } */ int colNum=rs.getMetaData().getColumnCount();//表格列数 buffer.append("<center>"); buffer.append("<form method='post' action='InformShow'>"); buffer.append("<table border=1 cellpadding=5>"); buffer.append("<tr>"); int i=1; while(true){ String name=rs.getMetaData().getColumnName(i).toString(); buffer.append("<th>"+name+"</th>"); i++; if(i>colNum) break; } //-------------------------测试start---------------------------------------- // buffer.append("<th>"+"操作栏"+"</th>"); //--------------------------测试end---------------------------------------- buffer.append("</tr>"); int j=1; while(rs.next()){ buffer.append("<tr>"); for(i=1;i<=colNum;i++){ buffer.append("<td align='center'>"+rs.getString(i)+"</td>"); } //-------------------------测试start---------------------------------------- // buffer.append("<td>"+"<input type='submit' name='look"+j+"' value='查看'>"+"<input type='submit' name='change"+j+"' value='修改'>"+"<input type='submit' name='delete"+j+"' value='删除'>"+"</td>"); j++; //-------------------------测试end---------------------------------------- buffer.append("</tr>"); } buffer.append("</table>"); //测试分页 buffer.append("<br/>"); //分为几页，显示出来 //System.out.println("num值为"+num); // System.out.println("tableName值为"+tableName); //System.out.println("page值为"+page); if(num!=0){ if(this.getUserNum(tableName)%num!=0){ int m=this.getUserNum(tableName)/num+2; //总页数不大于7 if(m<7){ for(int cco=1;cco<m;cco++){ buffer.append(" <input type='submit' name='page' value='"+cco+"'>"); } } //总页数大于7 else{ //如果当前页小于3 if(page<5) { for(int cco=1;cco<7;cco++){ buffer.append(" <input type='submit' name='page' value='"+cco+"'>"); } buffer.append("……"); } //如果当前页大于m-3 else if(page>m-4){ buffer.append("……"); for(int cco=m-6;cco<m;cco++){ buffer.append(" <input type='submit' name='page' value='"+cco+"'>"); } } else{ buffer.appe