Handle more than 1000 records updates in Spring Hibernate

Question

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.

What would a clean solution look like to you? You should be able to build the SQL statement `where id = :id` and then run batches of the same SQL statement with different bind variable values from Java. That doesn't strike me as much cleaner than the options in the other answer though. — Justin Cave, Oct 03 '20 at 12:09
There are several options how to solve this: Utilize GTT, PTT, PLSQL,JSON or even MAGIC! Check out this link: https://asktom.oracle.com/pls/apex/asktom.search?tag=limit-and-conversion-very-long-in-list-where-x-in — Bjarte Brandt, Oct 03 '20 at 14:18

score 1 · Answer 1 · answered Oct 03 '20 at 12:20

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.

score 0 · Answer 2 · answered Oct 03 '20 at 14:00

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;
/

Handle more than 1000 records updates in Spring Hibernate

2 Answers2