20240112

1 上移、下移、置顶逻辑

倒序逻辑：

使用一个sort_order字段来排序

新增：当新增第一个时初始化100000

新增第N个：max(sort_order) -1 ,放在下面

上移：当前sort_order和大于当前sort_order替换（已经最顶不能上移

下移：当前sort_order和小于当前sort_order替换（已经最低不能下移

置顶：当前sort_order和max(sort_order)替换

mybatis

  <!-- 读取上一条记录-->
  <select id="selectPreviousOrder" resultType="java.lang.Integer" parameterType="java.util.Map">
    SELECT ifnull(iorder, 1) previousOrder
    FROM goods    
    WHERE  iorder <![CDATA[ < ]]> #{iorder}
    order by iorder desc limit 0,1
  </select>
 
  <!-- 读取下一条记录-->
  <select id="selectNextOrder" resultType="java.lang.Integer" parameterType="java.util.Map">
    SELECT ifnull(iorder, 1) nextOrder
    FROM goods
    WHERE  iorder > #{iorder}
    order by iorder  limit 0,1
  </select>
 
  <!-- 修改当前的排序-->
  <update id="updateSelfOrder" parameterType="java.util.Map">
    UPDATE goods SET iorder = #{iorder} WHERE au_id = #{auId}
  </update>
 
  <!--修改当前的上一个或者下一个的排序 -->
  <update id="updateOtherOrder" parameterType="java.util.Map">
    UPDATE goods SET iorder = #{iorder} WHERE iorder = #{originalOrder}
  </update>

service

    @Transactional
    public int movelOrder(Goods goods, String operateType) {
        try {
                //当前移动的分类
                Map<String, Object> selfMap = new HashMap<>();
                selfMap.put("auId", goods.getAuId());
                //当前移动分类的上一个或者下一个
                Map<String, Object> otherMap = new HashMap<>();
                otherMap.put("iorder", goods.getIorder());
                //获取最大排序值
                Map<String, Object> order = goodsMapper.selectMaxOrder();
                if ("top".equals(operateType)) { //置顶
                //获取最大排序值
                int maxOrder = order == null ? 1 : Integer.valueOf(order.get("maxOrder").toString()) + 1;
                selfMap.put("iorder", maxOrder);
                //修改当前排序值为最大排序值+1
                goodsMapper.updateSelfOrder(selfMap);
            }
            if ("up".equals(operateType)) { //上移
                //获取下一条记录iorder
                int nextOrder = goodsMapper.selectNextOrder(paramMap);
                //修改下一条的为当前值
                otherMap.put("originalOrder", nextOrder );
                goodsMapper.updateOtherOrder(otherMap);
                //修改自己的排序为下一条
                selfMap.put("iorder", nextOrder);
                goodsMapper.updateSelfOrder(selfMap);
            }
            if ("down".equals(operateType)) { //下移
                //获取上一条记录iorder
                int previousOrder = goodsMapper.selectPreviousOrder(paramMap);
                otherMap.put("originalOrder", previousOrder );
                //修改上一条的为当前值
                goodsMapper.updateOtherOrder(otherMap);
                //修改自己的排序为上一条
                selfMap.put("iorder", previousOrder );
                goodsMapper.updateSelfOrder(selfMap);
            }
        } catch (Exception e) {
            logger.error("移动异常" + e.getMessage() );
            return 0;
        }
        return 1;
    }