Mybatis分页搜索与PageHelper

分页搜索

分页就是利用sql的limit语法，只截取数据表的一段并传至前端显示。

PageInfo

public class PageInfo<T> {
    private List<T> list;
    private int size;
    private int totalPage;
    private int totalCount;
    private int currentPage;

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public int getSize() {
        return size;
    }

    public void setSize(int size) {
        this.size = size;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    @Override
    public String toString() {
        return "PageInfo{" +
               "list=" + list +
               ", size=" + size +
               ", totalPage=" + totalPage +
               ", totalCount=" + totalCount +
               ", currentPage=" + currentPage +
               '}';
    }
}

DAO

UserDao.java

List<User> findAllByName(@Param("start") int start,@Param("name") String name);

int getCountByName(@Param("name")String name);

user.xml

<select id="findAllByName" resultType="user">
    select * from user
    <if test="name != null and name != ''">
        where name like concat('%',#{name},'%')
    </if>
    limit #{start},5;
</select>
<select id="getCountByName" parameterType="String" resultType="int">
    select count(*) from user
    <if test="name != null and name != ''">
        where name = #{name}
    </if>
</select>

Service

    @Override
    public PageInfo<User> findAllByName(int currentPage, String name)
    {
        PageInfo<User> pageInfo = new PageInfo<>();
        int count = userDao.getCountByName(name);
        int realCurrentPage = currentPage < 0 ? 0 : currentPage > count / 5 ? count / 5 : currentPage;
        pageInfo.setSize(5);
        pageInfo.setTotalPage(count / 5);
        pageInfo.setCurrentPage(realCurrentPage);
        pageInfo.setTotalCount(count);
        pageInfo.setList(userDao.findAllByName(realCurrentPage * 5, name));
        return pageInfo;
    }

Controller

@RequestMapping(path = "findAll.do")
public ModelAndView listUser(@RequestParam(defaultValue = "0") int currentPage, @RequestParam(defaultValue = "") String name)
{
    PageInfo<User> pageInfo = userService.findAllByName(currentPage, name);
    PageInfo<User> pageInfo = new PageInfo<>(users);
    ModelAndView mv = new ModelAndView();
    mv.addObject("users", pageInfo.getList());
    mv.addObject("currentPage",pageInfo.getCurrentPage());
    mv.addObject("totalPage",pageInfo.getTotalPage());
    mv.setViewName("user-list");
    return mv;
}

JSP

<c:forEach items="${users}" var="user">
<tr>
	<td><input id="ids" name="ids" type="checkbox" value="${user.id}"></td>
	<td>${user.id}</td>
	<td>${user.name}</td>
	<td>${user.password}</td>
	<td>${user.age}</td>
	<td class="text-center">
		<a href="/user/toUpdate.do?id=${user.id}" class="btn bg-olive btn-xs">更新</a>
		<a href="/user/deleteById.do?id=${user.id}" class="btn bg-olive btn-xs">删除</a>
	</td>
</tr>
</c:forEach>

PageHelper

PageHelper是一个分页插件，使用十分简单。支持十几个数据库，并且可以与Spring集成。

pom.xml

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>最新版本</version>
</dependency>

spring配置文件

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  <!-- 其他配置 -->
  <property name="plugins">
    <array>
      <bean class="com.github.pagehelper.PageInterceptor">
        <property name="properties">
          <!--使用下面的方式配置参数，一行配置一个 -->
          <value>
              rowBoundsWithCount=true
              offsetAsPageNum=true
              pageSizeZero=true
              reasonable=true
          </value>
        </property>
      </bean>
    </array>
  </property>
</bean>

使用

Mapper

<select id="selectAll" parameterType="String" resultType="user">
    select * from user
    <if test="name != null and name != ''">
        where name = #{name}
    </if>
</select>

Service

@Override
public List<User> listUser(int currentPage, String name)
{
    PageHelper.startPage(currentPage, 5, true, true, false);
    List<User> users = userDao.selectAll(name);
    return users;
}

Controller

@RequestMapping(path = "findAll.do")
public ModelAndView listUser(@RequestParam(defaultValue = "0") int currentPage, @RequestParam(defaultValue = "") String name)
{
    List<User> users = userService.listUser(currentPage, name);
    PageInfo<User> pageInfo = new PageInfo<>(users);
    ModelAndView mv = new ModelAndView();
    mv.addObject("users", pageInfo.getList());
    mv.addObject("currentPage",pageInfo.getPageNum());
    mv.addObject("totalPage",pageInfo.getPages());
    mv.setViewName("user-list");
    return mv;
}