JDBC
JDBC stands for Java Database Connectivity, which is a standard Java API
for database-independent connectivity between the Java programming
language and a wide range of databases.
The JDBC API consists of classes and methods that are used to perform
various operations like: connect, read, write and store data in the
database.
JDBC Architecture:
The JDBC API supports both two-tier and three-tier processing models for
database access. JDBC Architecture consists of two layers:
• JDBC API: This provides the application-to-JDBC Manager
connection.
• JDBC Driver API: This supports the JDBC Manager-to-Driver
Connection.
The JDBC API uses a driver manager and database-specific drivers to
provide transparent connectivity to heterogeneous databases.
The JDBC driver manager ensures that the correct driver is used to access
each data source. The driver manager is capable of supporting multiple
concurrent drivers connected to multiple heterogeneous databases.
Common JDBC Components:
The JDBC API provides the following interfaces and classes:
• DriverManager: This class manages a list of database drivers.
• Driver: This interface handles the communications with the
database server.
• Connection : This interface with all methods for contacting a
database.
• Statement : You use objects created from this interface to submit
the SQL statements to the database.
• ResultSet: These objects hold data retrieved from a database after
you execute an SQL query using Statement objects
• SQLException: This class handles any errors that occur in a
database application.
JDBC Driver
JDBC Driver is a software component that enables java application to
interact with the database. There are 4 types of JDBC drivers:
• Type-1 Driver or JDBC-ODBC bridge
• Type-2 Driver or Native API Partly Java Driver
• Type-3 Driver or Network Protocol Driver
• Type-4 Driver or Thin Driver
Type-1 Driver or JDBC-ODBC bridge
Type-1 Driver acts as a bridge between JDBC and other database
connectivity mechanism (ODBC). This driver converts JDBC calls into
ODBC calls and redirects the request to the ODBC driver.
Advantage
• Easy to use
• Allow easy connectivity to all database supported by the ODBC
Driver.
Disadvantage
• Slow execution time
• Dependent on ODBC Driver.
Type-2 Driver or Native API Partly Java Driver
The Native API driver uses the client-side libraries of the database. The
driver converts JDBC method calls into native calls of the database API. It
is not written entirely in java.
Advantage
• Faster as compared to Type-1 Driver
• Contains additional features.
Disadvantage
• Requires native library
• Increased cost of Application
Type-3 Driver or Network Protocol Driver
This driver translates the JDBC calls into a database server independent
and Middleware server-specific calls. Middleware server further translates
JDBC calls into database specific calls. It is fully written in java.
Advantage
• Does not require any native library to be installed.
• Database Independency.
• Provide facility to switch over from one database to another
database.
Disadvantage
• Slow due to increase number of network call.
Type-4 Driver or Thin Driver
The thin driver converts JDBC calls directly into the vendor-specific
database protocol. That is why it is known as thin driver. It is fully written
in Java language.
Advantage:
• Better performance than all other drivers.
• No software is required at client side or server side.
Disadvantage:
• Drivers depend on the Database.
Java Database Connectivity with 5 Steps
There are 5 steps to connect any java application with the database using
JDBC. These steps are as follows:
• Register the Driver class
• Create connection
• Create statement
• Execute queries
• Close connection
1) Register the driver class
The forName() method of Class class is used to register the driver class.
This method is used to dynamically load the driver class.
Example to register the OracleDriver class:
Here, Java program is loading oracle driver to esteblish database
connection.
Class.forName("oracle.jdbc.driver.OracleDriver");
2) Create the connection object
The getConnection() method of DriverManager class is used to establish
connection with the database.
Example to establish connection with the Oracle database:
Connection con=DriverManager.getConnection
( "jdbc:oracle:thin:@localhost:1521:xe", "system", "password");
3) Create the Statement object
The createStatement() method of Connection interface is used to create
statement. The object of statement is responsible to execute queries with
the database.
Example to create the statement object:
Statement stmt=con.createStatement();
4) Execute the query
The executeQuery() method of Statement interface is used to execute
queries to the database. This method returns the object of ResultSet that
can be used to get all the records of a table.
Example to execute query:
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
5) Close the connection object
By closing connection object statement and ResultSet will be closed
automatically. The close() method of Connection interface is used to
close the connection.
Example to close connection:
con.close();
Example to Connect Java Application with Oracle database
import java.sql.*;
class OracleCon
{
public static void main(String args[])
{
try
{
//step1 load the driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
//step2 create the connection object
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
//step3 create the statement object
Statement stmt=con.createStatement();
//step4 execute query
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getString(3));
//step5 close the connection object
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
In this example, we are connecting to an Oracle database and getting
data from emp table. Here, system and oracle are the username and
password of the Oracle database.
