Mybatis手写分页，非常灵活

先写一个实体类，封装查询条件

public class QueryBase {

    private int page = 1;

    private int pageSize = Constant.PAGING_SIZE;

    private String sortBy;

    private String sortMethod;

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public String getSortBy() {
        return sortBy;
    }

    public void setSortBy(String sortBy) {
        this.sortBy = sortBy;
    }

    public String getSortMethod() {
        return sortMethod;
    }

    public void setSortMethod(String sortMethod) {
        this.sortMethod = sortMethod;
    }

    public String getLimitString() {

        if(pageSize > 0 & page > 0) {
            return ((page * pageSize) - pageSize) + "," + pageSize;
        }
        return "";
    }
}

再写一个实体类，用来接收查询的结果

public class QueryResult {
    private int page;
    private int pageSize;
    private int totalRecord;

    private Object data;

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getTotalRecord() {
        return totalRecord;
    }

    public void setTotalRecord(int totalRecord) {
        this.totalRecord = totalRecord;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }
}

订单表的实体类

@JsonIgnoreProperties({
        "channelStoreId"
})
public class Order {

    private Integer id;
    private String outerId;
    private String orderSn;
    private int storeId;
    private int channelStoreId;
    private String storeName;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getOuterId() {
        return outerId;
    }

    public void setOuterId(String outerId) {
        this.outerId = outerId;
    }

    public String getOrderSn() {
        return orderSn;
    }

    public void setOrderSn(String orderSn) {
        this.orderSn = orderSn;
    }

    public int getStoreId() {
        return storeId;
    }

    public void setStoreId(int storeId) {
        this.storeId = storeId;
    }

    public int getChannelStoreId() {
        return channelStoreId;
    }

    public void setChannelStoreId(int channelStoreId) {
        this.channelStoreId = channelStoreId;
    }

    public String getStoreName() {
        return storeName;
    }

    public void setStoreName(String storeName) {
        this.storeName = storeName;
    }
}

假设要根据门店id和订单状态查询订单列表

public class QueryOrder extends QueryBase{

    private Integer storeId;
    private List<Integer> orderState;

    public Integer getStoreId() {
        return storeId;
    }
    public void setStoreId(Integer storeId) {
        this.storeId = storeId;
    }
    public List<Integer> getOrderState() {
        return orderState;
    }
    public void setOrderState(List<Integer> orderState) {
        this.orderState= orderState;
    }

}

再写dao

public interface OrderMapper {

    List<Order> findOrderList(QueryOrder queryOrder);
    Integer findOrderCount(QueryOrder queryOrder);

}

xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

<mapper namespace="com.mappers.OrderMapper" >

    <select id="findOrderList" parameterType="QueryOrder" resultMap="orderResult">
        SELECT * FROM `order`
        WHERE 1=1
        <if test="storeId != null and storeId > 0"> AND store_id = #{storeId}</if>
        <if test="orderState != null"> AND order_state IN
            <foreach item="item" index="index" collection="orderState" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="sortBy != null">
            ORDER BY ${sortBy} <if test="sortMethod != null">${sortMethod}</if>
        </if>
        <if test="pageSize > 0 and page > 0">
            LIMIT ${limitString}
        </if>;
    </select>

    <select id="findOrderCount" parameterType="QueryTrade" resultType="Integer">
        SELECT COUNT(0) FROM order
        WHERE 1=1
        <if test="storeId != null and storeId > 0"> AND store_id = #{storeId}</if>
        <if test="orderState != null"> AND order_state IN
            <foreach item="item" index="index" collection="orderState" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>

    <resultMap type="com.entity.Order" id="orderResult">
        <id property="id" column="id"/>
        <result property="outerId" column="outer_id"/>
        <result property="orderSn" column="order_sn"/>
        <result property="storeId" column="store_id"/>
        <result property="storeName" column="store_name"/>
    </resultMap>

</mapper>

再写接口

@Service
public class OrderServiceImpl implements OrderService{

    @Autowired
    OrderMapper orderMapper;


    public QueryResult queryOrder(QueryOrder queryOrder) {

        QueryResult result = new QueryResult();
        result.setPage(queryOrder.getPage());
        result.setPageSize(queryOrder.getPageSize());
        result.setTotalRecord(orderMapper.findOrderCount(queryOrder));
        result.setData(orderMapper.findOrderList(queryOrder));

        return result;
    }

}

最后写一个controller

@Controller
@RequestMapping("/order")
public class OrderController {

    @Autowired
    OrderService orderService;

    // 查询订单详情
    @RequestMapping(value = "list", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
    @ResponseBody
    public Object getOrderList(HttpServletRequest request) {

        Integer page, pageSize, status, storeId;

        QueryOrder queryOrder = new QueryOrder();
        List<Integer> orderState = new ArrayList<Integer>();

        String sortBy = request.getParameter("sortBy");
        String sortMethod = request.getParameter("sortMethod");

        storeId = NumberUtils.toInt(request.getParameter("storeId"));
        status = NumberUtils.toInt(request.getParameter("status"));
        page = NumberUtils.toInt(request.getParameter("page"));
        pageSize = NumberUtils.toInt(request.getParameter("pageSize"));

        if (StringUtils.isBlank(sortBy))
            sortBy = "ctime";

        if (StringUtils.isBlank(sortMethod))
            sortMethod = "desc";

        queryOrder.setSortBy(sortBy);
        queryOrder.setSortMethod(sortMethod);
        queryOrder.setStoreId(storeId);
        queryOrder.setPage(page);
        queryOrder.setPageSize(pageSize);

        ResponseMsg msg = new ResponseMsg();

        if (status == 1) {
            orderState.add(1);
        } else if (status == 2) {
            orderState.add(2);
            orderState.add(3);
        } else if (status == 0) {
            orderState.add(1);
            orderState.add(2);
            orderState.add(3);
        } else {
            msg.setMsg("暂无数据");
            return msg;
        }

        queryOrder.setStatus(orderState);

        QueryResult result = orderService.queryOrder(queryOrder);

        msg.setMsg("ok");
        msg.setData(result);
        msg.setCode(0);

        return msg;
    }

}

这样，一个简单的分页接口已经完成，有什么需要改进的地方欢迎大家指出，谢谢！