MyBatis Batch Insert/Update For Oracle

by

The accepted answer is not the recommended way of handling batch operations. It does not show true batch statements since the batch executor mode should be used when opening a session. See this post in which a code contributor recommended that the proper way to batch update (or insert) is to open a session in batch mode and repeatedly call update (or insert) for a single record.

Here’s what works for me:

public void updateRecords(final List<GisObject> objectsToUpdate) {
    final SqlSession sqlSession = MyBatisUtils.getSqlSessionFactory().openSession(ExecutorType.BATCH);
    try {
        final GisObjectMapper mapper = sqlSession.getMapper(GisObjectMapper.class);
        for (final GisObject gisObject : objectsToUpdate) {
            mapper.updateRecord(gisObject);
        }
        sqlSession.commit();
    } finally {
        sqlSession.close();
    }
}

Do not use foreach in your update/insert and ensure that it only updates/inserts a single record. I was running into unsolvable oracle errors by doing it according to the accepted answer (invalid character, statement not ended, etc.). As the linked post indicates, the update (or insert) shown in the accepted answer is actually just a giant sql statement.

More Related Contents:

  1. Using setDate in PreparedStatement
  2. java.sql.SQLException: Exhausted Resultset
  3. How to enable logging for SQL statements when using JDBC
  4. Cleanest way to build an SQL string in Java
  5. Getting output from dbms_output.get_lines using JDBC
  6. JDBC ResultSet get columns with table alias
  7. Getting an exception ORA-00942: table or view does not exist – when inserting into an existing table
  8. How to Iterate through HashMap in MyBatis foreach?
  9. MyBatis executing multiple sql statements in one go, is that possible?
  10. having an exception when saving oracle type object in some entity
  11. How do I get the size of a java.sql.ResultSet?
  12. What is the best approach using JDBC for parameterizing an IN clause? [duplicate]
  13. How to print a query string with parameter values when using Hibernate
  14. Variable column names using prepared statements
  15. Where’s my invalid character (ORA-00911)
  16. MySQL and JDBC with rewriteBatchedStatements=true
  17. PLSQL JDBC: How to get last row ID?
  18. Setting Network Timeout for JDBC connection
  19. Hibernate Group by Criteria Object
  20. How do you query object collections in Java (Criteria/SQL-like)?
  21. How to print several reports with barcode \ or several barcodes in one report
  22. Iterating through a List Object in JSP
  23. How to use Annotations with iBatis (myBatis) for an IN query?
  24. convert string date to java.sql.Date [duplicate]
  25. How to use prepared statement for select query in Java?
  26. Hibernate native query – char(3) column
  27. Oracle JDBC : invalid username/password (ora-01017)
  28. Java JDBC connection status
  29. java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path
  30. How to view an image from blob column in Oracle with JasperReports?

Leave a Comment