why saveAll() always inserts data instead of update it?

Question

Spring Boot 2.4.0, DB is MySql 8.

Data is fetched every 15 seconds from remote with REST and storing it to MySql DB with saveAll().

Which call the save() method for all the given entities.

All data has set ID.
And I am expecting that if there is no such id at DB - it will be inserted.
If such ID is already presented at DB - it will be updated.

Here is snipped from the console:

Hibernate: 
    insert 
    into
        iot_entity
        (controller_ref, description, device_id, device_ref, entity_type_ref, hw_address, hw_serial, image_ref, inventory_nr, ip6address1, ip6address2, ip_address1, ip_address2, latlng, location, mac_address, name, params, status, tenant, type, id) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
...
2020-12-05 23:18:28.269 ERROR 15752 --- [  restartedMain] o.h.e.jdbc.batch.internal.BatchingBatch  : HHH000315: Exception executing batch [java.sql.BatchUpdateException: Duplicate entry '1' for key 'iot_entity.PRIMARY'], SQL: insert into iot_entity (controller_ref, description, device_id, device_ref, entity_type_ref, hw_address, hw_serial, image_ref, inventory_nr, ip6address1, ip6address2, ip_address1, ip_address2, latlng, location, mac_address, name, params, status, tenant, type, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2020-12-05 23:18:28.269  WARN 15752 --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1062, SQLState: 23000
2020-12-05 23:18:28.269 ERROR 15752 --- [  restartedMain] o.h.engine.jdbc.spi.SqlExceptionHelper   : Duplicate entry '1' for key 'iot_entity.PRIMARY'
2020-12-05 23:18:28.269 DEBUG 15752 --- [  restartedMain] o.s.orm.jpa.JpaTransactionManager        : Initiating transaction rollback after commit exception

org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [insert into iot_entity (controller_ref, description, device_id, device_ref, entity_type_ref, hw_address, hw_serial, image_ref, inventory_nr, ip6address1, ip6address2, ip_address1, ip_address2, latlng, location, mac_address, name, params, status, tenant, type, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; constraint [iot_entity.PRIMARY]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch

Here is how to fetch and to save look like:

@Override
@SneakyThrows
@Scheduled(fixedDelay = 15_000)
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void fetchAndStoreData() {
    IotEntity[] entities = restTemplate.getForObject(properties.getIotEntitiesUrl(), IotEntity[].class);

    log.debug("ENTITIES:\n{}", mapper.writerWithDefaultPrettyPrinter().writeValueAsString(entities));

    if (entities != null && entities.length > 0) {
        entityRepository.saveAll(List.of(entities));
    } else {
        log.warn("NO entities data FETCHED !!!");
    }
}

This method runs every 15 seconds.

Entity:

@Data
@Entity
@NoArgsConstructor
@EqualsAndHashCode(of = {"id"})
@ToString(of = {"id", "deviceId", "entityTypeRef", "ipAddress1"})
public class IotEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    private Integer id;
    // other fields

and Repository:

public interface EntityRepository extends JpaRepository<IotEntity, Integer> {
}

Here is snipped for iot entity at JSON format:

2020-12-05 23:18:44.261 DEBUG 15752 --- [pool-3-thread-1] EntityService : ENTITIES:
[ {
  "id" : 1,
  "controllerRef" : null,
  "name" : "Local Controller Unterföhring",
  "description" : "",
  "deviceId" : "",
  ...

So ID is definitely set.

Also, batching is enabled for a project. It shouldn't have any impact on saving.

I could not understand why it tries to insert a new entity instead of update the existing one?
Why it couldn't distinguish the difference between the old and new entities?

UPDATE:

Implemented Persistable for Entity:

@Data
@Entity
@NoArgsConstructor
@EqualsAndHashCode(of = {"id"})
@ToString(of = {"id", "deviceId", "entityTypeRef", "ipAddress1"})
public class IotEntity implements Serializable, Persistable<Integer> {
    private static final long serialVersionUID = 1L;

    @Id
    private Integer id;

    @Override
    public boolean isNew() {
        return false;
    }

    @Override
    public Integer getId() {
        return this.id;
    }

However, it fails with the same exception - Duplicate entry '1' for key 'iot_entity.PRIMARY'

If I will add @GeneratedValue like the following:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

It wouldn't fail. However, it will update the ID value by itself.

For example, it fetched with id = 15:

[ {
  "id" : 15,
  "carParkRef" : 15,
  "name" : "UF Haus 1/2",

And should be saved like following:

In fact it has id = 2 instead:

And it is incorrect.

Tried to add to storing service:

private final EntityManager entityManager;
...
List.of(carParks).forEach(entityManager::merge);

Fails with the same exception (with or without implementing Persistable). It tries to insert the value - insert into ... Duplicate entry '15' for key '... .PRIMARY'

Snippet from application.yml:

spring:
  # ===============================
  # = DATA SOURCE
  # ===============================
  datasource:
    url: jdbc:mysql://localhost:3306/demo_db
    username: root
    password: root
    initialization-mode: always

  # ===============================
  # = JPA / HIBERNATE
  # ===============================
  jpa:
    show-sql: true
    generate-ddl: true
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        format_sql: true
        generate_statistics: true

Here you could see pom file content.

How to fix this issue?

score 3 · Answer 1 · answered Dec 06 '20 at 17:44

3

The problem is likely that, since the @Id is not marked with @GeneratedValue, Spring Data assumes all detached (transient) entities passed to save()/saveAll() should have EntityManager.persist() invoked on them.

Try making IotEntity implement Persistable and returning false from isNew(). This will tell Spring Data to always use EntityManager.merge() instead, which should have the desired effect (i.e. inserting nonexistent entities and updating existing ones).

answered Dec 06 '20 at 17:44

crizzis

9,978
2
28
47

tried this approach and updated question accordingly. Is there any possibility to use `@GeneratedValue`? I tried AUTO and IDENTITY - both save to DB with their own identifaer (from 1) instead of using ID which already set. – catch23 Dec 06 '20 at 18:04
For the scenario in which all input has ids set - nope. If you relied on autogeneration, and thus, only existing entities in the `entities` table had their preexisting ids set, the original code should work for you out of the box, though – crizzis Dec 06 '20 at 18:39
It's a little weird that telling Spring Data entities are not new didn't work. Could you try injecting a plain `EntityManager` instead of the repository and calling `EntityManager.merge()` on all the entities? Let me know if that works – crizzis Dec 06 '20 at 18:46
1

Replacing `entityRepository.saveAll(List.of(entities))` with `List.of(entities).forEach(entityManager::merge)` (you need to inject `EntityManager` into the service, of course) – crizzis Dec 06 '20 at 19:31
1

do you have idea what exactly going on? How to fix it? – catch23 Dec 06 '20 at 21:15
Nope, sorry, I'm out of ideas. To the best of my knowledge, `merge()` should work in this scenario. Clearly, Hibernate thinks all of the entities are new for some strange reason. The only workaround I can offer would be to implement the upsert logic yourself (try to find each entity using `findById`, if found - update its properties, if not - call `.save()` on the new entity) – crizzis Dec 06 '20 at 22:36

catch23 · Accepted Answer · 2020-12-18T11:19:51.577

Looks like I found the root of this behaviour.

Main App launcher look like:

@AllArgsConstructor
@SpringBootApplication
public class Application implements CommandLineRunner {

    private final DataService dataService;
    private final QrReaderServer qrReaderServer;
    private final MonitoringService monitoringService;

    @Override
    public void run(String... args) {
        dataService.fetchAndStoreData();
        monitoringService.launchMonitoring();
        qrReaderServer.launchServer();
    }

All 3 steps have strict execution sequence. And the first one has to repeat for updating data locally if it is needed. Two other just servers which work with stored data only.

Where the first method look like:

@Scheduled(fixedDelay = 15_000)
public void fetchAndStoreData() {
    log.debug("START_DATA_FETCH");

    carParkService.fetchAndStoreData();
    entityService.fetchAndStoreData();
    assignmentService.fetchAndStoreData();
    permissionService.fetchAndStoreData();
    capacityService.fetchAndStoreData();

    log.debug("END_DATA_FETCH");
}

Also, this execution is scheduled as well.

When the app starts it tried to execute this fetching twice:

2020-12-14 14:00:46.208 DEBUG 16656 --- [pool-3-thread-1] c.s.s.s.data.impl.DataServiceImpl        : START_DATA_FETCH
2020-12-14 14:00:46.208 DEBUG 16656 --- [  restartedMain] c.s.s.s.data.impl.DataServiceImpl        : START_DATA_FETCH

2 threads run at the same catch and store in parallel - trying to insert data. (tables are recreated at every start).

All later fetches are fine, they are executed only by @Sceduled thread.

If comment @Sceduled - it will work fine without any Exceptions.

SOLUTION:

Added additional boolean property to service class:

@Getter
private static final AtomicBoolean ifDataNotFetched = new AtomicBoolean(true);

@Override
@Scheduled(fixedDelay = 15_000)
@Order(value = Ordered.HIGHEST_PRECEDENCE)
public void fetchAndStoreData() {
    ifDataNotFetched.set(true);
    log.debug("START_DATA_FETCH");

    // fetch and store data with `saveAll()`

    log.debug("END_DATA_FETCH");
    ifDataNotFetched.set(false);
}

And control the value after the application is started:

@Value("${sharepark.remote-data-fetch-timeout}")
private int dataFetchTimeout;
private static int fetchCounter;

@Override
public void run(String... args) {
    waitRemoteDataStoring();
    monitoringService.launchMonitoring();
    qrReaderServer.launchServer();
}

private void waitRemoteDataStoring() {
    do {
        try {
            if (fetchCounter == dataFetchTimeout) {
                log.warn("Data fetch timeout reached: {}", dataFetchTimeout);
            }

            Thread.sleep(1_000);

            ++fetchCounter;
            log.debug("{} Wait for data fetch one more second...", fetchCounter);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    } while (DataServiceImpl.getIfDataNotFetched().get() && fetchCounter <= dataFetchTimeout);
}

score 0 · Answer 3 · answered Dec 18 '20 at 17:36

0

Spring Data JPA uses combination of @version @Id field to decide the whether to merge or insert.

null @id and null @version would mean new record hence insert
if @id is present @version field is used to decide whether to merge or insert.
Update is only invoked when (update .... where id = xxx and version = 0)

Beacuse you have @id and @version missing, its trying to insert, because underlysing system decided this is new record and when run sql u get error.

answered Dec 18 '20 at 17:36

Zafar Ali

37
1
8

you missed that remote data has ID set. The local entity which represents this remote data has `@Id` annotation as well – catch23 Dec 18 '20 at 18:31

score 0 · Answer 4 · edited Aug 02 '21 at 04:37

0

Can you pls try with @GeneratedValue(strategy = GenerationType.AUTO) This worked for me.

edited Aug 02 '21 at 04:37

STA

30,729
8
45
59

answered Aug 02 '21 at 04:29

Harsh Shah

1

why saveAll() always inserts data instead of update it?

4 Answers4

Linked