Nov 4, 2014

Internals of database connectivity in java

Java Database Connection

Database connection in java is very important process in JDBC. This post is for demonstrating internals of java database connectivity. In this post we will get to know How to connect with database in java, Java database connectivity, internals of database connectivity in java. I will also give a sample example of java database connectivity.
For database connection we need to import some packages and need to use some special methods. Before getting into deep I will explain about these methods and packages. Following is details of how to connect with database.

Step by step explanation of Java database connection-

1. Import Package-  For creating database connection you need some functions and classes. These functions and classes are found in a package so we need to import that package using "import java.sql.*".

2. Loading Driver-  Driver is required for conversion of sql statements into back end database understandable form and vice versa.
           Class.forName("oracle.jdbc.driver.OracleDriver");
Class.forName is used to register the driver.

3. Create Connection- 
           DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");

4. Define SQL stmt in the form of String class's object

5. Send the SQL stmt

6. Excecute the query and get the result


Sample example of java database connectivity-

//STEP 1. Import "java.sql" package
import java.sql.*;

public class dbConnect
{
  // JDBC driver name and database URL
  static final String DRIVER = "com.mysql.jdbc.Driver";
  static final String URL = "jdbc:mysql://localhost/TEST";

 // Database credentials
 static final String USR  = "username";
 static final String PWD = "password";

 public static void main(String[] args) {
 Connection con = null;
 Statement stmtt = null;
 try
    {
     //STEP 2: Register JDBC driver
    Class.forName("com.mysql.jdbc.Driver");

    //STEP 3: Open a connection
    System.out.println("Connecting to database for I/O operation...");
    con = DriverManager.getConnection(URL,USR,PWD);

   //STEP 4: Execute a query
   System.out.println("Statement Creation for I/O operation...");
   stmtt = con.createStatement();
   String sqlstmt;
   sqlstmt = "SELECT id, first, last, age FROM Emp";
   ResultSet rs = stmt.executeQuery(sql);

   //STEP 5: Extracting the data from result set for next operation
   while(rs.next())
       {
        //Retrieve by column name
        int id1  = rs.getInt("id");
        int age2 = rs.getInt("age");
       String first1 = rs.getString("first");
       String last1 = rs.getString("last");

       //Display values
       System.out.print("ID: " + id1);
       System.out.print(", Age: " + age1);
       System.out.print(", First Name: " + first1);
      System.out.println(", Last Name: " + last1);
      }

     //STEP 6: Cleaning - up environment
     rs.close();
     stmtt.close();
     con.close(); 
    }
    catch(SQLException se)
   {
    //Handle errors for JDBC
    se.printStackTrace();
   }
  catch(Exception e)
  {
    //Handle errors for Class.forName  
    e.printStackTrace();
  }
  finally
  {
    //finally block is used for closing all  resources
   try
    {  
     if(stmt!=null)
     stmtt.close();
    }  
     catch(SQLException se2) 
    {
 
    }
   // nothing we can do
  try
  {
   if(con!=null)
   conn.close();
  }
 catch(SQLException se)
  {
    se.printStackTrace();
  }
  //end finally try
  }
  //end try
 System.out.println("bye!");
 }
}



OUTPUT-

It is very important to know, how to execute this program so now let's know it.
Type following command in command line.

C:\> javac dbConnect.java

After execution of this command you will get following result-


Connecting to database First Time...
Statement Creation...
ID: 101, Age:  25, First Name: Vikash, Last Name: Shukla

Id,Age,First Name,Last Name may be different as per records in database.