JDBC Connection with Oracle

There is a property file in which database name,user name,user password is saved.With the use of this file JDBC connection can be stablished with oracle.

//load a file that has all the information of database,user name,user password.
//It will throw ClassNotFoundException if class is not found
//If any exception due to database is occured then it  throws SQLException
String driverClass = "oracle.jdbc.driver.OracleDriver";
Connection con;
public void init(FileInputStream fs) throws ClassNotFoundException, SQLException, FileNotFoundException, IOException
    {
        Properties props = new Properties();
        props.load(fs);
        String url = props.getProperty("db.url");
        String userName = props.getProperty("db.user");
        String password = props.getProperty("db.password");
        Class.forName(driverClass);
 
        con=DriverManager.getConnection(url, userName, password);
    }

[ Read More ]

How to retrieve Datas from Database using Java Program

Initially connection is made for the Database and Query is written for retrieving the Datas.

import java.sql.*;

public class RetriveAllEmployees{
 public static void main(String[] args) { 
   System.out.println("Getting All Rows from employee table!"); 
   Connection con = null;  
   String url = "jdbc:mysql://localhost:3306/";  
   String db = "jdbc"; 
   String driver = "com.mysql.jdbc.Driver";  
   String user = "root"; 
   String pass = "root";
   
   try{
    Class.forName(driver); 
    con = DriverManager.getConnection(url+db, user, pass);
   
    Statement st = con.createStatement(); 
    ResultSet res = st.executeQuery("SELECT * FROM  employee");  
   
    System.out.println("Employee Name: " );
     
    while (res.next()) { 
      String employeeName = res.getString("employee_name"); 
      System.out.println(employeeName );  
    } 
    con.close();
   } catch (ClassNotFoundException e) {  
    System.err.println("Could not load JDBC driver");
    System.out.println("Exception: " + e); 
    e.printStackTrace();  
    } catch(SQLException ex) { 
     System.err.println("SQLException information");  
   
     while(ex!=null) { 
      System.err.println ("Error msg: " + ex.getMessage()); 
      System.err.println ("SQLSTATE: " + ex.getSQLState());
      System.err.println ("Error code: " + ex.getErrorCode()); 
      ex.printStackTrace();  ex = ex.getNextException(); // For drivers that support chained exceptions  
     }
    }  
   }
} 

[ Read More ]

JDBC and SAS Connectivity

Access a SAS Dataset using JDBC

import java.sql.*;
import java.util.Properties;

public class accessSASData {
 public static void main(String argv[]) {
  Connection connection;
  Properties props;
  int i;
  Statement statement;

  /* SAS datasets can be queried with a SQL statement itself */

  String queryString = "SELECT sup_id, sup_name "
    + "FROM mySasLib.suppliers ORDER BY sup_name";
  ResultSet result;
  double id;
  String name;
  try {
   // CONNECT TO THE SERVER BY USING A CONNECTION PROPERTY LIST
   Class.forName("com.sas.rio.MVADriver");
   props = new Properties();
   props.setProperty("user", "jdoe");
   props.setProperty("password", "4ht8d");

   /* SAS libref and library name */

   props.setProperty("librefs", "mySasLib c:\\sasdata';");
   connection = DriverManager.getConnection(
     "jdbc:sasiom://c123.na.abc.com:8591", props);
   // ACCESS DATA
   statement = connection.createStatement();
   result = statement.executeQuery(queryString);
   while (result.next()) {
    id = result.getDouble(1);
    name = result.getString(2);
    System.out.println(id + " " + name);
   }
   statement.close();
   connection.close();
  } catch (Exception e) {
   System.out.println("error " + e);
  }
 }
}

[ Read More ]

Java object as a Blob

The sample code to store the java object [serialised] in to the oracle database as Blob and reterive the java object back from the blob

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInput;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * This class demontrates the code 
 * how to put an serialised java object in to database as BLOB and
 * how to get an serialised java object from database
 * 
 * 
 * SQL to be executed to create table
  CREATE TABLE "SAMPLE"
  (
    "ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "CONTENT" BLOB,
    CONSTRAINT "SAMPLE_PK" PRIMARY KEY ("ID") ENABLE
  )
 *
 */
public class InsertAndFetchJavaObjAsBLOB {

 static String userid = "<USER_NAME>"; //user name to connect to database schema
 static String password = "<PASSWORD>"; //password to connect to database schema
 static String url = "jdbc:oracle:thin:@<IP_ADDRESS>:<PORT>:<SID>"; //connection string specifying IP, port and SID for database
 
