large file from hive to rdbms(oracle)

Recently we have a requirement of dumping a sizable file(4+G) to oracle from s3. The file itself is hive-compatiable. so instead of downloading the file and generate sql for it, we decided to transfer the content using hive jdbc and persist in via jpa/hiberante. Hive On the hive side, one important thing is to make…

Optimize JPA one to many with fetch

 N+1 We all know the famous N+1 problem in the ORM world where the generated sql is not that optimized that we have to get the child collections with N number of separate sql query which leads to serious performance problem if there are many levels of objects tree. So we can fix the problem…

JPA hibernate @GeneratedValue with sequence on Oracle

When using Oracle, the JPA with hibernate implementation on the @GeneratedValue with sequence would be quite tricky. I am trying to make a summary here. The version used are JPA 2.1 and Hibernate 4.3.11.Final Default generator setting @GeneratedValue with strategy AUTO and default allocationSize(50) This will use the ‘org.hibernate.id.SequenceGenerator’ which will get a SEQ+1 value….

JPA SequenceGenerator with allocationSize 1 performance tuning

I had a blog last year about fixing the sequence number going wild by setting the allocationSize to 1. Overall it solves the inconsistency problem if you are using a sequence with ‘INCREMENT BY’ value 1 in database. Issue One problem comes up today is I am facing some performance issue with the above setting when I…

hive jdbc with Spring Beanpropertyrowmapper

In our project we need to port some hive table data to our local RDBMS(Oracle). For tables with a lot of columns(hundreds), it could be very tedious to wrote the hive sql and convert the resultSet to the Jpa entity object. Spring jdbctemplate provides us a good class which would do camel-case conversion to the underscore for us….

PESSIMISTIC_READ and PESSIMISTIC_WRITE

Readers–writer lock A database system is a highly concurrent environment, therefore many concurrency theory idioms apply to database access as well. Concurrent changes must be serialized to preserve data integrity, so most database systems use a two-phase locking strategy, even if it’s usually supplemented by a Multiversion concurrency control mechanism. Because a mutual exclusion locking…

Fix Hibernate JPA sequence generating odd id with allocationSize

I config my jpa entity class as usual using Intellij generated stub from DB schema with below config: However the id that is inserted into DB are way different(larger) than it should be. For example, id should be 10012 but it becomes 5223326. This odd number is actually getting from some other sequence. To fix…

batch / bulk insert/update in jpa/hibernate with flush and clear

For JPA use entity manager to do flush/clear For hibernate basically just switch the entity manager with hibernate session. When making new objects persistent flush() and then clear() the session regularly in order to control the size of the first-level cache. The suggest batch size is 20-50 by hibernate. However I found 1500 is good…

jpa performance over jdbc for large table

I have a table with about 80 million records. While I was doing a simple query using JPA with 2-3 predicates. It takes about 120s to get the result, comparing the 1s using JDBC. Notice, i am using exactly the same query that the jpa generates. This is somehow frustrating. To be honest, I have…

Transaction configuration with JPA and Spring 3.1

FROM HERE This is the fifth of a series of articles about Persistence with Spring. This article will focus on the configuration of transactions with Spring 3.1 and JPA. For a step by step introduction about setting up the Spring context using Java based configuration and the basic Maven pom for the project, see this…

“detached entity passed to persist error” with JPA

I got the answer, I was using: em.persist(user); I used merge in place of persist: em.merge(user); Here .persist() only will insert the record.If we use .merge() it will check is there any record exist with the current ID,If it exist it will update otherwise it will insert new record.    The error you described occurs…

hibernate/JPA get/find vs load difference

Hibernate load vs. Hibernate get/find Methods Well, if you were to compare the load and get methods of the Hibernate Session, you’d think that they looked pretty darned similar; and you’d be correct, but there are subtle and very important differences. First of all, the get method hits the database as soon as it is…