java+mysql+jsp分页处理(数据源)

 1.java代码Page.java

 /**
 *
 */
package com.bean;

import java.util.Vector;

import com.dao.QueryHelpDao;

/**
 * @author Admin
 *
 */
public class Page {

 private int curPage;// 当前的几页

 private int maxPage;// 共有多少页

 private int maxRowCount; // 共有多少行

 private int rowsPerPage;// 每一页有多少行，默认为10行

 private Vector vector = new Vector();// 用来存放最后的查询结果

 private String countSql = "";// 统计总的查询结果数目的sql

 private String selectSql = "";// 查询SQL语句

 private String formName = "item"; // 表单的名称

 public String getCountSql() {
  return countSql;
 }

 public void setCountSql(String countSql) {
  this.countSql = countSql;
 }

 public int getCurPage() {
  return curPage;
 }

 public void setCurPage(int curPage) {
  this.curPage = curPage;
 }

 public String getFormName() {
  return formName;
 }

 public void setFormName(String formName) {
  this.formName = formName;
 }

 public int getMaxPage() {
  return maxPage;
 }

 /**
  * 根据总行数计算总共有多少页
  *
  * @param maxPage
  */
 public void setMaxPage() {
  this.maxPage = (this.maxRowCount % this.rowsPerPage == 0) ? this.maxRowCount
    / this.rowsPerPage
    : this.maxRowCount / this.rowsPerPage + 1;
 }

 public int getMaxRowCount() {

  return maxRowCount;
 }

 /**
  * 返回查询结果的总记录数
  *
  * @param maxRowCount
  */
 public void setMaxRowCount() {
  // System.out.println();
  this.maxRowCount = QueryHelpDao.getInt(this.getCountSql());
 }

 public int getRowsPerPage() {
  return rowsPerPage;
 }

 public void setRowsPerPage(int rowsPerPage) {
  this.rowsPerPage = rowsPerPage;
 }

 public String getSelectSql() {
  return selectSql;
 }

 public void setSelectSql(String selectSql) {
  this.selectSql = selectSql;
 }

 public Vector getResult() {
  setMaxRowCount();// 总结果数
  setMaxPage(); // 总的页数
  String objectSql = getSelectSql() + " limit " + (getCurPage() - 1)*getRowsPerPage() + " , " + getCurPage() * getRowsPerPage();
  System.out.println("objectSql = " + objectSql);
  vector = QueryHelpDao.getHelp(objectSql);
  return vector;
 }
}

 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

QueryHelpDao.java

   package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Vector;

import com.util.DataSourceConn;

/**
 * @author Admin
 *
 */
public class QueryHelpDao {
 private static Connection conn ;
 private static Statement state ;
 private static ResultSet rs ;
 private static PreparedStatement pstmt ;
    /**
     * 传入一个查询将结果放入Vector中
     * @param querySql
     * @return
     */
 public static Vector getHelp(String querySql) {
  
  Vector vector = new Vector();
  conn = DataSourceConn.getConnection();
  try {
   conn.setReadOnly(true);
   pstmt = conn.prepareStatement(querySql);
   rs = pstmt.executeQuery();
   ResultSetMetaData rsmd = rs.getMetaData();
   int i = rsmd.getColumnCount();

   String as[] = new String[i];

   for (int j = 1; j <= i; j++) {
    as[j - 1] = (rsmd.getColumnName(j)).toUpperCase();
   }

   Hashtable hashtable = null;
   for (; rs.next(); vector.addElement(hashtable)) {
    hashtable = new Hashtable();
    for (int k = 1; k <= i; k++) {
     Object obj = rs.getObject(k);
     String s = as[k - 1];
     if(obj!=null) {
      hashtable.put(s, obj) ;
     } else {
      hashtable.put(s, "") ;
     }
    }
   }

  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   try {
    if (rs != null)
     rs.close();
   } catch (SQLException e) {
      e.printStackTrace() ;
   }
   try {
    if (pstmt != null)
     pstmt.close();
   } catch (SQLException e) {
    e.printStackTrace() ;
   }
   try {
    if (conn != null)
     conn.close();
   } catch (SQLException e) {
      e.printStackTrace() ;
   }
  }
  return vector ;
 }
 /**
  * 本方法的使用应该注意，只能使用统计函数count(*)例如：
  * select count(*) from forumpost where forumid=1;
  * @param querySql
  * @return
  */
 public static int getInt(String querySql) {
  int temp = 0;
     conn = DataSourceConn.getConnection();
  try{
   pstmt = conn.prepareStatement(querySql);
   rs = pstmt.executeQuery();
   while(rs.next()){
    temp = rs.getInt(1);
   }
  }catch(SQLException e){
   System.out.println("出现异常：/n"+"QueryHelp.getInt("+querySql+")/n");
   System.out.println("下面是详细信息：/n");
   e.printStackTrace();
  }finally{
   try {
    if (rs != null)
     rs.close();
   } catch (SQLException e) {
   }
   try {
    if (pstmt != null)
     pstmt.close();
   } catch (SQLException e) {
   }
   try {
    if (conn != null)
     conn.close();
   } catch (SQLException e) {
   }
  }
  
  return temp;
 } 
}
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

