【MyBatis】 How to use MyBatis to dynamically add Order By parameter

preface

We often need to use order by dynamically. The easiest way is to use string concatenation, but this is not secure and vulnerable to injection attacks. You can use MyBatis
tag to implement a secure dynamic order by function.

implementation

Add the orderByXxx parameter to the XxQuery class

The Boolean value indicates whether the order by of the field is required, and the suffix Desc indicates descending order. So there are two formats orderByXxxx and orderByXxxxDesc.

public class OrganizationLabelQuery implements Serializable {
    private Long pageNum;
    private Long pageSize;

    private Long id;
    private List<Long> ids;
    private String labelName;
    private Boolean available;

    private Boolean orderByAvailable;
    private Boolean orderByAvailableDesc;
    private Boolean orderByReferenceNumber;
    private Boolean orderByReferenceNumberDesc;
    private Boolean orderByLabelName;
    private Boolean orderByLabelNameDesc;
    private Boolean orderByCreateTime;
    private Boolean orderByCreateTimeDesc;
    private Boolean orderByUpdateTime;
    private Boolean orderByUpdateTimeDesc;
    
    // getters and getters
}
Copy the code

Use it in xxxxmapper.xml<trim>Dynamic tag addingorder byparameter

The focus here is on the code after the < WHERE > tag. Use a

to determine if an order BY statement is required, and if so, add order by. Then determine which parameter requires order by and use
to remove the excess.

<select id="listOrganizationLabels" resultMap="organizationLabel">
        select <include refid="organizationLabel"/>
        from organization_label
        <where>
            <if test="id ! = null">
                and id = #{id}
            </if>
            <if test="ids ! = null">
                and id in (
                <foreach collection="ids"  item="item" index="index" separator=",">
                    #{item}
                </foreach>
                )
            </if>
            <if test="labelName ! = null">
                and label_name like "%"#{labelName}"%"
            </if>
            <if test="available ! = null">
                and is_available = #{available}
            </if>
        </where>
        <if test="orderByAvailable || orderByAvailableDesc || orderByReferenceNumber || orderByReferenceNumberDesc || orderByLabelName ||  orderByLabelNameDesc || orderByCreateTime || orderByCreateTimeDesc || orderByUpdateTime || orderByUpdateTimeDesc">
            order by
            <trim suffixOverrides=",">
                <if test="orderByAvailable">
                    is_available,
                </if>
                <if test="orderByAvailableDesc">
                    is_available desc,
                </if>
                <if test="orderByReferenceNumber">
                    reference_number,
                </if>
                <if test="orderByReferenceNumberDesc">
                    reference_number desc,
                </if>
                <if test="orderByLabelName">
                    label_name,
                </if>
                <if test="orderByLabelNameDesc">
                    label_name desc,
                </if>
                <if test="orderByCreateTime">
                    create_time,
                </if>
                <if test="orderByCreateTimeDesc">
                    create_time desc,
                </if>
                <if test="orderByUpdateTime">
                    update_time,
                </if>
                <if test="orderByUpdateTimeDesc">
                    update_time desc,
                </if>
            </trim>
        </if>
    </select>
Copy the code

use

It’s easy to use, just set the fields that require order by.

        Result<PageInfo<OrganizationLabelDTO>> listOrganizationLabelsResult = 
                organizationLabelService.listOrganizationLabels(
                        new OrganizationLabelQuery
                                .Builder()
                                .pageNum(1L)
                                .pageSize(50L)
                                .orderByReferenceNumberDesc(true)
                                .orderByUpdateTime(true)
                                .build());
Copy the code