 static Connection con = null;

 public static void main(String[] args) throws Exception {
  boolean insert = true;
  if(insert){
  insertObjectToBlob(12,"Kevin", 12, 45);
  insertObjectToBlob(11,"Kevin", 11, 48);
  insertObjectToBlob(10,"Kevin", 10, 38);
  insertObjectToBlob(9,"Kevin", 9, 33);
  insertObjectToBlob(8,"Kevin", 8, 39);
  insertObjectToBlob(7,"Kevin", 7, 29);
  insertObjectToBlob(6,"Kevin", 6, 22);
  insertObjectToBlob(5,"Kevin", 5, 27);
  insertObjectToBlob(4,"Kevin", 4, 28);
  insertObjectToBlob(3,"Kevin", 3, 26);
  insertObjectToBlob(2,"Kevin", 2, 25);
  insertObjectToBlob(1,"Kevin", 1, 26);
  }
  readObjectFromBlob(1);
  readObjectFromBlob(5);
  readObjectFromBlob(7);
  readObjectFromBlob(10);
  readObjectFromBlob(12);
 }
 
 /**
  * This methos demontrates the code 
     * how to put an serialised java object in to database as BLOB and
  * @param id
  * @param name
  * @param div
  * @param rollNo
  */
 public static void insertObjectToBlob(int id, String name, int div, int rollNo) {
  System.out.println("Entering methos insertObjectToBlob with param id: "+id+" | name: "+name+" | div: "+div+" | rollNo: "+rollNo);
  Connection con = getOracleJDBCConnection();
  if (con != null) {
   try{
   System.out.println("Got Connection.");
   DatabaseMetaData meta = con.getMetaData();
   System.out.println("Driver Name : " + meta.getDriverName());
   System.out.println("Driver Version : " + meta.getDriverVersion());
   Statement stmt = con.createStatement();
   
   //create a java object
   Student s = new Student(name,div,rollNo);
   
   ByteArrayOutputStream baos = new ByteArrayOutputStream();                 
   ObjectOutputStream objOstream = new ObjectOutputStream(baos);                 
   objOstream.writeObject(s);                   
   objOstream.flush();                 
   objOstream.close();                   
   byte[] bArray = baos.toByteArray(); 
      
   System.out.println("bArray = " + bArray);                   
   PreparedStatement objStatement = con.prepareStatement("insert into sample(id,content) values (?,?)");                   
   objStatement.setInt(1,id);
   //objStatement.setBlob(2, blob );
   objStatement.setBytes(2, bArray);                 
   boolean a = objStatement.execute(); 
   
   System.out.println("Result of Insert: "+a);
   stmt.close();
   }catch(Exception e){
    e.printStackTrace();
   }finally{
    if(con != null){
     try {
      con.close();
     } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     }
    }
   }
  } else {
   System.out.println("Could not Get Connection");
  }
  System.out.println("Exiting methos insertObjectToBlob.");
 }
 /**
  * This methos demontrates the code 
     * how to get an serialised java object from database
  * @param id
  */
 public static void readObjectFromBlob(int id) {
  System.out.println("Entering methos readObjectFromBlob with param id: "+id);
  Connection connection = null;

  PreparedStatement pstmt = null;
  System.out.println("Deriver name");
  InputStream in = null;
  try {

   // Load the JDBC driver
   /*String driverName = "oracle.jdbc.driver.OracleDriver";
   Class.forName(driverName);*/

   System.out.println("Hello");
   // Create a connection to the database
   
   connection = getOracleJDBCConnection();
   System.out.println("Connectin before close :: " + connection);

   ResultSet rs = null;
   java.sql.Blob rs1 = null;
   String queryStr = null;

   // queryStr = new String("select * from CPOS_SALE_TRN_INVOICE_DTL");

   queryStr = new String(
     "select content from SAMPLE where id="+id);

   pstmt = connection.prepareStatement(queryStr);

   rs = pstmt.executeQuery();

   if (null != rs) {
    System.out.println("Result set found.");
    while (rs.next()) {
     System.out.println("Result set found. Inside while.");

     rs1 = (Blob) rs.getBlob(1);

     System.out.println("rs1: " + rs1);
     ByteArrayOutputStream baos = new ByteArrayOutputStream();
     byte[] buf = new byte[1024];

     in = rs1.getBinaryStream();

     System.out.println("in: " + in);

     int n = 0;
     while ((n = in.read(buf)) >= 0) {
      baos.write(buf, 0, n);
     }

     System.out.println("baos: " + baos);

     byte[] bytes = baos.toByteArray();
     System.out.println("bytes: " + baos);

     ByteArrayInputStream bis = new ByteArrayInputStream(bytes);
     System.out.println("bis: " + bis);
     ObjectInput in1 = new ObjectInputStream(bis);
     System.out.println("in1: " + in1);
     Object o = in1.readObject();
     System.out.println("object : " + o);
     if (o != null) {
      System.out.println("Object Class: "
        + o.getClass().getName());
      System.out.println(((Student)o).getName()+" | "+((Student)o).getStd()+" | "+((Student)o).getRollNo());

     }

    }
   }

  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   if (in != null) {
    try {
     in.close();
    } catch (IOException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
   }
  }
  System.out.println("Exiting methos readObjectFromBlob.");
 }

 public static Connection getOracleJDBCConnection() {

  try {
   Class.forName("oracle.jdbc.driver.OracleDriver");
  } catch (java.lang.ClassNotFoundException e) {
   System.err.print("ClassNotFoundException: ");
   System.err.println(e.getMessage());
  }

  try {
   con = DriverManager.getConnection(url, userid, password);
  } catch (SQLException ex) {
   System.err.println("SQLException: " + ex.getMessage());
  }

  return con;
 }

}

