Import MySQL .sql script from Java

Sometimes it is nice to programmatically run .sql scripts on a MySQL database using Java. This is easily achieved by using a simple ScriptRunner Class from the iBatis apache project. iBatis project is having it's own dependencies and if you want to check that class you need to add all the dependency. To overcome this issue i came across with a Slightly modified version of the com.ibatis.common.jdbc.ScriptRunner class from the iBATIS Apache project. it only removed dependency on Resource class and a constructor.

I made a simple java project which will execute the .sql script in local MySQL database.

Below are the steps to create new project and execute the .sql script in Java project.



Step 1 :
Go to File -> New -> Java Project.

Step 2 :
Enter your project name.

Step 3 :
Now we need to configure MySQL connector Driver jar file with this project.
Right click on project -> Build Path -> Configure Build Path










Step 4 :
Now click on Libraries tab and Add External JARs button and select the MySQL connecter jar file from your machine path.





Step 5 :
Now Create new package name -> com.test.script

Step 6 :
Create new Class "ImportMySQLDB.java" inside com.test.script package

Step 7 :
Summary of ImportMySQLDB class coding.

package com.test.script;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ImportMySQLDB {
static Connection connection = null;
   /**
    * @param args
   */
   public static void main(String[] args) {
 try{
  getConnection();
  ScriptRunner runner = new ScriptRunner(connection, true, true);
  runner.runScript(new BufferedReader(new FileReader("test_db/test_db.sql")));
 }catch(SQLException se){
  System.out.println("SQL Exception Occurs :=>"+se.toString());
 }catch(FileNotFoundException fe){
  System.out.println("File Not Found Exception Occurs :=>"+fe.toString());
 }catch(IOException ie){
  System.out.println("IO Exception Occurs :=>"+ie.toString());
 }

   }
 
   public static void getConnection(){
 String url = "jdbc:mysql://localhost:3306";
 try{
     Class.forName("com.mysql.jdbc.Driver");
     connection = DriverManager.getConnection(url, "root", "root");
 }catch(SQLException se){
     System.out.println("Failed to load mSQL driver."+se.toString());
 }catch(Exception e){
     System.out.println("Failed to Connect MySQL Connection :=>"+e.toString());
 }
   }

}

Step 8 :
Create new class "ScriptRunner.java" inside com.test.script package








Step 9 :
Copy all the code from ScriptRunner Github to New ScriptRunner class file

Step 10 :
Save all the pages and run the application from ImportMySQLDB class.

Step 11 :
Now you will see the process is going in Console tab.

All done!

Let me know how it goes at your end. Enjoy … :)

References :

https://gist.github.com/joe776/831762/
Stackoverflow
http://www.eclipse.org/