CODEWITHSUNDEEP
javasqloracle-databasehibernatejpa
sonic boom
sonic boom

Reputation: 904

Handle more than 1000 records updates in Spring Hibernate

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.

Upvotes: 0

Views: 1360

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6094

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

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143093

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.

Upvotes: 2

Related Questions