1. INSERT INTO tablename(field1,field2, field3, ...) VALUES(value1, value2, value3, ...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...;
INSERT INTO f_cameras (cameraname,cameraIndexCode) VALUES (?,?) ON DUPLICATE KEY UPDATE
cameraIndexCode= VALUES(cameraIndexCode),camername= VALUES(cameraname)
public void insertRegionList(List regionInfoData){
Log4JConfigureServlet.LOGINFO.info("insertRegionList start");
Log4JConfigureServlet.LOGINFO.info("regionInfoData.size()=" + regionInfoData.size());
List factoryList = new ArrayList<>();
List areaList = new ArrayList<>();
for (Region cc : regionInfoData){
if(cc.getParentIndexCode().equals("root000000")){
factoryList.add(cc);
}else if(!cc.getParentIndexCode().equals("-1")){
areaList.add(cc);
}
}
Log4JConfigureServlet.LOGINFO.info("factoryList==="+ JSON.toJSONString(factoryList));
Log4JConfigureServlet.LOGINFO.info("areaList===" + JSON.toJSONString(areaList));
Connection conn = null;
PreparedStatement pst = null;
PreparedStatement pst2 = null;
ResultSet rs = null;
try {
conn = DbUtil.getConnection();
if(conn!=null) {
conn.setAutoCommit(false);// 更改JDBC事务的默认提交方式
//cameraIndexCode设置为Unique索引
String sql = "INSERT INTO f_factory (factoryid,factory) " +
"VALUES (?,?) " +
"ON DUPLICATE KEY UPDATE " +
"factoryid= VALUES(factoryid)," +
"factory=VALUES(factory)";
String sql2 = "INSERT INTO f_areas (areaid,areaname,factoryid) " +
"VALUES (?,?,?) " +
"ON DUPLICATE KEY UPDATE " +
"areaid= VALUES(areaid)," +
"areaname=VALUES(areaname)," +
"factoryid=VALUES(factoryid)";
pst = (PreparedStatement) conn.prepareStatement(sql);
pst2 = (PreparedStatement) conn.prepareStatement(sql2);
Log4JConfigureServlet.LOGINFO.info("insertRegionList conn.prepareStatement(sql)");
for (Region cc:factoryList) {
pst.setString(1, cc.getIndexCode());
pst.setString(2, cc.getName());
pst.addBatch();
Log4JConfigureServlet.LOGINFO.info("insertRegionList pst.addBatch()");
}
for (Region cc:areaList){
pst2.setString(1, cc.getIndexCode());
pst2.setString(2, cc.getName());
pst2.setString(3, cc.getParentIndexCode());
pst2.addBatch();
Log4JConfigureServlet.LOGINFO.info("insertRegionList pst2.addBatch()");
}
pst.executeBatch();
pst2.executeBatch();
Log4JConfigureServlet.LOGINFO.info("insertRegionList pst.executeBatch()");
mit();
conn.setAutoCommit(true);
Log4JConfigureServlet.LOGINFO.info("insertRegionList end");
}
} catch (Exception e) {
try {
if(conn!=null)
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
String error = e.getCause().getLocalizedMessage();
if (error == null) {
error = e.getCause().toString();
}
Log4JConfigureServlet.LOGINFO.info("操作数据库失败错误为 is " + error);
} finally {
DbUtil.close(rs, pst, conn);
}
}
springboot mapper.xml写法：insertintof_cameras(cameraname,cameraIndexCode)
values(#{cameraname,jdbcType=VARCHAR},#{cameraIndexCode,jdbcType=VARCHAR})
ONDUPLICATEKEYUPDATE
cameraname=#{cameraa,jdbcType=VARCHAR},carNum=#{cameraIndexCode,jdbcType=INTEGER},optTime=CURRENT_TIMESTAMP2.新建Unique索引当Unique索引栏位的原始值和插入值一样时，执行update。反之，执行insert
