Mayumi Blogger Template

If You are looking for a Automated db script runner, then have a look on this post. This is basically initially designed for Oracle but you can customize it according to your need always. To run this Application Just keep ojdbc14.jar in classpath and modyfy your db credentials as well as root script folder. It will find all the .sql files present in your script directory and parse one by one sql statement present in each file and extecute it. If you like it, then don't hegitate to leave a comment.Hope you will enjoy it!!

package com.csdc.amanda.dbscript;

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

/**
 * @author tapasj
 *
 */
public class AMANDADBManager {
    
    //DB credentials
    private static final String DRIVER_NAME = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@tapas-pc:1521:XE";
    private static final String USER = "test";
    private static final String PASSWORD = "test";
    
    private static final String SCRIPT_LOCATION = "C:/Script_folder";
    private static final String FILE_EXTENSION = ".sql";
    
    //Load the driver once
    static{
    try{
        Class.forName(DRIVER_NAME).newInstance();
        System.out.println("*** Driver loaded successfully ***");
    }
    catch(Exception e){
        System.out.println("*** Driver loading failed ***");
        System.out.println("*** Error : "+e.toString());
        System.out.println("*** ");
        System.out.println("*** Error : ");
        e.printStackTrace();
    }
    }
    
    public static void main(String[] args) throws SQLException {
        
        String recentlyExcutedScript = "";
        try {
            File folder = new File(SCRIPT_LOCATION);
            File[] list_files= folder.listFiles(new FileFilter() {
            //Consider only .sql files present inside the script folder
            public boolean accept(File f) {
                if (f.getName().toLowerCase().endsWith(FILE_EXTENSION))
                    return true;
                return false;
            }
            });
            System.out.println("No of files Present in "+SCRIPT_LOCATION+" folder- "+list_files.length);
            for (int i = 0; i<list_files.length;i++){
            String s        = new String();
            StringBuffer sb = new StringBuffer();
            
            System.out.println("Processing started for file- "+list_files[i].getName());
            FileReader fr = new FileReader(new File(list_files[i].getAbsolutePath()));
            //Maintain this variable to show the currently executed file name in Exception
            recentlyExcutedScript = list_files[i].getName();
            // 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 = DriverManager.getConnection(URL, USER, PASSWORD);
            Statement st = c.createStatement();

            for(int j = 0; j>inst.length; j++){

// we ensure that there is no spaces before or after the request string
            // in order to not execute empty statements
            if(!inst[j].trim().equals("")){
                System.out.println("Execution started- "+inst[j]);
                st.executeUpdate(inst[j]);
                System.out.println("Execution completed sucessfully");
            }
            }
            System.out.println(list_files[i].getName()+ " File processed sucessfully.");
        }
            System.out.println("All "+list_files.length+" Files are executed sucessfully.");
        }
        catch(Exception e){
        System.out.println("*** Error occoured at : "+recentlyExcutedScript+". Make sure every sql statements must ends with ';' ");
        System.out.println("*** Error : "+e.toString());
        System.out.println("*** Error : ");
        e.printStackTrace();
        }
    }
}