xiaoyu19910321
bellus-
2017-02-21 02:35

mybatis中Mapper通用分页PageHelper+自定义sql语句【多表】+排序问题

10
  • pagehelper
  • 分页

项目:maven+eclipse+spring4+mybatis
介绍:使用通用mapper做单表查询很分页。

问题:通过mapper传入pages,rows。添加两个查询条件,然后在根据一个条件排序,同时会通过每条数据的id关联查询第二张表获取数据。现在其他的获取都正常,就是排序失效,后台dubug出查询语句没有问题,数据库执行正常,只是使用mapper的page接收数据时排序失效。

详细代码:

            pojo代码属性包含两个关联实体:
                            @Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@Column
private Integer orders;

@Column
private String name; 

@Column
private Integer propertyIndex;

@Column
private Long productCategory;

@Transient
private ProductCategory category  ;


@Transient
private List<AttributeOption> options =  new ArrayList<>() ;


            service代码
            public Page<Attribute> queryAttributeByPage(String searchValue, Integer page, Integer rows,String order) {

    PageHelper.startPage(page, rows);


    if(StringUtils.isBlank(searchValue)){
        searchValue = null;
    }else{
        searchValue = "%"+searchValue+"%";
    }

    Page<Attribute>  attributes = (Page<Attribute>) this.attributeMapper.queryAttributeByPage(searchValue,order);

    return attributes;
}

            DAO层代码:
                            public List<Attribute> queryAttributeByPage(@Param("searchValue")String searchValue, @Param("order")String order);


            Mapper.xml配置:
                            <!-- 配置resultMap -->
<resultMap id="attributeResultMap" type="cn.dajean.manage.pojo.Attribute" >
    <id column="id" property="id" />
    <result column="product_category" property="productCategory" jdbcType="BIGINT" />
    <association column="product_category" property="category"  select="queryProductCategoryById" autoMapping="true" /> 
   <collection column="id" property="options" select="queryOptionsByAttributeId" autoMapping="true" />

</resultMap>

<!-- 配置查询options延迟加载 -->
<select id="queryProductCategoryById" resultType="cn.dajean.manage.pojo.ProductCategory">
    SELECT * FROM tb_product_category WHERE id = #{productCategory}
</select>
<!-- 配置查询options延迟加载 -->
<select id="queryOptionsByAttributeId" resultType="cn.dajean.manage.pojo.AttributeOption">
    SELECT * FROM tb_attribute_option WHERE attribute = #{id}
</select>

<select id="queryAttributeByPage" resultMap="attributeResultMap">
    select * from tb_attribute 
    <where>
        <if test="searchValue != null">
           and  name like #{searchValue}  
        </if> 

    </where>
    <if test="order != null">
         order by #{order} 
    </if>
</select>

    dubug的sql执行语句:

Preparing: **SELECT count(*) FROM tb_attribute ORDER BY ? **
[DEBUG] ==> Parameters: orders desc(String)
[DEBUG] <== Total: 1
[DEBUG] ==> Preparing: select * from tb_attribute order by ? limit ?,?
[DEBUG] ==> Parameters: orders desc(String), 0(Integer), 20(Integer)
[DEBUG] ====> Preparing: SELECT * FROM tb_product_category WHERE id = ?
DEBUG] ====> Parameters: 14(Long)
[DEBUG] <==== Total: 1
以下关联查询,略
使用结果:可以正常分页,可以正常筛选条件,查询语句输出也正常。就是page接受结果中没有排序?这是为什么??大神来解答一下 。如果输自己写查询语句的就可以面回答了,那个我会写,这是项目需要用的mapper,帮助解决者得悬赏

  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

2条回答