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

Persistence.xml and the Oracle hint FIRST_ROWS

I found out that it is important to specify the exact version of Oracle in the persistence.xml file. I am currently using EclipseLink as the persistence framework provider.

Oracle hints were introduced to speed up/optimize SQL queries. Starting with Oracle 10, some of them have become almost unecessary because the optimizer was greatly improved. Also, depending on the structure of the queries and the database design (use of primary keys or not, index are where they should be or not, etc), hints can be counterproductive since they slow down queries instead of speeding them up.

SELECT * FROM (
SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (
SELECT col1, col2 from table1)
a WHERE ROWNUM <= 20)
WHERE rnum > 0;

This SQL query is used for pagination, was coded with JPA criteria and was generated by EclipseLink using ORACLE as the target database as specified in the following persistence.xml file :

<property name="eclipselink.target-database" value="Oracle" />

We use Oracle 11g. In order to indicate EclipseLink to produce SQL code for Oracle11, the target database should be ORACLE11, not ORACLE:

<property name="eclipselink.target-database" value="Oracle11" />

In that case, the SQL query generated no longer contains the FIRST_ROWS hint :

SELECT * FROM (
SELECT a.*, ROWNUM rnum  FROM (
SELECT col1, col2 from table1)
a WHERE ROWNUM <= 20)
WHERE rnum > 0;

If we examine the source code of EclipseLink, we can see in OraclePlatform.java the initial value of the HINT variable :

<b>protected</b> String HINT = "/*+ FIRST_ROWS */ ";

In the Oracle10Platform class, which inherits from OraclePlatform, that value is overriden :

public Oracle10Platform(){
super();
//bug 374136: override setting the FIRST_ROWS hint as this is not needed on Oracle10g
HINT = "";
}

EclipseLink error : Null primary key encountered in unit of work clone

With EclipseLink used as the JPA provider, the following error message “Null primary key encountered in unit of work clone” may appear if the value 0 is used for a field which is a primary key.

To resolve it, a few solutions :

1) start your primary key values from 1 instead of 0, which in my opinion is the best solution.

2) add the following annotation to the entity :


import javax.persistence.Entity;
import javax.persistence.Table;

import org.eclipse.persistence.annotations.IdValidation;
import org.eclipse.persistence.annotations.PrimaryKey;


/**
 * The persistent class for the HOUSE database table.
 * 
 */
@Entity
@Table(name = "HOUSE")
@PrimaryKey(validation=IdValidation.NULL)
public class House implements Serializable {

...

}

3) add the following property to the persistence.xml file :


<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
	xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">

	<persistence-unit name="myUnit">

		<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
		<jta-data-source>jdbc/MySource</jta-data-source>
		
		<class>com.blabla.myEntity</class>
						
		<properties>
                    <property name="eclipselink.allow-zero-id" value="true"/> 
		</properties>

	</persistence-unit>

</persistence>

Links :
http://eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_primarykey.htm

[JPA 2] Adding support to WebLogic 10.3.5 for JPA 2

WebLogic 10.3.5 is JAVA EE 5 certified. However JPA 2 is not part of the JAVA EE 5 (2006) specifications, it is part of the JAVA EE 6 (2009) specifications.
So since WebLogic Server implements the Java EE 5 specification, it is not required to support JPA 2. However it is possible to add support to WebLogic 10.3.5 for JPA 2. Look no further, the answer is of course on Oracle WebLogic website: Using Oracle TopLink with Oracle WebLogic Server

A default WebLogic installation already contains the files needed :

javax.persistence_1.0.0.0_2-0-0.jar
com.oracle.jpa2support_1.0.0.0_2-0.jar

So if you make the choice to configure things manually then you do not need to install a patch.
It is just a matter of declaring a PRE_CLASSPATH environment variable in the file commEnv.cmd (Windows) or commEnv.sh (Linux) located in the WL_HOME/common/bin/ folder.

Under Windows :

@rem JPA 2 activation

set PRE_CLASSPATH=%BEA_HOME%\modules\javax.persistence_1.0.0.0_2-0-0.jar;%BEA_HOME%\modules\com.oracle.jpa2support_1.0.0.0_2-0.jar

Under Linux :

PRE_CLASSPATH=${BEA_HOME}/modules/javax.persistence_1.0.0.0_2-0-0.jar:${BEA_HOME}/modules/com.oracle.jpa2support_1.0.0.0_2-0.jar
export PRE_CLASSPATH

If you do not configure WebLogic 10.3.5 to support JPA 2 you will get an error message like this one :

nested exception is:
	javax.ejb.EJBException: what do i do: seems an odd quirk of the EJB spec.
  The exception is:java.lang.NoSuchMethodError: javax.persistence.EntityManager.createQuery(Ljava/lang/String;Ljava/lang/Class;)Ljavax/persistence/TypedQuery;;
