工作中需要一次执行多条语句,本来想直接使用sql拼接成一个字符串进行批处理,原sql如下:
String sql = "";
for(int i=0; i<deviceInfo.getDevice_ip().length; i++){
if(deviceInfo.getDevice_ip()[i] != null && !deviceInfo.getDevice_ip()[i].equals("")){
String sqlu = "insert into tab_upperdevice (device_id,device_ip,port_type,shelf,frame,slot,port) values('" +
id +
"','" + deviceInfo.getDevice_ip()[i] +
"','" + deviceInfo.getPort_type()[i] +
"','" + deviceInfo.getShelf()[i] +
"','" + deviceInfo.getFrame()[i] +
"','" + deviceInfo.getSlot()[i] +
"','" + deviceInfo.getPort()[i] +"')" + ";";
sql = sql + sqlu;
}
}
发现执行不了,经过查找资料,发现oracle需要加begin end;字段才能执行多条语句。
修改后的sql如下:
begin
String sql = "";
for(int i=0; i<deviceInfo.getDevice_ip().length; i++){
if(deviceInfo.getDevice_ip()[i] != null && !deviceInfo.getDevice_ip()[i].equals("")){
String sqlu = "insert into tab_upperdevice (device_id,device_ip,port_type,shelf,frame,slot,port) values('" +
id +
"','" + deviceInfo.getDevice_ip()[i] +
"','" + deviceInfo.getPort_type()[i] +
"','" + deviceInfo.getShelf()[i] +
"','" + deviceInfo.getFrame()[i] +
"','" + deviceInfo.getSlot()[i] +
"','" + deviceInfo.getPort()[i] +"')" + ";";
sql = sql + sqlu;
}
}
end;
修改后的语句依然执行不了,不知道是啥原因。
最终解决方法是使用List来执行,代码:
List<String> sqluList = new ArrayList<String>();
for(int i=0; i<deviceInfo.getDevice_ip().length; i++){
if(deviceInfo.getDevice_ip()[i] != null && !deviceInfo.getDevice_ip()[i].equals("")){
String sqlu = "insert into tab_upperdevice (device_id,device_ip,port_type,shelf,frame,slot,port) values('" +
id +
"','" + deviceInfo.getDevice_ip()[i] +
"','" + deviceInfo.getPort_type()[i] +
"','" + deviceInfo.getShelf()[i] +
"','" + deviceInfo.getFrame()[i] +
"','" + deviceInfo.getSlot()[i] +
"','" + deviceInfo.getPort()[i] +"')";
sqluList.add(sqlu);
this.log.info("添加上联设备sql->" + sqlu);
}
}
之后把这个List传给总的List:
List<String> sqlList = new ArrayList<String>();
sqlList.addAll(createUpperDeviceSql(deviceInfo, id));
然后把这个List转成数组传给批处理执行:
String[] sqlArr = sqlList.toArray(new String[sqlList.size()]);
return jt.batchUpdate(sqlArr);