Ibatis startBatch() only works with SqlMapClient's own start and commit transactions, not with Spring-managed ones

Question

Hi,

I'm finding that even though I have code wrapped by Spring transactions, and it commits/rolls back when I would expect, in order to make use of JDBC batching when using Ibatis and Spring I need to use explicit SqlMapClient transaction methods.

I.e. this does batching as I'd expect:

dao.getSqlMapClient().startTransaction();
dao.getSqlMapClient().startBatch();

int i = 0;
for (MyObject obj : allObjects)
{
    dao.storeChange(obj);

    i++;
    if (i % DB_BATCH_SIZE == 0)
    {
        dao.getSqlMapClient().executeBatch();
        dao.getSqlMapClient().startBatch();
    }
}

dao.getSqlMapClient().executeBatch();
dao.getSqlMapClient().commitTransaction();

but if I don't have the opening and closing transaction statements, and rely on Spring to manage things (which is what I want to do!), batching just doesn't happen.

Given that Spring does otherwise seem to be handling its side of the bargain regarding transaction management, can anyone advise on any known issues here?

(Database is MySQL; I'm aware of the issues regarding its JDBC pseudo-batch approach with INSERT statement rewriting, that's definitely not an issue here)

Answer 1

Found the reason behind this one through reading various resources and trial and error, I've recorded our outcomes here as it may help someone else.

Turns out that the different behaviours were due to our DAO classes extending Spring's SqlMapClientTemplate. In that class you have two "choices" (I say choices; one's correct, one really isn't):

• direct use of insert(), update(), etc; using full Spring objects all the way

• getSqlMapClient().insert(), update(), etc; this one actually works using a com.ibatis... object as returned by getSqlMapClient(), rather than a Spring one

Both generally work, but from my reading the first option is better, e.g. if you're using Spring you want to be fully Spring-based, not "jump out" to Ibatis objects.

Now, SqlMapClientTemplate doesn't provide access to startBatch() / executeBatch() directly, just the handy insert(), update() stuff, so code such as this is necessary to do that kind of stuff. The below code works fully with our Spring managed transactions, and not an explicit code startTransaction() in sight.

(disclaimer this may contain errors due to my "anonymising" working code for clarity)

public class MyFunkyDao extends SqlMapClientDaoSupport
{
    private static final int DB_BATCH_SIZE = 1000;

    public void storeMyData(final List<MyData> listData)
    {
        getSqlMapClientTemplate().execute( new SqlMapClientCallback()
        {
            @Override
            public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException
            {
                int count = 0, total = 0;

                Map<String, Object> params = new HashMap<String, Object>();

                executor.startBatch();

                for (MyData data: listData)
                {
                    params.put("param name 1", data.getValue());

                    executor.insert("insertData", params);

                    count++;
                    if (count % DB_BATCH_SIZE == 0) 
                    {
                        total += executor.executeBatch();
                        executor.startBatch();
                    }

                    params.clear();
                }

                total += executor.executeBatch();

                return new Integer(total);
            }
        });
    }
}

Link: http://static.springsource.org/spring/docs/2.5.x/api/org/springframework/orm/ibatis/SqlMapClientTemplate.html