ajax+jdbc实现数据库分页-CSDN博客

1.背景

最近在做项目的过程中碰到了一个问题，就是页面上有4个tab标签，点击其中的一个都会把其他3个隐藏掉，只显示当前的tab页面，而这几个tab页面的内容是在同一个页面中，现在要求点击第4个标签能够实现数据库分页显示，每次显示的时候只局部刷新第4个tab页，前三个不能刷新，于是乎就想到了ajax。用ajax实现分页难点其实最主要的还是传递参数，主要包括当前页数和每页显示的数目，无论是发送ajax请求还是回调函数显示分页的内容，都需要动态的将参数设置并传递过去。

2.实例

首先建立mysql数据库person及其表person

create database person;
use person;
create table person(id varchar(20),name varchar(20),age varchar(20),sex varchar(20));

(1)index.jsp

<%@ page language="java" pageEncoding="UTF-8"%> <% String path = request.getContextPath(); %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <mce:script type="text/javascript" src="<%=path%></mce:script> </head> <body> <a href="javascript:load()" mce_href="javascript:load()">ajax+jdbc分页练习</a> <form> <div id="content"> </div> <input type='hidden'name='pages' id='cPage'/> <input type='hidden'name='pageSize' id='pSize'/> </form> </body> </html>

(2)zxc.js

(3)Person.java

package com.zxc.struts.bean; public class Person { private String id; private String name; private int age; private String sex; private String password; private String email; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public void setPassword(String password) { this.password = password; } public String getPassword() { return password; } public void setEmail(String email) { this.email = email; } public String getEmail() { return email; } }

(4)PageListData.java