[ Read More ]

Exporting the data from the Database using java

This Program is used to retrive the data from the Database .

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelFile {
 public static void main(String[] args) {   
  // TODO Auto-generated method stub   
  Connection con=null;   
  try {     
   String action,connString=null;   
   String start,end;   
   String smartcardquery=null;   
   ResultSet rssmartdata=null;   
   PreparedStatement pssmartdata=null;   
   Scanner sc=new Scanner(System.in);   
   System.out.print("Enter username"); // Enter the user name to connect the DB  
   String user=sc.next();   
   System.out.print("Enter password");  // Enter the password to connect the DB 
   String pwd= sc.next();   
   Class.forName("oracle.jdbc.OracleDriver");   
   System.out.println("Oracle JDBC driver loaded ok.");   
   connString = "jdbc:oracle:thin:" + user + "/" + pwd   
     + "@localhost:port no";   
   con = DriverManager.getConnection(connString);   
   System.out.println("Enter number of sheets to be created");   
   int numOfSheets=sc.nextInt();   
   String[] sheets = new String[numOfSheets];   
   HSSFWorkbook wb = new HSSFWorkbook();    
   for (int i=0; i<=sheets.length-1; i++)   
   {   
    System.out.println("1.ADD ITEM\n2.DELETE ITEM\n");   
    System.out.print("Enter the action to which report is needed ");   
    action=sc.next();   
    HSSFSheet sheet = wb.createSheet(action);              
    HSSFRow rowhead = sheet.createRow((short) 0);              
    rowhead.createCell((short) 0).setCellValue("ITEM NAME");                           
    rowhead.createCell((short) 1).setCellValue("QUANTITY)");                           
    rowhead.createCell((short) 2).setCellValue(" COST");                           
    smartcardquery = "select ITEMNAME,QUANTITY,COST from INVENTORY where action=?;    
      pssmartdata = con.prepareStatement(smartcardquery);   
    pssmartdata.setString(1, action);   
    rssmartdata=pssmartdata.executeQuery();    
    while (rssmartdata.next()) {                                   
     HSSFRow row = sheet.createRow((short) index);                                   
     row.createCell((short) 0).setCellValue(rssmartdata.getString(1));                                   
     row.createCell((short) 1).setCellValue(rssmartdata.getString(2));                                   
     row.createCell((short) 2).setCellValue(rssmartdata.getString(3));    
    }    
   }   
   FileOutputStream fileOut = new FileOutputStream("Report.xls");                           
   wb.write(fileOut);   
   fileOut.close();                           
   System.out.println("Data is saved in excel file.");                           
   rssmartdata.close();                           
   con.close();                   
  }    
  catch (Exception e) {   
   e.printStackTrace();   

  }   

 }
}

[ Read More ]

Meta Data Reader

The Code here takes a Table name as the String Parameter and Displays the Table's MetaData in two ways

Solution 1 : Through ResultSetMetaData (java.sql.ResultSetMetaData)
Solution 2 : Through DatabaseMetaDataSet (java.sql.DatabaseMetaData)

