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

Furthermore, have you even tried the many solutions proposed to you in relation to stackoverflow.com/questions/5801838/…? — Jonas Kongslund
– Jonas Kongslund, Commented Apr 28, 2011 at 7:18
What's wrong with trying yourself? Why do you expect others to do your work? — user330315
– user330315, Commented Apr 28, 2011 at 7:40
Solution mentioned in the question (stackoverflow.com/a/3055008/948268) works for script having insert, create table etc statements. But for script having create or replace trigger(I think the one with PL/SQL block) it fails with java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement. Could someone help resolve this issue? — Kuldeep Jain
– Kuldeep Jain, Commented Dec 20, 2013 at 13:19

djangofan · Accepted Answer · 2011-07-19 20:17:43Z

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

Collectives™ on Stack Overflow

Running SQL script file through Java

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related