java - 通过Java运行SQL脚本文件

Question

为了执行整个SQL脚本文件[其中包括创建表语句和一些定义创建表的规则]。我找到了这个解决方案来实现它 --->“只要您不介意对 Ant 存在运行时依赖性，就有一种从 Java 执行 SQL 脚本而无需自己阅读的好方法。在我看来，这种依赖性是在您的情况下非常合理。这是示例代码，其中 SQLExec 类位于 ant.jar 中:

private void executeSql(String sqlFilePath) {
    final class SqlExecuter extends SQLExec {
        public SqlExecuter() {
            Project project = new Project();
            project.init();
            setProject(project);
            setTaskType("sql");
            setTaskName("sql");
        }
    }

    SqlExecuter executer = new SqlExecuter();
    executer.setSrc(new File(sqlFilePath));
    executer.setDriver(args.getDriver());
    executer.setPassword(args.getPwd());
    executer.setUserid(args.getUser());
    executer.setUrl(args.getUrl());
    executer.execute();
}

不知道能不能成功!!!

任何人都可以提供一些关于上述解决方案的提示吗？我的意思是如何让代码工作，并让我知道执行 SQL 脚本文件的任何其他解决方案!!!

谢谢，马赫什

Answer 1

这是我从互联网上某个地方(我不记得在哪里)偷来的代码片段，我知道它效果很好:它不会按照下面的方式运行。它可能会为您提供足够的线索来让您的工作正常运行，或者您可能会在 Google 上的某个地方找到源代码:

private void runScriptOnce(Connection conn, Reader reader) throws IOException, SQLException {
    StringBuffer command = null;
    try {
        LineNumberReader lineReader = new LineNumberReader(reader);
        String line = null;
        while ((line = lineReader.readLine()) != null) {
            if (command == null) {
                command = new StringBuffer();
            }
            String trimmedLine = line.trim();
            if (trimmedLine.startsWith("--")) {
                println(trimmedLine);
            } else if (trimmedLine.length() < 1
                    || trimmedLine.startsWith("//")) {
                // Do nothing
            } else if (trimmedLine.length() < 1
                    || trimmedLine.startsWith("--")) {
                // Do nothing
            } else if (!fullLineDelimiter
                    && trimmedLine.endsWith(getDelimiter())
                    || fullLineDelimiter
                    && trimmedLine.equals(getDelimiter())) {
                command.append(line.substring(0, line
                        .lastIndexOf(getDelimiter())));
                command.append(" ");
                Statement statement = conn.createStatement();

                println(command);

                boolean hasResults = false;
                if (stopOnError) {
                    hasResults = statement.execute(command.toString());
                } else {
                    try {
                        statement.execute(command.toString());
                    } catch (SQLException e) {
                        e.fillInStackTrace();
                        printlnError("Error executing: " + command);
                        printlnError(e);
                    }
                }

                if (autoCommit && !conn.getAutoCommit()) {
                    conn.commit();
                }

                ResultSet rs = statement.getResultSet();
                if (hasResults && rs != null) {
                    ResultSetMetaData md = rs.getMetaData();
                    int cols = md.getColumnCount();
                    for (int i = 0; i < cols; i++) {
                        String name = md.getColumnLabel(i);
                        print(name + "\t");
                    }
                    println("");
                    while (rs.next()) {
                        for (int i = 0; i < cols; i++) {
                            String value = rs.getString(i);
                            print(value + "\t");
                        }
                        println("");
                    }
                }

                command = null;
                try {
                    statement.close();
                } catch (Exception e) {
                    // Ignore 
                }
                Thread.yield();
            } else {
                command.append(line);
                command.append(" ");
            }
        }
        if (!autoCommit) {
            conn.commit();
        }
    } catch (SQLException e) {
        e.fillInStackTrace();
        printlnError("Error executing: " + command);
        printlnError(e);
        throw e;
    } catch (IOException e) {
        e.fillInStackTrace();
        printlnError("Error executing: " + command);
        printlnError(e);
        throw e;
    } finally {
        conn.rollback();
        flush();
    }
}

private String getDelimiter() {
    return delimiter;
}

private void print(Object o) {
    if (logWriter != null) {
        System.out.print(o);
    }
}

private void println(Object o) {
    if (logWriter != null) {
        logWriter.println(o);
    }
}

private void printlnError(Object o) {
    if (errorLogWriter != null) {
        errorLogWriter.println(o);
    }
}

private void flush() {
    if (logWriter != null) {
        logWriter.flush();
    }
    if (errorLogWriter != null) {
        errorLogWriter.flush();
    }
}