java搜索分页_Java条件查询分页——总结

问题

在工作中经常会遇到分页查询，我们都知道其实分页查询也很简单，思想都是类似的，不过你见过的分页方法有哪些呢？

详解

一、Easyui的分页查询

1、工具类：

Page.java：

public class Page {

private Integer page;

private Integer rows;

private Integer start;

public Integer getPage() {

return page;

}

public void setPage(Integer page) {

this.page = page;

}

public void setStart(Integer start) {

this.start = start;

}

public Integer getRows() {

return rows;

}

public void setRows(Integer rows) {

this.rows = rows;

}

public Integer getStart() {

return (page-1) * rows;

}

}

EasyUIResult.java:

public class EasyUIResult {

private Integer total;

private List> rows;

public EasyUIResult(Integer total, List> rows) {

super();

this.total = total;

this.rows = rows;

}

public Integer getTotal() {

return total;

}

public void setTotal(Integer total) {

this.total = total;

}

public List> getRows() {

return rows;

}

public void setRows(List> rows) {

this.rows = rows;

}

}

2、代码使用

UserPage.java:

public class UserPage extends Page {

private String usercode;

public String getUsercode() {

return usercode;

}

public void setUsercode(String usercode) {

this.usercode = usercode;

}

}

UserController.java:

@RequestMapping("/list")

@ResponseBody

public EasyUIResult listAllUser(UserPage userPage){

List list =userService.listAllUser(userPage);

Integer total = userService.findUserCount(userPage);

EasyUIResult result =new EasyUIResult(total, list);

return result;

}

UserServiceImpl.java:

public List listAllUser(UserPage userPage){

return userMapper.findAllUsers(userPage);

}

public Integer findUserCount(UserPage userPage){

return userMapper.findUserCount(userPage);

}

UserMapper.java:

List findAllUsers(UserPage userPage);

Integer findUserCount(UserPage userPage);

UserMapper.xml:

select

from sys_user

where usercode like '%${usercode}%'

limit #{start},#{rows}

select

count(*)

from sys_user

where usercode like '%${usercode}%'

3、总结

easyui的页面什么都不用管，因为他会向后台传递page(pc)、rows(ps),query(url),后台返回total(总页数)和rows(list)

二、url重写(页面上不显示查询条件，拼接在url上)(goods)

1、工具类

PageBean.java:

public class PageBean {

private int pc;//当前页

private int tp;//总页数 TR/PS

private int tr;//总记录数

private int ps;//每页记录数

private String url;//查询条件

private List beanList;//当前页数据

public int getPc() {

return pc;

}

public void setPc(int pc) {

this.pc = pc;

}

public int getTp() {

return tr%ps==0?tr/ps:tr/ps+1;

}

>>>>>>>>>>>>

get、set方法

>>>>>>>>>>>>

}

Expression.java:

public class Expression {

private String name;

private String operator;

private String value;

>>>>>>>>>>>>

get、set方法

>>>>>>>>>>>>

}

PageConstants.java:

public class PageConstants {

public static final int BOOK_PAGE_SIZE=12;

public static final int ORDER_PAGE_SIZE=5;

}

2、使用方法

BookServlet.java:

//单条件查询

public String findByBname(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

//获得当前页

int pc =getPc(request);

//获得每页记录数

int ps = PageConstants.BOOK_PAGE_SIZE;

//获取查询参数

String bname = request.getParameter("bname");

//调用service获取pageBean

PageBean pageBean = bookService.findByBname(bname, pc, ps);

//获得url

String url =getUrl(request);

pageBean.setUrl(url);

request.setAttribute("pageBean", pageBean);

return"f:/jsps/book/list.jsp";

}

//组合查询

public String findByCombination(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

//获得当前页

int pc =getPc(request);

//获得每页记录数

int ps = PageConstants.BOOK_PAGE_SIZE;

//获取查询参数

Book book =CommonUtils.toBean(request.getParameterMap(), Book.class);

//调用service获取pageBean

PageBean pageBean = bookService.findByCombination(book, pc, ps);

//获得url

String url =getUrl(request);

pageBean.setUrl(url);

request.setAttribute("pageBean", pageBean);

return"f:/jsps/book/list.jsp";

}

