java 根据指定字段排序（mysql）

冷不防 2024-03-22 18:22 15阅读 0赞

### 需求： ###

查询数据的时候，由前端指定字段和排序方式进行排序。

这时候要怎么做呢？ 要定义一个相应的类，排序的时候，是动态拼接的。 要考虑多个字段，不同排序方式的情况。

### 处理 ###

#### OrderField ####

import io.swagger.annotations.ApiModel;
    import io.swagger.annotations.ApiModelProperty;
    import org.apache.commons.collections4.ListUtils;
    import org.apache.commons.lang3.StringUtils;
    
    import java.util.List;
    import java.util.Locale;
    import java.util.stream.Collectors;
    
    @ApiModel(value = "排序字段")
    public class OrderField {
        @ApiModelProperty("排序字段")
        private String fieldName;
    
        @ApiModelProperty("排序方式：ASC,DESC，默认ASC")
        private SortOrder sortOrder = SortOrder.ASC;
    
        public OrderField(String fieldName, SortOrder sortOrder) {
            this.fieldName = fieldName;
            this.sortOrder = sortOrder;
        }
    
        public OrderField() {
        }
    
        public String getFieldName() {
            return this.fieldName;
        }
    
        public void setFieldName(String fieldName) {
            this.fieldName = fieldName;
        }
    
        public SortOrder getSortOrder() {
            return this.sortOrder;
        }
    
        public void setSortOrder(SortOrder sortOrder) {
            this.sortOrder = sortOrder;
        }
    
        public enum SortOrder {
            ASC("asc"), DESC("desc");
    
            String order;
    
            SortOrder(String order) {
                this.order = order;
            }
    
            public String getOrder() {
                return order;
            }
    
            public static SortOrder fromString(String op) {
                return valueOf(op.toUpperCase(Locale.ROOT));
            }
        }
    
        public static String setSqlOrderFields(List<OrderField> orderFields) {
            return ListUtils.emptyIfNull(orderFields).stream().filter(e -> StringUtils.isNotBlank(e.getFieldName()))
                    .map(e -> e.getFieldName() + " " + e.getSortOrder()).collect(Collectors.joining(","));
    
        }
    
    }

#### 请求参数类 ####

在请求参数类里面加上面的内容：

@Data
    public class CodeModelParam {
        @ApiModelProperty("字典类型")
        private String codeType; 
    
        @ApiModelProperty("字典名称")
        private String mean; 
    
        @ApiModelProperty("状态 0SA 启用， 0SX 禁用")
        private String state;
     
        @ApiModelProperty("排序的字段和方式")
        private List<OrderField> orderFields;
    
    }

#### xml ####

