Execute .sql file using java

[Sirish Kumar Gongal Reddy]

Hello,

How can I execute .sql(List of .sql files in a resides in local directory)files using java. What I need to do is,

1) Read .sql files one by one from local directory and execute them in oracle environment using java. If it throughs any execption I need to capture that exception.

Note: My .sql file will includes lot of database scripts each file will take around 15 to 20 min to execute.

Many thanks,
Sirish

[Jan Cumps]

Does the file contain data definition (DDL) scripts, like CREATE TABLE or CREATE PROCEDURE, BEGIN .... END?
Or does it only data manipulation (DML) commands, like INSERT INTO, DELETE FROM and UPDATE?

[D Rog]

I have a small project wich is capable to execute SQL script files with different statements including view, sp creations. Merhaps you find it useful

http://sourceforge.net/project/platformdownload.php?group_id=212014

[Sirish Kumar Gongal Reddy]

Hello,

Thanks for replies. Yes my scripts only includes DML operations like drop the tables and load tables with default tables and check all tables are created properly or not like stuff. No procedure or functions.

My .sql scripts do kind of security check for database before deploying my actual application.

Many thanks,
Sirish

[Sirish Kumar Gongal Reddy]

Hello,

I got the solution. Here is the method I wrote and I found some inputs from sun forums. Thanks Again!

public boolean executeDBScripts(String aSQLScriptFilePath, Statement stmt) throws IOException,SQLException {
boolean isScriptExecuted = false;
try {
BufferedReader in = new BufferedReader(new FileReader(aSQLScriptFilePath));
String str;
StringBuffer sb = new StringBuffer();
while ((str = in.readLine()) != null) {
sb.append(str + "\n ");
}
in.close();
stmt.executeUpdate(sb.toString());
isScriptExecuted = true;
} catch (Exception e) {
System.err.println("Failed to Execute" + aSQLScriptFilePath +". The error is"+ e.getMessage());
}
return isScriptExecuted;
}

[Theo Arril]

Hello,
I tried your solution but I got many errors.

First of all you must know that there's no direct way to execute an SQL script file directly through JDBC
You can't load a SQL file into one statement and excute it.

You will have to write your own (very little) parser to split the script in individual statements and execute them one by one.
Splitting in Statements is the biggest challenge here ; so here is my solution :


DatabaseReseter.java

import java.io.BufferedReader;

import java.io.File;
import java.io.FileReader;

import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;

public class Database  {

    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";

    static
    {
        try
        {
            Class.forName(DRIVER_NAME).newInstance();
            System.out.println("*** Driver loaded");
        }
        catch(Exception e)
        {
            System.out.println("*** Error : "+e.toString());
            System.out.println("*** ");
            System.out.println("*** Error : ");
            e.printStackTrace();
        }

    }

    private static final String URL = "jdbc:mysql://localhost/mydatabase";
    private static final String USER = "root";
    private static final String PASSWORD = "password";
    private static String INSTRUCTIONS = new String();