//获得url

private String getUrl(HttpServletRequest request) {

String url = request.getRequestURI()+"?"+request.getQueryString();

int index =url.indexOf("&pc=");

if(index!=-1){

url=url.substring(0, index);

}

return url;

}

//获得pc

private int getPc(HttpServletRequest request) {

int pc =1;

String param =request.getParameter("pc");

if(param!=null&&!param.trim().isEmpty()){

try{

pc = Integer.parseInt(param);

}catch(RuntimeException e){};

}

return pc;

}

BookService.java:

//单条件查询

public PageBean findByBname(String bname,int pc,int ps){

try {

return bookDao.findByBname(bname, pc, ps);

} catch (SQLException e) {

throw new RuntimeException(e);

}

}

//组合查询

public PageBean findByCombination(Book criteria,int pc,int ps){

try {

return bookDao.findByCombination(criteria, pc, ps);

} catch (SQLException e) {

throw new RuntimeException(e);

}

}

BookDao.java

//单条件查询

public PageBean findByBname(String bnaem,int pc,int ps) throws SQLException{

List expressions = new ArrayList();

expressions.add(new Expression("bname", "like", "%"+bnaem+"%"));

return findByCriteria(expressions, pc, ps);

}

//组合查询

public PageBean findByCombination(Book criteria,int pc,int ps) throws SQLException{

List exceptions = new ArrayList();

exceptions.add(new Expression("bname","like","%"+criteria.getBname()+"%"));

exceptions.add(new Expression("author","like","%"+criteria.getAuthor()+"%"));

exceptions.add(new Expression("press","like","%"+criteria.getPress()+"%"));

return findByCriteria(exceptions, pc, ps);

}

//通用查询

public PageBean findByCriteria(List expressions,int pc,int ps ) throws SQLException{

//拼接语句和参数

StringBuilder wheresql =new StringBuilder(" where 1=1");

List params = new ArrayList();

for (Expression expression : expressions) {

wheresql.append(" and "+expression.getName())

.append(" ").append(expression.getOperator()).append(" ");

if(!expression.getOperator().equals("is null")){

wheresql.append("?");

params.add(expression.getValue());

}

}

//总记录数

String sql="select count(*) from t_book"+wheresql;

Number number = (Number)qr.query(sql, new ScalarHandler(),params.toArray());

int tr=number.intValue();

//当前页记录

sql ="select * from t_book"+wheresql+" order by orderBy limit ?,?";

params.add((pc-1)*ps);

params.add(ps);

List beanList = qr.query(sql, new BeanListHandler(Book.class),params.toArray());

//创建pagebean

PageBean pageBean = new PageBean();

pageBean.setPc(pc);

pageBean.setPs(ps);

pageBean.setTr(tr);

pageBean.setBeanList(beanList);

return pageBean;

}

pager.jsp:

%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

function _go() {

var pc = $("#pageCode").val();//获取文本框中的当前页码

if(!/^[1-9]\d*$/.test(pc)) {//对当前页码进行整数校验

alert('请输入正确的页码！');

return;

}

if(pc > ${pageBean.tp}) {//判断当前页码是否大于最大页

alert('请输入正确的页码！');

return;

}

location = "${pageBean.url}&pc="+pc;

}

上一页

上一页

最大页，那么begin=最大页-5 end=最大页 --%>

${i }

${i }

...

下一页

下一页

共${pageBean.tp }页

到

页

确定

三、表单提交分页(在页面上设置表单和条件进行查询)

1、工具类：

PageHelper.java:

private Integer pageNum = 1;// 当前页数

private Integer pageSize = 10;// 每页显示条数

private Integer maxPageNum;// 总页数

private Integer dataTotal;// 总记录数

private List> list;//数据集合

>>>>>>>>>>>>

get、set方法

>>>>>>>>>>>>

