报错信息:
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [ INSERT INTO M_OP (ID,AG,FLAG) VALUES(?,?,?)]; .....Incorrect decimal value: 'null' for column 'AG' at row 1 .... nested exception is java.sql.BatchUpdateException:
应用场景: 应用中通过jdbc从远程Oracle数据源读取A表数据,再通过jdbc插入Mysql数据库B张表。因为A、B两表字段均可为null。导致从A读出数据为null时,使用setString(int parameterIndex, String x)null值无法转换,导致后续sql执行失败,但是替换为void setObject(int parameterIndex, Object x)正常转换。
代码如下:
public List<Map<String, Object>> getDetail(String No) {
String sql = " SELECT ID, AG,'01' AS FLAG FROM AG_DETAIL WHERE FLAG ='02' AND NO = ? ";
return jdbcOracle.queryForList(sql, No);
}
public int[] insDetail(final List<Map<String, Object>> list) {
String sql = " INSERT INTO M_OP (ID,AG,FLAG,) VALUES(?,?,?)";
return jdbcMysql.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Map<String, Object> map = list.get(i);
ps.setString(1, String.valueOf(map.get("ID")));
ps.setString(2, String.valueOf(map.get("AG")));
ps.setString(3, String.valueOf(map.get("FLAG")));
}
@Override
public int getBatchSize() {
return list.size();
}
});
}
应用中调用:insDetail(getDetail(no));
解决:把setString(int parameterIndex, String x)替换为setObject(int parameterIndex, Object x).
代码如下:
public int[] insDetail(final List<Map<String, Object>> list) {
String sql = " INSERT INTO M_OP (ID,AG,FLAG,) VALUES(?,?,?)";
return jdbcMysql.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Map<String, Object> map = list.get(i);
ps.setObject(1, map.get("ID"));
ps.setObject(2, map.get("AG"));
ps.setObject(3, map.get("FLAG"));
}
@Override
public int getBatchSize() {
return list.size();
}
});
}