nested exception is: javax.ejb.EJBException: what do i do: seems an odd quirk of the EJB spec.
The exception is:java.lang.NoSuchMethodError: javax.persistence.EntityManager.createQuery(Ljava/lang/String;Ljava/lang/Class;)Ljavax/persistence/TypedQuery;

javax.ejb.EJBException: what do i do: seems an odd quirk of the EJB spec.
 The exception is:java.lang.NoSuchMethodError: javax.persistence.EntityManager.createQuery(Ljava/lang/String;Ljava/lang/Class;)Ljavax/persistence/TypedQuery;

There is no need to edit the file weblogic-application.xml and to add some additional prefered application package. That one for instance works :

<?xml version="1.0" encoding="UTF-8" ?>
<weblogic-application xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:wls="http://www.bea.com/ns/weblogic/90"
	xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/j2ee_1_4.xsd http://www.bea.com/ns/weblogic/90 http://www.bea.com/ns/weblogic/90/weblogic-application.xsd">
	<prefer-application-packages>
		<package-name>org.apache.commons.lang.*</package-name>
		<package-name>org.eclipse.persistence.*</package-name>
	</prefer-application-packages>
	<application-param>
		<param-name>webapp.encoding.default</param-name>
		<param-value>UTF-8</param-value>
	</application-param>
	<session-descriptor>
		<persistent-store-type>replicated_if_clustered</persistent-store-type>
	</session-descriptor>
</weblogic-application>


[JPA 2] Criteria API and MetaModel

I want to mention the Criteria API, a very cool API which in my opinion is not used as much as it should be. The developers who implement the specification (JSR 317: Java Persistence 2.0) do an impressive work. Thanks to them we have an API that makes it possible to write type safe queries in an object oriented way.
Usually Java developers write queries using JPQL or they write native queries. The fact is that the API Criteria has a (small) learning curve : you have to explore the API and study some basic queries examples before writing your own queries. The first time you use it, it does not seem as intuitive as JPQL.

The Criteria API is particularly convenient when writing queries which do not have a static where clause. For instance in the case of a web page where the user can make a research based on optional criterias. Then the generated query is not always the same.

The Criteria API has its advantages and its disadvantages : it produces typesafe queries that can be checked at compile time but on the other hand queries can be a bit hard to read (apparently unlike QueryDSL or EasyCriteria).
Another advantage is that it can help to avoid SQL injection since the user input is validated or escaped by the JDBC driver (which is not the case with native queries).

To create typesafe queries, one uses the canonical metamodel class associated to an entity (an idea originally proposed by Gavin King, as far as i know). A possible definition of a metamodel class could be that one : it is a class that provides meta information about a managed entity. By default, it has the same name as the entity plus an underscore. For instance if an entity is called Employee then the metamodel class is called Employee_. It is annotated with the annotation javax.persistence.StaticMetamodel.
Fortunately you do not have to write them, you can generate them using an annotation processor, through Eclipse or a Maven plugin for instance.
I chose to generate the metamodel classes with the help of the Hibernate Metamodel Generator (an annotation processor) and the maven-processor-plugin maven plugin, at each build so that they are updated whenever the entities are modified. It is a good way to keep the metamodel classes up to date.

<plugin>
<groupId>org.bsc.maven</groupId>
<artifactId>maven-processor-plugin</artifactId>
<version>2.0.5</version>
<dependencies>
<!-- Annotation processor to generate the JPA 2.0 metamodel classes for typesafe criteria queries -->
  <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-jpamodelgen</artifactId>
    <version>1.2.0.Final</version>
  </dependency>
</dependencies>
<executions>
  <execution>
    <id>process</id>
    <goals>
      <goal>process</goal>
    </goals>
    <phase>generate-sources</phase>
    <configuration>
      <outputDirectory>${project.basedir}/src/main/.metamodel/</outputDirectory>
      <processors>
        <processor>org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor</processor>
      </processors>
    </configuration>
  </execution>
</executions>
</plugin>

<!--  add sources (classes generated inside the .metamodel folder) to the build -->
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>build-helper-maven-plugin</artifactId>
<version>1.7</version>
<executions>
  <execution>
    <id>add-source</id>
    <phase>generate-sources</phase>
    <goals>
      <goal>add-source</goal>
    </goals>
    <configuration>
      <sources>
        <source>${basedir}/src/main/.metamodel</source>
      </sources>
    </configuration>
  </execution>
</executions>
</plugin>

And here is the kind of dynamic query one can create :
Continue reading

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

My book on EJB 3 is now on sale

I received an email from the editor informing me that the book is now printed and will be available in bookstores next week (from July 5th, 2010).

It is already available for sale on Amazon and ENI.

An online version is also available.

Here is the cover of the book :

SO3EJB_max