- A+
所属分类:程序设计
数据库采用mysql,数据库建库数据库代码没有提供,请自行添加。在数据库中添加user表,3行字段即可。
该代码可作为通用分页组件使用。
第一个文件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>
沙发
博主很强大啊,呵呵,学到了
1层
@苗苗 谢谢你的支持,欢迎你下次光临本博