高级查询，分页

高级查询，分页：

1：service层：

代码：

 1   @Override
 2     public QueryLimitPageObj queryLimitPage(ProductQueryObj productQueryObj) {
 3         QueryLimitPageObj queryLimitPageObj = new QueryLimitPageObj();
 4       //查询列表
 5         List<Product> products = productDao.chooseQuery(productQueryObj);
 6         queryLimitPageObj.setData(products);
 7         //当前页
 8         Integer currentPage = productQueryObj.getCurrentPage();
 9         queryLimitPageObj.setCurrentPage(currentPage);
10         //每页显示条数
11         Integer pageSize = productQueryObj.getPageSize();
12         queryLimitPageObj.setPageSize(pageSize);
13         //总条数
14         int totalNum = productDao.getTotalNum(productQueryObj);
15         queryLimitPageObj.setTotalNum(totalNum);
16         //总页数
17          queryLimitPageObj.setTotalPage((totalNum + pageSize - 1)/ pageSize);
18         return queryLimitPageObj;
19     }

View Code

2：封装分页对象：

3.封装高级查询对象：

sql :

    <sql id="queryCommon">
        <where>
            <if test="productName!=null and productName!=''">
                AND product_name=#{productName}
            </if>
            <if test="stockNum!=null and stockNum!=''">
                AND stock_num=#{stockNum}
            </if>
            <if test="minSalePrice!=null and minSalePrice!=''">
                AND sale_price>=#{minSalePrice}
            </if>
            <if test="maxSalePrice!=null and maxSalePrice!=''">
                AND sale_price &lt;= #{maxSalePrice}
            </if>
        </where>
    </sql>


    <select id="chooseQuery" parameterType="com.floor.shop.query.ProductQueryObj"
            resultType="com.floor.shop.model.Product">
        SELECT id,product_name productName ,sale_price salePrice,stock_num stockNum,`type` type FROM product
        <include refid="queryCommon"/>
        limit #{startIndex},#{pageSize}
    </select>


    <select id="getTotalNum" parameterType="com.floor.shop.query.ProductQueryObj" resultType="int">
        SELECT COUNT(1) totalNum FROM product
        <include refid="queryCommon"/>
    </select>