需求和思路
- 序号是从0开始的整数
- N个对象, 上移下移用一个算法, 复杂度 o(N), 尽可能少查询更新语句
- 允许多选移动,选中对象没有排序
- 拓展功能, 可以移动指定步数
- 单选对象, 交换序号
- 移动到是将一批对象朝向一个指定序号的对象target移动, 可理解为一批对象和一个对象a之间的一批对象（包括a）交换位置,序号重新计算。
- 通过增加一个类适配器, 可以对多种排序需求的业务对象进行更新.
- 有可能优化成mysql存储过程和函数
结果预览
下移操作, 两条更新语句
[update AAA set seq = tmp.seq2,
mtime = now() from (
values
( 25,24 ),( 15,12 )
)as tmp(seq1, seq2)
where
rootid = 'ssss' and
AAA.seq = tmp.seq1, update AAA set seq = seq + (1),
mtime = now() where id in ([1628803892483670028, 1628803892483670029, 1628803892483670030, 1628803892483670040])]
移动指定步数
toUpdate保存int数组, {需要更新的对象序号, 更新后的序号}
算法思路来自剑指 Offer II 119. 最长连续序列
package com.rancy;
import lombok.Data;
import java.util.*;
/**
* 最终执行两条更新语句. 有一条范围查询. 允许移动指定步数, step有正负
*
* @author Rancy 2023/2/21
*/
@Data
@SuppressWarnings("rawtypes")
public class MoveStepsOrderUtil extends AbstractOrderUtil {
protected int step;
protected Set<Integer> set;
protected List<int[]> toUpdate;
protected int maxSeq;
public MoveStepsOrderUtil() {
}
public MoveStepsOrderUtil(int step) {
super();
this.step = step;
}
@Override
public int getMax() {
return maxSeq;
}
@Override
public void compute() {
int step = this.step;
int s = step < 0 ? -1 : 1;
Set<Integer> set = this.set;
List<int[]> toUpdate = this.toUpdate;
int tmp;
for (Integer i : set) {
if (set.contains(i - s)) {
continue;
}
tmp = i;
while (set.contains(tmp += s)) {
}
int j = tmp + step;
do {
toUpdate.add(new int[]{tmp, i});
i += s;
} while ((tmp += s) != j);
}
}
@Override
protected void fetchObjs() {
}
@Override
protected void init() {
set = new HashSet<>();
for (Map map : movingObjs) {
set.add((Integer) map.get(ORDER));
}
this.toUpdate = new ArrayList<>();
}
@Override
public boolean isOptimized() {
if (step != 1 && step != -1) {
return false;
}
if (movingObjs != null && movingObjs.size() == 1) {
Integer seq = (Integer) movingObjs.get(0).get(ORDER);
toUpdate.add(new int[]{seq + step, seq});
return true;
}
return false;
}
@Override
public void checkRange() {
if (step == 0) {
throw new IllegalArgumentException("0 step");
}
if (step > 0) {
int max = 0;
for (Integer i : set) {
max = Math.max(max, i);
}
maxSeq = max + step;
if (!queryDB()) {
throw new RuntimeException("Out of Bounds: " + maxSeq);
}
return;
}
int min = Integer.MAX_VALUE;
for (Integer i : set) {
min = Math.min(min, i);
}
min = min + step;
if (min < 1) {
throw new RuntimeException("Out of Bounds: " + min);
}
}
@Override
public void updateAll() {
service.updateAll();
}
@Override
protected boolean queryDB() {
return service.queryDBRange();
}
}
基础接口
package com.rancy;
/**
* 抽象的移动类.
*
* @author Rancy 2023/1/18
*/
public interface OrderUtil<T> {
void move();
}
抽象移动类带有模板方法
package com.rancy;
import lombok.Data;
import java.util.List;
import java.util.Map;
/**
* @author Rancy 2023/2/23
*/
@Data
public abstract class AbstractOrderUtil implements OrderUtil<Map<?, ?>> {
public static boolean testMode = false;
public static String ORDER = "ORDER";
public static String ID = "ID";
protected MoveServiceProvider service;
protected List<Map<?, ?>> movingObjs;
public int getMax() {
return Integer.MAX_VALUE;
}
/**
* 有优化办法
*/
protected boolean isOptimized() {
return true;
}
abstract protected void checkRange();
abstract protected void init();
abstract protected void compute();
abstract protected void fetchObjs();
protected boolean queryDB() {
return false;
}
@Override
public void move() {
init();
checkRange();
fetchObjs();
if (!isOptimized()) {
compute();
}
updateAll();
}
protected void updateAll() {
service.updateAll();
}
}
移动到
package com.rancy;
import lombok.Data;
import java.util.BitSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author Rancy 2023/2/10
*/
@Data
@SuppressWarnings({"UnusedReturnValue"})
public class MoveToOrderUtil extends AbstractOrderUtil {
protected int left;
protected int maxSeq;
protected int minSeq;
protected int right;
protected int movingNum;
protected BitSet bitSet;
protected HashMap<Integer, Map<?, ?>> datasource;
protected List<String> updateIds;
protected List<Integer> updateOrders;
private int k, l, target;
/**
* 移动到操作,选中对象和目标位置处的对象所连成的范围内的对象,全部需要修改
*/
@Override
public void compute() {
int k = this.k, l = this.l;
for (int i = left; i <= right; i++) {
int j = bitSet.get(i) ? l++ : k++;
updateObject(datasource.get(i), j);
}
}
public Map<?, ?> updateObject(Map<?, ?> map, int newPos) {
updateIds.add((String) map.get("ID"));
updateOrders.add(newPos);
// 暂时不做修改对象
return map;
}
@Override
protected void fetchObjs() {
}
@Override
protected boolean queryDB() {
return false;
}
@Override
protected void init() {
}
@Override
protected boolean isOptimized() {
return false;
}
@Override
protected void checkRange() {
if (left <= target && right >= target) {
throw new RuntimeException("Can not move to, target is in the moving range");
}
if (target > right) {
right = target;
k = left;
l = target - movingNum + 1;
} else {
left = target;
l = target;
k = target + movingNum;
}
minSeq = left;
maxSeq = right;
}
}
模拟服务类
package com.rancy;
import lombok.Data;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import static com.rancy.AbstractOrderUtil.ID;
/**
* @author Rancy 2023/2/23
*/
@Data
public abstract class MoveServiceProvider {
protected AbstractOrderUtil carrier;
protected void before() {
}
protected void move(AbstractOrderUtil carrier) {
this.carrier = carrier;
carrier.service = this;
before();
carrier.move();
}
public List<String> getMovingIds() {
List<String> ids = carrier.movingObjs.stream().
map(map -> (String) map.get(ID)).collect(Collectors.toList());
return ids;
}
public void moveUp1() {
move(new MoveStepsOrderUtil(-1));
}
public void moveDown1() {
move(new MoveStepsOrderUtil(+1));
// doMove(new MoveStepsCarrier(+2));
}
public void moveSteps(int step) {
move(new MoveStepsOrderUtil(step));
}
public boolean queryDBRange() {
return false;
}
public List<Map<?, ?>> fetchObjs() {
return null;
}
public void updateAll() {
}
}
测试类
package com.rancy;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.util.*;
import static com.rancy.AbstractOrderUtil.ID;
import static com.rancy.AbstractOrderUtil.ORDER;
/**
* 测试移动类
*
* @author Rancy 2023/1/13
*/
@SuppressWarnings({"unchecked", "rawtypes"})
public class CarrierTest {
public static HashMap<Integer, Map> orderedMaps;
public static Map idMaps;
public static List<Map<?, ?>> moving;
static class TestStepsCarrierMoveServiceProvider extends MoveServiceProvider {
@Override
protected void before() {
carrier.setMovingObjs(moving);
}
@Override
public boolean queryDBRange() {
return orderedMaps.get(carrier.getMax()) != null;
}
@Override
public void updateAll() {
HashMap<String, Map> res = new HashMap<>();
MoveStepsOrderUtil carrier = (MoveStepsOrderUtil) this.carrier;
List<String> list = getMoveStepsCarrierSql(carrier);
System.out.println(list);
int step = carrier.getStep();
for (int[] ints : carrier.getToUpdate()) {
int seq1 = ints[0];
int seq2 = ints[1];
Map map = orderedMaps.get(seq1);
map.put(ORDER, seq2);
String id = (String) map.get(ID);
res.put(id, map);
}
List<String> movingIds = getMovingIds();
for (String i : movingIds) {
Map o = (Map) idMaps.get(i);
Integer seq = (Integer) o.get(ORDER);
o.put(ORDER, seq + step);
res.put(i, o);
}
idMaps.putAll(res);
}
public List<String> getMoveStepsCarrierSql(MoveStepsOrderUtil carrier) {
StringBuilder b = new StringBuilder();
List<int[]> toUpdate = carrier.getToUpdate();
int step = carrier.getStep();
Iterator<int[]> iterator = toUpdate.iterator();
int[] ints = iterator.next();
// 正序倒序覆盖都可以
// 正序倒序覆盖都可以
while (true) {
b.append("( ").append(ints[0]).append(",")
.append(ints[1]).append(" )");
if (!iterator.hasNext()) {
break;
}
ints = iterator.next();
b.append(',');
}
// 根据序号运算更新选中对象,更新后序号临时重复, 需要再根据id更新被覆盖对象的序号
String id = rootId;
String sql = String.format("update %s set seq = tmp.seq2,\n mtime = now() from (\n values\n %s\n )as tmp(seq1, seq2)\n where\n rootid = '%s' and\n %s.seq = tmp.seq1", table, b, id, table);
List<String> ids = getMovingIds();
String sql1 = String.format("update %s set seq = seq + (%d),\n mtime = now() where id in (%s)", table, step, ids.toString());
ArrayList<String> updates = new ArrayList<>();
updates.add(sql);
updates.add(sql1);
return updates;
}
}
static String table = "AAA";
static String rootId = "ssss";
public static void main(String[] args) {
// mock();
before();
TestStepsCarrierMoveServiceProvider director = new TestStepsCarrierMoveServiceProvider();
director.moveDown1();
printResult();
}
@SuppressWarnings("rawtypes")
public static void before() {
int[] toMove = {12, 13, 14, 24};
Map idMaps = sampleMap();
System.out.println("toMap = " + idMaps);
HashMap<Integer, Map> orderedMaps = getOrderedMaps(idMaps);
System.out.println("idsMap = " + orderedMaps);
ArrayList<Map<?, ?>> moving = new ArrayList<>();
for (int i : toMove) {
Map e = orderedMaps.get(i);
if (e == null) {
throw new IllegalArgumentException("Illegal seq: " + i);
}
moving.add(e);
}
CarrierTest.idMaps = idMaps;
CarrierTest.orderedMaps = orderedMaps;
CarrierTest.moving = moving;
}
private static void printResult() {
HashMap<Integer, Map> orderedMaps;
System.out.println("after move...");
orderedMaps = getOrderedMaps(idMaps);
int k = 0;
for (Integer i : orderedMaps.keySet()) {
Map m = orderedMaps.get(i);
int compare = ((Integer) m.get("NO")).compareTo(k);
if (compare != 0) {
System.out.print((compare < 0 ? "↓" : "↑") + "\t");
}
System.out.println(m);
k++;
}
}
public static Map sampleMap() {
Map mock = mock();
return mock;
}
/**
* 总是返回一个复制的新map
*
* @param idMaps
* @return
*/
@SuppressWarnings("rawtypes")
public static HashMap<Integer, Map> getOrderedMaps(Map idMaps) {
HashMap<Integer, Map> orderedMaps = new HashMap<>();
idMaps.forEach((s, o) -> {
Map m = (Map) o;
Object id = m.get(ORDER);
orderedMaps.put((Integer) id, new HashMap<>(m));
});
return orderedMaps;
}
public static Map mock() {
HashMap<String, Object> map = new HashMap<>();
final IdWorker idWorker = new IdWorker();
idAssigner = () -> String.valueOf(idWorker.nextId());
// idAssginer = () -> {
// return UUID.randomUUID().toString();
// };
for (int i = 0; i < 26; i++) {
HashMap<String, Object> m = new HashMap<>();
String id = newId();
m.put("ID", id);
m.put("Alias", (char) ('A' + i));
m.put("NO", i);
m.put("ORDER", i);
map.put(id, m);
}
ObjectMapper objectMapper = new ObjectMapper();
objectMapper.setSerializationInclusion(JsonInclude.Include.NON_EMPTY);
try {
objectMapper.writeValueAsString(map);
} catch (JsonProcessingException e) {
throw new RuntimeException(e);
}
// JacksonJsonParser parser = new JacksonJsonParser();
// System.out.println(parser.parseMap(""));
return map;
}
static IdAssigner idAssigner;
public static String newId() {
return idAssigner.newId();
}
interface IdAssigner {
String newId();
}
}
测试效果
int[] toMove = {12, 13, 14, 24}; 是下移一步的对象.
得到两条更新的SQL.
......
after move...
{NO=0, ORDER=0, ID=1628785010842787840, Alias=A}
{NO=1, ORDER=1, ID=1628785010842787841, Alias=B}
{NO=2, ORDER=2, ID=1628785010842787842, Alias=C}
{NO=3, ORDER=3, ID=1628785010842787843, Alias=D}
{NO=4, ORDER=4, ID=1628785010842787844, Alias=E}
{NO=5, ORDER=5, ID=1628785010842787845, Alias=F}
{NO=6, ORDER=6, ID=1628785010842787846, Alias=G}
{NO=7, ORDER=7, ID=1628785010842787847, Alias=H}
{NO=8, ORDER=8, ID=1628785010842787848, Alias=I}
{NO=9, ORDER=9, ID=1628785010842787849, Alias=J}
{NO=10, ORDER=10, ID=1628785010842787850, Alias=K}
{NO=11, ORDER=11, ID=1628785010842787851, Alias=L}
↑ {NO=15, ORDER=12, ID=1628785010842787855, Alias=P}
↓ {NO=12, ORDER=13, ID=1628785010842787852, Alias=M}
↓ {NO=13, ORDER=14, ID=1628785010842787853, Alias=N}
↓ {NO=14, ORDER=15, ID=1628785010842787854, Alias=O}
{NO=16, ORDER=16, ID=1628785010842787856, Alias=Q}
{NO=17, ORDER=17, ID=1628785010842787857, Alias=R}
{NO=18, ORDER=18, ID=1628785010842787858, Alias=S}
{NO=19, ORDER=19, ID=1628785010842787859, Alias=T}
{NO=20, ORDER=20, ID=1628785010842787860, Alias=U}
{NO=21, ORDER=21, ID=1628785010842787861, Alias=V}
{NO=22, ORDER=22, ID=1628785010842787862, Alias=W}
{NO=23, ORDER=23, ID=1628785010842787863, Alias=X}
↑ {NO=25, ORDER=24, ID=1628785010842787865, Alias=Z}
↓ {NO=24, ORDER=25, ID=1628785010842787864, Alias=Y}
进程已结束,退出代码0
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
