Hibernate Batch processing for child table rows

Question

I tried the solution for batch insert in this link Hibernate Batch insert, but still not working the way it should. Below is the relevant code for your reference. I am uploading just one trial balance file which consists of 100 trial balance rows. So one trial balance is a list of 100 trial balance rows and I need to batch insert these 100 rows of data. This is a REST API and am using JSON managed reference since its a bi-directional relationship.

The result of below code is 6 rows in trial balance table are inserted, whereas the trial balance row table has 100 rows inserted only for the last trial balance id created, for the remaining id's no data was inserted in trial balance row table.

Can you point out where am I going wrong?

***TRIAL_BALANCE Entity***

@Entity
@Table(name="TRIAL_BALANCE")
public class TrialBalance {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="ID")
private int id;

@OneToMany(mappedBy="trialBalance", fetch=FetchType.EAGER, cascade=CascadeType.ALL)
@JsonManagedReference
private List<TrialBalanceRow> trialBalanceRows;

public void addTrialBalanceRows(List<TrialBalanceRow> tbRows) {

if ( trialBalanceRows == null ) {
trialBalanceRows = new ArrayList<TrialBalanceRow>();
}
for ( TrialBalanceRow tb : tbRows ) {
trialBalanceRows.add(tb);
tb.setTrialBalance(this);
}
}

***TRIAL_BALANCE_ROW Entity***

@Entity
@Table(name="TRIAL_BALANCE_ROW")
public class TrialBalanceRow {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="ID")
private int id;

@JsonBackReference
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="TRIAL_BALANCE_ID")
private TrialBalance trialBalance;

***TrialBalanceDOAImpl.java file***

public void addTrialBalance(TrialBalance trialBalance) {
    try {

        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();
        for ( int i=0; i<1200; i++ ) {

        session.save(trialBalance);
        if( i % 50 == 0 ) { // Same as the JDBC batch size
              //flush a batch of inserts and release memory:
              session.flush();
              session.clear();
           }
        }
        tx.commit();
        session.close();

    } catch (HibernateException e) {
        e.printStackTrace();
        throw new DataNotSavedException();
    }
}

hibernate properties

hibernate.dialect = org.hibernate.dialect.MySQLDialect
hibernate.show_sql = true
hibernate.format_sql = false
spring.jpa.properties.hibernate.jdbc.time_zone = UTC
spring.jpa.properties.hibernate.jdbc.batch_size = 50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true
spring.jpa.properties.hibernate.id.new_generator_mappings = false

Hibernate Config

private Properties hibernateProperties() {
    Properties properties = new Properties();
    properties.put("hibernate.dialect", environment.getRequiredProperty("hibernate.dialect"));
    properties.put("hibernate.show_sql", environment.getRequiredProperty("hibernate.show_sql"));
    properties.put("hibernate.format_sql", environment.getRequiredProperty("hibernate.format_sql"));
    properties.put("hibernate.jdbc.time_zone", 
            environment.getRequiredProperty("spring.jpa.properties.hibernate.jdbc.time_zone"));
    properties.put("hibernate.jdbc.batch_size", 
            environment.getRequiredProperty("spring.jpa.properties.hibernate.jdbc.batch_size"));
    properties.put("hibernate.order_inserts", 
            environment.getRequiredProperty("spring.jpa.properties.hibernate.order_inserts"));
    properties.put("hibernate.order_updates", 
            environment.getRequiredProperty("spring.jpa.properties.hibernate.order_updates"));
    properties.put("hibernate.jdbc.batch_versioned_data", 
            environment.getRequiredProperty("spring.jpa.properties.hibernate.jdbc.batch_versioned_data"));
    properties.put("hibernate.id.new_generator_mappings", 
            environment.getRequiredProperty("spring.jpa.properties.hibernate.id.new_generator_mappings"));

    return properties;        

}

Logs

