第一个文件PagedbClass.java
| 代码如下 | 复制代码 |
| package com.kanba.tools; import java.sql.*; import com.kanba.connection.Conn; public class PagedbClass { Connection con = null; Statement stmt = null; ResultSet rs = null; ResultSetMetaData resultsMeta =null; int rows = 0; public PagedbClass() { con = new Conn().getConnection(); } public ResultSet executeQuery(String sql) throws SQLException{ ResultSet rs = null; try{ stmt = con.createStatement(); rs = stmt.executeQuery(sql); while(rs.next()) this.rows ++; rs = stmt.executeQuery(sql); } catch (SQLException e){ System.out.print("Query:"+e.getMessage()); } this.rs = rs; return rs; } public boolean executeUpdate(String sql){ try{ stmt = con.createStatement(); stmt.executeUpdate(sql); return true; } catch(SQLException e){ System.out.print("Update:"+e.getMessage()); return false; } } public int getColumns(){ int columns = 0; try{ this.resultsMeta = this.rs.getMetaData(); columns = this.resultsMeta.getColumnCount(); } catch (SQLException e) {} return columns; } public int getRows(){ return this.rows; } public void closedb(){ try{ con.close(); }catch(SQLException e){ e.printStackTrace(); } } } |
|
第二个文件分页通用类 PageQuery.java
| 代码如下 | 复制代码 |
|
package com.kanba.tools; import java.sql.*; import javax.servlet.http.*; public class PageQuery { int Offset; // 记录偏移量 int Total; // 记录总数 int MaxLine; // 记录每页显示记录数 ResultSet rs; // 读出的结果 int TPages; // 总页数 int CPages; // 当前页数 String PageQuery; // 分页显示要传递的参数 String Query; // query 语句 String QueryPart; // " FROM " 以后的 query 部分 String FilePath; PagedbClass db; // object of dbclass //constructer do nothing public PageQuery() { // 每页显示十行 MaxLine = 30; db = new PagedbClass(); } //********读取记录*************** // 主要工作函数，根据所给的条件从表中读取相应的记录 public ResultSet myQuery(String query, HttpServletRequest req) throws SQLException { String query_part, os; int begin, offset; // 截取 " FROM " 以后的 query 语句 begin = query.indexOf(" FROM "); query_part = query.substring(begin, query.length()).trim(); // 计算偏移量 os = req.getParameter("offset"); if (os == null) Offset = 0; else Offset = Integer.parseInt(os); // 获取文件名 FilePath = req.getRequestURI(); Query = query; QueryPart = query_part; // 计算总的记录条数 String SQL = "SELECT Count(*) AS total " + this.QueryPart; rs = db.executeQuery(SQL); if (rs.next()) Total = rs.getInt(1); // 设置当前页数和总页数 TPages = (int)Math.ceil((double)this.Total/this.MaxLine); CPages = (int)Math.floor((double)Offset/this.MaxLine+1); // 根据条件判断，取出所需记录 if (Total > 0) { SQL = Query + " LIMIT " + Offset + " , " + MaxLine; rs = db.executeQuery(SQL); } return rs; } public void close(){ db.closedb(); } // 显示总页数 public int getTotalPages() { return TPages; } //显示当前所在页数 public int getCurrenPages() { return CPages; } //**********显示翻页提示栏************* // 显示首页、下页、上页、尾页 public String PageLegend() { String str = ""; int first, next, prev, last; first = 0; next = Offset + MaxLine; prev = Offset - MaxLine; last = (this.TPages - 1) * MaxLine; if(Offset >= MaxLine) str += " <A href=" + FilePath + "?offset=" + first + ">首页</A> "; else str += " 首页 "; if(prev >= 0) str += " <A href=" + FilePath + "?offset=" + prev + ">前页</A> "; else str += " 前页 "; if(next < Total) str += " <A href=" + FilePath + "?offset=" + next + ">后页</A> "; else str += " 后页 "; if(TPages != 0 && CPages < TPages) str += " <A href=" + FilePath + "?offset=" + last + ">尾页</A>"; else str += " 尾页 "; str += " 页次：" + getCurrenPages() + "/" + getTotalPages() + "页 "; str += MaxLine + "条/页 " + "共" + Total + "条"; String pageNum; return str; } } |
|
数据库连接文件 Conn.java
| 代码如下 | 复制代码 |
|
package com.kanba.connection import java.sql.*; public class Conn { public Conn(){} //这个是连接数据库的代码，也可以通过连接池等技术实现。 String driverClass="org.gjt.mm.mysql.Driver"; String url = "jdbc:mysql://localhost/test?user=root&password=0&useUnicode=true&characterEncoding=gb2312"; String username="root"; String password="pwd"; Connection conn=null; //初始化 public Connection getConnection() { try { Class.forName(driverClass); //加载驱动 // System.out.println("jiazaichenggong"); } catch (ClassNotFoundException e) { e.printStackTrace(); } try { conn=DriverManager.getConnection(url); //建立连接conn } catch (SQLException e) { e.printStackTrace(); } return conn; } } 分页测试文件 page.jsp <%@ page language="java" import="java.sql.*, com.kanba.tools.*" %> <%@ page contentType="text/html; charset=gb2312" %> <jsp:useBean id="pq" scope="page" class="com.kanba.tools.PageQuery" /> <html> <body bgcolor="#8BA9C9"> <table bgcolor="#fecda9" cellspacing=0> <% String query = "SELECT * FROM users"; // 注意这个" FROM "一定要大写，程序中需要靠这个FROM后的query语句判断总行数。 ResultSet rs = pq.myQuery(query, request); String bar = pq.PageLegend(); //读取分页提示栏 out.println("<tr><td colspan=2>"+bar+"</td></tr>"); out.println("<tr><td colspan=2><hr size=1 color=blue></td></tr>"); while (rs.next()) { //说明：rs.getString(2)等是列名相对应的位置，不要用rs.getString("colmn_name")这种形式 %> <tr><td><%=rs.getString(1)%></td><td><%=rs.getString(2)%></td></tr> <% } rs.close;pq.close(); %> </table> </body> </html> |
|
时间： 2024-09-29 04:53:29