package com.zxc.struts.bean; import java.util.ArrayList; import java.util.Locale; import javax.servlet.http.HttpServletRequest; public class PageListData { // 保存分页数据对象 private ArrayList dataArray = null; // 记录总数 private int count = 0; // 每页显示记录数 private int pageSize = 0; // 当前页数 private int page = 1; public PageListData() { super(); newDataArray(); } public ArrayList getDataArray() { return dataArray; } public void setDataArray(ArrayList dataArray) { this.dataArray = dataArray; } public void newDataArray() { if (dataArray == null) dataArray = new ArrayList(); } public void setData(Object data) { newDataArray(); getDataArray().add(data); } public void setData(int i, Object data) { newDataArray(); getDataArray().add(i, data); } // 记录总数 public int getCount() { return count; } public void setCount(int count) { this.count = count; } // 总页数 public int getPageCount() { if(count%pageSize==0){ return count/pageSize; }else{ return count/pageSize+1; } } // 每页显示录数 public int getPageSize() { return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } // 当前页数 public int getPage() { return page; } public void setPage(int page) { this.page = page; } /**分页表现层 * 封装分页栏函数必需被包含在某个Form之中 * @param request HttpServletRequest * @return String pages 当前页号 pageSize 每页显示行数 */ public String getFooter(HttpServletRequest request) { String previous = "previous"; String next ="next"; StringBuffer pageStr = new StringBuffer(""); pageStr.append("<center><p class='pages'>"); int nTotalPages = getPageCount(); //总页数 int nCurrentPage = getPage(); //当前页 int nPrevPage = getPage() - 1; //上一页 int nNextPage = getPage() + 1; //下一页 int first = 0; int last = 0; if (nCurrentPage > 1){ pageStr.append("<a οnclick='document.getElementById(/"cPage/").value=" + nPrevPage + ";load();' class='prevpage' style="cursor: pointer;" mce_style="cursor: pointer;" ><strong>" + previous +"</strong></a>"); } for (int i = 1; i <= nTotalPages; i++) { if (i == nCurrentPage){ pageStr.append("<span style="padding:1px 8px 1px 8px;color:#fff;text-decoration:none!important;background:url(./pgback.png) no-repeat center center;" mce_style="padding:1px 8px 1px 8px;color:#fff;text-decoration:none!important;background:url(pgback.png) no-repeat center center;">" + i + "</span>"); } else { if(nCurrentPage == 1 && (i == 2 || i==3)) { pageStr.append("<a id='Pagelist' οnclick='document.getElementById(/"cPage/").value="+ i +";load();' style="cursor: pointer;" mce_style="cursor: pointer;">" + i + "</a> "); } else if (i != 1 && nCurrentPage >= 5 && i < nCurrentPage) { if(first == 0) { pageStr.append("<span class='pgempty'>...</span>"); first = 1; } if(nCurrentPage == nTotalPages && i == nTotalPages - 1) { pageStr.append("<a id='Pagelist' οnclick='document.getElementById(/"cPage/").value="+ i +";load();' style="cursor: pointer;" mce_style="cursor: pointer;">" + i + "</a> "); } } else if( i <= nTotalPages && nCurrentPage <= nTotalPages - 4 && i > (nCurrentPage + 1)) { if(last == 0) { pageStr.append("<span class='pgempty'>...</span>"); last = 1; } if(i==nTotalPages){ pageStr.append("<a id='Pagelist' οnclick='document.getElementById(/"cPage/").value="+ i +";load();' style="cursor: pointer;" mce_style="cursor: pointer;">" + i + "</a> "); } } else { pageStr.append("<a id='Pagelist' οnclick='document.getElementById(/"cPage/").value="+ i +";load();' style="cursor: pointer;" mce_style="cursor: pointer;">" + i + "</a> "); } } } if (nCurrentPage < nTotalPages) { pageStr.append("<a class='nextpage'style='cursor: pointer;' οnclick='document.getElementById(/"cPage/").value=" + nNextPage + ";load();'><strong>" + next +"</strong></a></center>"); } pageStr.append("</p>"); return pageStr.toString(); } // public String getPager(HttpServletRequest request) { // String previous = "previous"; // String next = "next"; // // StringBuffer pageStr = new StringBuffer(""); // pageStr.append("<div id='pager' class='pager'>"); // pageStr.append("<div id='pagenumber' class='pagedisplay pages'></div>"); // pageStr.append("<input class='pagesize' type='hidden' value='"+ pageSize // + "' name='pageSize' id='pageSize' />"); // pageStr.append("<input type='hidden' value='"+ previous // + "' name='labpre' id='labpre' />"); // pageStr.append("<input type='hidden' value='"+ next // + "' name='labnext' id='labnext' />"); // pageStr.append("</div>"); // // return pageStr.toString(); // } }

(5)DBAccess.java

package com.zxc.struts.bean; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBAccess { /** * @param args */ public DBAccess(){ } public static Connection getConnection(){ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block System.out.println("驱动程序未找到"); e.printStackTrace(); } String url="jdbc:mysql://localhost:3306/person"; String user="root"; String password="123"; Connection conn=null; try { conn=DriverManager.getConnection(url, user, password); } catch (SQLException e) { // TODO Auto-generated catch block System.out.println("数据库连接出错啦!"); e.printStackTrace(); } System.out.println("Connection连接成功!"); return conn; } public static void main(String[] args) { // TODO Auto-generated method stub Connection connection=getConnection(); } }

(6)数据访问层ManagerPerson.java

package com.zxc.struts.bean; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.zxc.struts.bean.DBAccess; public class ManagerPerson { /** * @param args */ public static void main(String[] args) { // TODO Auto-generated method stub //往数据库插入100条数据 List list1=new ArrayList(); for (int i = 1; i < 101; i++) { Person person = new Person(); person.setId(String.valueOf(i)); person.setName("A"); person.setAge(i + 20); person.setSex("man"); list1.add(person); } insertData(list1); } //返回的map中包含所需的结果集和总的记录数 public static Map getList(int pageSize,int currentPage){ String sql="select * from person"; Connection conn=null; PreparedStatement ps=null; ResultSet rs=null; Map result=null; List list=null; Person p=null; try{ conn=DBAccess.getConnection(); result=new HashMap(); list=new ArrayList(); ps=conn.prepareStatement(sql); rs=ps.executeQuery(); if((currentPage-1)*pageSize!=0) rs.absolute((currentPage-1)*pageSize); int i=0; while(rs.next()&&i++<pageSize){ p=new Person(); p.setId(rs.getString("id")); p.setName(rs.getString("name")); p.setSex(rs.getString("sex")); p.setAge(Integer.parseInt(rs.getString("age"))); list.add(p); } result.put("list", list); rs.close(); ps.close(); sql="select count(*) from person"; ps=conn.prepareStatement(sql); rs=ps.executeQuery(sql); if(rs.next()){ result.put("tatalcount", rs.getInt(1)); } rs.close(); ps.close(); conn.close(); }catch(Exception e){ e.printStackTrace(); } return result; } //将list中的数据插入到数据库中 public static void insertData(List list){ String sql="insert into person values(?,?,?,?)"; Connection conn=DBAccess.getConnection(); PreparedStatement ps=null; ResultSet rs=null; int n=0; for(int i=0;i<list.size();i++){ try{ Person p=(Person)list.get(i); ps=conn.prepareStatement(sql); ps.setString(1, p.getId()); ps.setString(2, p.getName()); ps.setString(3, String.valueOf(p.getAge())); ps.setString(4,p.getSex()); n=ps.executeUpdate(); if(n==1){ System.out.println("插入一条数据成功！"); }else{ System.out.println("插入一条数据失败!"); } }catch(Exception e){ e.printStackTrace(); } } try{ ps.close(); conn.close(); }catch(SQLException e){ e.printStackTrace(); } } }

(7)PaginationAction.java

/* * Generated by MyEclipse Struts * Template path: templates/java/JavaClass.vtl */ package com.zxc.struts.action; import java.io.IOException; import java.util.ArrayList; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.struts.action.Action; import org.apache.struts.action.ActionForm; import org.apache.struts.action.ActionForward; import org.apache.struts.action.ActionMapping; import com.zxc.struts.bean.ManagerPerson; import com.zxc.struts.bean.PageListData; import com.zxc.struts.bean.Person; /** * MyEclipse Struts Creation date: 08-10-2010 * * XDoclet definition: * * @struts.action validate="true" */ public class PaginationAction extends Action { public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) { // TODO Auto-generated method stub StringBuffer sb = new StringBuffer(); // 从Ajax发过来的URL情求中获得当前页和每页显示的数目 int currPage = (request.getParameter("pages") == null || request.getParameter("pages").equals("null") || request .getParameter("pages").equals("")) || request.getParameter("pages").equals("[object]") ? 1 : Integer.parseInt(request.getParameter("pages")); int pageSize = (request.getParameter("pageSize") == null || request.getParameter("pageSize").equals("null") || request.getParameter("pageSize").equals("") || request .getParameter("pageSize").equals("[object]")) ? 5 : Integer .parseInt(request.getParameter("pageSize")); // 调用后台方法，map中包含所需的结果集和总的记录数 Map map = ManagerPerson.getList(pageSize, currPage); PageListData listdata = new PageListData(); // 设置总记录数 listdata.setCount(Integer.parseInt(map.get("tatalcount") == null ? "" : map.get("tatalcount").toString())); // 取得list列表 List list = (List) map.get("list"); // 设置当前页 listdata.setPage(currPage); // 设置每页显示的数目 listdata.setPageSize(pageSize); Person person = null; // 开始组装页面显示的内容 sb.append("<table align='center'><thead><tr>"); sb.append("<td style='width:20%'>id</td>"); sb.append("<td style='width:30'>name</td>"); sb.append("<td style='width:25%'>sex</td>"); sb.append("<td style='width:25%'>age</td>"); sb.append("</tr></thead><tbody>"); if (list != null && list.size() > 0) { for (int i = 0; i < list.size(); i++) { person = (Person) list.get(i); sb.append("<tr>"); sb.append("<td>").append(person.getId()).append("</td>"); sb.append("<td>").append(person.getName()).append("</td>"); sb.append("<td>").append(person.getSex()).append("</td>"); sb.append("<td>").append(person.getAge()).append("<td>"); } } sb.append("</tbody></table>"); sb.append(listdata.getFooter(request)); // 将当前页和每页显示的数目用"##"分割追加到sb的后面 sb.append("##" + currPage + "##" + pageSize); try { response.getWriter().write(sb.toString()); response.getWriter().flush(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } }

(8)struts-config.properties

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.3//EN" "http://struts.apache.org/dtds/struts-config_1_3.dtd"> <struts-config> <form-beans /> <global-exceptions /> <global-forwards /> <action-mappings > <action path="/pagination" type="com.zxc.struts.action.PaginationAction" cancellable="true" > <forward name="success" path="/pagesort.jsp"/> </action> </action-mappings> <controller nocache="true"/> <message-resources parameter="com.zxc.struts.ApplicationResources"/> </struts-config>

3.运行效果