2

I'm having trouble getting Hibernate to perform a bulk insert on MySQL.

I have the primary key generation type to Sequence and below is my dao.xml

    <?xml version="1.0" encoding="UTF-8"?>
  <beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd
        ">

    <context:component-scan base-package="com.test" />
    <context:annotation-config />

    <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="shutdown">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true" />
        <property name="username" value="root"/>
        <property name="password" value=""/>
        <property name="idleTimeout" value="200"/>
        <property name="maximumPoolSize" value="5"/>
        <property name="connectionTimeout" value="30000"/>
        <property name="validationTimeout" value="5000"/>
        <property name="initializationFailFast" value="true"/>
        <property name="maxLifetime" value="1800000"/>
    </bean>

    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="persistenceUnitName" value="test-jpa"/>
        <property name="dataSource" ref="dataSource"/>
        <property name="jpaDialect">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
        </property>
        <property name="jpaPropertyMap">
            <map>
                <entry key="hibernate.jdbc.batch_size" value="10"/>
                <entry key="hibernate.order_inserts" value="true"/>
                <entry key="hibernate.order_updates" value="true"/>
                <entry key="hibernate.format_sql" value="true"/>
            </map>
        </property>
        <!--<property name="jpaProperties">-->
            <!---->
        <!--</property>-->
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="showSql" value="true"/>
                <property name="generateDdl" value="false"/>
                <property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect"/>
                <property name="database" value="MYSQL"/>
            </bean>
        </property>
    </bean>

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor">
        <property name="defaultPersistenceUnitName" value="test-jpa"/>
    </bean>

    <tx:annotation-driven/>

</beans>

This enables single query to be executed every time. I am also flushing and clearing the entity manager accordingly.

Tech Stack: Spring 4, Hibernate 5.2.

Edit 1: I have also gone through the below link but no luck https://vladmihalcea.com/how-to-batch-insert-and-update-statements-with-hibernate/

Edit 2:

I have followed the below link as well...but still no luck.

JPA customize the JDBC batch size is not working

datasource-proxy is printing the below log

Name:my-ds, Connection:2, Time:0, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["insert into student (created_by, created_on, last_modified_on, version, name) values (?, ?, ?, ?, ?)"], Params:[(test,2018-05-13 11:51:29.184,2018-05-13 11:51:29.184,0,Ashok Test 16)]

1 Answer 1

4

When you are using the IDENTITY generator, Hibernate cannot use batching for INSERT statements. It can only batch UPDATE and DELETE statements.

Nevertheless, switching to TABLE generator is even worse.

So, for MySQL, the only way to batch INSERT statements is if you generate the INSERT statements using JDBC.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.