Mybatis自定义SQL动态排序的实现

最新推荐文章于 2025-04-21 10:58:32 发布

秃飞猛进

最新推荐文章于 2025-04-21 10:58:32 发布

阅读量4k

点赞数 5

文章标签： mybatis

本文链接：https://blog.youkuaiyun.com/weixin_44329984/article/details/109338676

版权

本文介绍如何在MyBatis中实现自定义排序功能，包括单字段排序和多字段排序的方法，并提供了具体的Java类和XML配置示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、需求

根据条件查询列表，要求在查询的条件中可以自定义排序字段和升序降序。
在这里插入图片描述

2、实现

queryParam

public class XxxQueryCondition extends BaseQueryParam {
    private String name;
    private Integer status;
    private Integer level;
}
/**------------------------------------*/

@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("基础查询参数")
public class BaseQueryParam {
    @ApiModelProperty("是否分页：true-是，false-不分页")
    private Boolean isPaging = false;

    @ApiModelProperty("页码")
    private Integer pageIndex = 0;

    @ApiModelProperty("页面大小")
    private Integer pageSize = 10;

    @ApiModelProperty("排序字段")
    private String sort;

    @ApiModelProperty("升序: asc， 降序: desc")
    private String order;
}

xml

<?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="com.xx.XxxMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.xxx.entity.XxxEntity">
        <id column="id" property="id"/>
        <result column="status" property="status"/>
        <result column="level" property="level"/>
        <result column="xx_code" property="eventNo"/>
        ......
    </resultMap>

    <sql id="selectByConditionSql">
        SELECT * FROM t_test A
        LEFT JOIN t_test_relation B
        ON A.id = B.geologic_hazard_id
        <where>
            <if test="condition.code !=null and condition.code !=''">
                AND A.xx_code LIKE CONCAT('%',#{condition.code},'%')
            </if>
            <if test="condition.status !=null">
                AND A.status = #{condition.status}
            </if>
            ......
            
            <if test="condition.startTime !=null">
                AND A.xx_date >= #{condition.startTime}
            </if>
            <if test="condition.endTime !=null">
                AND A.xx_date  <![CDATA[ <= ]]> #{condition.endTime}
            </if>
            <if test="condition.entity_id !=null and condition.entity_id !=''">
                AND B.biz_entity_id = #{condition.entity_id}
            </if>
        </where>
        ORDER BY
        <choose>
            <when test="condition.sort != null and condition.sort != ''">
                A.${condition.sort}
                <if test="condition.order != null and condition.order != ''">
                    ${condition.order}
                </if>
            </when>
            <otherwise>
                A.id asc , A.create_time asc
            </otherwise>
        </choose>
    </sql>

    <select id="selectPageByCondition" resultMap="BaseResultMap"
            parameterType="com.xxx.XxxQueryCondition">
        <include refid="selectByConditionSql"></include>
    </select>

    <select id="selectByCondition" resultMap="BaseResultMap"
            parameterType="com.xxx.XxxQueryCondition">
        <include refid="selectByConditionSql"></include>
    </select>

</mapper>

3、升级版本（多个排序字段）

queryParam

@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("基础查询参数")
public class BaseQueryParam {
    @ApiModelProperty("是否分页：true-是，false-不分页")
    private Boolean isPaging = false;

    @ApiModelProperty("页码")
    private Integer pageIndex = 0;

    @ApiModelProperty("页面大小")
    private Integer pageSize = 10;

    @ApiModelProperty(value = "排序",example = "sortMap:{" +
            "field_01:desc" +
            "field_02:asc" +
            "}")
    private Map<String, String> sortMap;

}

xml

<sql id="selectByConditionSql">
        SELECT * FROM t_table_01 t1
        LEFT JOIN t_table_02 t2
        ON t1.id = t2.t2_id
        <where>
            t1.is_delete = 0
            <if test="condition.status !=null">
                AND t1.status = #{condition.status}
            </if>
            <if test="condition.level !=null">
                AND t1.level = #{condition.level}
            </if>
            <if test="condition.type !=null">
                AND t1.event_type = #{condition.type}
            </if>
            ...

            <if test="condition.startTime !=null">
                AND t1.xx_date >= #{condition.startTime}
            </if>
            <if test="condition.endTime !=null">
                AND t1.xx_date  <![CDATA[ <= ]]> #{condition.endTime}
            </if>
            <if test="condition.xxEntityId !=null and condition.xxEntityId !=''">
                AND t2.xx_entity_id = #{condition.xxEntityId}
            </if>
        </where>
        ORDER BY
        <choose>
            <when test="condition.sortMap != null and condition.sortMap != ''">
                <!--如果排序map不为空，遍历-->
                <foreach item="value" index="key" collection="condition.sortMap.entrySet()" separator=",">
                    t1.${key} ${value}
                </foreach>
            </when>
            <otherwise>
                t1.id asc , t1.create_time asc
            </otherwise>
        </choose>
    </sql>

注意

1、这里 A.${condition.sort} 不能使用 #，否则，MyBatis会创建预编译的语句，然后为它设置相应的值．MyBatis会自动将排序字段当成一个字符串，等同于order by 'create_time' 'desc'，执行无效

2、$不能防止Sql注入！