I have a scenario where a rest service which fetches huge data is persisted into a table. This service is required to execute every hour and if any update of existing data or new records is found in the response, the respective record should be updated in the database. Given this scenario, for every rest service response, I have to query the database and this would create multiple round trips to the database. Please advise if there are any ways to prevent this database round trips from the design perspective in Hibernate. Today I’m doing a linear search for every record and comparing the data.
First of all, you don’t need to all that in a single long-running transaction. You can split it into multiple batches that are executed one after the other, therefore reducing transaction response time for every individual batch step.
Now, each batch iteration will process N items. If the incoming entities don’t have an identifier, it means they are new. If they have some identifier or some natural identifier, you can fetch all existing entities matching a list of natural identifiers. So, if you find any matching entity, you can do the modifications and the UPDATE will be executed by Hibernate automatically.
Again, fetching multiple entities by their identifiers or natural identifiers can be done with a single query, as you don’t have to execute one SELECT per entity.
You should also use JDBC batch updates, as explained in this article.