    public static Connection getConnection() throws SQLException
    {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void resetDatabase() throws SQLException
    {
        String s            = new String();
        StringBuffer sb = new StringBuffer();

        try
        {
            FileReader fr = new FileReader(new File("mySQLFile.sql"));
            // be sure to not have line starting with "--" or "/*" or any other non aplhabetical character

            BufferedReader br = new BufferedReader(fr);

            while((s = br.readLine()) != null)
            {
                sb.append(s);
            }
            br.close();

            // here is our splitter ! We use ";" as a delimiter for each request
            // then we are sure to have well formed statements
            String[] inst = sb.toString().split(";");

            Connection c = Database.getConnection();
            Statement st = c.createStatement();

            for(int i = 0; i<inst.length; i++)
            {
                // we ensure that there is no spaces before or after the request string
                // in order to not execute empty statements
                if(!inst[i].trim().equals(""))
                {
                    st.executeUpdate(inst[i]);
                    System.out.println(">>"+inst[i]);
                }
            }
  
        }
        catch(Exception e)
        {
            System.out.println("*** Error : "+e.toString());
            System.out.println("*** ");
            System.out.println("*** Error : ");
            e.printStackTrace();
            System.out.println("################################################");
            System.out.println(sb.toString());
        }

    }

That's my solution, so try and tell me about it !!

[Jeanne Boyarsky]

Theo Arril wrote:First of all you must know that there's no direct way to execute an SQL script file directly through JDBC
You can't load a SQL file into one statement and excute it.

Through JDBC, no. But you can run it as an Oracle command line through Runtime.exec

[Theo Arril]

how about PostgreSQL ?

[Harsha Muthy]

@Theo Arril - Awesome code. I tried this and it works for me. Thanks a ton, you really saved me.

[Tom Enders]

Theo Arril wrote:Hello,
I tried your solution but I got many errors.

First of all you must know that there's no direct way to execute an SQL script file directly through JDBC
You can't load a SQL file into one statement and excute it.

You will have to write your own (very little) parser to split the script in individual statements and execute them one by one.
Splitting in Statements is the biggest challenge here ; so here is my solution :



That's my solution, so try and tell me about it !!

Your parser does not work on a file that contains transactions. I found this when I tried to run it on a file to create a database which contained triggers. So, I rewrote the parser. Here is the code, the comments should be enough to explain it. So use this just replace the split line in Theo's solution with the following code.

            //begin the sql file parser to separate the sql commands into
            //separate array entries. This parser requires that your
            //sql statements be typed in uppercase because that is the 
            //convention of the author.

            //Step 1: Assume that every SQL statement ends with a semi colon
            String[] stmts = sb.toString().split(";");

            //Step 2: Put Transactions back into a single statement.
            for(int i=0;i<stmts.length;i++){
                //if the current statement starts a transaction
                if(stmts[i].contains("BEGIN")){
                    int tInt = i;
                    //find the end of the transaction or the end of the file
                    //whichever comes first
                    while(tInt<stmts.length && !stmts[tInt].contains("END")) {
                        tInt++;
                    } //end while

                    //add a semicolon to the first sql entry in the transaction
                    //which will be in the same array entry as the BEGIN
                    //statement
                    stmts[i] += ";";

                    //loop through the remaining transaction and place them
                    //into the transaction start entry appending semicolons
                    //at the end of each statement
                    for(int j = (i+1); j< tInt; j++) {
                        stmts[i] += "\n" + stmts[j] + ";";
                        //blank out the current transaction entry so that the
                        //executer skips it
                        stmts[j] = " ";
                    } //end for

                    //and the end statement to the end of the transaction
                    stmts[i] += "\nEND";

                    //remove the END transaction from the statement it is
                    //currently embedded in
                    String tStr[] = stmts[tInt].split("END");
                    stmts[tInt] = tStr[1];
                    //skip the statements blanked out earlier, actually pointing
                    //to the last transaction entry so that the for statement
                    //points to the first statement after the transaction
                    i = tInt - 1;
                } //end if

            } //end for

            //end sql file parsers

[neo Infinite]

Theo Arril wrote:Hello,
I tried your solution but I got many errors.

First of all you must know that there's no direct way to execute an SQL script file directly through JDBC
You can't load a SQL file into one statement and excute it.

You will have to write your own (very little) parser to split the script in individual statements and execute them one by one.
Splitting in Statements is the biggest challenge here ; so here is my solution :


DatabaseReseter.java

import java.io.BufferedReader;

import java.io.File;
import java.io.FileReader;

import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;

public class Database  {

    private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";

    static
    {
        try
        {
            Class.forName(DRIVER_NAME).newInstance();
            System.out.println("*** Driver loaded");
        }
        catch(Exception e)
        {
            System.out.println("*** Error : "+e.toString());
            System.out.println("*** ");
            System.out.println("*** Error : ");
            e.printStackTrace();
        }

    }

    private static final String URL = "jdbc:mysql://localhost/mydatabase";
    private static final String USER = "root";
    private static final String PASSWORD = "password";
    private static String INSTRUCTIONS = new String();

    public static Connection getConnection() throws SQLException
    {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void resetDatabase() throws SQLException
    {
        String s            = new String();
        StringBuffer sb = new StringBuffer();

        try
        {
            FileReader fr = new FileReader(new File("mySQLFile.sql"));
            // be sure to not have line starting with "--" or "/*" or any other non aplhabetical character

            BufferedReader br = new BufferedReader(fr);

            while((s = br.readLine()) != null)
            {
                sb.append(s);
            }
            br.close();

            // here is our splitter ! We use ";" as a delimiter for each request
            // then we are sure to have well formed statements
            String[] inst = sb.toString().split(";");

            Connection c = Database.getConnection();
            Statement st = c.createStatement();

            for(int i = 0; i<inst.length; i++)
            {
                // we ensure that there is no spaces before or after the request string
                // in order to not execute empty statements
                if(!inst[i].trim().equals(""))
                {
                    st.executeUpdate(inst[i]);
                    System.out.println(">>"+inst[i]);
                }
            }
  
        }
        catch(Exception e)
        {
            System.out.println("*** Error : "+e.toString());
            System.out.println("*** ");
            System.out.println("*** Error : ");
            e.printStackTrace();
            System.out.println("################################################");
            System.out.println(sb.toString());
        }

    }

That's my solution, so try and tell me about it !!

Thanks for the great solution first!
I tried using it, but the problem is that my .sql file has INSERT queries for multiple tables. And so I am setting the values for each table's primary keys
however it gives an error. Could you please suggest something here?

Please see the stack trace below:
"*** Driver loaded
*** Error : com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'
***
*** Error :
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'################################################

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1749)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1666)
at com.eurexchange.clear.tradeinquiry.service.TradeInquirySampleDatabase.setUpDatabase(TradeInquirySampleDatabase.java:71)
at com.eurexchange.clear.tradeinquiry.service.TradeInquiryServiceImplTest.before(TradeInquiryServiceImplTest.java:39)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:27)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)"

[Wendy L Gibbons]

Welcome to the ranch Neo
I suspect this means your script is wrong, do you understand the error you are getting?

[flo wer]

