分页插件的使用

    在项目中经常要用到分页功能，一种办法是自己去实现，另一种比较简单的办法是使用别人写好的插件。比如paginator或者pageHelper，下面介绍两种插件的使用。

mybatis-paginator

pom.xml

<!-- mybatis分页插件 -->

<dependency>

<groupId>com.github.miemiedev</groupId>

<artifactId>mybatis-paginator</artifactId>

<version>1.2.17</version>

</dependency>

mybatis.xml

<plugins>

<!-- 分页插件配置 -->

<plugin interceptor="com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor">

<property name="dialectClass" value="com.github.miemiedev.mybatis.paginator.dialect.MySQLDialect"/>

</plugin>

</plugins>

service.java

public class SaleChanceService {

@Autowired

private SaleChanceDao saleChanceDao;

public Map<String, Object> selectForPage(SaleChanceQuery query) {

// 构建一个分页对象

Integer page = query.getPage();

if (page == null) {

page = 1;

}

Integer pageSize = query.getRows();

if (pageSize == null) {

pageSize = 10;

}

String sort = query.getSort();

if (StringUtils.isBlank(sort)) {

sort = "id.desc"; // 数据库字段.desc/asc

}

PageBounds pageBounds = new PageBounds(page, pageSize, Order.formString(sort));

// 查询

List<SaleChance> saleChances = saleChanceDao.selectForPage(query, pageBounds);

PageList<SaleChance> result = (PageList<SaleChance>) saleChances;

// 返回分页结果

Paginator paginator = result.getPaginator();

Map<String, Object> map = new HashMap<>();

map.put("paginator", paginator);

map.put("rows", result);

map.put("total", paginator.getTotalCount());

return map;

}

}

dao

public interface SaleChanceDao {

// @Select("select * from t_sale_chance where is_valid = 1")

@SelectProvider(type = SaleChanceProvider.class, method="selectForPage")

List<SaleChance> selectForPage(SaleChanceQuery query, PageBounds pageBounds);

}

拼接sql语句

public class SaleChanceProvider {

private static Logger logger = LoggerFactory.getLogger(SaleChanceProvider.class);

public String selectForPage(final SaleChanceQuery query) {

String sql = new SQL(){{

SELECT("t.id, t.customer_name, t.overview, t.link_man, t.link_phone, t.create_man, "

+ " t.create_date, t.assign_man,t.assign_time,t.state");

FROM("t_sale_chance t");

WHERE("is_valid = 1");

if (StringUtils.isNoneBlank(query.getCustomerName())) {

AND().WHERE("customer_name like '%"+ query.getCustomerName() +"%'");

}

if (StringUtils.isNoneBlank(query.getOverview())) {

AND().WHERE("overview like '%"+ query.getOverview() +"%'");

}

if (StringUtils.isNoneBlank(query.getCreateMan())) {

AND().WHERE("create_man like '%"+ query.getCreateMan() +"%'");

}

if (query.getState() != null) {

AND().WHERE("state = #{state}");

}

}}.toString();

logger.debug("打印的sql是：{}", sql);

return sql;

}

}

mybatis-pageHelper

pom.xml

<!-- pageHelper 坐标添加 -->

<dependency>

<groupId>com.github.pagehelper</groupId>

<artifactId>pagehelper</artifactId>

<version>4.1.0</version>

</dependency>

controller

@ResponseBody

@RequestMapping("queryAccountsByParams")

public Map<String, Object> queryAccountsByParams(AccountQuery accountQuery){

accountQuery.setUserId(1);

return accountService.queryAccountsByParams(accountQuery);

}

service

public Map<String, Object> queryAccountsByParams(AccountQuery accountQuery){

PageInfo<Account> pageInfo= queryForPage(accountQuery);

Map<String, Object> map=new HashMap<String, Object>();

map.put("total", pageInfo.getTotal());

map.put("rows", pageInfo.getList());

return map;

}

public PageInfo<T> queryForPage(BaseQuery baseQuery){

PageHelper.startPage(baseQuery.getPage(), baseQuery.getRows());

return new PageInfo<T>(baseDao.queryForPage(baseQuery));

}

BaseQuery.java

public class BaseQuery {

private Integer page=1;

private Integer rows=10;

public Integer getPage() {

return page;

}

public void setPage(Integer page) {

this.page = page;

}

public Integer getRows() {

return rows;

}

public void setRows(Integer rows) {

this.rows = rows;

}

}

dao

public List<T> queryForPage(BaseQuery baseQuery);

mapper.xml

<!-- if 标签 参数校验 -->

<select id="queryForPage" parameterType="accountQuery"

resultType="account">

select

<include refid="account_sql" />

from t_account

<where>

user_id=#{userId}

<if test="aname !=null and aname !=''">

and aname like concat('%',#{aname},'%')

</if>

<if test="type !=null and type !=''">

and type=#{type}

</if>

<if test="time!=null and time!=''">

<![CDATA[

and create_time <=#{time}

]]>

</if>

</where>

</select>