不妨看看我用dwr和json完成简单分页-jsp dwr分页(mssqlserver2005)

以前用dwr写的分页，感觉稍微有点麻烦，简单，可以在csdn的http://download.csdn.net/source/1413703下载，sqlser2000的数据库。

 

说明：我对json不熟悉，也不是陌生的那种，就是没怎么使用过，只是知道一点点。如果你也是这样的话，不妨你先看看

这里我的上一篇，我还是觉得挺简单的，代码没怎么细化，总体上讲简单，附上一下文件的源文件。

 

1、分页毫无疑问肯定有数据库链接类，注意，这里是mssql2005的数据库

package com.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @Info 数据库链接管理类
 * @Email [e-mail]
 * @QQ 271069593
 */
public class DBManager {

	private static final String URL = "jdbc:sqlserver://localhost:1433;databasename=Struts";
	
	private static final String USERNAME = "sa";
	
	private static final String PASSWORD = "";
	
	public static Connection getConnection(){

		Connection con = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (Exception e) {
			System.out.println("DBManager.getConnection()" + e.getMessage());
			e.printStackTrace();
		}
		
		return con;
	}
	
	public static void Close(ResultSet rs ,PreparedStatement pst,Connection conn){
		
		try {
			if (rs != null)
				rs.close();
			if (pst != null)
				pst.close();
			if (conn != null)
				conn.close();
		} catch (Exception e) {
			System.out.println("DBManager.Close()" + e.getMessage());
			e.printStackTrace();
		}		
		
	}
	
}

 

2、数据库表结构信息

use struts

--drop table account
--新建表
create table account(
  id int identity(1,1) primary key,
  username varchar(10) default '',
  age int ,
  sex varchar(2)
)

--向表插入100条数据
declare @age int
declare @sex varchar(2)
set @age = 1
while(@age<=100)
begin
   if(@age%3=0)
   begin
     set @sex = '男'
   end
   else 
     set @sex = '女'
   insert into account values('冬冬',@age,@sex);
   set @age = @age + 1
end

--查询表数据
select * from account

 

3、dwr环境配置

(1)、web.xml配置

<servlet>
    <servlet-name>dwr-remote</servlet-name>
    <servlet-class>org.directwebremoting.servlet.DwrServlet</servlet-class>
    <init-param>
      <param-name>debug</param-name>
      <param-value>true</param-value>
    </init-param>
</servlet>
<servlet-mapping>
    <servlet-name>dwr-remote</servlet-name>
    <url-pattern>/dwr/*</url-pattern>
</servlet-mapping>

 (2)、dwr.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE dwr PUBLIC "-//GetAhead Limited//DTD Direct Web Remoting 3.0//EN" "dwr30.dtd">

<dwr>
	<allow>
		<create creator="new" javascript="p">
			<param name="class" value="com.dao.AnalysePage"></param>
		</create>
	</allow>
</dwr>

 

说明：我是把dwr3.0的dtd文件下载下来了，所以上面的这一行<!DOCTYPE dwr PUBLIC "-//GetAhead Limited//DTD Direct Web Remoting 3.0//EN" "dwr30.dtd">红字部分请注意。

 

4、分页处理类

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.util.ArrayList;

import com.db.DBManager;

/**
 * 处理完成分页，让dwr在页面上调用处理
 *
 */
public class AnalysePage {

	private Connection conn = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	
	/**
	 * 处理分页的方法
	 * 默认为每页显示10条数据
	 * @param page 页数
	 */
	public ArrayList<String> loadAll(int page){
		
		ArrayList<String> list = null;
		int pageSize = 10;
		
		String sql = "select top " + pageSize + " *,(select count(0) from Account) as datasizes from Account where id not in (select top " + (pageSize * (page-1)) + " id from Account)";
		System.out.println(sql);
		conn = DBManager.getConnection();
		try {
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCounts = rsmd.getColumnCount();
			if(rs.next()){
				list = new ArrayList<String>();
				
				int datasizes = rs.getInt("datasizes");
				int count = (int)Math.round(Math.ceil((double)datasizes/(double)pageSize));
				
				do{
					String json = "";
					
					for(int i=1;i<=columnCounts;i++){
						
						String columnName = rsmd.getColumnName(i);
						String columnValue = rs.getString(i);
						if("datasizes".equals(columnName)){
							columnValue = count + "";
						}
						if(i==1){
							json += "{";
						}
						
						if(i==columnCounts){
							json += columnName + ":'" + columnValue + "'";
							json += "}";
							break;
						}
						json += columnName + ":'" + columnValue + "',";
						
					}
					list.add(json);
					
				}while(rs.next());
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBManager.Close(rs, pst, conn);
		}
		
		return list;
		
	}
	
	public static void main(String[] args) {
		
		AnalysePage dao = new AnalysePage();
		ArrayList<String> list = dao.loadAll(1);
		System.out.println(list.toString());
		for (String string : list) {
			System.out.println(string);
		}
		
	}
	
}

 

5、index.jsp页面使用

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
%>

<!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" />    
  	
