aspose file tools*
The moose likes Object Relational Mapping and the fly likes JPA: Issue on batch insert using persist() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA: Issue on batch insert using persist()" Watch "JPA: Issue on batch insert using persist()" New topic
Author

JPA: Issue on batch insert using persist()

Purushothaman Jayaraman
Greenhorn

Joined: Aug 10, 2011
Posts: 2
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?

Thanks,
PJ
lokesh sree
Ranch Hand

Joined: Oct 27, 2009
Posts: 99

Hi,
The IntegrityConstratinViolation could be because you are trying to insert a record with a key while there is already a record present in the db with that key.
http://docs.oracle.com/javase/6/docs/api/java/sql/SQLIntegrityConstraintViolationException.html
http://stackoverflow.com/questions/4928943/java-sql-sqlintegrityconstraintviolationexception
You need to look at your table and see on what columns you have unique key constraint. Also, the exception stacktrace should give some details on the column which is causing the violation. And NO, there can't be duplicate records in the db.. There will be only one record in the db and if we try to insert a duplicate we will run into this violation exception.

Lokesh
( SCBCD 5, CCENT, SCJP 5 )
Purushothaman Jayaraman
Greenhorn

Joined: Aug 10, 2011
Posts: 2
Thank you very much Lokesh for your reply.

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.

Thanks,
PJ
lokesh sree
Ranch Hand

Joined: Oct 27, 2009
Posts: 99

Hi,
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::
https://answers.atlassian.com/questions/83387/unable-to-complete-confluence-setup

Hope that helps..
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1632
    
    7

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.


[How To Ask Questions][Read before you PM me]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: JPA: Issue on batch insert using persist()
 
Similar Threads
BODM / class diagram questions
[B&S] Create Method
Update Single value in a row
Bodgit ans Scraper DuplicateKeyException
Gaps in JTable for deleted records?