Java code to Run an SQL Server 2008 Script File (.SQL) Programmatically

Introduction:

The below Java Code is a sample for running an SQL Server script file (.SQL) file run in the database grammatically.

Environment:

Microsoft SQL Server Management Studio 2008
Java 6 (1.6.0_45)
JDBC Driver (SQLJDBC4.jar)


Java Code:

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerDriver;

public class SQLScriptFileToDatabase {
private static final String DRIVER_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
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:sqlserver://localhost:1433";
private static final String USER = "myusername";
private static final String PASSWORD = "mypassword";
private static String INSTRUCTIONS = new String();

public static void main(String[] args)
{
try {
SQLScriptFileToDatabase.resetDatabase();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
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("c:\\My_SQL_Script.txt"));
// 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 = SQLScriptFileToDatabase.getConnection();
PreparedStatement  ps=null;
System.out.println(" <<Started>>:");
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(""))
{
ps= c.prepareStatement( inst[i].toString() );
System.out.println(" <<" + i+ ">>:"+inst[i].toString() + " "  +ps.executeUpdate()  );
ps.close();


}
}
System.out.println(" <<Ended>>:");
}
catch(Exception e)
{
System.out.println("*** Error : "+e.toString());
System.out.println("*** ");
System.out.println("*** Error : ");
e.printStackTrace();
System.out.println("");
}
}
}