PC列表通用排序功能

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

/**
 * 给表的排序字段排序
 *
 * @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();
	}
}