Data Migration from Excelsheet to database using Java

This java code reads the excel sheet columnwise and then insert the data in database through SQL insert statement. The name of excelsheet columsn matches with the databse table columns.

import java.io.*;
import java.util.*;
import java.sql.*;
import jxl.*;
import oracle.jdbc.OracleResultSet;


public class interfaceDataImport  
{
    private Connection  conn                    = null;    // Connection Object
    private String         dbUser                  = "raj";  // DataBase UserName
    private String         dbPassword              = "raj";  // DataBase Password
 private String     connectionURL = "jdbc:oracle:thin:@locahost:1521:raj";    // Connection URL
 
 
 
 
 // Function to open DB Connection that returns Connection Object
    public Connection connect() throws SQLException, IllegalAccessException, InstantiationException, ClassNotFoundException {
        String driver_class  = "oracle.jdbc.driver.OracleDriver";
        String connectionURL = "jdbc:oracle:thin:@localhost:1521:raj";
        try {
            Class.forName(driver_class).newInstance();
            
            conn = DriverManager.getConnection(connectionURL, dbUser, dbPassword);
           // conn.setAutoCommit(true);
            System.out.println("Connected.n");
        }
        catch (IllegalAccessException e) {
            System.out.println("Illegal Access Exception: (Open Connection).");
            e.printStackTrace();
            throw e;
        }
        catch (InstantiationException e) {
            System.out.println("Instantiation Exception: (Open Connection).");
            e.printStackTrace();
            throw e;
        }
        catch (ClassNotFoundException e) {
            System.out.println("Class Not Found Exception: (Open Connection).");
            e.printStackTrace();
            throw e;
        }
        catch (SQLException e) {
            System.out.println("Caught SQL Exception: (Open Connection).");
            e.printStackTrace();
            throw e;
        }
        return conn;
    }


    // Function to close the DB Connection
    public void disconnect() throws SQLException {
        try {
            conn.close();
            System.out.println("Disconnected.n");
        }
        catch (SQLException e) {
            System.out.println("Caught SQL Exception: (Closing Connection).");
            e.printStackTrace();
            if (conn != null) {
                try {
                    conn.rollback();
                }
                catch (SQLException e2) {
                    System.out.println("Caught SQL (Rollback Failed) Exception.");
                    e2.printStackTrace();
                }
            }
            throw e;
        }
    }


    private void getFileData(File file) throws Exception{
        System.out.println("inside getFileData.......");
        
        ArrayList alColumnNames = new ArrayList();
        File inputWorkbook = file;
  int inputSheetCount = 0;
  int inputSheetRowCount = 0;
  int inputSheetColCount = 0;
  String sheetName = "";
  String sheetColumn = "";
  String sheetValue = "";
//  String sSqlSelect = "";

        try{
            Workbook w1nput = Workbook.getWorkbook(inputWorkbook);

   inputSheetCount = w1nput.getNumberOfSheets();  
   
            for(int sheetCount=0; sheetCount<inputSheetCount; sheetCount++){
                
                Sheet s = w1nput.getSheet(sheetCount);
                inputSheetRowCount = s.getRows();
                inputSheetColCount = s.getColumns();
    sheetName = s.getName();
                System.out.println("Copying...");

//    sSqlSelect = "Select count(*) count from "+sheetName;

//    if(selectRecord(sheetName) <= 0 ){

     System.out.println("TABLE..."+sheetName+"...EMPTY...");

     StringBuffer sbInsertColumn = new StringBuffer();
     String sInsCol = "";
     String sInsVal = "";
     String sSqlInsert = "";

     sbInsertColumn.append("INSERT INTO "+sheetName+" ( ");
     
     for(int i=0;i<inputSheetColCount;i++){
      Cell cData = s.getCell(i,0);        // gets Column Name
      sheetColumn = cData.getContents();
      sheetColumn=(sheetColumn!=null)?sheetColumn.trim():"";

      sbInsertColumn.append(sheetColumn+" , " );
     }

     sInsCol=sbInsertColumn.toString().trim();
 //    if(sInsCol.endsWith(", ")) {
      sInsCol=sInsCol.substring(0,(sInsCol.length()-2));
 //    }

     sInsCol = sInsCol+" ) ";


     for(int j=2;j<inputSheetRowCount;j++){
      StringBuffer sbInsertValue = new StringBuffer();
      sbInsertValue.append(" VALUES ( ");

      for(int k=0;k<inputSheetColCount;k++){
       Cell cValue = s.getCell(k,j);        // gets Column Value
       sheetValue = cValue.getContents();
       sheetValue=(sheetValue!=null)?sheetValue.trim():"";

 /*
       if(sheetValue.equalsIgnoreCase("(null)") || sheetValue.equalsIgnoreCase("null") ){
        sheetValue = "";
       }
 */
       sbInsertValue.append(" '"+sheetValue+"' , " );
      }

      sInsVal=sbInsertValue.toString().trim();
 //     if(sInsVal.endsWith(", ")) {
       sInsVal=sInsVal.substring(0,(sInsVal.length()-2));
 //     }

      sInsVal = sInsVal+" )";

      sSqlInsert = sInsCol + sInsVal;

      System.out.println("INSERT SQL::::::::j="+j+"::::::"+sSqlInsert);

      int count=insertRecord(sSqlInsert);

     }// for(int j=2;j<inputSheetRowCount;j++){

//    }// if(selectRecord(sSqlSelect) > 0 )

            }// end for

        }
        catch(Exception e){
            System.out.println("Exception getFileData::"+ e);
            e.printStackTrace();
            throw e;
        }

    }// end getFileData(File file)
    

