• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JPA: Issue on batch insert using persist()

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 100
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Purushothaman Jayaraman
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 100
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..
 
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic