mybatis 动态排序
public class Pagination { // 当前页 private Integer page = 1; // 一页显示条数 private Integer limit = 10; // 排序字段 private String field; // 排序类型(desc(降序)、asc(升序)、null(空对象,默认排序)) private String order; }
Map<String, Object> map = new HashMap<String, Object>(); Integer page = pagination.getPage(); Integer limit = pagination.getLimit(); map.put("start", (page-1)*limit); map.put("pageNo", limit); map.put("field", pagination.getField()); map.put("order", pagination.getOrder());
<select id="queryMarkVoluntarilyList" parameterType="map" resultType="MarkVoluntarilyDto"> select a.*, FROM_UNIXTIME(a.createTime,'%Y-%m-%d %H:%i:%s') AS createTimeStr from web_ei_voluntarily a <where> <if test="websiteName !=null and websiteName !=''"> a.websiteName like concat(#{websiteName},'%') </if> <if test="websiteUrl !=null and websiteUrl !=''"> and a.websiteUrl like concat(concat('%',#{websiteUrl}),'%') </if> <if test="companyName !=null and companyName !=''"> and a.companyName like concat(concat('%',#{companyName}),'%') </if> <if test="deptCode !=null and deptCode !=''"> <if test="containSub == '1'.toString()"> and a.deptCode like concat(#{deptCode},'%') </if> <if test="containSub == '0'.toString()"> and a.deptCode = #{deptCode} </if> </if> <if test="registrationCode !=null and registrationCode !=''"> and a.registrationCode = #{registrationCode} </if> <if test="creditCode !=null and creditCode !=''"> and a.creditCode like concat(#{creditCode},'%') </if> </where> <choose> <when test="field !=null and field !=''"> ORDER BY ${field} ${order} </when> <otherwise> ORDER BY a.createTime desc </otherwise> </choose> limit #{start}, #{pageNo} </select>
MyBatis想实现动态排序,即自己传排序字段以及排序方式,但是按正常传参会导致查询失败。
<select id="getUser" resultMap="userMapper"> SELECT * FROM XXX WHERE age = 20 ORDER BY #{sortField} #{sortType} </select>
因为上面的sql会被处理成如下,即参数会被加上引号当字符串处理。
SELECT * FROM XXX WHERE age = 20 ORDER BY 'sortField' 'sortType'
解决方法:
使用${}方式传参,不要使用#{}方式传参,即:
<select id="getUser" resultMap="userMapper"> SELECT * FROM XXX WHERE age = 20 ORDER BY ${sortField} ${sortType} </select>
但是要注意,正式因为${}是直接拼接SQL的,所以使用上要特别注意,因为会有SQL注入风险。而#{}是有预编译处理的,会通过占位符的形式,进行参数化,所以可以防止SQL注入的风险。
故乡明