java mybatis分页查询语句_mybatis分页查询的实现(一)

一、总结了mybatis中五种不同实现分页查询的方法

UserMapper.java接口文件

public interface UserMapper {

//分页查询

public List selectForPage1(int startIndex,int pageSize);

public List selectForPage2(Map map);

public Integer selectCount();

public List selectForPage3(PageBean pageBean);

//分页加模糊查询

public Integer selectCount2(String keywords);

public List selectForPage4(Map map);

}

工具类PageBean.java

public class PageBean {

private Integer currentPage;

private Integer startIndex;

private Integer pageSize=5;

private Integer totalCount;

private Integer totalPage;

public Integer getCurrentPage() {

return currentPage;

}

public void setCurrentPage(Integer currentPage) {

this.currentPage = currentPage;

this.startIndex=(this.currentPage-1)*this.pageSize;

}

public Integer getPageSize() {

return pageSize;

}

public void setPageSize(Integer pageSize) {

this.pageSize = pageSize;

}

public Integer getTotalCount() {

return totalCount;

}

public void setTotalCount(Integer totalCount) {

this.totalCount = totalCount;

//计算总页数

this.totalPage=(int)Math.ceil((this.totalCount*1.0/this.pageSize));

}

public Integer getTotalPage() {

return totalPage;

}

public void setTotalPage(Integer totalPage) {

this.totalPage = totalPage;

}

public Integer getStartIndex() {

return startIndex;

}

public void setStartIndex(Integer startIndex) {

this.startIndex = startIndex;

}

}

UserMapper.xml文件

其中查询5是模糊加分页查询语句

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select * from user limit #{param1},#{param2}

select * from user limit #{startIndex},#{pageSize}

select * from user

select count(*) from user

select * from user limit #{startIndex},#{pageSize}

select * from user

where name like "%"#{keywords}"%" or address like "%"#{keywords}"%"

limit #{startIndex},#{pageSize}

select count(*) from user where name like "%"#{value}"%" or address like "%"#{value}"%"

测试test

其中方法6是模糊加分页查询测试

public class myTest {

SqlSession session = MyBatisUtils.openSession();

UserMapper userMapper = session.getMapper(UserMapper.class);

@Test

public void selectForPage1() {

int currentPage=1;

int pageSize=5;

List selectForPage = userMapper.selectForPage1((currentPage-1)*pageSize, pageSize);

for (User user : selectForPage) {

System.out.println(user);

}

MyBatisUtils.closeSession(session);

}

@Test

public void selectForPage2() {

int currentPage=1;

int pageSize=5;

Map map=new HashMap<>();

map.put("startIndex", (currentPage-1)*pageSize);

map.put("pageSize", pageSize);

List selectForPage = userMapper.selectForPage2(map);

for (User user : selectForPage) {

System.out.println(user);

}

MyBatisUtils.closeSession(session);

}

@Test

public void selectForPage3() {

int currentPage=1;

int pageSize=5;

/**

* 参数1：开始条 偏移量,下标

* 参数2：参数总条数

*/

RowBounds rowBounds = new RowBounds((currentPage-1)*pageSize, pageSize);

//使用mybatis里面提供的api去写的

List list = session.selectList("com.gx.mapper.UserMapper.selectAll", null, rowBounds);

for (User user : list) {

System.out.println(user);

}

MyBatisUtils.closeSession(session);

}

@Test

public void selectForPage4() {

Integer count = userMapper.selectCount();

System.out.println(count);

int currentPage=1;

int pageSize=5;

Map map=new HashMap<>();

map.put("startIndex", (currentPage-1)*pageSize);

map.put("pageSize", pageSize);

List list = userMapper.selectForPage2(map);

for (User user : list) {

System.out.println(user);

}

System.out.println("当前第"+currentPage+"页，共"+count+"条");

MyBatisUtils.closeSession(session);

}

@Test

public void selectForPage5() {

PageBean bean = new PageBean();

bean.setCurrentPage(1);

bean.setPageSize(5);

//查询总条数

Integer count = userMapper.selectCount();

//放到pageBean

bean.setTotalCount(count);

List list = userMapper.selectForPage3(bean);

for (User user : list) {

System.out.println(user);

}

System.out.println("当前第"+bean.getCurrentPage()+"页，共"+count+"条");

MyBatisUtils.closeSession(session);

}

@Test

public void selectForPage6() {

String keywords="云6";

PageBean bean = new PageBean();

bean.setCurrentPage(1);

bean.setPageSize(5);

//查询总条数

Integer count = userMapper.selectCount2(keywords);

//放到pageBean

bean.setTotalCount(count);

Map map = new HashMap<>();

map.put("startIndex", bean.getStartIndex());

map.put("pageSize", bean.getPageSize());

map.put("keywords", keywords);

List list = userMapper.selectForPage4(map);

for (User user : list) {

System.out.println(user);

}

System.out.println("当前第"+bean.getCurrentPage()+"页，共"+count+"条");

MyBatisUtils.closeSession(session);

}

}