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

Z时代
2024-01-10
分类：技术分享

我想实现根据用户进行模糊查询，可以通过传参选择使用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>