2.JSP代码page.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="com.bean.*"%>
<%@ page import="com.dao.*"%>
<jsp:useBean id="pages" scope="page" class="com.bean.Page" />
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
 <head>
  <title>分页测试</title>

  <meta http-equiv="pragma" content="no-cache">
  <meta http-equiv="cache-control" content="no-cache">
  <meta http-equiv="expires" content="0">
  <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
  <meta http-equiv="description" content="This is my page">
  <!--
 <link rel="stylesheet" type="text/css" href="styles.css">
 -->
  <script language="javascript">
        function jumping() {
            document.item.submit() ;
            return ;
     }
        function goPage(pagenum) {
     document.item.jumpPage.value=pagenum;
     document.item.submit() ;
     return ;
     }
   </script>
 </head>

 <%
  PageInfroDao pageInfro = new PageInfroDao();
  String countSql = "select count(*) from forumpost ";
  String jumpPage = (request.getParameter("jumpPage") == null) ? "1" : request.getParameter("jumpPage");  
  System.out.println("jumpPage = " + jumpPage);
  String objSql = "SELECT * FROM forumpost ";
  System.out.println("objSql = " + objSql);

  //SimplePageDb2 sp = new SimplePageDb2();
  pages.setRowsPerPage(4); //设置每页显示多少行记录
  pages.setCurPage(Integer.parseInt(jumpPage)); //设置要跳转到哪一页
  pages.setCountSql(countSql); //设置统计所有记录说的sql
  pages.setSelectSql(objSql); //设置要查询的sql
  pages.setFormName("item"); //设置form表单的名字
  java.util.Vector vector = pages.getResult();
  /*
   //Page pages = new Page() ;
   pages.setRowsPerPage(2) ; //设置每页显示多少行记录
   pages.setCurPage(Integer.parseInt(jumpPage)) ;//设置要跳转到那一页
   pages.setCountSql(countSql) ;//设置统计的总信息条数
   pages.setSelectSql(objSql) ;//设置要查询的sql
   */
  //java.util.Vector vector = pages.getResult() ;//将查询结果放到Vector中
 %>
 <body>
  <form action="page.jsp" method="post" name="item">
   <table border="1" bordercolor="blue">
    <tr>
     <td>
      顺号
     </td>
     <td>
      帖子标题
     </td>
     <td>
      发言人
     </td>
    </tr>
    <%
       int start = (Integer.parseInt(jumpPage) - 1)
       * pages.getRowsPerPage() + 1;
     String PostName = "";
     String SpokesMan = "";
     //out.println("vector="+vector);
     //out.println("vector.size()="+vector.size());
     for (int i = 0; i < vector.size(); i++) {
      java.util.Hashtable hash = (java.util.Hashtable) vector
      .elementAt(i);
      PostName = (String) hash.get("POSTNAME");
      SpokesMan = (String) hash.get("SPOKESMAN");
    %>
    <tr>
     <td>
      <%=start + i%>
     </td>
     <td>
      <%=PostName%>
     </td>
     <td>
      <%=SpokesMan%>
     </td>
    </tr>
    <%
    }
    %>
   </table>
   <table>
    <tr>
     <td>
      每页
      <%=pages.getRowsPerPage()%>
      行&nbsp;&nbsp;&nbsp; 共
      <%=pages.getMaxRowCount()%>
      行&nbsp;&nbsp;&nbsp; 第
      <%=pages.getCurPage()%>
      页&nbsp;&nbsp;&nbsp; 共
      <%=pages.getMaxPage()%>
      页&nbsp;&nbsp;&nbsp;
      <br>
      <%
      if (pages.getCurPage() == 1) {
      %>

      首页&nbsp;&nbsp;上一页
      <%
      } else {
      %>
      <a href="javascript:goPage(1)"> 首页</a>&nbsp;&nbsp;
      <a href="javascript:goPage(<%=(pages.getCurPage() - 1)%>)">上一页</a>
      <%
      }
      %>
      <%
       if (pages.getCurPage() == pages.getMaxPage()) {
      %>
      &nbsp;&nbsp;下一页&nbsp;&nbsp; 尾页
      <%
      } else {
      %>
      <a href="javascript:goPage(<%=(pages.getCurPage() + 1)%>)">下一页</a>&nbsp;&nbsp;
      <a href="javascript:goPage(<%=pages.getMaxPage()%>)">尾页</a>&nbsp;&nbsp;
      <%
      }
      %>
      转到第
      <select name="jumpPage" onchange="jumping()">
       <%
        for (int i = 1; i <=

        pages.getMaxPage(); i++) {
       %>
       <%
         if (i ==

         pages.getCurPage()) {
       %>
       <option selected value=<%=i%>>
        <%=i%>
       </option>
       <%
       } else {
       %>
       <option value=<%=i%>>
        <%=i%>
       </option>
       <%
       }
       %>
       <%
       }
       %>
      </select>
     </td>
    </tr>
   </table>
  </form>
 </body>
