MyBatis如何写Mapper才能进行使用升序或者降序的模糊查询切换

Question

我想实现根据用户进行模糊查询，可以通过传参选择使用asc和desc进行查询，但是我在mapper文件里面配置#{sort}，一直报错，请问怎么解决？

@RequestMapping(value = "/v1/student/name", method={ RequestMethod.POST, RequestMethod.GET })
    public ResponseData selectByName(
            @RequestParam(value="name", required=false,defaultValue = "") String name,
            @RequestParam(value="sort", required=false,defaultValue = "desc") String sort,
            @RequestParam(value="start", required=false,defaultValue = "0") Integer start,
            @RequestParam(value="dataLength", required=false,defaultValue = "100") Integer dataLength){

        ResponseData responseData = new ResponseData();
        JSONObject jsonResultObject = new JSONObject();
        responseData.setCode("200");
        responseData.setStatus("success");
        responseData.setMessage("tenantMessage");
        responseData.setDetail("添加tenantMessage");
        List<Student> studentList = studentService.selectByName(name, sort, start, dataLength);
        Integer studentCount = studentService.getCount();
        jsonResultObject.put("student", studentList);
        jsonResultObject.put("count", studentCount);
        responseData.setData(jsonResultObject);
        return responseData;
    }

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.spring.inter.mapper.Mapper.StudentMapper">
    <resultMap id="BaseResultMap" type="cn.spring.inter.entity.Student">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="age" jdbcType="INTEGER" property="age" />
        <result column="sex" jdbcType="VARCHAR" property="sex" />
        <result column="phone" jdbcType="VARCHAR" property="phone" />
        <result column="address" jdbcType="VARCHAR" property="address" />
    </resultMap>
    <sql id="Base_Column_List">
      id, name, age, sex, phone, address
    </sql>
    <!-- 查询所有学生 -->
    <select id="getAll"  resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM student order by id desc limit #{start}, #{dataLength}
    </select>
    <!-- 查询学生数量 -->
    <select id="getCount" resultType="java.lang.Integer">
        SELECT count(id) FROM student
    </select>
    <!-- 添加一个学生 -->
    <insert id="addOne" parameterType="Student"
            useGeneratedKeys="true" keyProperty="id">
      INSERT INTO student (name, age, sex, phone, address)
      VALUES (#{name}, #{age}, #{sex}, #{phone}, #{address})
    </insert>

    <!--根据用户名查询-->
    <select id="selectByName" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM student WHERE name = #{name}
        order by id #{sort} limit #{start}, #{dataLength}
    </select>
</mapper>

——————————————————————

把mapper文件改成如下，继续报错中

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.spring.inter.mapper.Mapper.StudentMapper">
    <resultMap id="BaseResultMap" type="cn.spring.inter.entity.Student">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="age" jdbcType="INTEGER" property="age" />
        <result column="sex" jdbcType="VARCHAR" property="sex" />
        <result column="phone" jdbcType="VARCHAR" property="phone" />
        <result column="address" jdbcType="VARCHAR" property="address" />
    </resultMap>
    <sql id="Base_Column_List">
      id, name, age, sex, phone, address
    </sql>
    <!-- 查询所有学生 -->
    <select id="getAll"  resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM student order by id desc limit #{start}, #{dataLength}
    </select>
    <!-- 查询学生数量 -->
    <select id="getCount" resultType="java.lang.Integer">
        SELECT count(id) FROM student
    </select>
    <!-- 添加一个学生 -->
    <insert id="addOne" parameterType="Student"
            useGeneratedKeys="true" keyProperty="id">
      INSERT INTO student (name, age, sex, phone, address)
      VALUES (#{name}, #{age}, #{sex}, #{phone}, #{address})
    </insert>

    <!--根据用户名查询-->
    <select id="selectByName" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM student WHERE name = #{name}
        <choose>
            <when test="sort == desc">
                order by id DESC limit #{start}, #{dataLength}
            </when>
            <when test="sort == asc">
                order by id ASC limit #{start}, #{dataLength}
            </when>
            <otherwise>
                order by id DESC limit #{start}, #{dataLength}
            </otherwise>
        </choose>
    </select>
</mapper>

Answer 1

<!--根据用户名查询-->
<select id="selectByName" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List" />
    FROM student WHERE name = #{name}
    ORDER BY id ${sort} limit #{start}, #{dataLength}
</select>

【特别注意】此处SQL的两个参数，一定要使用${ }接收，而不能使用#{ }。否则，不会到达预期的效果。

${ }不会对传入的字符串进行处理。比如：传入的是 desc，${ }处理后的效果是ORDER BY pls.event_time desc，可以实现按照 pls.event_time字段倒序排序的效果。

#{ }会对传入的字符串进行处理。比如：传入的是desc，#{ }处理后的效果是ORDER BY pls.event_time 'desc'，会当成字符串常量，达不到按照pls.event_time字段倒序排序的效果

当然，${ }可能会引发SQL注入。一般情况下，都是使用#{ }的。只有这种不需要对传入的值进行转换的场景，才会使用${ }。

为了实现标题中的功能，恰好用到了${ }的 这个特性而已。

Answer 2

@Param("sort") String sort