英文:
Handle more than 1000 records updates in Spring Hibernate
问题
我有一个场景，需要更新超过1000条记录的日期字段。
我之前使用了本地查询，但是遇到了错误 ora-01795 maximum number of expressions in a list is 1000。
经过检查，我找到了像在这个答案中提到的那样，将in子句分解的解决方案。
但我觉得这个解决方案并不是很干净。
在Spring中，是否有其他我可以使用的方法，能更加优雅一些？请提供建议。
我当前的查询类似于：
@Modifying
@Query(value = "UPDATE MY_TABLE SET FLAGGED_DATE = :date WHERE ID IN (:ids)", nativeQuery = true)
void updateFlaggedDate(List<Long> ids, Date date);
我传递给列表的ID是从第三方API收集的。
英文:
I've a scenario in which I need to update a date field for more than 1000 records.
I was using native query but then got the error ora-01795 maximum number of expressions in a list is 1000.
Upon checking I found solutions like breaking the in clause like mentioned in this answer.
But I'm finding this solution, not a very clean one.
Is there any other approach I can use in Spring that is a bit cleaner? Please suggest.
My current query is like:
@Modifying
@Query(value = "UPDATE MY_TABLE SET FLAGGED_DATE = :date WHERE ID IN (:ids)", nativeQuery = true)
void updateFlaggedDate(List<Long> ids, Date date);
The Ids I'm passing in list is being collected from a 3rd party API.
答案1
得分: 1
如果您被允许创建一个新表，那就这样做：
创建一个名为 id_list 的表（列名为 id，数据类型为 number）。
将那个很长的 ID 列表存储到这个表中（如何存储？我不懂 Java，但我希望您知道如何做）。
然后在您的 UPDATE 语句中将其用作子查询，如下：
更新 my_table 表，设置
flagged_date = :date
其中 id 在（从 id_list 中选择的 id）
现在您不再受值数量的限制，它可能真的会非常庞大。
英文:
If you're allowed to create a new table, do it as
create table id_list (id number);
Store that looooong list of IDs into that table (how? I don't know Java but I hope you know how to do it).
Then use it as a subquery in your UPDATE statement as
update my_table set
flagged_date = :date
where id in (select id from id_list)
Now you aren't restricted by number of values, it can be really huge.
答案2
得分: 0
如果您不排斥使用PL/SQL，您可以运行类似下面示例的代码。这甚至可能比您最初使用的原始更新代码性能更好，因为您最初构建UPDATE语句的方式每次运行时都是不同的语句。Oracle每次运行查询时都需要生成执行计划，这可能是昂贵的。修改您的代码，使得每次运行相同的UPDATE语句，将有助于Oracle每次都使用相同的执行计划。
DECLARE
TYPE ids_t IS TABLE OF NUMBER;
l_ids ids_t := ids_t ();
BEGIN
--在这里构建您的集合
l_ids.EXTEND (3); --这将是您要添加到集合中的ID总数
l_ids (1) := 353;
l_ids (2) := 234;
l_ids (3) := 123;
FORALL i IN 1 .. l_ids.COUNT
UPDATE MY_TABLE
SET FLAGGED_DATE = :date
WHERE ID = l_ids (i);
END;
/
英文:
If you're not opposed to using PL/SQL, you can run code similar to the example below. This may even perform better than your original update that you are using because they way you were building the UPDATE statement originally is a different statement each time it is run. Oracle would need to come up with an execution plan each time the query is run which can be costly. Modifying your code so that the same UPDATE is run each time would help oracle use the same execution plan each time.
DECLARE
TYPE ids_t IS TABLE OF NUMBER;
l_ids ids_t := ids_t ();
BEGIN
--build up your collection here
l_ids.EXTEND (3); --This will be the total number of IDs that you are adding to the collection
l_ids (1) := 353;
l_ids (2) := 234;
l_ids (3) := 123;
FORALL i IN 1 .. l_ids.COUNT
UPDATE MY_TABLE
SET FLAGGED_DATE = :date
WHERE ID = l_ids (i);
END;
/
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论