The Code snippet has comments for easy understanding and use. 

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class MetaDataReader1 {

 public static void main(String[] args) throws SQLException {
  System.out.println("Connecting..");
  Connection conn = null;
  String url = "jdbc:oracle:thin:@192.168.5.43:1521:userdb";
  String driver = "oracle.jdbc.OracleDriver";
  String userName = "rajdb";
  String password = "password";

  // Making Connection......
  try {
   Class.forName(driver).newInstance();
   conn = DriverManager.getConnection(url, userName, password);
  } catch (Exception e) {
   System.out.println("Error in Connection");
  }
  System.out.println("Connected to the database");

  System.out.println("***************Method 1*******************");
  
  /*** Method 1 ***/
  
  String query="";
  /* The Query String */
  if (args.length == 0) {
   query = "Select * from TABLE1";     // in case no parameters are passed some default value
  } else if (args.length ==1){
   query = "Select * from " + args[0];
  } else {
   System.out.println("Invalid parameters");
   System.exit(0);
  }
  
  
  Statement stmt = conn.createStatement();
  ResultSet rs = null;
  try {
   rs = stmt.executeQuery(query);
  } catch (Exception e) {
   System.out.println("Unable to Execute Query");
   System.exit(0);
  }
  ResultSetMetaData rsMd = rs.getMetaData();
  System.out.print("The Number of Columns in the Table -> ");
  System.out.println(rsMd.getColumnCount() + "\n");

  System.out.println("*************Table MetaData***************");
  for (int i = 1; i <= rsMd.getColumnCount(); i++) {
   System.out.print(rsMd.getColumnName(i) + "\t");  // Column Name
   System.out.print(rsMd.getColumnClassName(i) + "\t");  // Column Class Type
   System.out.print(rsMd.getColumnTypeName(i) + "\t");  // Column Type in DB
   System.out.print(rsMd.getPrecision(i) + "\t");   // Column Precision
   System.out.println(rsMd.getColumnDisplaySize(i) + "\t");  // Column Size
    
  }

  System.out.println("\n***************Method 2*******************");
  System.out.println("*************Table MetaData***************");
  /*** Method 2 ***/
  DatabaseMetaData dbm = conn.getMetaData();
  ResultSet rs1 = dbm.getColumns("rajdb", "%", "WI_CONTROL_TBL", "%");
  while (rs1.next()) {
   String col_name = rs1.getString("COLUMN_NAME"); // Column name
   String data_type = rs1.getString("TYPE_NAME"); // Column Type in DB
   int data_size = rs1.getInt("COLUMN_SIZE");  // Column Size
   int nullable = rs1.getInt("NULLABLE");  // Column is Nullable or Not
   System.out.print(col_name + "\t" + 
     data_type + "(" + 
     data_size + ")" + "\t");
   if (nullable == 1) {
    System.out.print("YES NULLABLE\t");
   } else {
    System.out.print("NOT NULLABLE\t");
   }
   System.out.println();
  }
  System.out.println("\n***************Table Data*****************");

  int rowNumber = 0;
  int totColumns = rs.getMetaData().getColumnCount();
  while (rs.next()) {                                                      // ROW Iterator
   rowNumber++;
   int colNumber = 1;
   for (int i = 0; i < totColumns; i++) {                                 // Column Iterator
    // Reading type of Column No. = colNumber
    String s = rs.getMetaData().getColumnClassName(colNumber);
    int j = 0;
    /*
     * For Example I have taken only 3 type of data ... For any
     * different type of Column Class name one needs to add to the
     * code here and print accordingly
     */
    // Reading type of the Column
    if (s.equalsIgnoreCase("java.lang.String")) {
     j = 1;   // java.lang.String
    } else if (s.equalsIgnoreCase("java.sql.Timestamp")) {
     j = 2;   // java.sql.Timestamp
    } else if (s.equalsIgnoreCase("java.math.BigDecimal")) {
     j = 3;   // java.math.BigDecimal
    }
    // Printing Output of the Column
    switch (j) {
    case 1:
     System.out.print(rs.getString(colNumber++) + "\t");
     break;
    case 2:
     System.out.print(rs.getDate(colNumber++) + "\t");
     break;
    case 3:
     System.out.println(rs.getLong(colNumber++) + "\t");
    }
   }
   System.out.println();
  }
  System.out.println("\n******************************************");
  System.out.println("Total Number of Rows - >" + rowNumber);

  

  System.out.println("******************************************");
  System.out.println("Disconnecting...");
  conn.close();
  System.out.println("Disconnected from database");
 }
}