Hi there, I found your code an it was pretty helpful. I extended it, so that it can now handle files with multiple queries and comments.
All the class does is to provide a method for reading an sql file and returning a list of queries which can than be executed. As result sets can not be collected in a list that easy, you should take the returned query strings and execute them where you need the result set.

ATTENTION: Since comments are filtered out, you must not use comment signs within column names etc.

/**
 * This class can be used to read sql files into an array of Strings, each 
 * representing a single query terminated by ";" 
 * Comments are filtered out. 
 */
package sim_sql_queries;


import java.io.BufferedReader;  

import java.io.File;  
import java.io.FileReader; 
import java.util.ArrayList;

/*
 * ATTENTION: SQL file must not contain column names, etc. including comment signs (#, --, /* etc.)
 * 			like e.g. a.'#rows' etc. because every characters after # or -- in a line are filtered 
 * 			out of the query string the same is true for every characters surrounded by /* and */
/**/

public class SQLReader
{ 
    private static ArrayList<String> listOfQueries = null;
    
    /*
     * @param 	path	Path to the SQL file
     * @return			List of query strings 
     */
    public ArrayList<String> createQueries(String path)  
    { 
    	String queryLine = 		new String();
        StringBuffer sBuffer = 	new StringBuffer();
    	listOfQueries = 		new ArrayList<String>();
    	
	    try  
	    {  
	        FileReader fr = 	new FileReader(new File(path)); 	  
	        BufferedReader br = new BufferedReader(fr);  
	  
	        //read the SQL file line by line
	        while((queryLine = br.readLine()) != null)  
	        {  
	        	// ignore comments beginning with #
	        	int indexOfCommentSign = queryLine.indexOf('#');
	        	if(indexOfCommentSign != -1)
	        	{
	        		if(queryLine.startsWith("#"))
	        		{
	        			queryLine = new String("");
	        		}
	        		else 
	        			queryLine = new String(queryLine.substring(0, indexOfCommentSign-1));
	        	}
	        	// ignore comments beginning with --
	        	indexOfCommentSign = queryLine.indexOf("--");
	        	if(indexOfCommentSign != -1)
	        	{
	        		if(queryLine.startsWith("--"))
	        		{
	        			queryLine = new String("");
	        		}
	        		else 
	        			queryLine = new String(queryLine.substring(0, indexOfCommentSign-1));
	        	}
	        	// ignore comments surrounded by /* */
	        	indexOfCommentSign = queryLine.indexOf("/*");
	        	if(indexOfCommentSign != -1)
	        	{
	        		if(queryLine.startsWith("#"))
	        		{
	        			queryLine = new String("");
	        		}
	        		else
	        			queryLine = new String(queryLine.substring(0, indexOfCommentSign-1));
	        		
	        		sBuffer.append(queryLine + " "); 
	        		// ignore all characters within the comment
	        		do
	        		{
	        			queryLine = br.readLine();
	        		}
	        		while(queryLine != null && !queryLine.contains("*/"));
	        		indexOfCommentSign = queryLine.indexOf("*/");
	        		if(indexOfCommentSign != -1)
	        		{
	        			if(queryLine.endsWith("*/"))
	        			{
	        				queryLine = new String("");
	        			}
	        			else
	        				queryLine = new String(queryLine.substring(indexOfCommentSign+2, queryLine.length()-1));
	        		}
	        	}
	        	
	        	//  the + " " is necessary, because otherwise the content before and after a line break are concatenated
	        	// like e.g. a.xyz FROM becomes a.xyzFROM otherwise and can not be executed 
	        	if(queryLine != null)
	        		sBuffer.append(queryLine + " ");  
	        }  
	        br.close();
	        
	        // here is our splitter ! We use ";" as a delimiter for each request 
	        String[] splittedQueries = sBuffer.toString().split(";");
	        
	        // filter out empty statements
	        for(int i = 0; i<splittedQueries.length; i++)  
            {
	        	if(!splittedQueries[i].trim().equals("") && !splittedQueries[i].trim().equals("\t"))  
                {
	        		listOfQueries.add(new String(splittedQueries[i]));
                }
            }
	    }  
	    catch(Exception e)  
	    {  
	        System.out.println("*** Error : "+e.toString());  
	        System.out.println("*** ");  
	        System.out.println("*** Error : ");  
	        e.printStackTrace();  
	        System.out.println("################################################");  
	        System.out.println(sBuffer.toString());  
	    }
	    return listOfQueries;
    } 
}

To execute the queries, you have to do the setup for the JDBC Connection etc. After that do for example:

ArrayList<String> queries_01;
String pathname_01;

//... get queries from sql file .................
		pathname_01 = "C:/Temp/sql_queries/test.sql";
		queries_01 = sqlread.createQueries(pathname_01);
			
		ArrayList<ResultSet> RSList_01 = new ArrayList<ResultSet>();
		for(String query: queries_01)
		{
			RSList_01.add(pps.DoExecuteQuery(query));
		}
		
		try {
			ResultSet tmpRS = RSList_01.get(0);
			if(!tmpRS.isClosed())
			{
				if(tmpRS.next())
					System.out.println("Query 1: id = " + tmpRS.getInt("id"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}