JAVA对数据进行上移、下移、置顶（倒序排序）操作

解决思路：上移：取上一条记录排序号，将当前记录与上一条记录排序号调换位置

                  下移：取下一条记录排序号，将当前记录与下一条记录排序号调换位置

                  置顶：获取最大的记录排序号，修改当前记录的排序号为最大排序号+1

mybatis编写sql获取上一条记录排序号和下一条记录排序号

  <!-- 读取上一条记录-->
  <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>

具体实现方法：

    @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;
    }

如果有更好的思路，欢迎和我分享?