Problem with JdbcTemplate.batchUpdate()

[Daythryl]

I'm trying to get JdbcTemplate.batchUpdate() to work the way I would expect -- namely, I'd like it to hold off the commit until the whole batch has been processed. Perhaps one of you can explain the behavior I'm seeing.

First let me show some regular JDBC code that does what I'd like:

static void do_jdbc(){

String url = "jdbc:mysql://localhost/mydb";
Connection con;
Statement stmt = null;

try {

Class.forName("com.mysql.jdbc.Driver");

} catch (java.lang.ClassNotFoundException e) {

System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());

}

try {

con = DriverManager.getConnection(url, "root", "");
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIV E, ResultSet.CONCUR_UPDATABLE);
con.setAutoCommit(false);
for (int i = 0; i < 2; i++) {

StringBuffer buf = new StringBuffer("insert into table1(field1, field2) value").append("('dummy").append(i).append("', ").append(i).append(")");
stmt.addBatch(buf.toString());

}
int[] updateCounts = stmt.executeBatch();
con.commit();
stmt.close();

} catch (Exception e) {

e.printStackTrace();

}

}

This works fine and generates the following output in the MySql log file:

7 Query SET autocommit=1
7 Query SET autocommit=0
7 Query insert into table1(field1, field2) value('dummy0', 0)
7 Query insert into table1(field1, field2) value('dummy1', 1)
7 Query commit

When I try to do essentially the same thing using JdbcTemplate, tho', I get some unexpected behavior. Here's the code:

static void do_spring(){

BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUsername("root");
ds.setPassword("");
ds.setUrl("jdbc:mysql://localhost/mydb");

ds.setDefaultAutoCommit(false);
JdbcTemplate t = new JdbcTemplate();
t.setDataSource(ds);

BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {

public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
preparedStatement.setString(1, "dummy" + i);
preparedStatement.setInt(2, i);
}

public int getBatchSize() {

return 2;

}

};

try {

int[] rowCounts = t.batchUpdate("insert into table1(field1, field2) value(?,?)", setter);

} catch (Exception e) {

e.printStackTrace();

}

}

This generates the following in the log file:

2 Query SET autocommit=1
2 Query SET autocommit=0
2 Prepare [1]
2 Execute [1] insert into table1(field1, field2) value('dummy0',0)
2 Execute [1] insert into table1(field1, field2) value('dummy1',1)
2 Query SHOW WARNINGS
2 Query rollback
2 Query SET autocommit=1

If I leave out the

ds.setDefaultAutoCommit(false);

then 'autocommit' stays at '1' and the inserts work (no 'rollback'), but each one is committed individually, which is about two-orders-of-magnitude slower for any significant amount of data.

Can someone please tell me how to fix it with the Spring classes (programmatically, not in bean-definition XML files) so that it successfully updates the database, but all-at-once?

Thanks in advance.

[brownb2]

This is an old post but I'm seeing the same problem with SQLServer 2005.

I'm getting hold of the underlying connection from the jdbctemplate and switching the auto commit off and instead manually committing about every 10 batches (of 6000 inserts). The code didn't throw any sort of exception, it just seems to roll back.

Is there a magic call I can make in the Spring API to disable auto commit or is this approach with the underlying connection the correct way of doing it?

I believe (no reference please correct if wrong) that with auto commit a single batch => a single commit however I need to take the manual batch + commit approach because memory is limited and thus I cannot have a huge batch size.

[zeta]

Hi,
i know both post are kind of older but i would really appreciate some explanation.
Thanks!

[gwa]

I guess there is no transactional management so spring does an automatic rollback.

[zeta]

Well, thats the point: with traditional jdbc i would have:

Connection updateConn = null;
Connection selectConn = null;
Statement st = null;
PreparedStatement ps = null;
ResultSet rs = null;
Customer customer = null;

int block = 0;
int commit = 0;

String sqlUpdate = "UPDATE customer SET attribute = ? WHERE id = ?";
try{
selectConn = this.getConnection();
st = selectConn.createStatement();
updateConn = this.getConnection();
updateConn.setAutoCommit(false);
ps = updateConn.prepareStatement(sqlUpdate);

st.setFetchSize(2000);
rs = st.executeQuery("SELECT * FROM customer");

while(rs.next()) {
customer = myDecodeRow(rs,fieldList);
processCustomer(customer);
ps.setLong(1, customer.getAttribute());
ps.setLong(2, customer.getId());

ps.addBatch();// this "saves in memory"

if(block == 2000) {
ps.executeBatch();//this copies to a temporal table
block = 0;
}
if(commit == 20000) {
updateConn.commit();
commit = 0;
}

block++;
commit++;
}
}
...

Why with jdbcTemplate I have to commit every "memory batch size"?. I would like to be able to disable autocommit true and commit myself(programmatically) with higher frequency.

I hope you understand what i am saying.

[zeta]

Hi,

thinking about the problem, I really believe that the code above cannot be "translate" to Spring batch. In order to allow having different times to executeBatch/commit, the itemWriter interface would need to have counters so it would not be thread safe(and thus it would be able to parallelize). Is that true?

Thanks.

[zeta]

Sorry, I wanted to say: "and thus, it could NOT be able to be parallelized"