Is there a cleaner way of doing the next : <select id="getWithQueryData" resultMap="usuarioResult" parameterType="my.QueryData" > select * from t_user <if test="fieldName != null and ascDesc != null"> order by <choose> <when test="fieldName == 'name'"> c_name </when> <when test="fieldName == 'lastName'"> c_last_name </when> <when test="fieldName == 'email'"> c_email </when> <when test="fieldName == 'password'"> c_password </when> <when test="fieldName == 'age'"> i_age </when> </choose> <if test="ascDesc == 'asc'"> asc </if> <if test="ascDesc == 'desc'"> desc </if> </if> limit #{limit} offset #{offset}; As you could infer, QueryData looks like: public class FiltroBusquedaVO { private Integer offset; private Integer limit; private String fieldName; private String ascDesc; ... } Would be nice if I could get a column name given a fieldName. I mean, the result maps have that info. But it seems I can not get it from the xml. My example has just 5 fields, but what about 20 fields? Is there another way around of doing this less verbose?

mybatis ordering by many fields with dynamic sql

This is how I did it:

List<ProjectLockDBO> getProjectsLocks(@Param("projectId") Integer projectId, @Param("userId") Integer userId, @Param("limit") Integer limit,
            @Param("offset") Integer offset, @Param("sortAsc") List<String> sortAsc, @Param("sortDesc") List<String> sortDesc);

<select id="getProjectsLocks" resultMap="ProjectLockResult">
        SELECT pl.id,
               pl.project_id,
               pl.notes,
               pl.created_by_id,
               pl.created_at,
               u.id AS user_id,
               u.email AS user_email,
               u.first_name AS user_first_name,
               u.last_name AS user_last_name
        FROM projects_locks pl
        LEFT JOIN users u ON u.id = pl.created_by_id
        WHERE 1=1
        <if test="projectId != null"> AND pl.project_id = #{projectId}</if>
        <if test="userId != null"> AND pl.created_by_id = #{userId}</if>
        <choose>
            <when test="sortAsc != null or sortDesc != null">
                ORDER BY <if test="sortAsc != null">
                    <foreach collection="sortAsc" item="element" open="" close=" ASC" separator=",">
                        ${element}
                    </foreach>
                </if>
                <if test="sortAsc != null and sortDesc != null">, </if>
                <if test="sortDesc != null">
                    <foreach collection="sortDesc" item="element" open="" close=" DESC" separator=",">
                        ${element}
                    </foreach>
                </if>
            </when>
            <otherwise>ORDER BY pl.id;</otherwise>
        </choose>
        <if test="limit != null">LIMIT #{limit}</if>
        <if test="offset != null">OFFSET #{offset}</if>
    </select>

Where the 2 lists will contain strings like: "pl.project_id", "pl.id",

The critical part here is the dollar sign "$" in front of the {element}. If you will use the standard #, it won't work.

mybatis ordering by many fields with dynamic sql

2 Answers