java 上下移动数据排序

//向上移动就把上一条数据和本条数据的排序字段交换下

UPDATE sys_custom_group AS a,
 sys_custom_group AS b
SET a.seq = b.seq,
 b.seq = a.seq
WHERE
	a.id = '051d11d139794532963beb75f40c0afc' and 
EXISTS(select * from (select id from sys_custom_group where seq < 9 and Parent_ID = '1a83c7ac73c54be8843ffa0b3b5ee550'  order by seq desc  limit 0,1) r
	where r.id = b.id
)

后台只要获取，要移动的该条数据的id,和其顺序seq,我这里因为是tree形数据结构，所以还有个parentId;

 

--------------------------------------------------------------------------------------------

下面看在mybatis里怎么写；

区分flag  isUp;  如果是向上移动为true 向下移动为true

 

<update id="upOrDownGroup" parameterType="map" >
    UPDATE sys_custom_group AS a,
	 sys_custom_group AS b
	SET a.seq = b.seq,
	 b.seq = a.seq
	<if test="isUp == true ">
		WHERE
			a.id = #{sysCustomGroup.id,jdbcType=VARCHAR} and 
		EXISTS(select * from (select id from sys_custom_group where seq &lt; #{sysCustomGroup.seq,jdbcType=INTEGER} and Parent_ID =  #{sysCustomGroup.parentId,jdbcType=VARCHAR}  order by seq desc  limit 0,1) r
			where r.id = b.id
		)
	</if>
	<if test="isUp == false ">
		WHERE
			a.id = #{sysCustomGroup.id,jdbcType=VARCHAR} and 
		EXISTS(select * from (select id from sys_custom_group where seq &gt; #{sysCustomGroup.seq,jdbcType=INTEGER} and Parent_ID =  #{sysCustomGroup.parentId,jdbcType=VARCHAR}  order by seq asc  limit 0,1) r
			where r.id = b.id
		)
	</if>
  </update>