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?

