title: Spring jdbcTemplate vs PreparedStatement. Performance difference
body: <p>I am using Oracle 11g. I have 3 tables(<code>A,B,C</code>) in my database <code>A &lt;one-many&gt; B &lt;many-one&gt; C</code> 
  I have a piece of code, that performs three inserts: firstly in <code>A</code> and <code>C</code>, after that in <code>B</code>. This piece of code is executed a lot of times(<code>200000</code>) and makes <code>200000</code> insert operations in each table.</p>
  
  <p>I have two ways to make an insertion: </p>
  
  <ol>
  <li><p>jdbc PreparedStatement:</p>
  
  <pre><code>DataSource ds = jdbcTemplate.getDataSource();
  
  try (Connection connection = ds.getConnection();
       PreparedStatement statement = connection.prepareStatement(sql1);
       PreparedStatement statement2 = connection.prepareStatement(sql2);
       PreparedStatement statement3 = connection.prepareStatement(sql3);) {
  
       connection.setAutoCommit(false);
       final int batchSize = 20;
       int count = 0;
  
       for (int i=1; i&lt;= total; i++ ) {
  
           // Define sql parameters
  
           statement.setString(1, p1);
  
           statement2.setString(1, p2);
           statement2.setString(2, p3);
  
           statement3.setInt(1, p4);
           statement3.setString(2, p5);
  
           statement.addBatch();
           statement2.addBatch();
           statement3.addBatch();
  
           if (++count % batchSize == 0) {
                      statement.executeBatch();
                      statement.clearBatch();
                      statement2.executeBatch();
                      statement2.clearBatch();
                      statement3.executeBatch();
                      statement3.clearBatch();
                      connection.commit();
                      System.out.println(i);
           }
        }
  
        statement.executeBatch();
        statement.clearBatch();
        statement2.executeBatch();
        statement2.clearBatch();
        statement3.executeBatch();
        statement3.clearBatch();
        connection.commit();
     } 
     catch (SQLException e) {
         e.printStackTrace();
     }
  }
  </code></pre></li>
  <li><p>Spring jdbcTemplate:</p>
  
  <pre><code> List&lt;String&gt; bulkLoadRegistrationSql = new ArrayList&lt;String&gt;(20);
  
   for (int i=1; i&lt;= total; i++ ) {
  
       // 1. Define sql parameters p1,p2,p,3p4,p5
  
       // 2. Prepare sql using parameters from 1
       String sql1String = ...
       String sql2String = ...
       String sql3String = ...
  
       bulkLoadRegistrationSql.add(sql1String);
       bulkLoadRegistrationSql.add(sql2String);
       bulkLoadRegistrationSql.add(sql3String);
  
       if (i % 20 == 0) {
            jdbcTemplate.batchUpdate(bulkLoadRegistrationSql
                .toArray(new  String[bulkLoadRegistrationSql.size()]));
            //Clear inserted batch
            bulkLoadRegistrationSql = new ArrayList&lt;String&gt;(20);     
       }
  
  }
  </code></pre></li>
  </ol>
  
  <p>I measured execution time for <code>total = 200000</code> and results are very confusing for me. 
  Spring <code>jdbcTemplate</code> is executed in 1480 seconds,
  jdbc <code>PreparedStatement</code> in 200 seconds</p>
  
  <p>I looked into <code>jdbcTemplate</code> source and found, that it uses <code>Statement</code> underneath, which should be less efficient than <code>PreparedStatement</code>. However the difference in results is too big and I am not sure if this happens just because of the difference between <code>Statement</code> and <code>PreparedStatement</code>. What are your ideas on that? Should the results theoretically be equaled if <code>jdbcTemplate</code> is replaced on <code>namedParameterJdbcTemplate</code>?</p>