Actually I'm using JDBCTemplate which can batch insert using ParameterizedPreparedStatementSetter object.
However, it is very uncomfortable setting parameters in query when it has many parameters.
In my case, actually, it has 44 parameters.
Here is my code.
jdbcTemplate.batchUpdate(query.toString(), batchArgs, listSize,
new ParameterizedPreparedStatementSetter<DocUsageDTO>() {
@Override
public void setValues(PreparedStatement ps, DocUsageDTO arg) throws SQLException {
ps.setString(1, arg.getClientIp());
ps.setInt(2, arg.getClientOs());
ps.setString(3, arg.getClientOsVersion());
ps.setString(4, arg.getContentCode());
ps.setString(5, arg.getContentCreateTime());
ps.setString(6, arg.getContentName());
ps.setString(7, arg.getCurrentContentName());
ps.setString(8, arg.getDeptCode());
ps.setString(9, arg.getDeptName());
ps.setString(10, arg.getDomainCode());
ps.setString(11, arg.getEtc1());
ps.setString(12, arg.getEtc2());
ps.setString(13, arg.getEtc3());
ps.setString(14, arg.getEtc4());
ps.setString(15, arg.getEtc5());
ps.setString(16, arg.getFileSyncId());
ps.setString(17, arg.getFileSyncManagerCode());
ps.setString(18, arg.getFileSyncManagerName());
ps.setString(19, arg.getFileSyncRevisionNo());
ps.setString(20, arg.getFileSyncTagCode());
ps.setString(21, arg.getFileSyncTagName());
ps.setInt(22, arg.getLocationContext());
ps.setString(23, arg.getLogDate());
ps.setInt(24, arg.getLogType());
ps.setString(25, arg.getOwnerCode());
ps.setString(26, arg.getOwnerDeptCode());
ps.setString(27, arg.getOwnerDeptName());
ps.setString(28, arg.getOwnerName());
ps.setString(29, arg.getPositionCode());
ps.setString(30, arg.getPositionName());
ps.setString(31, arg.getProcessCode());
ps.setString(32, arg.getProcessName());
ps.setInt(33, arg.getProductType());
ps.setInt(34, arg.getPurpose());
ps.setInt(35, arg.getPurposeFailReason());
ps.setInt(36, arg.getPurposeStatus());
ps.setString(37, arg.getSecLevelCode());
ps.setString(38, arg.getSecLevelName());
ps.setString(39, arg.getUserCode());
ps.setString(40, arg.getUserName());
ps.setString(41, arg.getWriterCode());
ps.setString(42, arg.getWriterDeptCode());
ps.setString(43, arg.getWriterDeptName());
ps.setString(44, arg.getWriterName());
}
});
Is there any better way to optimize code in setting parameters using ParameterizedPreparedStatementSetter?
Your problem isn't ParameterizedPreparedStatementSetter, it's your query having so many parameters that any code will look bulky.
You could try NamedParameterJdbcTemplate to provide the parameters in a map, but then you'll be putting those 44 parameters into a map somehow, which will still require probably an equal amount of code.
You could turn your current StatementSetter into a real class (instead of the anonymous one it's now), then it would be at least encapsulated and be out of sight. After all it's just a problem of appearance, there's nothing wrong with the code itself. That would leave your service class looking a lot cleaner, with something like
jdbcTemplate.batchUpdate(query.toString(), batchArgs, listSize, new DocUsageDTOSetter());
Actually you don't necessarily need a map for NamedParameterJdbcTemplate, as it can use a bean source. However, this requires the parameter names to match the properties. This means that INSERT INTO FOO(bar, baz) VALUES (?, ?) becomes INSERT INTO FOO(bar, baz) VALUES (:myBeanProperty1, :myBeanProperty2), and you'll have to wrap each DTO. So you'd be moving (source code) characters to a different place, and adding some extra object creations.