[ Read More ]

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();
  }



 }
}

[ Read More ]

A sample java code for Connection Pooling for JDBC connection

This java code snippet implements a class that performs Connection pooling for JDBC connections and can be used with slight modification.It's a technique to allow multiple clinets to make use of a cached set of shared and reusable connection objects providing access to a database.

ConnectionPool Class

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;



 public class ConnectionPool implements Runnable 
{     
    // Number of initial connections to make. 
    private int initialConnectionCount = 5;     
    
    // A list of available connections for use. 
    private Vector availableConnections = new Vector(); 
    
    // A list of connections being used currently. 
    private Vector usedConnections = new Vector(); 
    
    // The URL string used to connect to the database 
    private String urlString = null; 
    
    // The username used to connect to the database 
    private String userName = null;     
    
    // The password used to connect to the database 
    private String password = null;     
    
    // The cleanup thread 
    private Thread cleanupThread = null; 
         
                                              
    //Constructor 
    public ConnectionPool(String url, String user, String passwd) throws SQLException 
    { 
        // Initialize the required parameters 
     urlString = url; 
        userName = user; 
        password = passwd; 

        for(int cnt=0; cnt<initialConnectionCount; cnt++) 
        { 
            // Add a new connection to the available list. 
            availableConnections.addElement(getConnection()); 
        } 
         
        // Create the cleanup thread 
        cleanupThread = new Thread(this); 
        cleanupThread.start(); 
    }     
     
    private Connection getConnection() throws SQLException 
    { 
        return DriverManager.getConnection(urlString, userName, password); 
    } 
     
    public synchronized Connection checkout() throws SQLException 
    { 
        Connection newConnxn = null; 
         
        if(availableConnections.size() == 0) 
        { 
            // Im out of connections. Create one more. 
             newConnxn = getConnection(); 
            // Add this connection to the "Used" list. 
             usedConnections.addElement(newConnxn); 
            // We dont have to do anything else since this is 
            // a new connection. 
        } 
        else 
        { 
            // Connections exist ! 
            // Get a connection object 
            newConnxn = (Connection)availableConnections.lastElement(); 
            // Remove it from the available list. 
            availableConnections.removeElement(newConnxn); 
            // Add it to the used list. 
            usedConnections.addElement(newConnxn);             
        }         
         
        // Either way, we should have a connection object now. 
        return newConnxn; 
    } 
     

    public synchronized void checkin(Connection c) 
    { 
        if(c != null) 
        { 
            // Remove from used list. 
            usedConnections.removeElement(c); 
            // Add to the available list 
            availableConnections.addElement(c);         
        } 
    }             
     
    public int availableCount() 
    { 
        return availableConnections.size(); 
    } 
     
    public void run() 
    { 
        try 
        { 
            while(true) 
            { 
                synchronized(this) 
                { 
                    while(availableConnections.size() > initialConnectionCount) 
                    { 
                        // Clean up extra available connections. 
                        Connection c = (Connection)availableConnections.lastElement(); 
                        availableConnections.removeElement(c); 
                         
                        // Close the connection to the database. 
                        c.close(); 
                    } 
                     
                    // Clean up is done 
                } 
                 
                System.out.println("CLEANUP : Available Connections : " + availableCount()); 
                 
                // Now sleep for 1 minute 
                Thread.sleep(60000 * 1); 
            }     
        } 
        catch(SQLException sqle) 
        { 
            sqle.printStackTrace(); 
        } 
        catch(Exception e) 
        { 
            e.printStackTrace(); 
        } 
    } 
}

import java.util.*; 
import java.sql.*; 

public class Main 
{ 
    public static void main (String[] args) 
    {     
        try  
        { 
            Class.forName("com.mysql.jdbc.Driver").newInstance();                          
        } 
        catch (Exception E)  
        { 
            System.err.println("Exception while loading driver"); 
            E.printStackTrace(); 
        } 
         
        try 
        {         
            ConnectionPool cp = new ConnectionPool("jdbc:mysql://localhost:3306/test","root","sam"); 
         
            Connection []connArr = new Connection[7]; 
         
            for(int i=0; i<connArr.length;i++) 
            { 
                connArr[i] = cp.checkout(); 
                System.out.println("Checking out..." + connArr[i]); 
                System.out.println("Available Connections ... " + cp.availableCount()); 
            }                 

            for(int i=0; i<connArr.length;i++) 
            { 
                cp.checkin(connArr[i]); 
                System.out.println("Checked in..." + connArr[i]); 
                System.out.println("Available Connections ... " + cp.availableCount()); 
            } 
        } 
        catch(SQLException sqle) 
        { 
            sqle.printStackTrace(); 
        } 
        catch(Exception e) 
        { 
            e.printStackTrace(); 
        }         
    } 
}

