为什么saveAll()总是插入数据而不是更新数据? [英] why saveAll() always inserts data instead of update it?
问题描述
Spring Boot 2.4.0 ，DB是MySql 8 .
每15秒钟使用REST从远程获取数据，并使用 saveAll()将其存储到MySql DB.
实际上，它具有 id = 2 :
这是不正确的.
试图添加到存储服务:
private最终EntityManager实体管理器；...List.of(停车场).forEach(entityManager :: merge); 具有相同异常的失败(无论是否实现Persistable).它尝试将值- insert插入到...键'.... PRIMARY'的重复条目'15'
来自 application.yml 的代码段:
spring:#==============================#=数据源#==============================数据源:网址:jdbc:mysql://localhost:3306/demo_db用户名:root密码:root初始化模式:始终#==============================#= JPA/HIBERNATE#==============================jpa:show-sql:正确generate-ddl:是休眠:ddl-auto:更新特性:休眠:format_sql:是generate_statistics:是 在这里您可以看到 pom文件内容.
如何解决此问题?
好像我找到了这种行为的根源.
主应用启动器如下:
@AllArgsConstructor@SpringBootApplication公共类应用程序实现CommandLineRunner {私有最终DataService dataService；私有的最终QrReaderServer qrReaderServer；私有最终MonitoringService MonitoringService;@Overridepublic void run(String ... args){dataService.fetchAndStoreData();monitoringService.launchMonitoring();qrReaderServer.launchServer();} 所有3个步骤都有严格的执行顺序.如果需要，第一个必须重复进行本地更新数据.另外两个仅用于存储数据的服务器.
第一种方法的外观如下:
@Scheduled(fixedDelay = 15_000)公共无效fetchAndStoreData(){log.debug("START_DATA_FETCH")；carParkService.fetchAndStoreData();entityService.fetchAndStoreData();assignmentService.fetchAndStoreData();PermissionService.fetchAndStoreData();CapacityService.fetchAndStoreData();log.debug("END_DATA_FETCH")；} 此外，执行计划也是预定的.
应用启动时，它尝试执行两次提取操作:
2020-12-14 14:00:46.208调试16656 --- [pool-3-thread-1] c.s.s.s.data.impl.DataServiceImpl:START_DATA_FETCH2020-12-14 14:00:46.208调试16656 --- [restartedMain] c.s.s.s.data.impl.DataServiceImpl:START_DATA_FETCH 2个线程以相同的速度运行并并行存储-尝试插入数据.(在每次启动时都会重新创建表).
以后所有的提取都可以，只能由 @Sceduled 线程执行.
如果注释 @Sceduled -可以正常运行，没有任何异常.
解决方案:
为服务类添加了其他布尔属性:
@Getter私有静态最终AtomicBoolean ifDataNotFetched = new AtomicBoolean(true);@Override@Scheduled(fixedDelay = 15_000)@Order(值= Ordered.HIGHEST_PRECEDENCE)公共无效fetchAndStoreData(){ifDataNotFetched.set(true);log.debug("START_DATA_FETCH")；//使用`saveAll()`获取和存储数据log.debug("END_DATA_FETCH")；ifDataNotFetched.set(false);} 在应用程序启动后控制值:
@Value("$ {sharepark.remote-data-fetch-timeout}")private int dataFetchTimeout;私有静态int fetchCounter;@Overridepublic void run(String ... args){waitRemoteDataStoring();monitoringService.launchMonitoring();qrReaderServer.launchServer();}私人无效waitRemoteDataStoring(){做 {尝试 {如果(fetchCounter == dataFetchTimeout){log.warn(达到数据提取超时:{}"，dataFetchTimeout)；}Thread.sleep(1_000);++ fetchCounter;log.debug("{}等待数据再获取一秒钟..."，fetchCounter)；} catch(InterruptedException e){Thread.currentThread().interrupt();}} while(DataServiceImpl.getIfDataNotFetched().get()&& fetchCounter< = dataFetchTimeout);} 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?
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);
}
这篇关于为什么saveAll()总是插入数据而不是更新数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
