Spring Jdbc template setAutocommit(false)

Question

I want insert multiple rows in db table. I am using SpringJdbc. How can i manage transaction in SpringJdbc connection. My code is:

@Override
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
public int addUserRelationshipMapping(final ArrayList<UserDO> userDOs, final long userId) throws UserDataException {
    final JdbcTemplate jd = this.getJdbctemplate();
    try {
        jd.getDataSource().getConnection().setAutoCommit(false);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    int totalAdded = 0;
    try {
        int[] isAdded = jd.batchUpdate(ADD_USER_RELATION_MAPPING, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                final long userRelationId = jd.queryForObject(USER_RELATION_KEY, Long.class);
                UserDO userDO = userDOs.get(i);
                    ps.setLong(1, userRelationId);
                    ps.setLong(2, userId);
                    ps.setLong(3, userDO.getprimaryUserId());
                    ps.setInt(4, 1);
                    ps.setInt(5, 0);
                    jd.getDataSource().getConnection().commit();
            }

            @Override
            public int getBatchSize() {
                return userDOs.size();
            }
        });
        totalAdded = isAdded.length;
    } catch (DuplicateKeyException dExp) {
        log.info("error for duplicate key exception ",dExp);
        log.error(dExp);

    } catch (DataAccessException dExp) {
        throw new UserDataException("error while adding user relation for userId is" + userId, dExp);
    }
    return totalAdded;
}

In this code userRelationId return always old values not updated table values. So will use database connection commit.

SOF question:Java MYSQL/JDBC query is returning stale data from cached Connection

I got error message:

Caused by: java.sql.SQLException: Can't call commit when autocommit=true

So i need help for this. Advance thanks.

Answer 1

See the example of how to set auto commit false

<bean id="database" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource">
    <bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        ...
        <property name="defaultAutoCommit" value="false" />
        ...
    </bean>
</property>

ref: spring 3.1: jdbcTemplate auto commit to false.

You can also try the annotation based connection transaction management ?
How can I config to turn off autocommit in Spring + JDBC?