MyBatis自定义参数排序
注意事项(很容易掉坑):
1. 正常传参使用#{}来接收参数,比如:m.grade = #{memberOrderBean.memberGrade},但是要排序的话order by后面需要使用${}符号,否则怎么排序失效。
2. 当使用了@Param修饰参数的时候,xml里面的parameterType不需要写。
Dao
/**
* 会员层级和会员购买详情搜索(共用该方法)
* @author jacklin
* @since 2020年09月18日
*/
List searchMemberGradeDetailList(Page page, @Param("memberOrderBean") MemberOrderBean memberOrderBean);
xml
SELECT m.grade memberGrade, m.pin, m.name memberName, m.phone, m.first_shopping_time, s.goods_code, s.goods_name, m.lately_buy_date,
m.shop_type, m.tag, m.tops_six, m.scm_money_six, m.scm_counts orderCounts, m.scm_money actualPay from member m
join sc_order s on m.name = s.sc_member_name
AND m.pin = #{memberOrderBean.pin}
AND m.grade = #{memberOrderBean.memberGrade}
AND m.name = #{memberOrderBean.memberName},
AND m.phone = #{memberOrderBean.phone}
AND m.tag = #{memberOrderBean.tag}
AND s.goods_code = #{memberOrderBean.goodsCode}
AND s.shop_type = #{memberOrderBean.shopType}
AND m.lately_buy_date between #{memberOrderBean.latelyBuyDate_begin} and #{memberOrderBean.latelyBuyDate_end}
AND s.goods_code not REGEXP 'UYCJ|UYSB|UYHC|UYBC|US|WPK|S1|S2|S3|CHM'
GROUP BY m.name
//注意排序使用${}符号,并且在GROUP BY 之后
order by ${memberOrderBean.column} ${memberOrderBean.order}
排序原来是(错误的写法):
order by #{memberOrderBean.column}
应该是改成这样(正确的写法):
order by ${memberOrderBean.column}