I am using below code to insert the record into database,
The above code is working fine when I do not have any duplicate value in database as like new Customer object.
If I have any duplicate value for Customer in database I am getting “SQLIntegrityConstraintViolationException”.
Here I am using persist () to insert the record into database for performance issue. If I use merge (), its working fine. but its need more time to complete the task.
In persist () case is there any option to identify which is duplicate record (i.e get duplicate record information)?
If more than one duplicate record present in DB, is there any option to identify which are duplicates records?
Here, before inserting the record into Database. I am having few record in database and not sure the new insert record (from code) will be similar existing recode in database.
If have any duplicate record (i.e record from database and record from code are same). I have to identify that particular error record.
Is the API is providing any support to get error record information and insert the other record without fail.
In my application I am using the huge record size (approx. 10000000 records) so if use merge() its need more time to finish the task. In this case I have identify the error record information.
Is it clear? Please let me know is any additional information needed on the same.
Yes, I think JPA provides a way to check if the entity/record already exists in the db or not..
However, the violation exception need not be only for the case of duplicate record insertion. It occurs even if the new record has one attribute which violates the unique constraints imposed on the table.
So, if we look at the stack trace it should mention the table and attribute which is causing this exception.. The stack trace will be similar to the one seen here::
To be honest if this is a very large batch you are better off with JDBC. This is not the type of thing ORM's excel at. You might look and see if your database has some sort of Upsert functionality.
Otherwise if you are set on using an ORM for this you will find in the documentation (assuming hibernate here) recommendations for improving performance and memory usage. This includes disabling 2nd level cache and setting the hibernate.jdbc.batch_size to the same size as your procedure batch size.
The code you posted is fine for a batch insert but you have updates too. This is not going to be efficient by just changing it to merge. If you see the documentation they recommend loading database records into memory one by one using a cursor (you are not going to accomplish this with JPA) and flushing the batch and clearing the context at set intervals similarly to what you do above (except above you do not have the need to load the object) . You can also think about using a stored procedure.
Remember merge will look in the persistence context for the object if it does not exist it goes to the database. This results in an extra select as well. You can't just change persist to merge and expect things to be performant.