动态sql实现分页查询

1.创建实体类对象需要查询的条件com.rl.ecps.model.QueryCondition

private Long brandId;
　　private Short auditStatus;
　　private Short showStatus;
　　private String itemName;
　　private Integer pageNo;
　　private Integer startNum;
　　private Integer endNum;

 

2.查询语句

查询条数：

 

<select id="selectItemByConditionCount" parameterType="com.rl.ecps.model.QueryCondition" resultType="int">
      select count(*) from eb_item t
          <where>
              <if test="brandId != null">
                  t.brand_id = #{brandId}
              </if>
              <if test="auditStatus != null">
                  and t.audit_status = #{auditStatus}
              </if>
              <if test="showStatus != null">
                  and t.show_status = #{showStatus}
              </if>
              <if test="itemName != null and itemName != ''">
                  and t.item_name like '%${itemName}%'
              </if>
          </where>
  </select>

分页查询：

<select id="selectItemByCondition" parameterType="com.rl.ecps.model.QueryCondition" resultMap="BaseResultMap">
  select *
  from (select rownum rw, a.*
          from (
          select * from eb_item t
          <where>
              <if test="brandId != null">
                  t.brand_id = #{brandId}
              </if>
              <if test="auditStatus != null">
                  and t.audit_status = #{auditStatus}
              </if>
              <if test="showStatus != null">
                  and t.show_status = #{showStatus}
              </if>
              <if test="itemName != null and itemName != ''">
                  and t.item_name like '%${itemName}%'
              </if>
          </where>
          order by t.item_id desc
     <![CDATA[
          ) a
         where rownum < #{endNum}) b
 where b.rw > #{startNum}
 ]]>
  </select>

 

3.分页实体类

public class Page {

    /**
    * 当前页码 （已知）
    */
    private int pageNo = 1;

    /**
    * 每页记录数（已知）
    */
    private int pageSize = 5;

    /**
    * 指定的查询条件下的总记录数
    */
    private int totalCount = 0;

    /**
    * 指定的查询条件下的总页数
    */
    private int totalPage = 1;

    /**
    * 查询的开始行号
    */
    private int startNum = 0;

    /**
    * 查询的结束行号
    */
    private int endNum = 0;

    /**
    * 查询的结果集
    */
    private List<?> list;

/**
     * totalCount        pageSize      totalPage
     *     0                10            1
     *        100                10            10
     *         92                10            10    
     * @return
     */
    public int getTotalPage() {
        totalPage = totalCount/pageSize;
        if(totalCount == 0 || totalCount%pageSize != 0){
            totalPage++;
        }
        return totalPage;
    }
public int getStartNum() {
        return (pageNo - 1) * pageSize;
    }

public int getEndNum() {
        return pageNo * pageSize + 1;
    }

 

4.service与control

service实现：

public Page selectItemByQC(QueryCondition qc) {
        //查询当前的查询条件下的总记录数
        int totalCount = itemDao.selectItemByConditionCount(qc);
        //创建page对象
        Page page = new Page();
        page.setPageNo(qc.getPageNo());
        page.setTotalCount(totalCount);
        //计算startNum和endNum
        Integer startNum = page.getStartNum();
        Integer endNum = page.getEndNum();
        //把值设置给sql的查询对象
        qc.setStartNum(startNum);
        qc.setEndNum(endNum);
        //查询结果集
        List<EbItem> itemList = itemDao.selectItemByCondition(qc);
        page.setList(itemList);
        return page;
    }

 

 

controller类：

@RequestMapping("/listItem.do")
    public String listItem(QueryCondition qc, Model model){
        List<EbBrand> bList = brandService.selectBrandAll();
        model.addAttribute("bList", bList);
        if(qc.getPageNo() == null){
            qc.setPageNo(1);
        }
        Page page = itemService.selectItemByQC(qc);
        model.addAttribute("page", page);
        //把qc写回去，目的是回显
        model.addAttribute("qc", qc);
        return "item/list";
    }