 public int insertRecord(String sqlInsert) throws Exception
 {
         int count = 0;
   Statement stmt = null;
  try
  {
   stmt = conn.createStatement();
   System.out.println("Insert statement made...");
   count = stmt.executeUpdate(sqlInsert);
   System.out.println("query  executed...");
   conn.commit();
   stmt.close();
   stmt = null;

  }
  catch(Exception e)
  {
   System.out.println("Caught Exception... "+e);
  }
  finally{
   if(stmt!=null){
    stmt.close();
   }
  }
  return count;
 }



 public int selectRecord(String tableName) throws Exception
 {
         ResultSet rs = null;
   Statement stmt = null;
   Statement stmt1 = null;
   String sDeleteSql = "";
   String sSelectSql = "";
         int count = 0;
  try
  {
   sDeleteSql = "Delete from "+tableName;
   System.out.println("DELETE SQL::::::::"+sDeleteSql);

   stmt1 = conn.createStatement();
   stmt1.executeUpdate(sDeleteSql);
   System.out.println("Delete query  executed...");
   stmt1.close();
   stmt1 = null;

   sSelectSql = "Select count(*) count from "+tableName;
   System.out.println("SELECT SQL::::::::"+sSelectSql);

   stmt = conn.createStatement();
   System.out.println("Select statement made...");
   rs = stmt.executeQuery(sSelectSql);
   System.out.println("Select query  executed...");
   if(rs != null && rs.next())
   {
    count =rs.getInt("count");
   }
   System.out.println("Count  "+count);
   stmt.close();
   rs.close();
   stmt = null;
  }
  catch(Exception e)
  {
   System.out.println("Caught Exception  "+e);
  }
  finally{
   if(stmt!=null){
    stmt.close();
   }
   if(stmt1!=null){
    stmt1.close();
   }
   if(rs!=null){
    rs.close();
   }
  }
  return count;
 }



 public static void main(String[] args) 
 {
  System.out.println("Hello World!");

  try{

  // write the file object to be uploaded to blob
        BufferedInputStream  bis = new BufferedInputStream(new FileInputStream("c:\Import-Data.xls"));

  File fileOut = new File("FileOut");
  FileOutputStream fos = new FileOutputStream(fileOut);
        BufferedOutputStream bos = new BufferedOutputStream(fos);

  byte[] buff = new byte[100000];
  int bytesRead; 

  // Simple read/write loop.
  while(-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
  bos.write(buff, 0, bytesRead);
  }
  // file write code over
  System.out.println("File writen successfully");



  interfaceDataImport pdi = new interfaceDataImport();
  pdi.connect();

  pdi.getFileData(fileOut);

  pdi.disconnect();

  fos.close();


  }
  catch(Exception e){
   e.printStackTrace();
  }



 }
}