Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Run SQL Script (.sql) containing DDL, DML, SELECT statements & PL/SQL Block through Reader in Java

, 30 Jul 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
DOWNLOAD Source Files: 1. ScriptRunner.java 2. Table.java 3. commons-lang.jar Introduction: In this Article, I will show you how to run a SQL script file using ScriptRunner class. Through this class, you can execute any DDL, DML statement including SELECT Statement and any PL/SQL Block like PROCEDUR

DOWNLOAD Source Files:
1. ScriptRunner.java
2. Table.java
3. commons-lang.jar

Introduction:

In this Article, I will show you how to run a SQL script file using ScriptRunner class. Through this class, you can execute any DDL, DML statement including SELECT Statement and any PL/SQL Block like PROCEDURE, FUNCTION, TRIGGER

Quick Start:

**
 *
 * @author Debopam Pal, Software Developer, National Informatics Center (NIC), India
 * @Date   July 29, 2014
 * @File   ScriptRunner.java
 */

import java.io.IOException;
import java.io.LineNumberReader;
import java.io.PrintWriter;
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.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang.StringUtils;

public class ScriptRunner {

    public static final String DEFAULT_DELIMITER = ";";
    public static final String PL_SQL_BLOCK_SPLIT_DELIMITER = "+";
    public static final String PL_SQL_BLOCK_END_DELIMITER = "#";

    private final boolean autoCommit, stopOnError;
    private final Connection connection;
    private String delimiter = ScriptRunner.DEFAULT_DELIMITER;
    private final PrintWriter out, err;

	/* To Store any 'SELECT' queries output */
    private List<Table> tableList;
	
	/* To Store any SQL Queries output except 'SELECT' SQL */
    private List<String> sqlOutput;

    public ScriptRunner(final Connection connection, final boolean autoCommit, final boolean stopOnError) {
        if (connection == null) {
            throw new RuntimeException("ScriptRunner requires an SQL Connection");
        }
		
        this.connection = connection;
        this.autoCommit = autoCommit;
        this.stopOnError = stopOnError;
        this.out = new PrintWriter(System.out);
        this.err = new PrintWriter(System.err);

        tableList = new ArrayList<Table>();
        sqlOutput = new ArrayList<String>();
    }

    public void runScript(final Reader reader) throws SQLException, IOException {
        final boolean originalAutoCommit = this.connection.getAutoCommit();
        try {
            if (originalAutoCommit != this.autoCommit) {
                this.connection.setAutoCommit(this.autoCommit);
            }
            this.runScript(this.connection, reader);
        } finally {
            this.connection.setAutoCommit(originalAutoCommit);
        }
    }

    private void runScript(final Connection conn, final Reader reader) throws SQLException, IOException {
        StringBuffer command = null;

        Table table = null;
        try {
            final LineNumberReader lineReader = new LineNumberReader(reader);
            String line = null;
            while ((line = lineReader.readLine()) != null) {
                if (command == null) {
                    command = new StringBuffer();
                }

                if (table == null) {
                    table = new Table();
                }

                String trimmedLine = line.trim();

				// Interpret SQL Comment & Some statement that are not executable
                if (trimmedLine.startsWith("--")
                        || trimmedLine.startsWith("//")
                        || trimmedLine.startsWith("#")
                        || trimmedLine.toLowerCase().startsWith("rem inserting into")
                        || trimmedLine.toLowerCase().startsWith("set define off")) {

                    // do nothing...
                } else if (trimmedLine.endsWith(this.delimiter) || trimmedLine.endsWith(PL_SQL_BLOCK_END_DELIMITER)) { // Line is end of statement
                    
                    // Append
                    if (trimmedLine.endsWith(this.delimiter)) {
                        command.append(line.substring(0, line.lastIndexOf(this.delimiter)));
                        command.append(" ");
                    } else if (trimmedLine.endsWith(PL_SQL_BLOCK_END_DELIMITER)) {
                        command.append(line.substring(0, line.lastIndexOf(PL_SQL_BLOCK_END_DELIMITER)));
                        command.append(" ");
                    }

                    Statement stmt = null;
                    ResultSet rs = null;
                    try {
                        stmt = conn.createStatement();
                        boolean hasResults = false;
                        if (this.stopOnError) {
                            hasResults = stmt.execute(command.toString());
                        } else {
                            try {
                                stmt.execute(command.toString());
                            } catch (final SQLException e) {
                                e.fillInStackTrace();
                                err.println("Error executing SQL Command: \"" + command + "\"");
                                err.println(e);
                                err.flush();
                                throw e;
                            }
                        }

                        rs = stmt.getResultSet();
                        if (hasResults && rs != null) {

                            List<String> headerRow = new ArrayList<String>();
                            List<List<String>> toupleList = new ArrayList<List<String>>();

                            // Print & Store result column names
                            final ResultSetMetaData md = rs.getMetaData();
                            final int cols = md.getColumnCount();
                            for (int i = 0; i < cols; i++) {
                                final String name = md.getColumnLabel(i + 1);
                                out.print(name + "\t");

                                headerRow.add(name);
                            }
							
                            table.setHeaderRow(headerRow);

                            out.println("");
                            out.println(StringUtils.repeat("---------", md.getColumnCount()));
                            out.flush();

                            // Print & Store result rows
                            while (rs.next()) {
                                List<String> touple = new ArrayList<String>();
                                for (int i = 1; i <= cols; i++) {
                                    final String value = rs.getString(i);
                                    out.print(value + "\t");

                                    touple.add(value);
                                }
                                out.println("");

                                toupleList.add(touple);
                            }
                            out.flush();

                            table.setToupleList(toupleList);
                            this.tableList.add(table);
                            table = null;
                        } else {
                            sqlOutput.add(stmt.getUpdateCount() + " row(s) affected.");

                            out.println(stmt.getUpdateCount() + " row(s) affected.");
                            out.flush();
                        }
                        command = null;
                    } finally {
                        if (rs != null) {
                            try {
                                rs.close();
                            } catch (final Exception e) {
                                err.println("Failed to close result: " + e.getMessage());
                                err.flush();
                            }
                        }
                        if (stmt != null) {
                            try {
                                stmt.close();
                            } catch (final Exception e) {
                                err.println("Failed to close statement: " + e.getMessage());
                                err.flush();
                            }
                        }
                    }
                } else if (trimmedLine.endsWith(PL_SQL_BLOCK_SPLIT_DELIMITER)) {
                    command.append(line.substring(0, line.lastIndexOf(this.PL_SQL_BLOCK_SPLIT_DELIMITER)));
                    command.append(" ");
                } else { // Line is middle of a statement

                    // Append
                    command.append(line);
                    command.append(" ");
                }
            }
            if (!this.autoCommit) {
                conn.commit();
            }
        } catch (final SQLException e) {
            conn.rollback();
            e.fillInStackTrace();
            err.println("Error executing SQL Command: \"" + command + "\"");
            err.println(e);
            err.flush();
            throw e;
        } catch (final IOException e) {
            e.fillInStackTrace();
            err.println("Error reading SQL Script.");
            err.println(e);
            err.flush();
            throw e;
        }
    }

