net.apexes.commons.lang.SQLScriptRunner Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of apexes-commons-lang Show documentation
There is a newer version: 2.1.6
package net.apexes.commons.lang;

import java.io.LineNumberReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Tool to run database scripts
 * @author jiangweibin
 */
public class SQLScriptRunner {

    private static final Logger LOG = Logger.getLogger(SQLScriptRunner.class.getName());

    private static final String DEFAULT_DELIMITER = ";";

    private final Connection connection;
    private final boolean stopOnError;
    private final boolean autoCommit;

    private String delimiter = DEFAULT_DELIMITER;
    private boolean fullLineDelimiter = false;

    /**
     * Default constructor
     */
    public SQLScriptRunner(Connection connection, boolean autoCommit, boolean stopOnError) {
        this.connection = connection;
        this.autoCommit = autoCommit;
        this.stopOnError = stopOnError;
    }

    public void setDelimiter(String delimiter, boolean fullLineDelimiter) {
        this.delimiter = delimiter;
        this.fullLineDelimiter = fullLineDelimiter;
    }

    /**
     * Runs an SQL script (read in using the Reader parameter) using the
     * connection passed in
     *
     * @param reader - the source of the script
     * @throws SQLException if any SQL errors occur
     */
    public void runScript(Reader reader) throws SQLException {
        StringBuilder command = null;
        boolean exception = false;
        try {
            LineNumberReader lineReader = new LineNumberReader(reader);
            String line;
            while ((line = lineReader.readLine()) != null) {
                if (command == null) {
                    command = new StringBuilder();
                }
                String trimmedLine = line.trim();
                if (trimmedLine.startsWith("--")) {
                    // Do nothing
                } 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(" ");

                    executeCommand(command.toString());

                    command = null;

                    Thread.yield();
                } else {
                    command.append(line);
                    command.append(" ");
                }
            }
            if (!autoCommit) {
                connection.commit();
            }
        } catch (SQLException e) {
            LOG.log(Level.SEVERE, "Error executing: " + command, e);
            exception = true;
            throw e;
        } catch (Exception e) {
            LOG.log(Level.SEVERE, "Error executing: " + command, e);
            exception = true;
        } finally {
            if (exception) {
                connection.rollback();
            }
        }
    }

    private void executeCommand(String command ) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            LOG.info(command);

            boolean hasResults = false;
            if (stopOnError) {
                hasResults = statement.execute(command);
            } else {
                try {
                    statement.execute(command);
                } catch (SQLException e) {
                    LOG.log(Level.SEVERE, "Error executing: " + command, e);
                }
            }

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

            if (hasResults) {
                try (ResultSet rs = statement.getResultSet()) {
                    StringBuilder sb = new StringBuilder();
                    ResultSetMetaData md = rs.getMetaData();
                    int cols = md.getColumnCount();
                    for (int i = 0; i < cols; i++) {
                        String name = md.getColumnLabel(i + 1);
                        sb.append(name).append("\t");
                    }
                    LOG.info(sb.toString());
                    while (rs.next()) {
                        sb.setLength(0);
                        for (int i = 0; i < cols; i++) {
                            String value = rs.getString(i + 1);
                            sb.append(value).append("\t");
                        }
                        LOG.info(sb.toString());
                    }
                }
            }
        }
    }

    private String getDelimiter() {
        return delimiter;
    }

}