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.