ORM或有效处理带有订单列的SQL表的东西

本文介绍了ORM或有效处理带有订单列的SQL表的东西的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我得到了一个使用SQL表的Java应用程序，该表包含一个实体的有序列表，按订单栏.我想在中间添加/从中间删除内容列表中.现在，我想知道是否有一些持久性框架/orm/you-name-it可以通过批量更新订单来提供这种功能柱子.

I got an Java application using SQL tables that contains an ordered list of entities, ordered byan order column. I would like to add/remove things in/from the middleof the list. Now, I'm wondering if some persistence framework / orm / you-name-it could provide this kind of functionality with batch update of ordercolumn.

在基本情况下，Hibernate(可能还有其他人)提供了这一点功能.问题在于，一次只能处理一个对象，这变成了列表足够大时出现问题.替代解决方案是使用批处理SQL更新来完成该任务，例如以下示例:

At the basic case Hibernate (and probably others also) provide thisfunctionality. The problem is that the objects are handled one at time, which becomesproblem when the list is large enough. Alternate solution would be todo the thing with a batch SQL update, like the following for example:

UPDATE table SET order_col = order_col + 1 WHERE order_col > 47
INSERT TO table VALUES ('new_id', 'new_description, ..., 47)

这是数据库引擎非常快地完成的操作，但不受支持.

which is done quite fast by the database engine but isn't supported.

现在，我了解到当考虑对象及其对象时，这种类型的批处理更新不太适合版本控制，脏检查等.我仍然要问别人是否有一些好主意或是否有坚持不懈的态度framework/ORM/you-name-它将提供一些帮助.我当然可以使用自定义SQL/HQL/...如果已经有解决方案(我想其他人可以之前已经做过这样的事情，甚至将其置于开源下).也欢迎与问题相关的其他好主意=)

Now, I understand that thiskind of batch updates don't fit so well when thinking the objects and theirversioning, dirty checking etc.I'd still ask if somebody has some nice ideas or if some persistenceframework / ORM / you-name-it would provide some help. Of course I can do the thing with custom SQL/HQL/... but was wonderingif there would be some solution already (I'd think somebody else couldhave done something like this before and even put it under open source). Also other good ideas related to the problem are welcome =)

推荐答案

如果您确实想以连续的连续顺序结束，则可以这样做:

If you really want to end up with a continuous sequential order, you can do it like this:

首先，将sortorder乘以1000

First of all, multiply the sortorder by say 1000

UPDATE testtable SET sortorder = sortorder * 1000

现在插入并插入适当的sortorder值，以将新条目放置在正确的位置.

Now do your inserts and insert suitable sortorder values to have the new entries in the right place.

现在使用 ROW_NUMBER 函数

UPDATE testtable
SET sortorder = subq.newsortorder
FROM
  (
  SELECT
    ID as innerID,
    ROW_NUMBER() OVER(ORDER BY sortorder ASC) as newsortorder
  FROM testtable
  ) subq
WHERE subq.innerID = ID

该ID被选择为innerID，因为不能为已更新的表加上别名，否则ID列将最终变得模棱两可.

The ID is selected as innerID as the updated table can't be aliased and the ID column would otherwise end up ambiguous.

当按sortorder排序时，这将用行号更新sortorder.

This is updating the sortorder with the row's number when sorted by sortorder.

这篇关于ORM或有效处理带有订单列的SQL表的东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Some