2018-07-12 16:28:25 TRACE AbstractEntityPersister:3142 - Updating entity: [in.greenstack.ikon.entity.TrialBalanceRow#107399] 2018-07-12 16:28:25 DEBUG AbstractBatchImpl:129 - Reusing batch statement 2018-07-12 16:28:25 DEBUG SQL:92 - update TRIAL_BALANCE_ROW set ACCOUNT_CODE=?, ACCOUNT_NAME=?, CLOSING_BALANCE=?, GL_AMOUNT_CURR=?, GL_AMOUNT_PROP=?, GL_AMOUNT_REV=?, OPENING_BALANCE=?, REMARKS=?, TRANSACTION_CR=?, TRANSACTION_DR=?, TRIAL_BALANCE_ID=? where ID=? Hibernate: update TRIAL_BALANCE_ROW set ACCOUNT_CODE=?, ACCOUNT_NAME=?, CLOSING_BALANCE=?, GL_AMOUNT_CURR=?, GL_AMOUNT_PROP=?, GL_AMOUNT_REV=?, OPENING_BALANCE=?, REMARKS=?, TRANSACTION_CR=?, TRANSACTION_DR=?, TRIAL_BALANCE_ID=? where ID=? 2018-07-12 16:28:25 TRACE AbstractEntityPersister:2723 - Dehydrating entity: [in.greenstack.ikon.entity.TrialBalanceRow#107399] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter 1 as [INTEGER] - [0] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [2] as [VARCHAR] - [POOJA ENTERPRISE] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [3] as [DOUBLE] - [0.0] 2018-07-12 16:28:25 TRACE BasicBinder:53 - binding parameter [4] as [DOUBLE] - [null] 2018-07-12 16:28:25 TRACE BasicBinder:53 - binding parameter [5] as [DOUBLE] - [null] 2018-07-12 16:28:25 TRACE BasicBinder:53 - binding parameter [6] as [DOUBLE] - [null] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [7] as [DOUBLE] - [0.0] 2018-07-12 16:28:25 TRACE BasicBinder:53 - binding parameter [8] as [VARCHAR] - [null] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [9] as [DOUBLE] - [14000.0] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [10] as [DOUBLE] - [14000.0] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [11] as [INTEGER] - [361] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [12] as [INTEGER] - [107399] 2018-07-12 16:28:25 TRACE AbstractEntityPersister:3142 - Updating entity: [in.greenstack.ikon.entity.TrialBalanceRow#107400] 2018-07-12 16:28:25 DEBUG AbstractBatchImpl:129 - Reusing batch statement 2018-07-12 16:28:25 DEBUG SQL:92 - update TRIAL_BALANCE_ROW set ACCOUNT_CODE=?, ACCOUNT_NAME=?, CLOSING_BALANCE=?, GL_AMOUNT_CURR=?, GL_AMOUNT_PROP=?, GL_AMOUNT_REV=?, OPENING_BALANCE=?, REMARKS=?, TRANSACTION_CR=?, TRANSACTION_DR=?, TRIAL_BALANCE_ID=? where ID=? Hibernate: update TRIAL_BALANCE_ROW set ACCOUNT_CODE=?, ACCOUNT_NAME=?, CLOSING_BALANCE=?, GL_AMOUNT_CURR=?, GL_AMOUNT_PROP=?, GL_AMOUNT_REV=?, OPENING_BALANCE=?, REMARKS=?, TRANSACTION_CR=?, TRANSACTION_DR=?, TRIAL_BALANCE_ID=? where ID=? 2018-07-12 16:28:25 TRACE AbstractEntityPersister:2723 - Dehydrating entity: [in.greenstack.ikon.entity.TrialBalanceRow#107400] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter 1 as [INTEGER] - [0] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [2] as [VARCHAR] - [Poonam Pride Bunglows] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [3] as [DOUBLE] - [0.0] 2018-07-12 16:28:25 TRACE BasicBinder:53 - binding parameter [4] as [DOUBLE] - [null] 2018-07-12 16:28:25 TRACE BasicBinder:53 - binding parameter [5] as [DOUBLE] - [null] 2018-07-12 16:28:25 TRACE BasicBinder:53 - binding parameter [6] as [DOUBLE] - [null] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [7] as [DOUBLE] - [3842.0] 2018-07-12 16:28:25 TRACE BasicBinder:53 - binding parameter [8] as [VARCHAR] - [null] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [9] as [DOUBLE] - [15467.0] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [10] as [DOUBLE] - [11625.0] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [11] as [INTEGER] - [361] 2018-07-12 16:28:25 TRACE BasicBinder:65 - binding parameter [12] as [INTEGER] - [107400] 2018-07-12 16:28:25 DEBUG BatchingBatch:384 - Executing batch size: 50

I am using Hibernate v5.2.17 with spring security and spring v5+.

Thanks, Bhavna

Answer 1

Just for anyone else who would need to refer in future, the solution that worked for me was writing own SQL queries for batch inserts as below. Also I removed the CASCADETYPE.ALL feature in parent entity.

@Override
    public void addTrialBalance(TrialBalance trialBalance) {
        try {

            //Batch insertion code
            Session session = sessionFactory.openSession();
            Transaction tx = session.beginTransaction();
            final int batchSize = 50;


            session.save(trialBalance);
            session.doWork(new Work()
            {
                public void execute(Connection con) throws SQLException
                {
                    PreparedStatement st = con.prepareStatement(""
                            + "insert into TRIAL_BALANCE_ROW (ACCOUNT_CODE, ACCOUNT_NAME, ....,TRIAL_BALANCE_ID) "
                            + "values (?, ?,...,?)");
                    List<TrialBalanceRow> tbRows = trialBalance.getTrialBalanceRows();
                    List<TrialBalanceRow> trialBalanceRows = new ArrayList<TrialBalanceRow>();
                    int count = 0;

                    for (TrialBalanceRow tb : tbRows)
                    {
                        trialBalanceRows.add(tb);
                        tb.setTrialBalance(trialBalance);

                        count++;
                        st.setString(1, tb.getAccountCode());
                        st.setString(2, tb.getAccountName());
                        st.setDouble(3, tb.getClosingBalance());
                        .
                        .
                        .
                        st.setInt(11, tb.getTrialBalance().getId());
                        System.out.println("Adding to batch .......");
                        st.addBatch();

                        if(count % batchSize == 0) {
                            System.out.println("Executing Batch Size of : " + batchSize);
                            st.executeBatch();
                        }
                    }
                    st.executeBatch();
                    st.close();
                }

            });

            session.refresh(trialBalance);
            tx.commit();
            session.close();


        } catch (HibernateException e) {
            e.printStackTrace();
            throw new DataNotSavedException();
        }
    }

Thanks Vlad for your references (especially the one on GENERATIONTYPE.AUTO) and all the help, but even with order_inserts I was not able to get the desired result. With above solution I am able to achieve the required performance.

Answer 2

The best way to figure out what happens is to enable JDBC logging.

In your case, it's not clear whether you are committing the transaction. Are you using @Transactional on your service?

Also, you should use a proper logging framework, not e.printStackTrace.