JDBC : Connection to databases

What is JDBC?

JDBC is an API that encapsulates the low−level calls needed for database access and interaction into one common interface. Both the Java Development Kit (JDK) and Java Runtime Environment (JRE) contain the API as part of the standard distribution. The API’s interfaces and classes reside in the java.sql and javax.sql packages. The standard components are packaged in java.sql while the enterprise elements are in javax.sql.

Steps for building a simple JDBC application

1. Import Packages

2. Register JDBC Driver

3. Open a Connection

4. Execute a Query

5. Extract data from Result Set

6. Clean-Up Environment

A Simple Program to demonstrate the JDBC application working:

This is a simple program that retrieves  and displays the values of particular columns from the Employees table stored in the database.

//STEP 1. Import packages

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Date;
import java.sql.SQLException;

public class FirstQuery {
   public static void main(String[] args) {
   Connection conn = null;     //Define Connection variable
   try{                        //Begin standard error handling

//STEP 2: Register JDBC driver

     String driver = "oracle.jdbc.driver.OracleDriver"; //for oracle database
     Class.forName(driver);
//STEP 3: Open a connection
     System.out.println("Connecting to database...");
     String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:XE";
/* this url will be different for the different databases */
     String user = "username"; 
     String password = "password";
     conn = DriverManager.getConnection(jdbcUrl,user,password);

//STEP 4: Execute a query

     Statement stmt = conn.createStatement();
     String sql;
     sql = "SELECT SSN, Name, Salary, Hiredate FROM Employees";
     ResultSet rs = stmt.executeQuery(sql);

//STEP 5: Extract data from result set

     while(rs.next()){
//Retrieve by column name

     int ssn= rs.getInt("ssn");
     String name = rs.getString("name");
//Retrieve by column index as an example

     double salary = rs.getDouble(3);
     Date date = rs.getDate(4);

//Display values

     System.out.print("SSN: " + ssn);
     System.out.print(", Name: " + name);
     System.out.print(", Salary: $" + salary);
     System.out.println(", HireDate: " + date);
   }

//STEP 6: Clean−up environment

  rs.close();
  stmt.close();
  conn.close();
 }catch(SQLException se){

//Handle errors for JDBC

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

//Handle errors for Class.forName

 e.printStackTrace();
 }finally{

//finally block used to close resources

 try{
       if(conn!=null)
           conn.close();

   }catch(SQLException se){
       se.printStackTrace();
   }
 }
 System.out.println("Finished!");
}      //end of main
}

The Expected Output after you have created the databse with some values of these columns:

Connecting to database...

SSN: 111111111, Name: Harish, Salary: $5000.55, HireDate: 2010−09−16

SSN: 419876541, Name: Vikas, Salary: $1500.75, HireDate: 2011−03−05

SSN: 312654987, Name: Aman, Salary: $2000.95, HireDate: 2012−01−11

SSN: 123456789, Name: Sumit, Salary: $3080.05, HireDate: 2009−09−07

SSN: 987654321, Name: John, Salary: $4351.27, HireDate: 2011−12−31

Finished!

The values that you will store in the databse will be shown.

This was just a simple demonstration program to just give some idea about the JDBC connectivity.
In the later posts, the more detailed and programs for entering the data in the databse, updating and creating the database using either Statement or preparedStatement objects.