public void setDataTotal(Integer dataTotal) {

this.dataTotal = dataTotal;

maxPageNum = (dataTotal + pageSize -1) / pageSize;

}

public Integer getLimitStart(Integer currentPage){

if (currentPage != null) {

this.pageNum = currentPage;

}

return (pageNum-1)*pageSize ;

}

BaseController.java:

略：

BaseService.java:

public abstract class BaseService {

public abstract BaseMapper setBaseMapper();

public int delete(Serializable id){

return this.setBaseMapper().deleteByPrimaryKey(id);

}

public int add(T t){

return this.setBaseMapper().insert(t);

}

public T get(Serializable id){

return this.setBaseMapper().selectByPrimaryKey(id);

}

public int update(T t){

return this.setBaseMapper().updateByPrimaryKey(t);

}

public int getCount(BaseQueryObject bqo){

return this.setBaseMapper().count(bqo);

}

public List list(BaseQueryObject bqo){

return this.setBaseMapper().selectAll(bqo);

}

}

BaseMapper.java:

public interface BaseMapper{

int deleteByPrimaryKey(Serializable id);

int insert(T t);

T selectByPrimaryKey(Serializable id);

int updateByPrimaryKey(T t);

int count(BaseQueryObject bqo);

List selectAll(BaseQueryObject bqo);

}

2、使用方法：

ItemController.java:

@RequestMapping("/item")

@Controller

public class ItemController extends BaseController {

@RequiredLogin

@RequestMapping("/manage/list")

public String list(Integer currentPage, Model model, @ModelAttribute("iqm") ItemQueryModel iqm) {

PageHelper ph = new PageHelper();

iqm.setLimitStart(ph.getLimitStart(currentPage));//设置从哪开始

iqm.setPageSize(ph.getPageSize());//设置每页显示条数

List list = itemService.list(iqm);

int count = itemService.getCount(iqm);

ph.setDataTotal(count);//总记录数

ph.setList(list);

model.addAttribute("page", ph);

return "manage/item/list";

}

}

ItemService.java:

@Service

public class ItemService extends BaseService{

@Autowired

private ItemMapper itemMapper;

@Override

public BaseMapper setBaseMapper() {

return itemMapper;

}

public List findItemList(Integer category) {

return itemMapper.findItemList(category);

}

}

ItemMapper.java

public interface ItemMapper extends BaseMapper{

List findItemList(@Param("category") Integer category);

}

ItemMapper.xml

id, category, name, introduction, price, unit, num, pic_old_name, pic_new_name, descp,

state, create_time, create_user, modify_time, modify_user, delete_time, delete_user,

del_state

del_state = 0