  	<script type='text/javascript' src='<%=path %>/dwr/interface/p.js'></script>
  	<script type='text/javascript' src='<%=path %>/dwr/engine.js'></script>
  	<script type='text/javascript' src='<%=path %>/dwr/util.js'></script>
  	
  	<style type="text/css">
  		#mybody td{
  			text-align: center;
  		}
  	</style>
  	
  	<script type="text/javascript">
  		
  		var pageid = 1;
  		var totalpage = 1;
  		
  		function loadPage(){
  			p.loadAll(pageid,analyseData);
  		}
  		
  		function analyseData(objjson){
  			
  			var nodata = document.getElementById("nodata");
  			if(objjson==null||objjson.length==0){
  				nodata.style.display = "block";
  				return ;
  			}
			var tbody = document.getElementById("mybody");
			
			removeTRS(tbody);//显示数据前，先清空数据
			
			for(var i=0;i<objjson.length;i++){
		    	var jj = objjson[i];
				eval("var json = " + jj + ";");
				totalpage = json.datasizes;
				document.getElementById("totalpage").innerHTML = totalpage;
				var tr = document.createElement("tr");
				for(var j in json){
					if("datasizes"==j){
						continue;
					}
					var td = document.createElement("td");
					td.innerHTML = json[j];
					tr.appendChild(td);
				}
				tbody.appendChild(tr);
			}
  			
  		}
  		
  		dwr.util.useLoadingMessage();
  		
  		/**
  		*清空tr的数据
  		*/
  		function removeTRS(obj){
  			
  			var len = obj.childNodes.length;
  			for(var i=0;i<len;i++){
  				obj.removeChild(obj.firstChild);
  			}
  		}
  		
  		function goFirstPage(){
 			//判断当前是否为第一页，如果为第一页则不必去加载数据 		
  			if(pageid==1){
  				alert("已经为首页！");
  				return;
  			}
  			pageid = 1;
  			p.loadAll(1,analyseData);
  			document.getElementById("thispage").innerHTML = pageid;
  		}
  		
  		function goNextPage(){
  			if(pageid==totalpage){
  				alert("已经为最末页，没有下1页！");
  				return;
  			}
  			pageid = pageid + 1;
  			document.getElementById("thispage").innerHTML = pageid;
  			p.loadAll(pageid,analyseData);
  		}
  		
  		function goPrePage(){
  			if(pageid==1){
  				alert("已经为第1页，没有上一页！");
  				return;
  			}
  			pageid = pageid - 1;
  			document.getElementById("thispage").innerHTML = pageid;
  			p.loadAll(pageid,analyseData);
  		}
  		
  		function goLastPage(){
  			if(pageid==totalpage){
  				alert('已经为最末页！');
  				return;
  			}
  			pageid = totalpage;
  			p.loadAll(pageid,analyseData);
  			document.getElementById("thispage").innerHTML = pageid;
  		}
  	</script>
  	
  	
  <body>
    
    <table style="border-collapse: collapse;" bordercolor="#FFCCFF" width="60%" border="1">
    	
    	<tr>
    		<th>编号</th>
    		<th>名称</th>
    		<th>年龄</th>
    		<th>性别</th>
    	</tr>
    	<tbody id="mybody">
    		<tr id="nodata" style="display: none;">
    			<td style="text-align: center;" colspan="4">没有数据！</td>
    		</tr>
    	</tbody>
    	<tr>
    		<td colspan="4" style="text-align: center;">
    			<a href="javascript:void(0);" onclick="goFirstPage();" style="padding:5px;">首页</a>
    			<a href="javascript:void(0);" onclick="goPrePage();" style="padding:5px;">上一页</a>
    			<a href="javascript:void(0);" onclick="goNextPage();" style="padding:5px;">下一页</a>
    			<a href="javascript:void(0);" onclick="goLastPage();" style="padding:5px;">末页</a>
    			<span id="thispage">1</span>/<span id="totalpage">loading...</span>
    		</td>
    	</tr>
    </table>
    
    <script type="text/javascript">
    	window.onload = loadPage;
    </script>
    
  </body>
</html>