<select id="getCodeModel" parameterType="com.web.code.entity.CodeModelParam" resultMap="map">
    		SELECT CODE_TYPE, CODE, MEAN, MEAN_US, REMARK, SORT_ID, STATE, TAG
    		FROM CODELIST
    		<where>
    			<if test="codeType !=null and codeType !='' ">
    				CODE_TYPE in
    				<foreach collection="codeType.split(',')" open="(" close=")" item="item" separator=",">
    					#{item}
    				</foreach>
    			</if>
    			<if test="mean !=null and mean !='' ">
    				AND MEAN LIKE CONCAT(CONCAT('%', #{mean}), '%')
    			</if>
    			<if test="state !=null and state !='' ">
    				AND STATE =  #{state}
    			</if>
    		</where>
    		<if test="orderFields != null and orderFields.size() > 0">
    			ORDER BY
    			<foreach collection="orderFields" item="item" index="index"  separator=",">
    				#{item.fieldName} #{item.sortOrder.order}
    			</foreach>
    		</if>
    	</select>

mapper和service:

// mapper
    List<Map<String, Object> getCodeModel(CodeModelParam codeModelParam);
    
    // service
    public List<CodeModel> getCodeModels(CodeModelParam codeModelParam) {
        return codeModelMapper.getCodeModel(codeModelParam);
    }

#### 测试： ####

@Test
        public void getCodeModels() {
            CodeModelParam codeModelParam = new CodeModelParam();
            codeModelParam.setCodeType("ALARM_CLASS");
            List<OrderField> orderFields = new ArrayList<>();
            OrderField orderField = new OrderField("SORT_ID", OrderField.SortOrder.fromString("DESC"));
            orderFields.add(orderField);
            codeModelParam.setOrderFields(orderFields);
            System.out.println(JSON.toJSONString(codeModelService.getCodeModels(codeModelParam)));
        }

#### 结果： ####

[
    	{
    		"code": "1",
    		"codeType": "ALARM_CLASS",
    		"mean": "平台告警",
    		"remark": "平台告警",
    		"sortId": "11",
    		"state": "0SA",
    		"tag": ""
    	},
    	{
    		"code": "2",
    		"codeType": "ALARM_CLASS",
    		"mean": "业务系统告警",
    		"remark": "业务系统告警",
    		"sortId": "2",
    		"state": "0SA",
    		"tag": ""
    	},
    	{
    		"code": "21",
    		"codeType": "ALARM_CLASS",
    		"mean": "进程类告警",
    		"remark": "进程类告警",
    		"sortId": "9",
    		"state": "0SA",
    		"tag": ""
    	},
    	{
    		"code": "22",
    		"codeType": "ALARM_CLASS",
    		"mean": "平衡类告警",
    		"remark": "平衡类告警",
    		"sortId": "4",
    		"state": "0SA",
    		"tag": ""
    	},
    	{
    		"code": "23",
    		"codeType": "ALARM_CLASS",
    		"mean": "阀值类告警",
    		"remark": "阀值类告警",
    		"sortId": "5",
    		"state": "0SA",
    		"tag": ""
    	}, 
    	{
    		"code": "82",
    		"codeType": "ALARM_CLASS",
    		"mean": "文件积压告警",
    		"remark": "文件积压告警",
    		"sortId": "11",
    		"state": "0SA",
    		"tag": ""
    	} 
    ]

结果并没有根据相应的进行排序。看了下日志：

![3f1a0eb5b54242f89985c0a8aa24daf3.png][]

Preparing: SELECT CODE_TYPE, CODE, MEAN, MEAN_US, REMARK, SORT_ID, STATE, TAG FROM CODELIST WHERE CODE_TYPE in ( ? ) ORDER BY ? ? 
     Parameters: ALARM_CLASS(String), SORT_ID(String), desc(String)

看着也好像没啥问题。 把内容放到sql那边执行下。就发现问题了。

SELECT * FROM codelist WHERE code_type = 'ALARM_CLASS' ORDER BY 'SORT_ID' 'DESC';

因为xml那边用的是

#{item.fieldName} #{item.sortOrder.order}

这样解析的时候，就变成字符串了， 实际并无法生效。

**再处理：**

<if test="orderFields != null and orderFields.size() > 0">
    			ORDER BY
    			<foreach collection="orderFields" item="item" index="index"  separator=",">
    				${item.fieldName} ${item.sortOrder.order}
    			</foreach>
    		</if>

再测试，输出的结果是：

![afcffad087804578955e9ccfa2040de3.png][]

Preparing: SELECT CODE_TYPE, CODE, MEAN, MEAN_US, REMARK, SORT_ID, STATE, TAG FROM CODELIST WHERE CODE_TYPE in ( ? ) ORDER BY SORT_ID desc 
    Parameters: ALARM_CLASS(String)

这样就符合要求了。

### 总结： ###

在使用动态拼接字段的时候，mybatis里面要用$进行处理，如果是\#的话，是字符串，里面包含要查询的字段，就不生效了。 这个要多注意！！！

[3f1a0eb5b54242f89985c0a8aa24daf3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/b2592ce2e8094873abef980fcd4d9c4f.png
[afcffad087804578955e9ccfa2040de3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/18/473e1b558a4f44c4adf435fee81dbfe5.png