springMVC详细配置+Mysql+jdbcTemplate实现分页功能

本例子使用到的相关技术有：springMVC+MySQL+jQuery+jdbcTemplate+jstl+json+bootstrap等

(P:网上很少找到完整的springMVC+mysql实现分页查询，所以就自己详细的做了一个完整的例子)

(环境：jdk1.6 +tomcat7.0+mysql+eclipse....)

(包/文件：json.lib+jquery.js+spring3.0相关jar+jstl.jar+bootstrap相关css\js.....)

(传输数据格式为：json)

( 

SpringMVC 的核心原理：

1，用户发送请求给服务器：url:user.do

2，服务器收到请求。发现DispatchServlet 可以处理。于是调用DispatchServlet。

3，DispatchServlet 内部，通过HandleMapping 检查这个url 有没有对应的Controller。如果有，则调用Controller.

4，Controller 开始执行。

5，Controller 执行完后，如果返回字符串，则ViewResolver 将字符串转化成相对应的视图对象；如果返回ModelAndView ，该对象本身就包含了视图对象信息。

6，DispatchServlet 将执行视图对象中的数据，输出给服务器。

7，服务器将数据输出给客户端。

)

一，先秀效果图(不是很美观，只为实现效果)：

二：整体结构

       1，src(由于工程名较敏感，所以******)

             

       2, webContent

            

三：主要配置文件

     1.web.xml 

<?xml version="1.0" encoding="UTF-8"?> <web-app version="2.5" 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_2_5.xsd">     <display-name>Spring3MVC</display-name>      <filter>       <filter-name>encodingFilter</filter-name>       <filter-class>           org.springframework.web.filter.CharacterEncodingFilter       </filter-class>       <init-param>           <param-name>encoding</param-name>           <param-value>UTF-8</param-value>       </init-param>       <init-param>           <param-name>forceEncoding</param-name>           <param-value>true</param-value>       </init-param>    </filter>    <filter-mapping>       <filter-name>encodingFilter</filter-name>       <url-pattern>/*</url-pattern>    </filter-mapping>        <servlet>         <servlet-name>spring</servlet-name>          <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>         <load-on-startup>1</load-on-startup>     </servlet>     <servlet-mapping>         <servlet-name>spring</servlet-name>         <url-pattern>*.do</url-pattern>      </servlet-mapping>        <welcome-file-list>         <welcome-file>index.jsp</welcome-file>     </welcome-file-list> </web-app>

     2.spring-servlet.xml     

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"xmlns:context="http://www.springframework.org/schema/context"xsi:schemaLocation="http://www.springframework.org/schema/beans  http://www.springframework.org/schema/beans/spring-beans-3.0.xsd  http://www.springframework.org/schema/context   http://www.springframework.org/schema/context/spring-context-3.0.xsd"><!-- 对web包中的所有类进行扫描，以完成Bean创建和自动依赖注入的功能 --><context:component-scan base-package="com.fingerknow.project" /> <!-- 启动Spring MVC的注解功能，完成请求和注解POJO的映射 --><beanclass="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter"><property name="cacheSeconds" value=http://blog.csdn.net/huahuagongzi9999/article/details/"0" /><property name="messageConverters"><list><beanclass="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter"></bean></list></property></bean><!--对模型视图名称的解析，即在模型视图名称添加前后缀 --><beanclass="org.springframework.web.servlet.view.InternalResourceViewResolver"p:prefix="/WEB-INF/views/" p:suffix=".jsp" /><!--文件上传配置 --><bean id="multipartResolver"class="org.springframework.web.multipart.commons.CommonsMultipartResolver"p:defaultEncoding="utf-8"><property name="maxUploadSize"><value>104857600</value></property><property name="maxInMemorySize"><value>4096</value></property></bean> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"><property name="driverClassName" value=http://www.07net01.com/program/"com.mysql.jdbc.Driver">

     3.***.properties     

projectURL=http://localhost:8080/fk/downloadURL=http://localhost:8080/fk/upload/download/ uploadURL=http://localhost:8080/fk/temp/

四：Java类(business为本例实例)

      1，AbstractDao     

public class AbstractDao extends JdbcDaoSupport {@Resource(name = "dataSource")public void setSuperDataSource(DataSource dataSource) {super.setDataSource(dataSource);}}

      2,BusinessDao(只展示分页查询方法)

