oracle:使用jdbc插入数据,表中的字段包括字符,日期,clob等字段
我使用的是jdk1.6, ojdbc14.jar, classes12.jar
// list是处理的数据
List<Object> list = Collections.emptyList();
crmConnect = CrmJdbcUtil.getCrmConnect();
// 使用批处理,则关闭自动提交
crmConnect.setAutoCommit(false);
String insert = "INSERT INTO NATURAL_PERSON_PHOTO(UNI_ID, TRADE_ID, TRANS_ID, TRADE_TIME, DATA_SOURCE, PHOTO_TYPE, PHOTO_NAME) VALUES(?, ?, ?, TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS'), ?, ?, ?)";
crmStatement = crmConnect.prepareStatement(insert);
// TRADE_ID, SUBSCRIBE_ID, ACCEPT_DATE, '1' AS DATA_SOURCE
for (int i = 0; i < list.size(); i++) {
JSONObject jsonObject = (JSONObject)list.get(i);
String tradeId = jsonObject.getString("TRADE_ID");
String transId = jsonObject.getString("SUBSCRIBE_ID");
// acceptDate的格式是yyyy-MM-dd HH:mm:ss
String acceptDate = jsonObject.getString("ACCEPT_DATE");
String dataSource = jsonObject.getString("DATA_SOURCE");
String queryDateFormate = DateUtil.dateFormate(acceptDate);
JSONArray array = savePhotoBase64(tradeId, transId, queryDateFormate, acceptDate, tableName, dataSource);
// 一个流水号下有多个照片
for (int j = 0; j < array.size(); j++) {
JSONObject object = array.getJSONObject(j);
String photoType = object.getString("photoType");
// 照片的base64串,很大,需要clob存储,blob是二进制存储,clob是字符串存储
String photoStr = object.getString("photoStr");
String uuid = UUID.randomUUID().toString();
crmStatement.setString(1, uuid);
crmStatement.setString(2, tradeId);
crmStatement.setString(3, transId);
// acceptDate的格式是yyyy-MM-dd HH:mm:ss
// 时间字段这样处理的话会丢失时分秒
// Date date1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(acceptDate);
// java.sql.Date date = new java.sql.Date(date1.getTime());
// crmStatement.setDate(4, date);
// 可以把TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS'),将时间字符串作为占位符,
// 这样就不会丢失时分秒了
crmStatement.setString(4, acceptDate);
crmStatement.setString(5, dataSource);
crmStatement.setString(6, photoType);
StringReader reader = new StringReader(photoStr);
crmStatement.setCharacterStream(7, reader, photoStr.length());
crmStatement.addBatch();
}
if(i % 100 == 0){
//2.执行
crmStatement.executeBatch();
//3.清空
crmStatement.clearBatch();
}
}
crmConnect.commit();