之前做过好多外包都没写过排序的实现,这次发现同事写的有问题,所以手动实现一个。不知是否有用。直接上代码。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
/**
* 给表的排序字段排序
*
* @author: 李涛
* @version: 2019年07月17日 16:51
*/
@Api(tags = "给列表排序")
@RestController
@RequestMapping("/common/sort")
@Validated
public class SortTableController {

@Autowired
private ICommonSV commonSV;

/**
* 拖拽标志对应的表和字段
*/
private static final Map<String, String> tables = new HashMap<>();

/**
* 拖拽标志对应的表和字段
*/
private static final Map<String, String> tablesWhere = new HashMap<>();

static {
// 表名
tables.put("app_banner", "sort_num");
tables.put("app_health_plate", "sort_num");
tables.put("app_start_page", "sort_num");
tables.put("clinic_manual", "sort");
tables.put("sys_menu", "menu_order");
tables.put("nav_dept_adv", "sort_num");
// 排序条件
tablesWhere.put("sys_menu", "and parent_id = #{params.p0}");
tablesWhere.put("clinic_manual", "and deleted !='01' ");
}

@Log
@ApiOperation("排序")
@ApiImplicitParams({
@ApiImplicitParam(value = "拖拽标志(nav_doctor_infor,nav_popu_dept_infor,nav_quick_entry_infor)", name = "tableName", paramType = "form"),
@ApiImplicitParam(value = "上", name = "top", paramType = "form"),
@ApiImplicitParam(value = "中", name = "mid", paramType = "form"),
@ApiImplicitParam(value = "下", name = "bottom", paramType = "form"),
@ApiImplicitParam(value = "条件", name = "whereCase", paramType = "form"),
})
@Transactional
@PostMapping("/sortTable")
public APIResponse sortTable(
@NotNull String tableName,
Long top,
@NotNull Long mid,
Long bottom,
String[] whereCase
) {
String sort = tables.get(tableName);
if (StringUtils.isBlank(sort)) {
throw new UnsupportedOperationException();
}
Map<String, Object> params = new HashMap<>();
//根据上下判定是上移还是下移
Boolean down = null;
Long midSort = null;
Long topSort = null;
Long bottomSort = null;
if (top == null) {
down = false;
} else if (bottom == null) {
down = true;
} else {
midSort = findSort(mid, tableName);
topSort = findSort(top, tableName);
bottomSort = findSort(bottom, tableName);
down = midSort > topSort && midSort > bottomSort;
}
// 执行更新操作
String updateSql = null;
if (down) {
if (topSort == null) {
topSort = findSort(top, tableName);
}
List<Long> ids = findIds(top, mid, tableName, whereCase, "first");
if (ids.isEmpty()) {
return APIResponseBuilder.successNoData();
}
updateSql = "update " + tableName + " set " + sort + " = " + sort + " + 1 where id in ( " + StringUtils.join(ids, ",") + " )";
commonSV.updateByParams(updateSql, params);
params.put("newSort", topSort);
params.put("id", mid);
updateSql = "update " + tableName + " set " + sort + " = #{params.newSort} where id = #{params.id} ";
commonSV.updateByParams(updateSql, params);
} else if (!down) {
if (bottomSort == null) {
bottomSort = findSort(bottom, tableName);
}
List<Long> ids = findIds(mid, bottom, tableName, whereCase, "last");
if (ids.isEmpty()) {
return APIResponseBuilder.successNoData();
}
updateSql = "update " + tableName + " set " + sort + " = " + sort + " - 1 where id in ( " + StringUtils.join(ids, ",") + " )";
commonSV.updateByParams(updateSql, params);
params.put("newSort", bottomSort);
params.put("id", mid);
updateSql = "update " + tableName + " set " + sort + " = #{params.newSort} where id = #{params.id} ";
commonSV.updateByParams(updateSql, params);
}
return APIResponseBuilder.successNoDataWithMsg("排序成功!");
}

/**
* 查询两个ID之间的ID有哪些
*
* @param startId
* @param endId
* @param tableName
* @param whereCase
* @param removeTag
* @return
*/
private List<Long> findIds(Long startId, Long endId, String tableName, String[] whereCase, String removeTag) {
String sort = tables.get(tableName);
String sql = " select id " +
" from " + tableName + " " +
" where " + sort + " >= (select " + sort + " from " + tableName + " where id = #{params.startId}) " +
" and " + sort + " <= (select " + sort + " from " + tableName + " where id = #{params.endId}) ";
String whereCaseStr = tablesWhere.get(tableName);
if (whereCaseStr != null) {
sql += whereCaseStr;
}
sql += " order by " + sort + " desc ";
Map<String, Object> params = new HashMap<>();
params.put("startId", startId);
params.put("endId", endId);
if (whereCase != null && whereCase.length > 0) {
for (int i = 0; i < whereCase.length; i++) {
params.put("p" + i, whereCase[i]);
}
}
List<JSONObject> longs = commonSV.queryListJSONObject(sql, params);
if (StringUtils.isNotBlank(removeTag) && !longs.isEmpty()) {
if ("last".equals(removeTag)) {
longs.remove(longs.size() - 1);
} else if ("first".equals(removeTag)) {
longs.remove(0);
}
}
return longs.stream().map(n -> n.getLong("id")).collect(Collectors.toList());
}

/**
* 根据ID查询序号
*
* @param id
* @param tableName
* @return
*/
private Long findSort(Long id, String tableName) {
if (id == null) {
return null;
}
String sql = " select " + tables.get(tableName) + " from " + tableName + " where id = #{params.id}";
Map<String, Object> params = new HashMap<>();
params.put("id", id);
Number sortNum = commonSV.selectField(sql, params, Number.class);
return sortNum.longValue();
}
}