Running SQL script file through Java

Question

In order to execute a whole SQL script file[which includes create table statements and some rules defining create table]. I found this solution in order to achieve it --->"There is great way of executing SQL scripts from Java without reading them yourself as long as you don't mind having a runtime dependency on Ant. In my opinion such a dependency is very well justified in your case. Here is sample code, where SQLExec class lives in 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();
}

I don't know whether it will work or not!!!

Can anybody give some hints to work on the above solution? I mean how to get the code work and also let me know any other solution to execute SQL script files!!!

Thanks, Mahesh

Answer 1

This is a code snippet I stole from the internet somewhere (I don't remember where) that I know works great: it wont run the way that it is below. It might give you enough clues to get yours working though, or you might find the source on Google somewhere:

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();
    }
}