[ Read More ]

JDBC 4.0 Enhancements in Java 6

Java developers no longer need to explicitly load JDBC drivers using code like Class.forName() to register a JDBC driver. The DriverManager class takes care of this by automatically locating a suitable driver when the DriverManager.getConnection() method is called. This feature is backward-compatible, so no changes are needed to the existing JDBC code.

JDBC 4.0 also provides an improved developer experience by minimizing the boiler-plate code we need to write in Java applications that access relational databases. It also provides utility classes to improve the JDBC driver registration and unload mechanisms as well as managing data sources and connection objects.

With JDBC 4.0, Java developers can now specify SQL queries using Annotations, taking the advantage of metadata support available with the release of Java SE 5.0 (Tiger). Annotation-based SQL queries allow us to specify the SQL query string right within the Java code using an Annotation keyword. This way we don't have to look in two different files for JDBC code and the database query it's calling. For example, if you have a method called getActiveLoans() to get a list of the active loans in a loan processing database, you can decorate it with a @Query(sql="SELECT * FROM LoanApplicationDetails WHERE LoanStatus = 'A'") annotation.

Also, the final version of the Java SE 6 development kit (JDK 6)--as opposed to the runtime environment (JRE 6)--will have a database based on Apache Derby bundled with it. This will help developers explore the new JDBC features without having to download, install, and configure a database product separately.

The major features added in JDBC 4.0 include:

1.    Auto-loading of JDBC driver class

2.    Connection management enhancements

3.    Support for RowId SQL type

4.    DataSet implementation of SQL using Annotations

5.    SQL exception handling enhancements

6.    SQL XML support

There are also other features such as improved support for large objects (BLOB/CLOB) and National Character Set Support. These features are examined in detail in the following section.

Auto-Loading of JDBC Driver

In JDBC 4.0, we no longer need to explicitly load JDBC drivers using Class.forName(). When the method getConnection is called, the DriverManager will attempt to locate a suitable driver from among the JDBC drivers that were loaded at initialization and those loaded explicitly using the same class loader as the current application.

The DriverManager methods getConnection and getDrivers have been enhanced to support the Java SE Service Provider mechanism (SPM). According to SPM, a service is defined as a well-known set of interfaces and abstract classes, and a service provider is a specific implementation of a service. It also specifies that the service provider configuration files are stored in the META-INF/services directory. JDBC 4.0 drivers must include the file META-INF/services/java.sql.Driver. This file contains the name of the JDBC driver's implementation of java.sql.Driver. For example, to load the JDBC driver to connect to a Apache Derby database, the META-INF/services/java.sql.Driver file would contain the following entry:

org.apache.derby.jdbc.EmbeddedDriver

Let's take a quick look at how we can use this new feature to load a JDBC driver manager. The following listing shows the sample code that we typically use to load the JDBC driver. Let's assume that we need to connect to an Apache Derby database, since we will be using this in the sample application explained later in the article:

    Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

    Connection conn =

        DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);

But in JDBC 4.0, we don't need the Class.forName() line. We can simply call getConnection() to get the database connection.

Note that this is for getting a database connection in stand-alone mode. If you are using some type of database connection pool to manage connections, then the code would be different.

Connection Management

Prior to JDBC 4.0, we relied on the JDBC URL to define a data source connection. Now with JDBC 4.0, we can get a connection to any data source by simply supplying a set of parameters (such as host name and port number) to a standard connection factory mechanism. New methods were added to Connection and Statement interfaces to permit improved connection state tracking and greater flexibility when managing Statement objects in pool environments. The metadata facility (JSR-175) is used to manage the active connections. We can also get metadata information, such as the state of active connections, and can specify a connection as standard (Connection, in the case of stand-alone applications), pooled (PooledConnection), or even as a distributed connection (XAConnection) for XA transactions. Note that we don't use the XAConnection interface directly. It's used by the transaction manager inside a Java EE application server such as WebLogic, WebSphere, or JBoss.