@Repository("businessDao")public class BusinessDao extends AbstractDao{ /** * 分页查询   * @param currentPage  当前页  * @param numPerPage   每页记录数 * @return  */public Pagination queryPageBusiness(Integer currentPage,Integer numPerPage) {  String sql="SELECT * FROM business ORDER BY businessId ASC ";Pagination page=new Pagination(sql, currentPage, numPerPage,  getJdbcTemplate());return page;    }}

       3,PageInation（分页工具类）      

public class Pagination extends JdbcDaoSupport{public static final int NUMBERS_PER_PAGE = 10; //一页显示的记录数  private int numPerPage;  //记录总数  private int totalRows;  //总页数  private int totalPages;  //当前页码  private int currentPage;   //起始行数  private int startIndex;  //结束行数  private int lastIndex;  //结果集存放List  private List resultList;  //JdbcTemplate jTemplate  private JdbcTemplate jTemplate;   /**   * 每页显示10条记录的构造函数,使用该函数必须先给Pagination设置currentPage，jTemplate初值   * @param sql oracle语句   */  public Pagination(String sql){    if(jTemplate == null){      throw new IllegalArgumentException("com.deity.ranking.util.Pagination.jTemplate is null,please initial it first. ");    }else if(sql.equals("")){      throw new IllegalArgumentException("com.deity.ranking.util.Pagination.sql is empty,please initial it first. ");    }    new Pagination(sql,currentPage,NUMBERS_PER_PAGE,jTemplate);  }    /**分页构造函数   * @param sql 根据传入的sql语句得到一些基本分页信息   * @param currentPage 当前页   * @param numPerPage 每页记录数   * @param jTemplate JdbcTemplate实例   */  public Pagination(String sql,int currentPage,int numPerPage,JdbcTemplate jTemplate){    if(jTemplate == null){      throw new IllegalArgumentException("com.deity.ranking.util.Pagination.jTemplate is null,please initial it first. ");    }else if(sql == null || sql.equals("")){      throw new IllegalArgumentException("com.deity.ranking.util.Pagination.sql is empty,please initial it first. ");    }    //设置每页显示记录数    setNumPerPage(numPerPage);    //设置要显示的页数    setCurrentPage(currentPage);    //计算总记录数    StringBuffer totalSQL = new StringBuffer(" SELECT count(*) FROM ( ");    totalSQL.append(sql);    totalSQL.append(" ) totalTable ");    //给JdbcTemplate赋值    setJdbcTemplate(jTemplate);    //总记录数    setTotalRows(getJdbcTemplate().queryForInt(totalSQL.toString()));    //计算总页数    setTotalPages();    //计算起始行数    setStartIndex();    //计算结束行数    setLastIndex();    System.out.println("lastIndex="+lastIndex);//////////////////        //构造oracle数据库的分页语句   /** StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");    paginationSQL.append(" SELECT temp.* ,ROWNUM num FROM ( ");    paginationSQL.append(sql);    paginationSQL.append(" ) temp where ROWNUM <= " + lastIndex);    paginationSQL.append(" ) WHERE num > " + startIndex);      */             //装入结果集    setResultList(getJdbcTemplate().queryForList(getMySQLPageSQL(sql,startIndex,numPerPage)));  }           /** * 构造MySQL数据分页SQL  * @param queryString * @param startIndex * @param pageSize * @return */public String getMySQLPageSQL(String queryString, Integer startIndex, Integer pageSize){String result = "";if (null != startIndex && null != pageSize){result = queryString + " limit " + startIndex + "," + pageSize;} else if (null != startIndex && null == pageSize){result = queryString + " limit " + startIndex;} else{result = queryString;}return result;}   public int getCurrentPage() {    return currentPage;  }   public void setCurrentPage(int currentPage) {    this.currentPage = currentPage;  }  public int getNumPerPage() {    return numPerPage;  }  public void setNumPerPage(int numPerPage) {    this.numPerPage = numPerPage;  }  public List getResultList() {    return resultList;  }  public void setResultList(List resultList) {    this.resultList = resultList;  }  public int getTotalPages() {    return totalPages;  } //计算总页数  public void setTotalPages() {    if(totalRows % numPerPage == 0){      this.totalPages = totalRows / numPerPage;    }else{      this.totalPages = (totalRows / numPerPage) + 1;    }  }  public int getTotalRows() {    return totalRows;  }  public void setTotalRows(int totalRows) {    this.totalRows = totalRows;  }  public int getStartIndex() {    return startIndex;  }  public void setStartIndex() {    this.startIndex = (currentPage - 1) * numPerPage;  }  public int getLastIndex() {    return lastIndex;  }  public JdbcTemplate getJTemplate() {    return jTemplate;  }  public void setJTemplate(JdbcTemplate template) {    jTemplate = template;  }    //计算结束时候的索引  public void setLastIndex() {    System.out.println("totalRows="+totalRows);///////////    System.out.println("numPerPage="+numPerPage);///////////    if( totalRows < numPerPage){      this.lastIndex = totalRows;    }else if((totalRows % numPerPage == 0) || (totalRows % numPerPage != 0 && currentPage < totalPages)){      this.lastIndex = currentPage * numPerPage;    }else if(totalRows % numPerPage != 0 && currentPage == totalPages){//最后一页      this.lastIndex = totalRows ;    }  }}

