sql中批量插入begin的使用
1 private static String ADD_ATTR_EXT_ITEM="insert into attr_ext_item(attr_ext_main_key,attr_name_key,attr_type,data_type,mode,
tolerance,target_value,attr_lower_value,attr_higher_value,fetch_attr_group,
fetch_attr_name,calc_expression,serial_port,serial_port_baud_rate,serial_port_data_format,
pages)"; 2 public boolean saveAttrExtItem(final List<AttrExtItem> listAttrExtItem,final int attrExtMainid) { 3 boolean result = true; 4 if (listAttrExtItem == null || listAttrExtItem.size() == 0) 5 return false; 6 StringBuffer buffer = new StringBuffer(); 7 try { 8 buffer.append("begin;" ); 9 for (int i = 0; i < listAttrExtItem.size(); i++) { 10 AttrExtItem att = listAttrExtItem.get(i); 11 buffer.append(ADD_ATTR_EXT_ITEM); 12 buffer.append(" values(" 13 +attrExtMainid 14 + ", " + att.getAttrNameKey() 15 + ", '" + att.getAttrType() 16 + "', '" + att.getDataType() 17 + "', '" + att.getMode() 18 + "', '" + att.getTolernace() 19 + "', '" + att.getTargetValue() 20 + "', '" + att.getAttrLowerValue() 21 + "', '" + att.getAttrHigherValue() 22 + "','" + att.getFetchAttrGroup() 23 + "', '" + att.getFetchAttrName() 24 + "', '" + att.getCalcExpression() 25 + "', '" + att.getSerialPort() 26 + "', " + att.getSerialPortbAUDRate() 27 + ", '" + att.getSerialPortDataFormat() 28 + "', '" + att.getPages() 29 +"'"+ ");"); 30 } 31 buffer.append("commit;"); 32 log.info("Add AttrExtItem sql: " + buffer.toString()); 33 this.getJdbcTemplate2().update(buffer.toString()); 34 } catch (Exception e) { 35 result = false; 36 log.error(e); 37 } 38 return result; 39 }
使用StringBuffer 拼接sql，append "begin" 和"commit"之间用循环处理要插入的多条sql数据。update语句也同理。
