当Mybatis的mapper文件传入的order by 为动态参数说的时候发现排序无法生效:
像下面这样,在choose when中的order by后的参数是用预编译的方式,用的是#号,这样是可以防止sql注入的问题,但是在传入order by参数的时候无法解析:
<select id="feescaleList" resultType="com.hasagei.modules.mebespoke.entity.HasageiMeBespoke" parameterType="com.hasagei.modules.mebespoke.entity.HasageiMeBespoke">
SELECT
A.ID AS id,
TO_CHAR(A.BESPOKE_DATE,'yyyy-mm-dd') AS bsepokeDate,
A.USER_ID AS userId,
A.BESPOKE_DATE AS bespokeDate,
A.BESPOKE_STATUS bespokeStatus,
A.PROJECT_ID AS projectId,
A.PERIOD_START AS periodStart,
A.PERIOD_END AS periodEnd,
A.FEESCALE_MONEY AS feescaleMoney,
A.FEESCALE_NAME AS feescaleName,
A.PAYMENT_TIME AS paymentTime,
A.PAYMENT_MONEY AS paymentMoney,
B.IDENTITY_CARD AS identityCard,
B.REALNAME AS realname,
B.SJ AS sj,
B.GZZH AS gzzh,
B.PHOTOELECTRIC_CARD AS photoelectricCard,
B.SEX AS sex,
B.BIRTH_DATE AS birthDate,
B.STUDENT_ID AS studentId,
B.EXAMINE_NUMBER AS examineNumber,
B.DEPARTMENT AS department,
B.FACULTY AS faculty,
C.MEC_NAME AS mecName
FROM
TSINGHUA_ME_BESPOKE A LEFT JOIN TSINGHUA_USERINFO B ON A.USER_ID=B.ID
LEFT JOIN MEC_ITEM C ON A.PROJECT_ID=C.MEC_NUMBER
WHERE 1=1
<if test='bespokeDateGteJ != null and bespokeDateLteJ != null '>
<if test='bespokeDateLteJ != "" and bespokeDateLteJ != ""'>
AND A.PAYMENT_TIME <![CDATA[<=]]> to_date(#{bespokeDateLteJ,jdbcType=DATE},'yyyy-MM-dd HH24:MI:SS')
AND A.PAYMENT_TIME <![CDATA[>=]]> to_date(#{bespokeDateGteJ,jdbcType=DATE},'yyyy-MM-dd HH24:MI:SS')
</if>
</if>
<choose>
<when test='orderByFiled!=null and orderByFiled!="" and orderBySe!=null and orderBySe!=""'>
ORDER BY #{orderByFiled} #{orderBySe}
</when>
<otherwise>
ORDER BY A.PAYMENT_TIME DESC
</otherwise>
</choose>
</select>
最简单的解决办法是将#换为$,但是这样会有sql注入的问题