通过JDBC执行长insert语句

Question

我有一个结构为INSERT语句的备份文件。我的方法是编写一个方法从文件中加载这些语句，并使用JDBC模板执行它们。

public void restoreFile(File f) throws Exception {
    LineIterator it = FileUtils.lineIterator(f, "UTF-8");
    try {
        while (it.hasNext()) {
            String insertStatement = it.nextLine();
            if (!insertStatement.startsWith("--") && !insertStatement.isEmpty()) {
                insertStatement = insertStatement.replace(";", "");
                executeSqlStatement(insertStatement);
            }
        }
    } finally {
        it.close();
    }
}

public void executeSqlStatement(String sqlStatement) throws Exception {

    PreparedStatementCreator statement = new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement statement = connection.prepareStatement(sqlStatement);
            return statement;
        }
    };
    getJdbcTemplate().update(statement);
}

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-01704: string literal too long

编辑：另外，SQL Developer如何处理这些长字符串文本？

Answer 1

黑客来了：

public void executeSqlStatement(String sqlStatement) throws Exception {

    PreparedStatementCreator statement = new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection)
                    throws SQLException {
            List<String> params = new ArrayList<>();
            if (sqlStatement.length() >= 4000) {
                Pattern pattern = Pattern.compile("'(([^'\n]|'')+)'");
                Matcher m = pattern.matcher(sqlStatement);
                StringBuffer sb = new StringBuffer();
                while (m.find()) {
                    params.add(m.group(1).replace("''", "'"));
                    m.appendReplacement(sb, "?");
                }
                m.appendTail(sb);
                sqlStatement = sb.toString();
            }
            PreparedStatement statement = connection.prepareStatement(sqlStatement);
            for (int i = 0; i < params.size(); ++i) {
                statement.setString(1 + i, params[i]);
            }
            return statement;
        }
    };
    getJdbcTemplate().update(statement);
}

如您所见：它试图通过提取字符串文本来缩小较大的SQL。