jdbc批量新增数据带事务回滚_jdbctemplate batchupdate 回滚-CSDN博客

本文链接：https://blog.csdn.net/on_the_stree/article/details/115466054
jdbc批量新增数据
  @Transactional(rollbackFor=RuntimeException.class)
    public int synDataSource() {
        //先删除数据源
        String delSql = "delete from data_source";
        jdbcTemplate.update(delSql);

            Map<String, Object> map = dbService.getDbByFeign("");
            List<LinkedHashMap<String, Object>> list = (List<LinkedHashMap<String, Object>>) map.get("data");
            String sql = " insert into data_source(ID,TYPE,NAME,JNDI_NAME,STATUS,HIVEORIMPALA_USER,\n" +
                    " HIVEORIMPALA_URL,HIVEORIMPALA_NAME,HIVEORIMPALA_PWD,HDFS_USER,FILE_PATH,\n" +
                    " JDBC_NAME,JDBC_PWD,DRIVER_NAME,DRIVER_URL,OBJCLASS,DBTYPE,JDBC_CODE,\n" +
                    " FTP_HOST,FTP_PORT,FTP_HOST_UNAME,FTP_HOST_UMM,FTP_HOME,SFTP_HOST,SFTP_PORT,\n" +
                    " SFTP_HOST_UNAME,SFTP_HOST_UMM,SFTP_HOME,CITY_CODE) values(?,?,?,?,?,?,?,?,?,?,?," +
                    " ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try {
            jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    LinkedHashMap<String, Object> linkedHashMap = list.get(i);
                    int index = 1;
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("ID")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("TYPE")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("NAME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("JNDI_NAME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("STATUS")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("HIVEORIMPALA_USER")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("HIVEORIMPALA_URL")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("HIVEORIMPALA_NAME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("HIVEORIMPALA_PWD")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("HDFS_USER")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("FILE_PATH")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("JDBC_NAME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("JDBC_PWD")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("DRIVER_NAME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("DRIVER_URL")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("OBJCLASS")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("TYPE_NAME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("JDBC_CODE")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("FTP_HOST")));
                    ps.setInt(index++, Integer.parseInt(linkedHashMap.get("FTP_PORT")==null?"0":linkedHashMap.get("FTP_PORT").toString()));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("FTP_HOST_UNAME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("FTP_HOST_UMM")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("FTP_HOME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("SFTP_HOST")));
                    ps.setInt(index++, Integer.parseInt(linkedHashMap.get("SFTP_PORT")==null?"0":linkedHashMap.get("SFTP_PORT").toString()));
//                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("SFTP_PORT")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("SFTP_HOST_UNAME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("SFTP_HOST_UMM")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("SFTP_HOME")));
                    ps.setString(index++, StringUtil.emptyStr(linkedHashMap.get("CITY_CODE")));
                }
                @Override
                public int getBatchSize() {
                    return list.size();
                }
            });
        } catch (DataAccessException e) {
            throw new RuntimeException(e.getMessage());
        }
        return 0;
    }
事务回滚对TRUNCATE 不生效需要改成delete;