How do I stop spring data JPA from doing a SELECT before a save()?

Question

We are writing a new app against an existing database. I'm using Spring Data JPA, and simply doing a

MyRepository.save()

on my new entity, using

MyRepository extends CrudRepository<MyThing, String>

I've noticed in the logs that hibernate is doing a Select before the insert, and that they are taking a long time, even when using the indexes.

I've searched for this here, and the answers I've found usually are related to Hibernate specifically. I'm pretty new to JPA and it seems like JPA and Hibernate are pretty closely intertwined, at least when using it within the context of Spring Data. The linked answers suggest using Hibernate persist(), or somehow using a session, possibly from an entityManager? I haven't had to do anything with sessions or entityManagers, or any Hibernate API directly. So far I've gotten simple inserts done with save() and a couple @Query in my Repositories.

No. We see this happening on 2 tables. One of them there is a single field for the primary key, which we actually get from another table of pre-generated numbers. I'm using a simple Id for that table. The other is a composite Id, where I'm using an EmbeddedId — user26270, Aug 11 '17 at 13:19
Being able to see the sql logs you refer to would be kind of useful. — Gimby, Aug 11 '17 at 14:52
@Gimby what specifically are you looking for, and how can I get it? Is there a logging level and jpa/hibernate package I can set to more detail? we're using an application.yml for configuration; It's currently logging the generated sql it's using; I actually just took that sql and ran it in dbvisualizer; it ran immediately in dbvisualizer, but took several seconds while running in the app — user26270, Aug 11 '17 at 15:18
Yes, the generated SQL. Generally it is a good idea that when you're talking about something you actually show it. The same goes for the code of your entity. — Gimby, Aug 14 '17 at 09:03

Bertrand88 · Answer 1 · 2018-08-11T10:17:07.863

46

Here is the code of Spring SimpleJpaRepository you are using by using Spring Data repository:

@Transactional
public <S extends T> S save(S entity) {

    if (entityInformation.isNew(entity)) {
        em.persist(entity);
        return entity;
    } else {
        return em.merge(entity);
    }
}

It does the following:

By default Spring Data JPA inspects the identifier property of the given entity. If the identifier property is null, then the entity will be assumed as new, otherwise as not new.

Link to Spring Data documentation

And so if one of your entity has an ID field not null, Spring will make Hibernate do an update (and so a SELECT before).

You can override this behavior by the 2 ways listed in the same documentation. An easy way is to make your Entity implements Persistable (instead of Serializable), which will make you implement the method "isNew".

edited Aug 11 '18 at 10:17

answered Nov 09 '17 at 09:44

Bertrand88

701
6
14

5

The Persistable entity solution was amazing!! – Nick Div May 17 '18 at 07:49
1

This approach seems not to work on `@OneToMany` relationships. I tried to call `save` on the "One" entity, having made my "Many" entity override "Persistable". Still there were many Selects. – slartidan Jan 28 '20 at 16:04
Persistable isn't working for me unfortunately... I'm using Kotlin if that makes a difference – janedoe Apr 19 '20 at 17:39

score 20 · Answer 2 · answered May 03 '18 at 09:38

If you provide your own id value then Spring Data will assume that you need to check the DB for a duplicate key (hence the select+insert).

Better practice is to use an id generator, like this:

@Entity
public class MyThing {
    @Id
    @GeneratedValue(generator = "uuid2")
    @GenericGenerator(name = "uuid2", strategy = "uuid2")
    private UUID id;
}

If you really must insert your own id and want to prevent the select+insert then implement Persistable, e.g.

@Entity
public class MyThing implements Persistable<UUID> {

    @Id
    private UUID id;

    @Override
    public UUID getId() {
        return id;
    }

    //prevent Spring Data doing a select-before-insert - this particular entity is never updated
    @Override
    public boolean isNew() {
        return true;
    }
}

Note that the second example will break the default delete and deleteAll methods, since they use this method to decide if the entity exists and should therefore be deleted. — Tim Malich, Aug 20 '20 at 16:03

score 1 · Answer 3 · answered Nov 25 '21 at 18:15

1

I created a custom method in the @Repository:

    public void persistAll(Iterable<MyThing> toPersist) {
        toPersist.forEach(thing -> entityManager.persist(thing));
    }

answered Nov 25 '21 at 18:15

payne

4,691
8
37
85

firstpostcommenter · Answer 4 · 2022-10-18T10:00:06.740

If you provide your own ID value then Spring Data will assume that you need to check the DB for a duplicate key (hence the select+insert).

One option is to use a separate autogenerated ID column as Primary key but this option seems redundant. Because if you already have a Business/Natural ID that is unique then it is easier to make this as the @ID column instead of having a separate ID column.

So how to solve the problem?

The solution is to use @javax.persistence.Version on a new versionNumber column in all the tables. If you have a parent and child table then use @Version column in all the entity classes.

Add a column in the Entity class like this:

  @javax.persistence.Version
  @Column(name = "data_version")
  private Long dataVersion;

add column in SQL file:

"data_version"          INTEGER DEFAULT 0

Then I see that Spring data does not do Select before doing Insert.

Node, that default non required and spring set 0 manual. – gooogenot Jul 04 '23 at 11:22 — gooogenot, Jul 04 '23 at 11:22
For kotlin this is work: @Version val version: Long? = null – gooogenot Jul 04 '23 at 11:22 — gooogenot, Jul 04 '23 at 11:22

How do I stop spring data JPA from doing a SELECT before a save()?

4 Answers4

Linked