jsp:
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>获得注册信息</title>
<script type="text/javascript" src="<%=path %>/js/jquery-1.9.1.min.js"></script>
<script type="text/javascript" src="<%=path %>/js/exportExcel.js"></script>
<style type="text/css">
.table td{ text-align:center; height:28px; line-height:28px; padding:0 5px; font-size:12px; color:#2b2b2b; border-bottom:1px dashed #cbcbcb;}
</style>
</head>
<body>
id:<input type="text" name="id" id="id"> 姓名:<input type="text" name="name" id="name">
<input type="button" οnclick="getRegInfo();" value="查询"> <input type="button" οnclick="exportExcel()" value="导出数据">
<!--<form action="export.do" method="post">
<input type="submit" value="导出数据">
</form>-->
<table width="100%" border="0" cellspacing="0" cellpadding="0" class="table">
<tr id="tabTitle"></tr>
<tbody id="tabBody" style="cursor:pointer"></tbody>
</table>
<div id="ttPage" class="page_bg">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="200">
</td>
<td align="right">
共 <span id="totalPage"></span>页
当前第<span id="currentPage"></span>页
共 <span id="totalCount"></span>条记录
每页
<select id="pageSize" name="pageSize" οnchange="goto_page('changePage')">
<option value='10'>
10
</option>
<option value='50'>
50
</option>
<option value='100'>
100
</option>
</select>
条
<a οnclick="goto_page('first')" style="cursor: pointer; color: blue">[首页]</a>
<a οnclick="goto_page('pre')" style="cursor: pointer; color: blue">[上一页]</a>
<a οnclick="goto_page('next')" style="cursor: pointer; color: blue">[下一页]</a>
<a οnclick="goto_page('last')" style="cursor: pointer; color: blue">[末页]</a>
<label>跳转
<input id="page" name="page" type="text" style="border:solid 1px cornflowerblue;width:30px" />
<a οnclick="goto_page('jump')" style="cursor: pointer; color: blue">
GO
</a>
</label>
</td>
</tr>
</table>
</div>
</body>
</html>
js:
$(document).ready(function(){ getRegInfo(); }); function getRegInfo(){ var id = $.trim($("#id").val()); var name = $.trim($("#name").val()); var rand = Math.random(); $.ajax( { url : "./pageExport.do", type : "GET", async : false, data: "id="+id+"&name="+encodeURI(encodeURI(name))+"&rand="+rand, // dataType : 'json', //contentType : "application/x-www-form-urlencoded; charset=utf-8", success : function(data, textStatus) { var json = eval(data); changePage(json); }, error : function(XMLHttpRequest, textStatus, errorThrown) { alert("服务器请求失败!"); } }); } function changePage(json){ var title = []; title.push( "<th>id</th>"); title.push( "<th>姓名</th>"); title.push( "<th>密码</th>"); $("#tabTitle").html(title.join("")); var body = []; if (json == undefined || json.length == 0) { body.push( "<tr><td colspan='3'>没有数据!</td></tr>"); } else { for(var i = 0, len = json[0].list.length;i < len; i++){ body.push( "<tr>"); body.push( "<td>"+(json[0].list[i].id == null ? "" : json[0].list[i].id)+"</td>") body.push( "<td>"+(json[0].list[i].name == null ? "" : json[0].list[i].name)+"</td>") body.push( "<td>"+(json[0].list[i].psw == null ? "" : json[0].list[i].psw)+"</td>") body.push( "</tr>") } } $("#tabBody").html(body.join("")); $("#currentPage").text(json[0].currentPage); $("#totalCount").text(json[0].totalCount); $("#totalPage").text(json[0].totalPage); // $("#pageSize").html("<option value='"+pageSize+"' selected>"+pageSize+"</option>") $(".selector").val(pageSize); } //翻页 function goto_page(currentPage) { var curp = $("#currentPage").text(); var totalPage = $("#totalPage").text(); if(currentPage == "next"){ curp = curp*1+1*1; } if(currentPage == "pre"){ curp = curp*1 - 1*1; } if(currentPage == "first"){ curp = 1; } if(currentPage == "last"){ curp = totalPage; } if(currentPage == "changePage"){ curp = curp; } if(currentPage == "jump"){ curp = $("#page").val(); } var pageSize = $("#pageSize").val();//每页显示多少行 if (pageSize == null || pageSize == "" || pageSize == undefined) { pageSize = 10; // 默认每页10条 } var rand = Math.random(); var id = $.trim($("#id").val()); var name = $.trim($("#name").val()); $.ajax( { url : "./pageExport.do", type : "GET", async : false, data: "id="+id+"&name="+name+"&rand="+rand+"¤tPage="+curp+"&pageSize="+pageSize, success : function(data, textStatus) { var json = eval(data); changePage(json); }, error : function(XMLHttpRequest, textStatus, errorThrown) { alert("服务器请求失败!"); } }); } function exportExcel(){ var id = $.trim($("#id").val()); var name = $.trim($("#name").val()); var rand = Math.random(); }
java控制器代码:
//mysql分页
@ResponseBody
@RequestMapping(value = { "pageExport.do" }, method = { RequestMethod.GET,RequestMethod.POST })
public void pageExportExcel(HttpServletRequest request,HttpServletResponse response) throws Exception{
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter();
List<Map<String,Object>> list = null;
String id = request.getParameter("id")==null ? "" :request.getParameter("id");
String name = request.getParameter("name")==null ? "" : java.net.URLDecoder.decode(request.getParameter("name"), "UTF-8");
String temp=request.getParameter("currentPage");
if(temp==null || temp.equals("null")){
currentPage=1;
}else {
currentPage=Integer.parseInt(temp);
}
int pageSize = request.getParameter("pageSize")==null ? 10 : Integer.parseInt(request.getParameter("pageSize"));//每页显示的记录数
int totalCount=regDao.findInfoRowCount(id,name);
int totalPage = totalCount%pageSize==0 ? totalCount/pageSize : totalCount/pageSize+1;//总页面数
if(currentPage<1||currentPage==1){
currentPage=1;
}else if (currentPage>totalPage) {
currentPage=totalPage;
}
try {
list = regDao.getregList(id,name,currentPage,pageSize);
} catch (Exception e) {
e.printStackTrace();
}
Map<String, Object> map = new HashMap<String, Object>();
map.put("list", list);
map.put("currentPage", currentPage);
map.put("totalCount", totalCount);
map.put("totalPage", totalPage);
map.put("pageSize", pageSize);
JSONArray js = JSONArray.fromObject(map);
out.print(js);
out.close();
}
接口代码:
package com.test.dao;
import java.util.List;
import java.util.Map;
public interface RegDao {
public List<Map<String, Object>> getregList(String id, String name,int currentPage,int pageSize);
public int findInfoRowCount(String id,String name);
}
接口实现代码:
package com.test.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.mysql.jdbc.Statement;
import com.test.dao.RegDao;
@Repository("regDao")
@Transactional
public class RegDaoImpl implements RegDao{
private static Logger logger = Logger.getLogger(RegDaoImpl.class);
private JdbcTemplate jdbcTemplate;
@Resource(name = "jdbcTemplate")
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public List<Map<String, Object>> getregList(String id, String name,int currentPage,int pageSize) {
StringBuffer sb = new StringBuffer();
if(!"".equals(id) || !"".equals(name)){
sb.append(" where 1=1 ");
if(!"".equals(id)){
sb.append("and id = "+id+"");
}
if(!"".equals(name)){
sb.append(" and name like '"+name+"'");
}
}
String sql = "select * from student "+sb+" order by id LIMIT "+(currentPage-1)*pageSize+","+pageSize+"";
List<Map<String,Object>> list = null;
try {
list = jdbcTemplate.queryForList(sql);
} catch (Exception e) {
// TODO: handle exception
}
return list;
}
public int findInfoRowCount(String id,String name) {
int rtn=0;
StringBuffer sb = new StringBuffer();
if(!"".equals(id) || !"".equals(name)){
sb.append(" where 1=1 ");
if(!"".equals(id)){
sb.append("and id = "+id+"");
}
if(!"".equals(name)){
sb.append(" and name like "+name+"");
}
}
String sql = "select count(id) from student "+sb+" order by id";
try {
rtn = jdbcTemplate.queryForInt(sql);
} catch (Exception e) {
// TODO: handle exception
}
return rtn;
}
}
转载于:https://blog.51cto.com/qihoushangshu/1560722