</html>
3.连接池

package com.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * @author Admin
 *
 */
public class DataSourceConn {
 
    /**
     * 获得连接池资源，获得连接
     * @return
     */
 public static synchronized Connection getConnection() {
  DataSource ds = null;
  //获得连接池
  try {
   Context initCtx = new InitialContext();
   Context envCtv = (Context) initCtx.lookup("java:comp/env");//固定用法
   ds = (DataSource) envCtv.lookup("jdbc/dataSource");
//   System.out.println("ds = " +ds) ;
   // 获得数据库连接
   if (ds != null) {
    try {
     return ds.getConnection();
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     System.out.println("连接池没有找到");
     e.printStackTrace();
    }

   }
  } catch (NamingException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return null;
 }
   
 public static synchronized void closeConnection(Connection conn) {
  if(conn==null) {
   try {
    conn.close() ;
    
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    System.out.println("关闭连接失败");
    e.printStackTrace();
   }
  }
 }
 
 public static synchronized void closeStatement(Statement state) {
  if(state==null) {
   try {
    state.close() ;
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    System.out.println("关闭Statement失败") ;
    e.printStackTrace();
   }
  }
 }
 
 public static synchronized void closeResultSet(ResultSet rs) {
  if(rs==null) {
   try {
    rs.close() ;
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    System.out.println("关闭ResultSet失败") ;
    e.printStackTrace();
   }
  }
 }
 
 public static synchronized void closePreparedStatement(PreparedStatement pstmt) {
  if(pstmt==null) {
   try {
    pstmt.close() ;
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    System.out.println("关闭ResultSet失败") ;
    e.printStackTrace();
   }
  }
 }
}