Introduction to JDBC and Java Data Base Connectivity

Introduction

JDBC , Java Data Base Connectivity as the name is the set of API , which provides the set of interfaces and classes for connecting Java applications with database.In this lesson , we are going to learn about the JDBC API , because databases are ultimate storage resources for data on which programs does the operation and creates the information.Without persistence applications are just like a train without destination.

What is JDBC

• JDBC is a standard API for connecting Java with databases independently with almost all types of databases.Because all DBMS are uses O.S native API for creating and persist data in database , we need a bridge between DBMS and Java.This requirement is fulfilled by JDBC API with different type of drivers for different database technologies.It provided a standard API for executing SQL statements and supports basic SQL functionality.

Pre-requisites for JDBC

To Start with JDBC one must be conversant with Core-Java technologies for java programming and command over SQL for writing statements and queries with database.

System requirements

• Install  JDK  in your System
• Install any one DBMS like MySQL, Oracle, JavaDB e.t.c .
• Install or download JDBC driver provided by your DBMS vendor.(JavaDB comes with default driver and for Oracle (classes12.jar)  and  for Mysql ( Connector/J).Note :-  different types of JDBC drivers are avilable.
• Write your programs for connecting and using databse from your applications.

Architecture

As Java program makes a call to JDBC API, which loads the JDBC driver to interact with driver.There is no need to change the code logic and all you have to change database engine.Architecture of JDBC looks like as shown below.

Steps involved in JDBC

• Loading or registering the JDBC (database) driver .
• Creating a JDBC connection with Database.
• Creating a statement or query.
• Executing the query or statement and processing the results if needed.

Registering  or Loading JDBC Drivers

In this step, a JDBC driver registration will be done with the application.it will creates an instance of driver class.Through this driver only our programs connects with database.The following code illustrates the Driver registration process for oracle thin driver.

                try
  { 
  OracleDataSource ods = new OracleDataSource();                                   
                }

Opening or Creating a  connection

In this step, we create a connection with the database.It uses a username, password, and a jdbc url to establish a connection to the database and  connection object will be returned. A JDBC Connection represents a session with a specific database.

try
{

ods.setURL("jdbc:oracle:thin:"+username+"/"+password+"@127.0.0.1:1521:XE");

con=ods.getConnection();
 if
(con!=null) System.out.println("Connected successfully to oracle..............:");
 else 
 System.out.println("Unable to open Database connection....");
}
catch(SQLException e)
{
}

Creating and Executing a statement 

After connection establishment our next step is to interact with the database.It will be through SQL statements.Creating a statement involves in creating a statement  which can be sql command it is given below.After statement creation execution If it is a query which returns rows will returns a result set as shown below.If it is an update operation nothing will be returned.

 Statement stmt = con.createStatement(); 
 ResultSet rs = stmt.executeQuery("SELECT * FROM person"); 
 while (rs.next()) 
{ 
 int x = rs.getInt(1); 
 String s = rs.getString(2); 
 float f = rs.getFloat(3);
}

There are three types of statements ; Statement,PreparedStatement,CallableStatement.We will read about these three in-depth later.

Close Connection

Finally, but most important for optimization for speed  up of  database operations is closing connections.If connections are not closed, sessions are alive with database .

con.close();

Example program for a sample JDBC 
Why does the neighborhood buck?