RowId Support

The RowID interface was added to JDBC 4.0 to support the ROWID data type which is supported by databases such as Oracle and DB2. RowId is useful in cases where there are multiple records that don't have a unique identifier column and you need to store the query output in a Collection (such Hashtable) that doesn't allow duplicates. We can use ResultSet's getRowId() method to get a RowId and PreparedStatement's setRowId() method to use the RowId in a query.

An important thing to remember about the RowId object is that its value is not portable between data sources and should be considered as specific to the data source when using the set or update methods in PreparedStatement and ResultSet respectively. So, it shouldn't be shared between different Connection and ResultSet objects.

The method getRowIdLifetime() in DatabaseMetaData can be used to determine the lifetime validity of the RowId object. The return value or row id can have one of the values listed in below table

RowId Value	Description
ROWID_UNSUPPORTED	Doesn't support ROWID data type.
ROWID_VALID_OTHER	Lifetime of the RowID is dependent on database vendor implementation.
ROWID_VALID_TRANSACTION	Lifetime of the RowID is within the current transaction as long as the row in the database table is not deleted.
ROWID_VALID_SESSION	Lifetime of the RowID is the duration of the current session as long as the row in the database table is not deleted.
ROWID_VALID_FOREVER	Lifetime of the RowID is unlimited as long as the row in the database table is not deleted.

Annotation-Based SQL Queries

The JDBC 4.0 specification leverages annotations (added in Java SE 5) to allow developers to associate a SQL query with a Java class without writing a lot of code to achieve this association. Also, by using the Generics (JSR 014) and metadata (JSR 175) APIs, we can associate the SQL queries with Java objects specifying query input and output parameters. We can also bind the query results to Java classes to speed the processing of query output. We don't need to write all the code we usually write to populate the query result into a Java object. There are two main annotations when specifying SQL queries in Java code: Select and Update.

Select Annotation

The Select annotation is used to specify a select query in a Java class for the get method to retrieve data from a database table. Table 2 shows various attributes of the Select annotation and their uses.

Name	Type	Description
sql	String	SQL Select query string.
value	String	Same as sql attribute.
tableName	String	Name of the database table against which the sql will be invoked.
readOnly, connected, scrollable	Boolean	Flags used to indicate if the returned DataSet is read-only or updateable, is connected to the back-end database, and is scrollable when used in connected mode respectively.
allColumnsMapped	Boolean	Flag to indicate if the column names in the sql annotation element are mapped 1-to-1 with the fields in the DataSet.

Here's an example of Select annotation to get all the active loans from the loan database:

interface LoanAppDetailsQuery extends BaseQuery {

        @Select("SELECT * FROM LoanDetais where LoanStatus = 'A'")

        DataSet<LoanApplication> getAllActiveLoans();

}

The sql annotation allows I/O parameters as well (a parameter marker is represented with a question mark followed by an integer). Here's an example of a parameterized sql query.

interface LoanAppDetailsQuery extends BaseQuery {

        @Select(sql="SELECT * from LoanDetails

                where borrowerFirstName= ?1 and borrowerLastName= ?2")

        DataSet<LoanApplication> getLoanDetailsByBorrowerName(String borrFirstName,

                String borrLastName);

}

  Update Annotation

The Update annotation is used to decorate a Query interface method to update one or more records in a database table. An Update annotation must include a sql annotation type element. Here's an example of Update annotation:

interface LoanAppDetailsQuery extends BaseQuery {

        @Update(sql="update LoanDetails set LoanStatus = ?1

                where loanId = ?2")

        boolean updateLoanStatus(String loanStatus, int loanId);

}

SQL Exception Handling Enhancements

Exception handling is an important part of Java programming, especially when connecting to or running a query against a back-end relational database. SQLException is the class that we have been using to indicate database related errors. JDBC 4.0 has several enhancements in SQLException handling. The following are some of the enhancements made in JDBC 4.0 release to provide a better developer's experience when dealing with SQLExceptions:

1. New SQLException sub-classes
2. Support for causal relationships
3. Support for enhanced for-each loop