       4,BusinessService       

/** * 业务处理 * @author Administrator * */@Service("BusinessService")   public class BusinessService { @Resource    private BusinessDao businessDao;/** * 分页查询   * @param currentPage  当前页  * @param numPerPage   每页记录数 * @return  */public Pagination queryPageBusiness(Integer currentPage,Integer numPerPage) {   return businessDao.queryPageBusiness(currentPage, numPerPage); }}

       5,BusinessController        

@Controller@RequestMapping(value = http://blog.csdn.net/huahuagongzi9999/article/details/"/business")public class BusinessController {@Resourceprivate  BusinessService businessService;private final static String uploadURL=propertiesUtil.getUrl("uploadURL");   /**   * 分页查询所有 * @param request * @param response  */ @RequestMapping(value = "queryPageBusiness.do") public void queryPageBusiness(HttpServletRequest request,HttpServletResponse response) {String message = "";String status = ""; PrintWriter out = null;List<Map<String, Object>> businessList =null;Pagination page=null;  Map<String, Object> map = new HashMap<String, Object>();try {out = response.getWriter();String currentPage  = URLDecoder.decode(request.getParameter("currentPage"));String numPerPage  = URLDecoder.decode(request.getParameter("numPerPage"));if("".equals(currentPage)||"".equals(numPerPage)){ page =businessService.queryPageBusiness(1, 10);  }else{ page =businessService.queryPageBusiness(StringUtil.getInteger(currentPage), StringUtil.getInteger(numPerPage));  } List list=page.getResultList();businessList=new ArrayList<Map<String,Object>>(); for (int i = 0,len=list.size();i<len; i++) {Map<String, Object> maps=new HashMap<String, Object>();Map mapRe=(Map)list.get(i);       maps.put("businessPic", StringUtil.nullOrBlank(mapRe.get("businessPic")+"")?"?"":uploadURL+mapRe.get("businessPic"));maps.put("businessName", mapRe.get("businessName"));maps.put("businessId", mapRe.get("businessId"));maps.put("businessEname", mapRe.get("businessEname"));  maps.put("createTime", FormatDateTime.formatDateTime("yyyy-MM-dd", mapRe.get("createTime")+""));businessList.add(maps); }message="success"; status = Constants.RETURN_STATUS_0;} catch (Exception e1) { e1.printStackTrace();message="failure";status = Constants.RETURN_STATUS_1;}finally{ map.put("message", message); map.put("totalPage", page.getTotalPages());  map.put("currentPage", page.getCurrentPage());  map.put("totalRows", page.getTotalRows());  map.put("numPerPage", page.getNumPerPage());  map.put("status", status);  map.put("businessList", businessList);  //必须设置字符编码，否则返回json会乱码 response.setContentType("text/html;charset=UTF-8");     out.write(JSONSerializer.toJSON(map).toString());out.flush();out.close();}  } }

  

五，web（分页jsp代码）

     

<%@ page language="java" contentType="text/html; charset=utf-8"    pageEncoding="utf-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <c:set var="ctx" value="${pageContext.request.contextPath }" /><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>分页列表</title><link href="${ctx}/bootstrap/css/bootstrap.css" rel="stylesheet"><link href="${ctx}/bootstrap/css/bootstrap-responsive.css" rel="stylesheet"><link rel="stylesheet" href="${ctx}/css/bootstrap-responsive.min.css" /> <link rel="stylesheet" href="${ctx}/css/jquery-ui.css" /><link rel="stylesheet" href="${ctx}/css/uniform.css" />  <link rel="stylesheet" href="${ctx}/css/select2.css" /><link rel="stylesheet" href="${ctx}/css/unicorn.main.css" /><link rel="stylesheet" href="${ctx}/css/common.css" />  <script src="${ctx}/js/jquery-1.9.0.js"></script>  </head><body>     <div class="container" id="businessEname_div"><div class="row">     <div class="span1"></div> <div class="span10">         <div class="widget-box"><div class="widget-title"><h5>商圈列表</h5> </div><div class="widget-content nopadding"><table class="table table-bordered table-striped table-hover data-table"> <thead><tr>     <th style="vertical-align:middle;width:10px;"><input type="checkbox" name="chkAll" id="chkAll"></th>  <th>logo</th><th>名称</th> <th>英文名</th> <th>注册日期</th>  </tr>  </thead><tbody id="tby">      </tbody>   </table>    </div>     </div>  <div class="pagination">      <input type="hidden" id="totalPage_input"/>   <ul>      <li><a href="javascript:void(0);" id="firstPage">首页</a></li>    <li><a href="javascript:void(0);" id="shang">上一页</a></li>    <li><a href="javascript:void(0);" id="xia">下一页</a></li>    <li><a href="javascript:void(0);" id="lastPage">末页</a></li>      <li>共<lable id="totalPage"></lable>页</li>      <li>第<lable id="currentPage"></lable>页</li>      <li>共<lable id="totalRows"></lable>条记录</li>     </ul>     </div></div>     <div class="span1"></div>        </div>  </div>       <script type="text/javascript">     /**  * V1.0  */  $(document).ready(function() {         var currentPage=1; //第几页       var numPerPage=5; //每页显示条数            //分页查询    var  queryByPage=function(){  $("#tby tr").remove();        $.ajax({          type: "post",            url: "${ctx}/business/queryPageBusiness.do?¤tPage="+currentPage+"&numPerPage="+numPerPage,            dataType: "json",   /*这句可用可不用，没有影响*/      contentType: "application/json; charset=utf-8",          success: function (data) {        var array=data.businessList;           var tby=$("#tby");      var totalPage=data.totalPage;       $("#totalPage_input").val(totalPage);       $("#currentPage").html(currentPage);      $("#totalRows").html(data.totalRows);      $("#totalPage").html(totalPage);    //循环json中的数据     for(var i=0,len=array.length;i<len;i++){       var td1=$("<td style='vertical-align:middle;width:10px;'><input type='checkbox' name='chk'></td>");      var td2 =$("<td width='140px'><img  src='http://blog.csdn.net/huahuagongzi9999/article/details/"+array[i].businessPic+"' style='width:135px;height:125px;background-color: none;border: none;'></td>");    var td3 =$("<td>"+array[i].businessName+"</td>");      var td4 =$("<td>"+array[i].businessEname+"</td>");       var td5 =$("<td>"+array[i].createTime+"</td>");       var tr=$("<tr></tr>");     tr.append(td1).append(td2).append(td3).append(td4).append(td5);    tr.appendTo(tby);        }      },          error: function (XMLHttpRequest, textStatus, errorThrown) {         alert(errorThrown);         }      });      }        //初始化列表       queryByPage(currentPage,numPerPage);            //首页      $("#firstPage").bind("click",function(){      currentPage=1;      queryByPage(currentPage,numPerPage);       });               //上一页        $("#shang").click(function(){        if(currentPage==1){          alert("已经到达第一页");          return ;        }else{          currentPage--;           queryByPage();         }      });            //下一页        $("#xia").click(function(){         if(currentPage==$("#totalPage_input").val()){          alert("已经到达最后一页");          return ;        }else{           currentPage++;          queryByPage();         }      });            //末页      $("#lastPage").bind("click",function(){       currentPage=$("#totalPage_input").val();       queryByPage(currentPage,numPerPage);        });            //隔行变色  function changeColor(){$("#tby>tr:odd").css("background-color","#E9EBEF");$("#tby>tr:even").css("background-color","#ffffff"); }         });      </script> </body></html>