数据库保存顺序信息

原理

下面的方案如果一直在最后追加会导致 100 行数据，order 字段的长度就变成10。应该考虑平衡树之类的处理方式。

可以使用字符串字段作为排序，使用数字会有精度问题和更新排序问题，使用字符串问题是最小的，只需要查询两行数据即可更新。

package com.apitable.abranch.utils;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

public class StringOrder<T> {
    private List<OrderData<T>> list = new ArrayList<>();
    private class OrderData<T> {
        T data;
        String order;

    }

    public List<OrderData<T>> getSortedResult(){
        list.sort(new Comparator<OrderData<T>>() {
            @Override
            public int compare(OrderData<T> o1, OrderData<T> o2) {
                return o1.order.compareTo(o2.order);
            }
        });
        return list;
    }

    public void addElem(T data){
        if (list.isEmpty()) {
            OrderData<T> d = new OrderData<T>();
            d.data = data;
            d.order = "1";
            list.add(d);
            return;
        }
        OrderData<T> tOrderData = list.get(list.size() - 1);
        String newO = tOrderData.order.endsWith("9") ? tOrderData.order + "1" :
                String.valueOf(Long.parseLong(tOrderData.order) + 1);
        OrderData<T> d = new OrderData<T>();
        d.data = data;
        d.order = newO;
        list.add(d);
        this.getSortedResult();
    }

    public void addElem(T data, int index){
        if (list.isEmpty() || index >= (this.list.size() - 1)) {
            this.addElem(data);
            return;
        }
        String pre = list.get(index - 1).order;
        String next = list.get(index).order;
        Long preL = Long.parseLong(pre);
        Long nextL = Long.parseLong(next);
        String newOrder = (pre.endsWith("9") || preL + 1 == nextL) ? pre + "1" : String.valueOf(preL + 1);
        if (newOrder.equals(next)) {
            newOrder = pre + "01";
        }
        OrderData<T> d = new OrderData<>();
        d.order = newOrder;
        d.data = data;
        list.add(d);
        this.getSortedResult();
    }

    public static void main(String[] args) {
        StringOrder<Integer> stringOrder = new StringOrder<>();

        for (int i = 10; i > 0; i--) {
            stringOrder.addElem(i);
        }

        stringOrder.addElem(14, 3);
        stringOrder.addElem(14, 8);
        stringOrder.addElem(14, 1000);
        stringOrder.addElem(7);

        for (int i = 0; i < 10; i++) {
            stringOrder.addElem(20 + i);
        }
        for (int i = 0; i < 10; i++) {
            stringOrder.addElem(30 + i, 20 + i);
        }

        stringOrder.print();
    }

    private void print() {
        int index = 0;
        for (OrderData<T> tOrderData : this.list) {
            System.out.println(index + ": " + tOrderData.data + " --- " + tOrderData.order);
            index++;
        }
    }
}

插入到 MongoDb

db.sortTests.insertMany([{ a: 0, b: 10, c: "1" },
             { a: 1, b: 9, c: "2" },
             { a: 2, b: 8, c: "3" },
             { a: 3, b: 14, c: "31" },
             { a: 4, b: 7, c: "4" },
             { a: 5, b: 6, c: "5" },
             { a: 6, b: 5, c: "6" },
             { a: 7, b: 4, c: "7" },
             { a: 8, b: 14, c: "71" },
             { a: 9, b: 3, c: "8" },
             { a: 10, b: 2, c: "9" },
             { a: 11, b: 1, c: "91" },
             { a: 12, b: 14, c: "92" },
             { a: 13, b: 7, c: "93" },
             { a: 14, b: 20, c: "94" },
             { a: 15, b: 21, c: "95" },
             { a: 16, b: 22, c: "96" },
             { a: 17, b: 23, c: "97" },
             { a: 18, b: 24, c: "98" },
             { a: 19, b: 25, c: "99" },
             { a: 20, b: 30, c: "9901" },
             { a: 21, b: 31, c: "9902" },
             { a: 22, b: 32, c: "9903" },
             { a: 23, b: 33, c: "9904" },
             { a: 24, b: 34, c: "9905" },
             { a: 25, b: 35, c: "9906" },
             { a: 26, b: 36, c: "9907" },
             { a: 27, b: 37, c: "9908" },
             { a: 28, b: 38, c: "9909" },
             { a: 29, b: 39, c: "99091" },
             { a: 30, b: 26, c: "991" },
             { a: 31, b: 27, c: "992" },
             { a: 32, b: 28, c: "993" },
             { a: 33, b: 29, c: "994" }
         ])

查询实现

db.sortTests.find().sort({c: -1}).skip(5).limit(2)