spring mvc mysql分页查询条件_springmvc+java+mysql分页条件查询自学代码-CSDN博客

本文链接：https://blog.csdn.net/weixin_27945229/article/details/113465447

jsp:

String path = request.getContextPath();

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";

HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

获得注册信息

.table td{ text-align:center; height:28px; line-height:28px; padding:0 5px; font-size:12px; color:#2b2b2b; border-bottom:1px dashed #cbcbcb;}

id: 姓名:

共页

当前第页

共条记录

每页

100

条

[首页]

[上一页]

[下一页]

[末页]

跳转

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( "

id");

title.push( "

姓名");

title.push( "

密码");

$("#tabTitle").html(title.join(""));

var body = [];

if (json == undefined || json.length == 0) {

body.push( "

没有数据！");

} else {

for(var i = 0, len = json[0].list.length;i

body.push( "

");

body.push( "

"+(json[0].list[i].id == null ? "" : json[0].list[i].id)+"")

body.push( "

"+(json[0].list[i].name == null ? "" : json[0].list[i].name)+"")

body.push( "

"+(json[0].list[i].psw == null ? "" : json[0].list[i].psw)+"")

body.push( "

}

$("#tabBody").html(body.join(""));

$("#currentPage").text(json[0].currentPage);

$("#totalCount").text(json[0].totalCount);

$("#totalPage").text(json[0].totalPage);

// $("#pageSize").html(""+pageSize+"")

$(".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+"&currentPage="+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> 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 map = new HashMap();

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> 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> 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> 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;

}