Java通用分页
Java通用分页
一. 要分页我们必须要有数据库,所以我们先准备下数据库,其数据库脚步如下:
- --以下是创建数据库和数据库表以及向数据库插入数据 use master
Go
if exists(select * from sysdatabases where name='pagination')
drop database pagination
Go
create database pagination
Go
use pagination
Go
create table userInfo (
[userID] int identity(1,1) primary key not null,
[userName] nvarchar(50) not null,
[userPassword] nvarchar(50) not null,
)
Go
insert userInfo values('xuyesheng','8888')
insert userInfo values('jiaojiao','8888')
insert userInfo values('administrator','888')
insert userInfo values('xuyesheng1','123')
insert userInfo values('xuyesheng2','123')
insert userInfo values('xuyesheng3','123')
insert userInfo values('xuyesheng4','123')
insert userInfo values('xuyesheng5','123')
insert userInfo values('xuyesheng6','123')
Go
select * from userInfo二: 通用分页的存储过程,如下:
---------------创建存储过程--------------
Go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
------------------------------------
--
--用途:用于需要分页显示的数据
--时间:2009年08月22日
--描述:通用的存储过程分页程序
--
-------------------------------------
create PROCEDURE [dbo].[GetData]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@OrderfldName varchar(255), -- 排序字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(500) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @OrderfldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @OrderfldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @IsCount != 0
begin
if @strWhere != ''
set @strSQL = 'select count(' + @OrderfldName + ') as Total from [' + @tblName + ']'+ ' where ' + @strWhere
else
set @strSQL = 'select count(' + @OrderfldName + ') as Total from [' + @tblName + ']'
end
exec (@strSQL)
--测试下
Go
----参数说明exec [GetData] '表明','查找的列名* 表示查找所有信息','主键',页大小,第几页,非
--表示返回记录数,[排序类型,非则降序排列],条件
--得到分页的数据
exec [GetData] 'userInfo','*','userID',5,1,0,0,null
--得到总共的条数
exec [GetData] 'userInfo','*','userID',5,1,1,0,null
--根据条件查询
exec [GetData] 'userInfo','*','userID',5,1,0,0,' userID like ''%2%'' '存储过程我们有了我们只要给一下几个参数就OK了
@tblName varchar -- 表名
@fldName varchar -- 字段名
@OrderfldName -- 排序字段名
@PageSize int -- 页尺寸
@PageIndex int -- 页码
@IsCount bit -- 返回记录总数, 非 0 值则返回
@OrderType bit -- 设置排序类型, 非 0 值则降序
@strWhere varchar -- 查询条件 (注意: 不要加 where)
三 通用包到这里我们先不用急,先将一下三个Java类(Pagination.java Call.java AutoSetData.java)封装一下,然后打成JAR包,以后我们直接导入JAR包即可
Pagination.java
存储过程我们有了我们只要给一下几个参数就OK了
@tblName varchar -- 表名
@fldName varchar -- 字段名
@OrderfldName -- 排序字段名
@PageSize int -- 页尺寸
@PageIndex int -- 页码
@IsCount bit -- 返回记录总数, 非 0 值则返回
@OrderType bit -- 设置排序类型, 非 0 值则降序
@strWhere varchar -- 查询条件 (注意: 不要加 where)到这里我们先不用急,先将一下三个Java类(Pagination.java Call.java AutoSetData.java)封装一下,然后打成JAR包,以后我们直接导入JAR包即可
Pagination.java
- import java.util.List;
- public class Pagination {
- // 总共的数据量
- private int totle;
- // 每页显示多少条
- private int pageSize;
- // 共有多少页
- private int totlePage;
- // 当前是第几页
- private int index;
- // 数据
- private List data;
- // 连接路径
- private String path;
- public void setTotle(int totle) {
- this.totle = totle;
- }
- public void setPageSize(int pageSize) {
- this.pageSize = pageSize;
- }
- public void setIndex(int index) {
- this.index = index;
- }
- public void setPath(String path) {
- this.path = path;
- }
- public int getTotle() {
- return totle;
- }
- public int getPageSize() {
- return pageSize;
- }
- public int getTotlePage() {
- return (this.totle + this.pageSize - 1) / this.pageSize;
- }
- public int getIndex() {
- return index;
- }
- public List getData() {
- return data;
- }
- public void setData(List data) {
- this.data = data;
- }
- public String getPageDisplay() {
- StringBuffer displayInfo = new StringBuffer();
- if (index == 0 || pageSize == 0) {
- displayInfo.append("没有分页的信息!");
- } else {
- displayInfo.append("<div class='pager'>");
- displayInfo.append("共" + totle + "条记录每页<span style="color:#FF0000" mce_style="color:#FF0000">" + pageSize
- + "</span>条");
- displayInfo.append("第<span style="color:#FF0000" mce_style="color:#FF0000">" + index
- + "</span>页/共"
- + this.getTotlePage() + "页");
- // 判断如果当前是第一页 则“首页”和“第一页”失去链接
- if (index == 1) {
- displayInfo.append(" 首页 ");
- displayInfo.append("上一页 ");
- } else {
- displayInfo.append(" <a href="" + path
- + "index=1" mce_href="" + path
- + "index=1">首页</a> ");
- displayInfo.append("<a href="" + path + "index=" + (index - 1)
- + "" mce_href="" + path + "index=" + (index - 1)
- + "">上一页</a> ");
- }
- if (index >= this.getTotlePage()) {
- displayInfo.append("下一页 ");
- displayInfo.append("最后一页 ");
- } else {
- displayInfo.append("<a href="" + path + "index=" + (index + 1)
- + "" mce_href="" + path + "index=" + (index + 1)
- + "">下一页</a> ");
- displayInfo.append("<a href="" + path + "index="
- + this.getTotlePage() + "" mce_href="" + path + "index="
- + this.getTotlePage() + "">最后一页</a> ");
- }
- displayInfo.append("</div>");
- }
- return displayInfo.toString();
- }
- }
Pagination.java文件源码我们有了,下面的是Call.java类的源码:
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.Collection;
- import java.util.Iterator;
- import java.util.List;
- public class Call {
- private AutoSetData auto = new AutoSetData();
- public List execProcedure(String driver, String url, String userName,
- String pwd, Object[] ob, Class c) {
- try {
- Class.forName(driver);
- Connection conn = DriverManager.getConnection(url, userName, pwd);
- String sql = "exec GetData ?,?,?,?,?,?,?,null";
- List list = new ArrayList();
- // Ltest是我测试用类,实际操作请注入相关对象,支持set,get,is,read,writer为前缀数据对,更多请继续添加。
- Collection collection = auto.get(conn, c.newInstance().getClass(),
- sql, ob);
- for (Iterator it = collection.iterator(); it.hasNext();) {
- Object obj = c.newInstance();
- list.add(it.next());
- }
- return list;
- } catch (Exception e) {
- e.printStackTrace();
- }
- return null;
- }
- }
AutoSetData.java 自动将数据封装到实体类中
- import java.lang.reflect.Method;
- import java.lang.reflect.Modifier;
- import java.sql.CallableStatement;
- import java.sql.Connection;
- import java.sql.ResultSet;
- import java.sql.ResultSetMetaData;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.Collection;
- public class AutoSetData {
- private Object[] beanMatch(Class clazz, String beanProperty) {
- Object[] result = new Object[2];
- char beanPropertyChars[] = beanProperty.toCharArray();
- beanPropertyChars[0] = Character.toUpperCase(beanPropertyChars[0]);
- String s = new String(beanPropertyChars);
- String names[] = { ("set" + s).intern(), ("get" + s).intern(),
- ("is" + s).intern(), ("write" + s).intern(),
- ("read" + s).intern() };
- Method getter = null;
- Method setter = null;
- Method methods[] = clazz.getMethods();
- for (int i = 0; i < methods.length; i++) {
- Method method = methods[i];
- // 只取公共字段
- if (!Modifier.isPublic(method.getModifiers()))
- continue;
- String methodName = method.getName().intern();
- for (int j = 0; j < names.length; j++) {
- String name = names[j];
- if (!name.equals(methodName))
- continue;
- if (methodName.startsWith("set")
- || methodName.startsWith("read"))
- setter = method;
- else
- getter = method;
- }
- }
- result[0] = getter;
- result[1] = setter;
- return result;
- }
- private void beanRegister(Object object, String beanProperty, String value) {
- Object[] beanObject = beanMatch(object.getClass(), beanProperty);
- Object[] cache = new Object[1];
- Method getter = (Method) beanObject[0];
- Method setter = (Method) beanObject[1];
- try {
- // 通过get获得方法类型
- String methodType = getter.getReturnType().getName();
- if (methodType.equalsIgnoreCase("long")) {
- cache[0] = new Long(value);
- setter.invoke(object, cache);
- } else if (methodType.equalsIgnoreCase("int")
- || methodType.equalsIgnoreCase("integer")) {
- cache[0] = new Integer(value);
- setter.invoke(object, cache);
- } else if (methodType.equalsIgnoreCase("short")) {
- cache[0] = new Short(value);
- setter.invoke(object, cache);
- } else if (methodType.equalsIgnoreCase("float")) {
- cache[0] = new Float(value);
- setter.invoke(object, cache);
- } else if (methodType.equalsIgnoreCase("double")) {
- cache[0] = new Double(value);
- setter.invoke(object, cache);
- } else if (methodType.equalsIgnoreCase("boolean")) {
- cache[0] = new Boolean(value);
- setter.invoke(object, cache);
- } else if (methodType.equalsIgnoreCase("java.lang.String")) {
- cache[0] = value;
- setter.invoke(object, cache);
- } else if (methodType.equalsIgnoreCase("java.io.InputStream")) {
- } else if (methodType.equalsIgnoreCase("char")) {
- cache[0] = (Character.valueOf(value.charAt(0)));
- setter.invoke(object, cache);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- public Collection get(final Connection connection, final Class clazz,
- String sql,Object[] obj) {
- // 创建PreparedStatement
- CallableStatement ptmt = null;
- // 创建resultset
- ResultSet rset = null;
- // 创建collection
- Collection collection = null;
- try {
- // 赋予实例
- ptmt = connection.prepareCall(sql);
- for (int i = 1; i <= obj.length; i++) {
- ptmt.setObject(i,obj[i-1]);
- }
- rset = ptmt.executeQuery();
- collection = get(rset, clazz);
- } catch (SQLException e) {
- System.err.println(e.getMessage());
- } finally {
- try {
- // 关闭rs并释放资源
- if (rset != null) {
- rset.close();
- rset = null;
- }
- // 关闭ps并释放资源
- if (ptmt != null) {
- ptmt.close();
- ptmt = null;
- }
- } catch (SQLException e) {
- System.err.println(e.getMessage());
- }
- }
- return collection;
- }
- public Collection get(final ResultSet result, final Class clazz) {
- // 创建collection
- Collection collection = null;
- try {
- ResultSetMetaData rsmd = result.getMetaData();
- // 获得数据列数
- int cols = rsmd.getColumnCount();
- // 创建等同数据列数的arraylist类型collection实例
- collection = new ArrayList(cols);
- // 遍历结果集
- while (result.next()) {
- // 创建对象
- Object object = null;
- try {
- // 从class获得对象实体
- object = clazz.newInstance();
- } catch (Exception e) {
- }
- // 循环每条记录
- for (int i = 1; i <= cols; i++) {
- beanRegister(object, rsmd.getColumnName(i), result
- .getString(i));
- }
- // 将数据插入collection
- collection.add(object);
- }
- } catch (SQLException e) {
- System.err.println(e.getMessage());
- } finally {
- }
- return collection;
- }
- }
看这里我们引入了jstl.jar standard.jar sqljdbc.jar pagination.jar四个jar包 缺一不可奥。
下面我们创建一个实体类,代码如下:
- public class UserInfo {
- private int userID;
- private String userName;
- private String userPassword;
- public int getUserID() {
- return userID;
- }
- public void setUserID(int userID) {
- this.userID = userID;
- }
- public String getUserName() {
- return userName;
- }
- public void setUserName(String userName) {
- this.userName = userName;
- }
- public String getUserPassword() {
- return userPassword;
- }
- public void setUserPassword(String userPassword) {
- this.userPassword = userPassword;
- }
- }
实体类我们创建完毕,现在我们创建servlet:
- import java.io.IOException;
- import java.util.ArrayList;
- import java.util.List;
- import javax.servlet.ServletException;
- import javax.servlet.http.HttpServlet;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import com.ant.util.Call;
- import com.ant.util.Pagination;
- import com.xuyesheng.entity.UserInfo;
- public class PageServlet extends HttpServlet {
- public void doGet(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- this.doPost(request, response);
- }
- public void doPost(HttpServletRequest request, HttpServletResponse response)
- throws ServletException, IOException {
- //取得jsp页面传递来的页数,参数名 index 不可更改
- String index = request.getParameter("index");
- int num = 0;
- if (index == null) {
- num = 1;
- } else {
- num = Integer.parseInt(index);
- }
- //创建封装数据类的对象 call
- Call call = new Call();
- //创建数组存储 存储过程的参数
- Object obj[] = { "userInfo", "*", "userID", 5, num+"", 0+"", 0+"" };
- //调用执行存储过程的方法
- List li = call.execProcedure(
- "com.microsoft.sqlserver.jdbc.SQLServerDriver",
- "jdbc:sqlserver://localhost:1433;databaseName=pagination",
- "sa", "as", obj, UserInfo.class);
- //保存用户名
- List<String> list = new ArrayList<String>();
- //便利集合
- for (int i = 0; i < li.size(); i++) {
- UserInfo ui = (UserInfo) li.get(i);
- list.add(ui.getUserName());
- list.add(ui.getUserPassword());
- }
- //创建 分页对象
- Pagination p = new Pagination();
- //设置页数
- p.setIndex(num);
- //设置页大小
- p.setPageSize(Integer.parseInt(obj[3].toString()));
- //设置总共的条数
- p.setTotle(call.getTotle());
- //设置数据
- p.setData(list);
- //跳转的路径
- p.setPath("page.do?");
- request.setAttribute("page", p);
- request.getRequestDispatcher("index.jsp").forward(request, response);
- }
- }
到这里我们就差一个页面了:
页面代码如下:
- <%@ page language="java" pageEncoding="GBK"%>
- <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
- <html>
- <head>
- <title>java通用分页显示数据</title>
- </head>
- <body>
- <table width="507" height="160" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#999900">
- <tr>
- <td height="40" align="center" valign="middle" bgcolor="#FF9999"><h2>许业生用java实现通用分页功能</h2></td>
- </tr>
- <tr>
- <td height="35" align="center" valign="middle" bgcolor="#FF9999"><h3>用户名</h3></td>
- </tr>
- <c:forEach items="${requestScope.page.data}" var="li" >
- <tr>
- <td height="35" align="center" valign="middle" bgcolor="#CC99CC" >
- <c:out value="${li}"/>
- </td>
- </tr>
- </c:forEach>
- <tr>
- <td height="40" align="center" valign="middle" ><c:out value="${requestScope.page.pageDisplay}" escapeXml="false"/></td>
- </tr>
- </table>
- </body>
- </html>
其中web.xml文件中的servlet的路径配置如下:
- <?xml version="1.0" encoding="UTF-8"?>
- <web-app version="2.4"
- xmlns="http://java.sun.com/xml/ns/j2ee"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
- http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
- <servlet>
- <description>通用分页Servlet</description>
- <servlet-name>PageServlet</servlet-name>
- <servlet-class>com.xuyesheng.servlet.PageServlet</servlet-class>
- </servlet>
- <servlet-mapping>
- <servlet-name>PageServlet</servlet-name>
- <url-pattern>/page.do</url-pattern>
- </servlet-mapping>
- <welcome-file-list>
- <welcome-file>index.jsp</welcome-file>
- </welcome-file-list>
- </web-app>
到这里我们的通用分页已经结束了,该怎么访问呢?
呵呵 大家肯定比我知道啦!http://localhost:8080/pagination/page.do
大功告成!谢谢给位光临!
然后我们将刚才的三个.java文件打成JAR包,下次我们用的时候直接导入这个jar包就行了
四:项目使用
现在我们新建一个项目测试一下如何使用:
在我们创建项目的时候给项目添加jstl标签支持,我们在页面会用到,项目框架如下: