Batch insert data with JPA, Hibernate and PostgreSQL

So you coded the data layer of your application using JPA (with for instance the Hibernate implementation) and when the application reaches the staging environment, you hear it is too slow !
Too many queries, too long queries, etc.
What do you do ? You try to optimize your data layer !
One possible optimization is to batch insert/update data.
The following properties must be set in persistence.xml :

...
      <property name="hibernate.jdbc.batch_size" value="50" />
      <property name="hibernate.order_inserts">true</property>
      <property name="hibernate.order_updates">true</property>
...

Now the big question is : how do i know it works ?
Checking the Hibernate logs still shows that several insert queries where generated instead of 1 (version used is hibernate-core-4.3.6.Final.jar) :


02 nov. 2017 19:02:42,190 DEBUG SQL(109) -
insert
    into
        lambda
        (id_lambda, color, city)
    values
        (?, ?, ?)
02 nov. 2017 19:02:42,193 DEBUG AbstractBatchImpl(145) - Reusing batch statement
02 nov. 2017 19:02:42,193 DEBUG SQL(109) -
    insert
    into
        lambda
        (id_lambda, color, city)    
  values
        (?, ?, ?)
02 nov. 2017 19:02:42,196 DEBUG AbstractBatchImpl(145) - Reusing batch statement
02 nov. 2017 19:02:42,196 DEBUG SQL(109) -
    insert
    into
        lambda
        (id_lambda, color, city)    
    values
        (?, ?, ?)
02 nov. 2017 19:02:42,199 DEBUG AbstractBatchImpl(145) - Reusing batch statement
02 nov. 2017 19:02:42,199 DEBUG SQL(109) -
    insert
    into
        lambda
        (id_lambda, color, city)    
    values
        (?, ?, ?)
02 nov. 2017 19:02:42,201 DEBUG BatchingBatch(110) - Executing batch size: 4
02 nov. 2017 19:02:42,253 DEBUG Expectations(78) - Success of batch update unknown: 0
02 nov. 2017 19:02:42,254 DEBUG Expectations(78) - Success of batch update unknown: 1
02 nov. 2017 19:02:42,254 DEBUG Expectations(78) - Success of batch update unknown: 2
02 nov. 2017 19:02:42,254 DEBUG Expectations(78) - Success of batch update unknown: 3
02 nov. 2017 19:02:42,254 DEBUG SQL(109) -

The thing to remember and keep in mind is that the Hibernate logs are one thing and what ACTUALLY happens in the database is another thing.

The postgreSQL logs (C:\Program Files\PostgreSQL\9.4\data\pg_log\postgresql-2017-11-06_100916.log) show a batched insert statement :


2017-11-02 19:02:42 CET LOG:  execute <unnamed>: insert into lambda (id_lambda, color, city) values ($1, $2, $3),($4, $5, $6),($7, $8, $9),($10, $11, $12)
2017-11-02 19:02:42 CET DETAIL:  parameters : $1 = '1', $2 = 'blue', $3 = 'Paris', $4 = '2', $5 = 'red', $6 = 'Dakar', $7 = '3', $8 = 'red', $9 = 'Lisbon', $10 = '4', $11 = 'violet', $12 = 'Helsinki'

To make it work with postgreSQL it is necessary to use the latest JDBC postgreSQL driver :

...
 <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.1.4.jre7</version>
  </dependency>
...

That version of the driver supports batched insert statements through the use of the reWriteBatchedInserts property :

...
<bean id="poolPostgresql" class="org.apache.tomcat.jdbc.pool.PoolProperties">
...
<property name="dbProperties">
     <value>reWriteBatchedInserts=true</value>
</property>
</bean>
...

More info on that property here :
https://github.com/pgjdbc/pgjdbc/blob/master/README.md

Generate the database schema with Hibernate3 Maven Plugin

There is a nice Maven plugin for JPA/Hibernate that makes it possible to quickly generate the database schema (SQL) and save it in a file.
The artifactId of this plugin is hibernate3-maven-plugin.
It will scan all JPA annotations in the class files of the entities and generate the corresponding SQL queries.
A persistence.xml file is required.

  1. With version 2.2 :

Content of the pom.xml :


<build>
  <plugins>
...
<plugin>
				<groupId>org.codehaus.mojo</groupId>
				<artifactId>hibernate3-maven-plugin</artifactId>	
                                <version>2.2</version>			
				<configuration>
		       	   <components>
						<component>
							<name>hbm2ddl</name>
							<implementation>jpaconfiguration</implementation>																									
						</component>							
					</components>
				   <componentProperties>
                    <drop>true</drop>
                    <create>true</create>
                    <export>false</export>
                    <format>true</format>                    <outputfilename>schema-${DataBaseUser}-${DatabaseName}.sql</outputfilename>
                    <persistenceunit>myPU</persistenceunit>
                    <propertyfile>src/main/resources/database.properties</propertyfile>
                </componentProperties>
			  </configuration>		
			  <dependencies>
			  	<dependency>
					<groupId>com.oracle</groupId>
					<artifactId>ojdbc14</artifactId>
					<version>10.2.0.2.0</version>
				</dependency>			  
			  </dependencies>					
		</plugin>
	  </plugins>
	</build>

Continue reading

Eclipse Dali vs Hibernate Tools

The process of mapping tables to entities is greatly simplified with tools like Eclipse Dali and Hibernate Tools, both available as Eclipse plugins. It avoids mapping them by hand, which in my opinion is prone to mapping errors and takes more time. And I really do not see why one should map them by hand when great tools like Eclipse Dali and Hibernate Tools are available.
In my book, I describe the use of the Eclipse Dali plugin to automatically generate the entities.
Lately I have also used Hibernate Tools and I have already noticed a few differences between these two tools.
I am going to list some of these differences.
Continue reading