ajax+jdbc实现数据库分页

来源：互联网发布：软件测试岗位要求编辑：程序博客网时间：2024/04/29 14:55

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 onclick='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' onclick='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' onclick='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' onclick='document.getElementById(/"cPage/").value="+ i +";load();' style="cursor: pointer;" mce_style="cursor: pointer;">" + i + "</a> ");}} else {pageStr.append("<a id='Pagelist' onclick='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;' onclick='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 blockSystem.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 blockSystem.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 stubStringBuffer 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 blocke.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.运行效果