CODEWITHSUNDEEP
spring-bootkotlinspring-data-jpa
Tobias Hermann
Tobias Hermann

Reputation: 10956

How to make JpaRepository only save the IDs that do not yet exist?

Given a table:

CREATE TABLE mytable (
    some_id      BIGINT PRIMARY KEY NOT NULL,
    timestamp    TIMESTAMP WITH TIME ZONE NOT NULL
);

An entity:

@Entity
@Table(name = "mytable")
class Thing(
        @Id
        var some_id: Long,
        var timestamp = ZonedDateTime.now()
)

A repository:

interface MyRepo : JpaRepository<Thing, Long>

And this loop:

val myRepo: MyRepo

while (True) {
    val some_id: Long = ComesFromSomewhere()
    if (myRepo.findById(some_id) == null) {
        myRepo.save(Thing(some_id))
    }
}

The intention is to only store a timestamp for an ID the first time this ID occurs.

The code above works, but results in over 100 save transactions per second in my use case, which puts heavy load on the DB. So I'd like to optimize, accumulate a batch of Things and then use something like saveAll to have everything in one transaction.

Just using saveAll however would also overwrite the timestamps of the IDs already present in the table, which is not intended.

How can this be solved, ideally without too much low-level code?

Upvotes: 0

Views: 70

Answers (1)

Vlad Dinulescu
Vlad Dinulescu

Reputation: 1221

If you put the above loop within a @Transactional method, you should have only 1 transaction - with lots of INSERT-s and SELECT-s, of course, but 1 transaction. This would relieve the burden on the DB.

Also, save() is the proper method to call in this case. There's also a method called saveAndFlush() which is the opposite of what you want.

You could also limit the number of SELECTs by doing repo.findAllById() instead of lots of findById().

Upvotes: 1

Related Questions