AND name LIKE concat('%',#{name},'%')

AND category = #{category}

AND state = #{state}

SELECT

FROM item

ORDER BY id DESC

LIMIT #{limitStart},#{pageSize}

SELECT COUNT(id) FROM item

select

from item

where id = #{id,jdbcType=INTEGER}

delete from item

where id = #{id,jdbcType=INTEGER}

insert into item (id, category, name,

introduction, price, unit,

num, pic_old_name, pic_new_name,

descp, state, create_time,

create_user, modify_time, modify_user,

delete_time, delete_user, del_state

)

values (#{id,jdbcType=INTEGER}, #{category,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR},

#{introduction,jdbcType=VARCHAR}, #{price,jdbcType=INTEGER}, #{unit,jdbcType=VARCHAR},

#{num,jdbcType=INTEGER}, #{picOldName,jdbcType=VARCHAR}, #{picNewName,jdbcType=VARCHAR},

#{descp,jdbcType=VARCHAR}, #{state,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP},

#{createUser,jdbcType=INTEGER}, #{modifyTime,jdbcType=TIMESTAMP}, #{modifyUser,jdbcType=INTEGER},

#{deleteTime,jdbcType=TIMESTAMP}, #{deleteUser,jdbcType=INTEGER}, #{delState,jdbcType=INTEGER}

)

update item

set category = #{category,jdbcType=INTEGER},

name = #{name,jdbcType=VARCHAR},

introduction = #{introduction,jdbcType=VARCHAR},

price = #{price,jdbcType=INTEGER},

unit = #{unit,jdbcType=VARCHAR},

num = #{num,jdbcType=INTEGER},

pic_old_name = #{picOldName,jdbcType=VARCHAR},

pic_new_name = #{picNewName,jdbcType=VARCHAR},

descp = #{descp,jdbcType=VARCHAR},

state = #{state,jdbcType=INTEGER},

create_time = #{createTime,jdbcType=TIMESTAMP},

create_user = #{createUser,jdbcType=INTEGER},

modify_time = #{modifyTime,jdbcType=TIMESTAMP},

modify_user = #{modifyUser,jdbcType=INTEGER},

delete_time = #{deleteTime,jdbcType=TIMESTAMP},

delete_user = #{deleteUser,jdbcType=INTEGER},

del_state = #{delState,jdbcType=INTEGER}

where id = #{id,jdbcType=INTEGER}

SELECT

FROM item

WHERE category = #{category} AND state = 0 AND del_state = 0

ORDER BY id DESC

LIMIT 0,4

3、页面

page.jsp:

当前 ${page.pageNum}/${page.maxPageNum} 页

首页

上一页

下一页

尾页

跳转

$(function(){

//获取当前页

var currentPage = ${page.pageNum};

//获取最大页

var maxPage = ${page.maxPageNum};

//首页

$("#first").click(function(){

$("#currentPage").val(1);

$("#myForm").submit();

});

//上一页

$("#pre").click(function(){

if(currentPage <= 1){

currentPage = 1;

}else{

currentPage--;

}

$("#currentPage").val(currentPage);

$("#myForm").submit();

});

//下一页

$("#next").click(function(){

if(currentPage >= maxPage){

currentPage = maxPage;

}else{

currentPage++;

}

$("#currentPage").val(currentPage);

$("#myForm").submit();

});

//尾页

$("#last").click(function(){

$("#currentPage").val(maxPage);

$("#myForm").submit();

});

//输入跳转页

var v = 1;//默认值为1

$("#skip").keyup(function(){

//把非数字的都替换掉

$(this).val($(this).val().replace(/[^\d]/g,""));

v = parseInt($(this).val(), 10);//转换成十进制数字

});

//跳转

$("#go").click(function(){

if($("#skip").val() == ''){

return;

}

if(v <= 1){

//首页

v = 1;

}

if (v >= maxPage){

//尾页

v = maxPage;

}

$("#currentPage").val(v);

$("#myForm").submit();

});

});

list.jsp:

请选择

新鲜水果

海鲜水产

猪肉牛肉

禽类蛋品

新鲜蔬菜

速冻食品

 搜索

$("[name=category] option[value='${iqm.category}']").attr("selected","selected");

-----------------------------------------------------------------------------------------------------------------------------------------

序号商品类别商品名称单价(元)单位库存状态操作

${i.index+1}

新鲜水果

海鲜水产

猪肉牛肉

禽类蛋品

新鲜蔬菜

速冻食品

${item.name}${item.unit}${item.num}

已上架

未上架

下架

上架

编辑

删除

查看

暂无数据

-------------------------------------------------------------------------------------------------------------------------------------

四、路径作为参数

1、工具类：

BaseController.java:

public class BaseController {

//分页

public Integer pageNum = 1;

public Integer pageCount = 20;//每页显示条数

public Integer maxPageNum;//总页数

public Integer dataTotal;//总记录数

//设置总条数

protected void setDataTotal(int dataTotal){

this.dataTotal = dataTotal ;

maxPageNum = (dataTotal + pageCount -1) / pageCount;

}

//前5后4的算法

protected int[] getBeginAndEnd(Integer currentPage) {

int[] pagelen=new int[2];

int totalPage = maxPageNum;

int begin = 1;

int end = totalPage == 0 ? 1 : totalPage;//如果最大页码数为0,则end=1，否则end=最大页码数,

if((end - begin) <= 9){

//显示十个页码，不足十个不做处理

}else{

if((currentPage - begin) >= 5){

if((currentPage+4) >= totalPage){

end = totalPage;

begin = end - 9;

}else{

end = currentPage + 4;

begin = currentPage -5;

}

}else{

if((currentPage -5) >= 1){

begin = currentPage - 5;

end = begin + 9;

}else{

begin = 1;

end=begin + 9;

}

}

}

pagelen[0] = begin;

pagelen[1] = end;

return pagelen;

}

}

PageVo.java:

public class PageVO {

private Integer start;//从哪开始

private Integer pageCount;//每页显示条数

private Integer type;//类型

private String q; //为查询扩展一个字段

>>>>>>>>>>>>>>>

set\get

>>>>>>>>>>>>>>>

}

2、如何使用工具

HomeController.java:

@RequestMapping(value="/{type}/page/{currentPage}",method=RequestMethod.GET)

public String findItem(@PathVariable("type") int type,@PathVariable("currentPage")Integer currentPage,Model model){

//分页查询

PageVO pageVO = new PageVO();

pageVO.setStart((currentPage-1)*pageCount);//从哪开始

pageVO.setPageCount(pageCount);//每页显示条数

pageVO.setType(type);//模块类型

List list = homeService.getAllByHomeType(pageVO);

model.addAttribute("list", list);

model.addAttribute("item", Home.hometypeMap.get(type));

model.addAttribute("modular", "首页");//模块

//分页信息

//设置总数

Integer count = homeService.getCountByType(type);

setDataTotal(count);

int[] arr = getBeginAndEnd(currentPage);//起始页和结束页

int start = arr[0];//起始页

int end = arr[1];//结束页

model.addAttribute("start", start);

model.addAttribute("end", end);

model.addAttribute("type", type);//把type带到页面,为了分页请求

model.addAttribute("currentPage", currentPage);//把当前页带到页面,为了控制样式

return "home/item";

}

@RequestMapping("/search")

public String search(String q,Integer currentPage,Model model){

//分页查询

PageVO pageVO = new PageVO();

if (currentPage == null) {

currentPage = 1;

}

q = (q == null ? "请输入关键字" : q.trim());

pageVO.setStart((currentPage-1)*pageCount);//从哪开始

pageVO.setPageCount(pageCount);//每页显示条数

pageVO.setQ(q);

List list = homeService.getAllByTitle(pageVO);

model.addAttribute("list", list);

model.addAttribute("q", q);

//分页信息

//设置总数

Integer count = homeService.getCountByTitle(q);

setDataTotal(count);

int[] arr = getBeginAndEnd(currentPage);//起始页和结束页

int start = arr[0];//起始页

int end = arr[1];//结束页

model.addAttribute("start", start);

model.addAttribute("end", end);

model.addAttribute("currentPage", currentPage);//把当前页带到页面,为了控制样式

return "home/query";

}

HomeService.java:

public List getAllByHomeType(PageVO pageVO) {

return homeMapper.getAllByHomeType(pageVO);

}

public Integer getCountByType(int type) {

return homeMapper.getCountByType(type);

}

public List getAllByTitle(PageVO pageVO) {

return homeMapper.getAllByTitle(pageVO);

}

public Integer getCountByTitle(String title) {

return homeMapper.getCountByTitle(title);

}

HomeMapper.java:

public interface HomeMapper {

List getAllByHomeType(PageVO pageVO);

Integer getCountByType(int homeType);

List getAllByTitle(PageVO pageVO);

Integer getCountByTitle(String title);

}

HomeMaper.xml:

SELECT * FROM home WHERE homeType = #{type} AND delState = 0 ORDER BY id DESC LIMIT #{start},#{pageCount};

SELECT COUNT(id) FROM home WHERE homeType = #{homeType} AND delState = 0 ;

SELECT * FROM home

delState = 0

AND title LIKE concat('%',#{q},'%')

ORDER BY id DESC LIMIT #{start},#{pageCount};

SELECT COUNT(id) FROM home

delState = 0

AND title LIKE concat('%',#{value},'%')