Ruben Laguna's blog

Sep 18, 2009 - 4 minute read - Comments - clear entity entitymanager extra hsqldb inefficient java openjpa persist sql

OpenJPA: Generated SQL contains extra UPDATEs

I’m trying to use OpenJPA to insert some entries in the database and I’m getting a strange number of UPDATEs beside the INSERTs.

I isolated the problem to the following snippet of code

private void start() {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("persistencexmltest1PU");
    EntityManager em = emf.createEntityManager();
    for (int i = 0; i < 10; i**) {
        em.getTransaction().begin();
        MyEntity n =new MyEntity();
        n.setValue(i);
        em.persist(n);
        em.getTransaction().commit();
    }
}

The generated SQL looks like this:

INSERT INTO MYTABLE (ID, VALUE, CREATED) VALUES (?, ?, ?) [params=(int) 1, (int) 0, (null) null]
INSERT INTO MYTABLE (ID, VALUE, CREATED) VALUES (?, ?, ?) [params=(int) 2, (int) 1, (null) null]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 1]
INSERT INTO MYTABLE (ID, VALUE, CREATED) VALUES (?, ?, ?) [params=(int) 3, (int) 2, (null) null]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 2]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 1]
INSERT INTO MYTABLE (ID, VALUE, CREATED) VALUES (?, ?, ?) [params=(int) 4, (int) 3, (null) null]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 3]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 2]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 1]
INSERT INTO MYTABLE (ID, VALUE, CREATED) VALUES (?, ?, ?) [params=(int) 5, (int) 4, (null) null]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 3]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 4]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 2]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 1]
INSERT INTO MYTABLE (ID, VALUE, CREATED) VALUES (?, ?, ?) [params=(int) 6, (int) 5, (null) null]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 3]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 4]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 2]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 5]
UPDATE MYTABLE SET CREATED = ? WHERE ID = ? [params=(null) null, (int) 1]
...

pay attention to the extra UPDATE statements after each INSERT, the number of extra UPDATEs grows as well. From zero UPDATEs after the first INSERT, one in the second, two in the third, and so on. I don’t need to say that this is of course really inefficient. I don’t know what is ultimate cause of this but this started when I added a @Temporal(TemporalType.TIMESTAMP) annotation to the entity class. And I can make it go away by calling EntityManager.clear() after each em.getTransaction().commit()

UPDATE: Ok I found a bug report stating that this behaviour is only observed when the entity classes are not enhanced. . So the best solution is use the enhancer. But it really doesn’t work for me, I still get the extra UPDATEs even with enhanced classes. So I’m stuck with the EntityManager.clear() for now.. If the class is PROPERLY ENHANCED the problem goes away.

Check the logs (enable them with <property name="openjpa.Log" value="DefaultLevel=TRACE"/> in persistence.xml) and make sure that you don’t see any entry like

5968  persistencexmltest1PU  INFO   [main] openjpa.Enhance - Creating subclass for "[class com.rubenlaguna.MyEntity]". This means that your application will be less efficient and will consume more memory than it would if you ran the OpenJPA enhancer. Additionally, lazy loading will not be available for one-to-one and many-to-one persistent attributes in types using field access; they will be loaded eagerly instead.

If you see the Creating subclass for message means that the class wasn’t enhanced, as I read OpenJPA really need enhanced classes, un - enhanced are just for testing and trivial examples.+

For reference:

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/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_1_0.xsd">
    <persistence-unit name="persistencexmltest1PU" transaction-type="RESOURCE_LOCAL">
        <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
        <class>com.rubenlaguna.MyEntity</class>
        <properties>
            <property name="openjpa.ConnectionPassword" value=""/>
            <property name="openjpa.ConnectionDriverName" value="org.hsqldb.jdbc.JDBCDriver"/>
            <property name="openjpa.ConnectionUserName" value="sa"/>
            <property name="openjpa.ConnectionURL" value="jdbc:hsqldb:file:/Users/ecerulm/everjavatest"/>
            <property name="openjpa.Log" value="SQL=TRACE"/>
            <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(SchemaAction='add,deleteTableContents',ForeignKeys=true)"/>
        </properties>
    </persistence-unit>
</persistence>

MyEntity.java

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package com.rubenlaguna;

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/****
*
* author Ruben Laguna <ruben.laguna at gmail.com>
 */
`Entity
`Table(name = "MYTABLE")
public class MyEntity {
    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Integer id;

    @Column(name = "VALUE")
    private Integer attr1;

    @Column(name = "CREATED")
    @Temporal(TemporalType.TIMESTAMP)
    private Date created;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getValue() {
        return attr1;
    }

    public void setValue(Integer value) {
        this.attr1 = value;
    }

}

References: