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()%>
行 共
<%=pages.getMaxRowCount()%>
行 第
<%=pages.getCurPage()%>
页 共
<%=pages.getMaxPage()%>
页
<br>
<%
if (pages.getCurPage() == 1) {
%>
首页 上一页
<%
} else {
%>
<a href="javascript:goPage(1)"> 首页</a>
<a href="javascript:goPage(<%=(pages.getCurPage() - 1)%>)">上一页</a>
<%
}
%>
<%
if (pages.getCurPage() == pages.getMaxPage()) {
%>
下一页 尾页
<%
} else {
%>
<a href="javascript:goPage(<%=(pages.getCurPage() + 1)%>)">下一页</a>
<a href="javascript:goPage(<%=pages.getMaxPage()%>)">尾页</a>
<%
}
%>
转到第
<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();
}
}
}
}