    /**
     * @return the tableList
     */
    public List<Table> getTableList() {
        return tableList;
    }

    /**
     * @param tableList the tableList to set
     */
    public void setTableList(List<Table> tableList) {
        this.tableList = tableList;
    }

    /**
     * @return the sqlOutput
     */
    public List<String> getSqlOutput() {
        return sqlOutput;
    }

    /**
     * @param sqlOutput the sqlOutput to set
     */
    public void setSqlOutput(List<String> sqlOutput) {
        this.sqlOutput = sqlOutput;
    }
}

How to Call:

// Creating object of ScriptRunner class
ScriptRunner scriptRunner = new ScriptRunner(con, false, true);

String aSQLScriptFilePath = "path/to/sql/script.sql";
// Executing SQL Script
scriptRunner.runScript(new FileReader(aSQLScriptFilePath));

// Optional Part...
List<Table> tableList; // Used to store result of 'SELECT' Query execution
List<String> sqlOutput; // Used to store result of any quires except 'SELECT' quires

tableList = scriptRunner.getTableList();
sqlOutput = scriptRunner.getSqlOutput();

// Now iterate through tableList to display all tables with headers & rows
// Iterate through sqlOutput to display how many rows are affected.

// End of Optional Part...

/*
The above are optional part as because the output of Execution of all SQL Statements will be displayed automatically in Standard Output area. You may store the result if you want to display anywhere else.
*/

Warnings:

1. Remember, ROLLBACK cannot be possible for DDL Statements (CREATE, UPDATE, DELETE). So, be careful during writing SQL Queries.

Rules to Obey:

1. You must end every SQL Statement with ; (Semicolon)
2. You must end every statement within PL/SQL Block with + (Plus)
3. You must end every PL/SQL Block with # (Hash)

Reference:

1. Tool to run database scripts
2. How to Execute SQL Script file using Java JDBC
3. mybatis, SQL Mapping Framework for Java

If you've any doubt, please post your questions. If you really like this article, please share it.

Don’t forget to Vote or Comment about my writing.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Debopam Pal
Software Developer National Informatics Centre (NIC)
India India
Hello! Myself Debopam Pal. I've completed my MCA degree from West Bengal University of Technology at 2013. I'm from India. I’ve started to work with MS Technologies in 2013 specially in C# 4.0, ASP.NET 4.0. I've also worked in PHP 5. Now I work in JAVA/J2EE, Struts2. Currently I'm involved in a e-Governance Project since Jan, 2014. In my leisure time I write Blog, Articles as I think that every developer should contribute something otherwise resource will be finished one day. Thank you for your time.
 
Visit: Linkedin Profile | Facebook Profile | Google+ Profile | CodeProject Profile
Follow on   Google+   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411023.1 | Last Updated 30 Jul 2014
Article Copyright 2014 by Debopam Pal
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid