使用mybatis实现多个字段动态排序（#{}和${}的区别）

#{}:

直接传的属性值，类似这样的 是加了“”的

 #{doctorId,jdbcType=BIGINT}

${}:

这种不加引号，一般在orderby中使用。如下

 order by
        <foreach item="value" index="key" collection="sortMap.entrySet()" separator=",">
            ${key} ${value}
        </foreach>

实现多字段动态排序

**
 * @Author xty
 * @Description
 * @Date 15:41 2018/11/05
 */
public class OutPatientEncounterDetailRequest extends PageRequest {

    private static final long serialVersionUID = -2037535116111838237L;

    @ApiModelProperty("排序标识(不允许出现数据库字段名称)")
    private String orderKey;
    @ApiModelProperty("排序类型(升序(asc)、降序(desc))")
    private String orderType = "asc";
    @ApiModelProperty(value = "排序map",hidden = true)
    private Map<String,String> sortMap;

其中数据库字段名为了安全性不能暴露在前段。因此自定义标识在后端转换存入map中。以至于字段排序的传入顺序让前段拼接完传入。

**
 * @Author xty
 * @Description
 * @Date 15:41 2018/11/05
 */
 public Map getSort(OutPatientEncounterDetailRequest request) {
        Map<String, String> sortmap = new HashMap<>();
        String[] keys = request.getOrderKey().split(",");
        String[] types = request.getOrderType().split(",");
        for (int i = 0; i < keys.length; i++) {
            
            if ("a".equals(keys[i])) {
                sortmap.put("数据库字段名", types[i]);
            } else if ("b".equals(keys[i])) {
                sortmap.put("数据库字段名", types[i]);
            } else if ("c".equals(keys[i])) {
                sortmap.put("数据库字段名", types[i]);
            }
        }
        Map<String, String> resultMap = new HashMap<>();
        for (Map.Entry<String, String> entry : sortmap.entrySet()) {
            String key = "a." + entry.getKey();
            resultMap.put(key, entry.getValue());
        }
        return resultMap;
    }

传参类似这样

{
                "patientDivision":1,
                "encounterStatus":2,
                "orderKey":"b,a",
                "orderType":"desc,desc"
	}

 

动态把排序字段和排序规则放入map。然后放入xml里面执行 

 <select id=""
  parameterType=""
  resultType="">
        SELECT
        a.id AS id
        FROM
        表名 a
        WHERE
        a.***= #{***,jdbcType=BIGINT}
        AND a.active = 1
        <choose>
            <when test="patientDivision != null and patientDivision != '' and patientDivision == 1">
                AND a.数据库字段 = #{deptId,jdbcType=BIGINT}
            </when>
            <when test="patientDivision != null and patientDivision != '' and patientDivision == 3">
                AND (a.数据库字段 = #{doctorId,jdbcType=BIGINT} OR a.数据库字段is null)
            </when>
        </choose>
        <if test="encounterStatus != null and encounterStatus != ''">
            AND a.数据库字段 = #{encounterStatus,jdbcType=TINYINT}
        </if>
        <choose>
            <when test="encounterStatus != null and encounterStatus != '' and encounterStatus ==1">
                <if test="beginTime != null and beginTime != ''">
                   AND a.数据库字段 >= str_to_date(#{beginTime,jdbcType=VARCHAR},'%Y-%m-%d')
                </if>
                <if test="endTime != null and endTime != ''">
                  AND str_to_date(#{endTime,jdbcType=VARCHAR},'%Y-%m-%d') >= a.service_date
                </if>
                <if test="queryStr != null and queryStr != ''">
                    and (
                    a.数据库字段 like concat("%", #{queryStr, jdbcType = VARCHAR}, "%") escape '/'
                    or 数据库字段 like concat("%",#{queryStr,jdbcType=VARCHAR},"%") escape '/'
                    )
                </if>
            </when>
            <when test="encounterStatus != null and encounterStatus != '' and encounterStatus ==2">
                <if test="beginTime != null and beginTime != ''">
                  AND a.数据库字段>= str_to_date(#{beginTime,jdbcType=VARCHAR},'%Y-%m-%d %h:%i:%s')
                </if>
                <if test="endTime != null and endTime != ''">
                  AND str_to_date(#{endTime,jdbcType=VARCHAR},'%Y-%m-%d %h:%i:%s') >= a.encounter_date
                </if>
                <if test="queryStr != null and queryStr != ''">
                    and (
                    a.数据库字段 like concat("%", #{queryStr, jdbcType = VARCHAR}, "%") escape '/'
                    or 数据库字段 like concat("%",#{queryStr,jdbcType=VARCHAR},"%") escape '/'
                    or  数据库字段 like concat("%",#{queryStr,jdbcType=VARCHAR},"%") escape '/'
                    )
                </if>
            </when>
        </choose>
        order by
        <foreach item="value" index="key" collection="sortMap.entrySet()" separator=",">
            ${key} ${value}
        </foreach>
    </select>

还发现了当没有数据的时候 mybatis是不执行orderby的 。

StringEscapeUtils.escapeSql(queryStr)这个方法可以防止sql注入