JDBC : Connection to databases

12:11 AM 1 Comments A+ a-

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
//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

//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

 }catch(SQLException se){

//Handle errors for JDBC

 }catch(Exception e){

//Handle errors for Class.forName


//finally block used to close resources


   }catch(SQLException se){
}      //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

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.

Currently working as a Senior Software Engineer at IGATE. Cricket Crazy, Likes Movies, Music (Hindi Songs) and Masti. Surfing the net for something informative and interesting is my second favorite time pass after online gaming. Would like to travel the whole world...


Write comments
March 24, 2013 at 6:29 PM delete

simply what was I searching for. Thanks


Please leave your valuable comments here...