JDBC : Connection to databases

12:11 AM 3 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 at Symantec in Chennai. Previously worked as a Senior Software Engineer at IGATE. Loves to explore new things, loves to code and solve challenging problems with the help of it. Like any other normal person, loves to watch movies, listen to songs (specially when travelling, it is must), getting to know people, travelling and whatever makes me feel good!