一、数据库分页所需技术
1、所需Jar包:
2、c3p0-config.xml配置文件:
<c3p0-config >
<default-config >
<property name ="driverClass" > com.mysql.jdbc.Driver</property >
<property name ="jdbcUrl" >
<![CDATA[jdbc:mysql://127.0.0.1:3306/abc?useUnicode=true&characterEncoding=UTF-8]]>
</property >
<property name ="user" > root</property >
<property name ="password" > 1234</property >
<property name ="initialPoolSize" > 2</property >
<property name ="maxIdleTime" > 30</property >
<property name ="maxPoolSize" > 10</property >
<property name ="minPoolSize" > 2</property >
<property name ="maxStatements" > 50</property >
</default-config >
</c3p0-config >
3、项目框架:
二、各框架的实现类
1、主页:::index.jsp:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html >
<head >
<title > 数据库分页技术演示</title >
</head >
<body >
<c:redirect url ="/PageServlet" > </c:redirect >
</body >
</html >
2、web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-app version ="3.0"
xmlns ="http://java.sun.com/xml/ns/javaee"
xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation ="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" >
<display-name > </display-name >
<servlet >
<servlet-name > PageServlet</servlet-name >
<servlet-class > cn.hncu.servlets.PageServlet</servlet-class >
</servlet >
<servlet-mapping >
<servlet-name > PageServlet</servlet-name >
<url-pattern > /PageServlet</url-pattern >
</servlet-mapping >
<welcome-file-list >
<welcome-file > index.jsp</welcome-file >
</welcome-file-list >
</web-app >
2、utils层:::c3p0Pool数据库连接池:
package cn.hncu.pubs;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Pool {
private static DataSource ds;
private static ThreadLocal<Connection> t = new ThreadLocal<Connection>();
static {
try {
ds = new ComboPooledDataSource();
} catch (Exception e) {
throw new RuntimeException("数据库连接池创建失败!" , e);
}
}
public static DataSource getDataSource (){
return ds;
}
/**
* 获得数据库连接池中的对象
* @return
* @throws SQLException
*/
public static Connection getConn () throws SQLException{
Connection con = t.get();
if (con==null ){
con = ds.getConnection();
t.set(con);
}
return con;
}
public static void clearConFromThreadLocal (){
t.set(null );
}
}
3、servlet层 :::PageServlet.java:
package cn.hncu.servlets;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.hncu.service.IPageService;
import cn.hncu.service.PageServiceImpl;
public class PageServlet extends HttpServlet {
private IPageService service = new PageServiceImpl();
public void doGet (HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost (HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String spageNo = request.getParameter("page" );
if (spageNo==null || spageNo.trim().length()==0 ){
spageNo="1" ;
}
Integer pageNo = Integer.parseInt(spageNo);
try {
Map<String, Object> result = service.query(pageNo);
request.setAttribute("result" , result);
request.getRequestDispatcher("/jsps/show.jsp" ).forward(request, response);
} catch (Exception e) {
}
}
}
4、service层:::IPageService.java:
package cn.hncu.service;
import java.util.Map;
public interface IPageService {
public Map<String, Object> query (Integer pageNo) throws Exception;
}
4、service层:::PageServiceImpl.java:
package cn.hncu.service;
import java.util.Map;
import cn.hncu.dao.PageDAO;
import cn.hncu.dao.PageDaoJdbc;
public class PageServiceImpl implements IPageService {
private PageDAO dao = new PageDaoJdbc();
@Override
public Map<String, Object> query (Integer pageNo) throws Exception {
return dao.query(pageNo);
}
}
5、dao层:::PageDAO:
package cn.hncu.dao;
import java.util.Map;
public interface PageDAO {
public Map<String, Object> query (Integer pageNo) throws Exception;
}
5、dao层:::PageDaoJdbc:
package cn.hncu.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import cn.hncu.pubs.C3p0Pool;
public class PageDaoJdbc implements PageDAO {
@Override
public Map<String, Object> query (Integer pageNo) throws Exception {
int pageSize = 10 ;
Map<String, Object> result = new HashMap<String, Object>();
result.put("currentPage" , pageNo);
QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
String sql = "select count(1) from stud" ;
int rows = Integer.parseInt(run.query(sql, new ScalarHandler())+"" );
int pageCount = rows/pageSize + (rows%pageSize==0 ? 0 :1 );
result.put("pageCount" ,pageCount);
int startN = (pageNo-1 )*pageSize;
sql = "select *from stud limit " +startN+"," +pageSize;
List<Map<String, Object>> datas = run.query(sql, new MapListHandler());
result.put("datas" , datas);
return result;
}
}
MySQL数据库:::
6、前端页面:::show.jsp:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html >
<head >
</head >
<body >
<h2 > 学生信息</h2 >
<c:forEach items ="${result.datas}" var ="map" >
${map.id},${map.name},${map.pwd}<br />
</c:forEach >
<hr >
<c:if test ="${result.currentPage!=1}" >
<a href ="<c:url value='/PageServlet?page=${result.currentPage-1}'/>" > 上一页</a >
</c:if >
<c:forEach begin ="1" end ="${result.pageCount}" var ="page" >
<c:if test ="${result.currentPage!=page}" >
<a href ="<c:url value='/PageServlet?page=${page}'/>" > ${page }</a >
</c:if >
<c:if test ="${result.currentPage==page}" >
<font size ="12px" color ="red" > ${page}</font >
</c:if >
</c:forEach >
<c:if test ="${result.pageCount!=result.currentPage}" >
<a href ="<c:url value='/PageServlet?page=${result.currentPage+1}'/>" > 下一页</a >
</c:if >
<hr >
<select onchange ="sub(this);" >
<c:forEach begin ="1" end ="${result.pageCount}" var ="page" >
<option value ="${page }" <c:if test ="${page==result.currentPage }" > selected="selected"</c:if > >
第${page}页
</option >
</c:forEach >
</select >
<script type ="text/javascript" >
function sub (obj) {
window.location.href="<c:url value='/PageServlet?page='/>" +obj.value;
}
</script >
<hr >
<c:if test ="${result.currentPage!=1}" >
<a href ="<c:url value='/PageServlet?page=${result.currentPage-1}'/>" > 上一页</a >
</c:if >
<c:set var ="currentPage" value ="${result.currentPage}" > </c:set >
<c:set var ="count" value ="${result.pageCount}" > </c:set >
<c:if test ="${currentPage<=5}" >
<c:set var ="start" value ="1" > </c:set >
<c:if test ="${count/10>0}" > <c:set var ="end" value ="10" > </c:set > </c:if >
<c:if test ="${count/10==0}" > <c:set var ="end" value ="${count}" > </c:set > </c:if >
</c:if >
<c:if test ="${currentPage>5}" >
<c:set var ="start" value ="${currentPage-5}" > </c:set >
<c:if test ="${currentPage+4>count}" > <c:set var ="end" value ="${count}" > </c:set > </c:if >
<c:if test ="${currentPage+4<=count}" > <c:set var ="end" value ="${currentPage+4}" > </c:set > </c:if >
</c:if >
<c:forEach begin ="${start}" end ="${end}" var ="i" >
<c:if test ="${i!=currentPage}" >
<a href ="<c:url value='/PageServlet?page=${i}'/>" > ${i}</a >
</c:if >
<c:if test ="${i==currentPage}" >
<font size ="12px" color ="red" > ${i}</font >
</c:if >
</c:forEach >
<c:if test ="${result.pageCount!=result.currentPage}" >
<a href ="<c:url value='/PageServlet?page=${result.currentPage+1}'/>" > 下一页</a >
</c:if >
</body >
</html >
7、执行效果:::