How to pass parameters to hibernate in Spring 4.3.4 and Hibernate 5.2.5

Question

I was trying to enable hibernate batch insert and updates, but no matter where I put the parameters they didn't seem to ever take affect. My Spring configuration is mostly just XML. We're using Spring 4.3.4, Hibernate 5.2.5, Spring Data 1.10.5, and MySQL Connector 5.1.38

My MySql Connection string is jdbc:mysql://localhost:3306/DB_NAME_HERE?autoReconnect=true&useTimezone=false&rewriteBatchedStatements=true

My jpaContext.xml has this in it (I'm omitting the whole thing, and just including the parts where I set the parameters)

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" primary="true">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.jdbc.batch_size">100</prop>
            <prop key="hibernate.order_inserts">true</prop>
            <prop key="hibernate.order_updates" >true</prop>
            <prop key="hibernate.jdbc.fetch_size" >400</prop>
            <prop key="hibernate.jdbc.batch_versioned_data" >true</prop>
        </props>
    </property>
</bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="JpaPersistenceUnit" />
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="showSql" value="false"/>
            <property name="generateDdl" value="false"/>
            <property name="database" value="MYSQL"/>
        </bean>
    </property>
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.jdbc.batch_size">100</prop>
            <prop key="hibernate.order_inserts">true</prop>
            <prop key="hibernate.order_updates" >true</prop>
            <prop key="hibernate.jdbc.fetch_size" >400</prop>
            <prop key="hibernate.jdbc.batch_versioned_data" >true</prop>
        </props>
    </property>
</bean>

My persistance.xml looks like this

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.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_2_0.xsd">
    <persistence-unit name="JpaPersistenceUnit">
        <properties>
            <property name="hibernate.session_factory.interceptor" value="com.siftit.startup.HibernateInterceptor" />
            <property name="hibernate.jdbc.batch_size" value="100"/>
            <property name="hibernate.order_inserts" value="true"/>
            <property name="hibernate.order_updates" value="true"/>
            <property name="hibernate.jdbc.fetch_size" value="400"/>
            <property name="hibernate.jdbc.batch_versioned_data" value="true"/>
        </properties>
    </persistence-unit>
    <persistence-unit name="warehousePersistenceUnit"/>
</persistence>

The reason for the duplicated parameters in each area is that I was trying to figure out where they belonged, but no matter where I put them they didn't seem to work.

Here's the hibernate logging output for the configuration:

18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - SessionFactory name : null
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Automatic flush during beforeCompletion(): enabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Automatic session close at end of transaction: disabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Statistics: disabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Deleted entity synthetic identifier rollback: disabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Default entity-mode: pojo
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Check Nullability in Core (should be disabled when Bean Validation is on): enabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Allow initialization of lazy state outside session : disabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Using BatchFetchStyle : LEGACY
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Default batch fetch size: -1
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Maximum outer join fetch depth: 2
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Default null ordering: NONE
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Order SQL updates by primary key: disabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Order SQL inserts for batching: disabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - multi-tenancy strategy : NONE
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - JTA Track by Thread: enabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Query language substitutions: {}
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - JPA query language strict compliance: disabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Named query checking : enabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Second-level cache: enabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Second-level query cache: disabled
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Second-level query cache factory: org.hibernate.cache.internal.StandardQueryCacheFactory@2033240a
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Second-level cache region prefix: null
18:38:03,975 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Optimize second-level cache for minimal puts: disabled
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Structured second-level cache entries: disabled
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Second-level cache direct-reference entries: disabled
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Automatic eviction of collection cache: disabled
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - JDBC batch size: 15
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - JDBC batch updates for versioned data: enabled
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Scrollable result sets: enabled
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Wrap result sets: disabled
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - JDBC3 getGeneratedKeys(): enabled
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - JDBC result set fetch size: null
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Connection release mode: ON_CLOSE
18:38:03,991 [ContainerBackgroundProcessor[StandardEngine[Catalina]]] DEBUG                         org.hibernate.cfg.Settings: - Generate SQL with comments: disabled

Everything I've tried so far has failed, and it seems like I must be doing something stupid, because everyone else's seems to work, but so far I've had no luck.

Answer 1

If you want efficient inserts and updates there are at least two things you need to do.

Make sure all your entities use TableGenerated ids

@TableGenerator(name="USER_GENERATOR", table="SEQUENCE", pkColumnName="SEQ_NAME",
        valueColumnName="SEQ_COUNT", pkColumnValue="USER_SEQ", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "USER_GENERATOR")

If you use auto increment for id generation, each insert must it own SQL string, and JPA will need to do select LAST_INSERT_ID() to get the auto increment id, after each insert. My Guess is that hibernate.jdbc.fetch_size is simply ignored by the driver.

I have not tried this with the newest MySQL JDBC driver, but a few years ago, you needed to tell the driver to rewrite the batch statements, by including rewriteBatchedStatements=true on JDBC URL. If you did not do that, you may get many Inserts in a batch, but every insert will include all columns names, and you will not get the benefit of Insert into Table (columns...) Values (values..., values..., )

Edit: According to the MySQL JDBC documentation there are only two valid options, read all rows into the result set up front, or read the rows using a cursor one row at the time. My Guess is that hibernate.jdbc.fetch_size is simply ignored by the driver.

Answer 2

hibernate.jdbc.batch_size Will configure batching on the underlying JDBC connection used by Hibernate.

Along with this you need to implement some way to batch and execute your updates/inserts.

EntityManager em = getEntityManager();
int batchSize = 1000;
for (int i = 0; i < persons.size(); i++) {
    Person person = persons.get(i);
    em.persist(person);
    if(i % batchSize == 0) {
        em.flush();
        em.clear();
    }
 }
em.flush();
em.clear();