  New SQLException classes

The new subclasses of SQLException were created to provide a means for Java programmers to write more portable error-handling code. There are two new categories of SQLException introduced in JDBC 4.0:

• SQL non-transient exception
• SQL transient exception

Non-Transient Exception: This exception is thrown when a retry of the same JDBC operation would fail unless the cause of the SQLException is corrected. Table 3 shows the new exception classes that are added in JDBC 4.0 as subclasses of SQLNonTransientException (SQLState class values are defined in SQL 2003 specification.):

Exception class	SQLState value
SQLFeatureNotSupportedException	0A
SQLNonTransientConnectionException	08
SQLDataException	22
SQLIntegrityConstraintViolationException	23
SQLInvalidAuthorizationException	28
SQLSyntaxErrorException	42

Transient Exception: This exception is thrown when a previously failed JDBC operation might be able to succeed when the operation is retried without any intervention by application-level functionality. The new exceptions extending SQLTransientException are listed in Table 4.

Exception class	SQLState value
SQLTransientConnectionException	08
SQLTransactionRollbackException	40
SQLTimeoutException	None

 Causal Relationships

The SQLException class now supports the Java SE chained exception mechanism (also known as the Cause facility), which gives us the ability to handle multiple SQLExceptions (if the back-end database supports a multiple exceptions feature) thrown in a JDBC operation. This scenario occurs when executing a statement that may throw more than one SQLException .

We can use getNextException() method in SQLException to iterate through the exception chain. Here's some sample code to process SQLException causal relationships:

catch(SQLException ex) {

     while(ex != null) {

        LOG.error("SQL State:" + ex.getSQLState());

        LOG.error("Error Code:" + ex.getErrorCode());

        LOG.error("Message:" + ex.getMessage());

        Throwable t = ex.getCause();

        while(t != null) {

            LOG.error("Cause:" + t);

            t = t.getCause();

        }

        ex = ex.getNextException();

    }

}

Enhanced For-Each Loop

The SQLException class implements the Iterable interface, providing support for the for-each loop feature added in Java SE 5. The navigation of the loop will walk through SQLException and its cause. Here's a code snippet showing the enhanced for-each loop feature added in SQLException.

catch(SQLException ex) {

     for(Throwable e : ex ) {

        LOG.error("Error occurred: " + e);

     }

}

Support for National Character Set Conversion

Following is the list of new enhancements made in JDBC classes when handling the National Character Set:

1. JDBC data types: New JDBC data types, such as NCHAR, NVARCHAR, LONGNVARCHAR, and NCLOB were added.
2. PreparedStatement: New methods setNString, setNCharacterStream, and setNClob were added.
3. CallableStatement: New methods getNClob, getNString, and getNCharacterStream were added.
4. ResultSet: New methods updateNClob, updateNString, and updateNCharacterStream were added to ResultSet interface.

  Enhanced Support for Large Objects (BLOBs and CLOBs)

The following is the list of enhancements made in JDBC 4.0 for handling the LOBs:

1. Connection: New methods (createBlob(), createClob(), and createNClob()) were added to create new instances of BLOB, CLOB, and NCLOB objects.
2. PreparedStatement: New methods setBlob(), setClob(), and setNClob() were added to insert a BLOB object using an InputStream object, and to insert CLOB and NCLOB objects using a Reader object.
3. LOBs: There is a new method (free()) added in Blob, Clob, and NClob interfaces to release the resources that these objects hold.

Now, let's look at some of the new classes added to the java.sql and javax.jdbc packages and what services they provide.

JDBC 4.0 API: New Classes

RowId (java.sql)

As described earlier, this interface is a representation of an SQL ROWID value in the database. ROWID is a built-in SQL data type that is used to identify a specific data row in a database table. ROWID is often used in queries that return rows from a table where the output rows don't have an unique ID column.

Methods in CallableStatement, PreparedStatement, and ResultSet interfaces such as getRowId and setRowId allow a programmer to access a SQL ROWID value. The RowId interface also provides a method (called getBytes()) to return the value of ROWID as a byte array. DatabaseMetaData interface has a new method called getRowIdLifetime that can be used to determine the lifetime of a RowId object. A RowId's scope can be one of three types:

1. Duration of the database transaction in which the RowId was created
2. Duration of the session in which the RowId was created
3. The identified row in the database table, as long as it is not deleted

DataSet (java.sql)

The DataSet interface provides a type-safe view of the data returned from executing of a SQL Query. DataSet can operate in a connected or disconnected mode. It is similar to ResultSet in its functionality when used in connected mode. A DataSet, in a disconnected mode, functions similar to a CachedRowSet. Since DataSet extends List interface, we can iterate through the rows returned from a query.

There are also several new methods added in the existing classes such as Connection (createSQLXML, isValid) and ResultSet (getRowId).

[ Read More ]