How to execute a database script in java / Various ways to execute a database query file in java

A database script is a file which contains multiple queries separated by some separator. Executing a script is most commonly used to apply patches on an application where multiple database operations are performed on an existing database such as some values of existing records need to be changed, a new table should be created, data type of some columns are changed, columns need to be added or dropped.

How to execute a database script in java

This post will demonstrate a couple of methods to execute a database script from a java program. Typically both these approaches will involve the execution of queries using JDBC. The database type used in both the approaches is MySQL. Assume the database script which will be executed contains all type of queries and looks as below :

Method 1 : Using ScriptRunner of iBatis

Apache iBatis has a class com.ibatis.common.jdbc.ScriptRunner which provides provision to execute a script file containing multiple queries. Constructor of ScriptRunner takes 3 arguments :

1. A java.sql.Connection object.
2. A boolean which indicates whether query execution should be auto committed or not.
3. A boolean which configures whether the execution should be stopped in case of error.

ScriptRunner’s runScript method is used to execute the script. This method takes a java.io.Reader as argument. Hence we pass a java.io.BufferedReader as an argument to this method. This BufferedReader points to the actual script file which contains SQL queries to be executed. java.sql.Connection object is created using the usual JDBC methods.

Format of script file should follow below structural points :

1. All queries in the script file should be separated by semi-colon(;). If queries do not end with “;”, they are not executed and no message is printed.
2. Lines starting with “–” are taken as comments and printed on console.
3. Lines starting with “//” are ignored.

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import com.ibatis.common.jdbc.ScriptRunner;

public class DBScriptRunner {

  public static void main(String[] args) {
        try {
		executeScriptUsingScriptRunner();
	} catch (IOException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	}
    }

    static void executeScriptUsingScriptRunner() throws IOException, SQLException {
	// initialize script path
	String scriptFilePath = "e:/script.sql";
	Reader reader = null;
	Connection con = null;
	try {
		// load driver class for mysql
		Class.forName("com.mysql.jdbc.Driver");
		// create connection
		con = DriverManager.getConnection("jdbc:mysql://localhost:3306/codippa", 
                            "root", "root");
		// create ScripRunner object
		ScriptRunner scriptExecutor = new ScriptRunner(con, false, false);
		// initialize file reader
		reader = new BufferedReader(new FileReader(scriptFilePath));
		// execute script with file reader as input
		scriptExecutor.runScript(reader);
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		// close file reader
		if (reader != null) {
			reader.close();
		}
		// close db connection
		if (con != null) {
			con.close();
		}
	}
    }
}

Output

Result of running the above program produces the following output at the console

Method 2 : Using Statement object

This is the traditional approach which creates JDBC connection and using this connection retrieves a java.sql.Statement object. Using this statement object, queries are executed. Script file is read line by line where each line is a database query. Each query is passed to the execute method of Statement and it is executed on the database as shown in the code below.

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DBScriptRunner {

   public static void main(String[] args) {
	try {
		executeScriptUsingStatement();
	} catch (IOException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	}
   }

   static void executeScriptUsingStatement() throws IOException, SQLException {
	String scriptFilePath = "e:/script.sql";
	BufferedReader reader = null;
	Connection con = null;
	Statement statement = null;
	try {
		// load driver class for mysql
		Class.forName("com.mysql.jdbc.Driver");
		// create connection
		con = DriverManager.getConnection("jdbc:mysql://localhost:3306/codippa", 
                             "root", "root");
		// create statement object
		statement = con.createStatement();
		// initialize file reader
		reader = new BufferedReader(new FileReader(scriptFilePath));
		String line = null;
		// read script line by line
		while ((line = reader.readLine()) != null) {
			// execute query
			statement.execute(line);
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		// close file reader
		if (reader != null) {
			reader.close();
		}
		// close db connection
		if (con != null) {
			con.close();
		}
	}
   }
}

Let’s tweak in

1. It is also possible to add queries collectively in a batch using the addBatch method of java.sql.Statement object. Then when the batch reaches a specified size(such as 10), execute all the queries at once using executeBatch method. This improves performance since the number of database trips are reduced.
2. A java.io.FileNotFoundException is raised when the script file does not exist at the given location in both the methods.