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.
JDBC API is a Java API that can access any kind of tabular data, especially
data stored in a Relational Database. JDBC works with Java on a variety of
platforms, such as Windows, Mac OS, and the various versions of UNIX.

JDBC Driver
1. JDBC Drivers
1.

JDBC-ODBC bridge driver

2.

Native-API driver

3.

Network Protocol driver

4.

Thin driver

JDBC Driver is a software component that enables java application to interact with the
database.There are 4 types of JDBC drivers:
1. JDBC-ODBC bridge driver
2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)

1) JDBC-ODBC bridge driver

The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBCODBC bridge driver converts JDBC method calls into the ODBC function calls. This is now

discouraged because of thin driver.

Advantages:

easy to use.

can be easily connected to any database.

Disadvantages:

Performance degraded because JDBC method call is converted into the ODBC
function calls.

The ODBC driver needs to be installed on the client machine.

2) Native-API 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:

performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:

The Native driver needs to be installed on the each client machine.

The Vendor client library needs to be installed on client machine.

3) Network Protocol driver

The Network Protocol driver uses middleware (application server) that converts JDBC calls
directly or indirectly into the vendor-specific database protocol. It is fully written in java.

Advantage:

No client side library is required because of application server that can perform many
tasks like auditing, load balancing, logging etc.

Disadvantages:

Network support is required on client machine.

Requires database-specific coding to be done in the middle tier.

Maintenance of Network Protocol driver becomes costly because it requires databasespecific coding to be done in the middle tier.

4) 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 depends on the Database.

JDBC Architecture
The JDBC API supports both two-tier and three-tier processing models for database
access but in general, 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.

Following is the architectural diagram, which shows the location of the driver
manager with respect to the JDBC drivers and the Java application.
Common JDBC Components
The JDBC API provides the following interfaces and classes
DriverManager: This class manages a list of database drivers. Matches connection
requests from the java application with the proper database driver using
communication sub protocol. The first driver that recognizes a certain subprotocol
under JDBC will be used to establish a database Connection.
Driver: This interface handles the communications with the database server. You
will interact directly with Driver objects very rarely. Instead, you use DriverManager
objects, which manages objects of this type. It also abstracts the details associated
with working with Driver objects.
Connection: This interface with all methods for contacting a database. The
connection object represents communication context, i.e., all communication with
database is through connection object only.

Statement: You use objects created from this interface to submit the SQL
statements to the database. Some derived interfaces accept parameters in addition
to executing stored procedures.
ResultSet: These objects hold data retrieved from a database after you execute an
SQL query using Statement objects. It acts as an iterator to allow you to move
through its data.
SQLException: This class handles any errors that occur in a database application

JDBC & Statement

The Statement interface is used to execute a simple SQL statement with no
parameters. For create, insert, update or delete statement, uses
Statement.executeUpdate(sql);
select query, uses Statement.executeQuery(sql)
Heres an example to show you how to insert a record into table via JDBC statement.
To issue a insert statement, calls the Statement.executeUpdate() method like this :
Java
Statement statement = dbConnection.createStatement();
// execute the insert SQL stetement
statement.executeUpdate(insertTableSQL);

5 Steps to connect to the database in java

1.

Register the driver class

2.

Create the connection object

3.

Create the Statement object

4.

Execute the query

5.

Close the connection object

There are 5 steps to connect any java application with the database in java using JDBC.
They are as follows:

Register the driver class

Creating connection

Creating statement

Executing queries

Closing 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.

Syntax of forName() method

1.

public static void forName(String className)throws ClassNotFoundException

Example to register the OracleDriver class

1.

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.

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException
2) public static Connection getConnection(String url,String name,String password)
throws SQLException

Example to establish connection with the Oracle database

1.

Connection con=DriverManager.getConnection(

2.

"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.

Syntax of createStatement() method

1.

public Statement createStatement()throws SQLException

Example to create the statement object

1.

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.

Syntax of executeQuery() method

1.

public ResultSet executeQuery(String sql)throws SQLException

Example to execute query

1.
2.
3.
4.
5.

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.

Syntax of close() method

1.

public void close()throws SQLException

Example to close connection

1.

con.close();

Example to connect to the mysql database

For connecting java application with the mysql database, you need to follow 5 steps to
perform database connectivity.
In this example we are using MySql as the database. So we need to know following
informations for the mysql database:
1. Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver.
2. Connection URL: The connection URL for the mysql database
is jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the
database, localhost is the server name on which mysql is running, we may also use
IP address, 3306 is the port number and sonoo is the database name. We may use
any database, in such case, you need to replace the sonoo with your database name.
3. Username: The default username for the mysql database is root.
4. Password: Password is given by the user at the time of installing the mysql
database. In this example, we are going to use root as the password.
Let's first create a table in the mysql database, but before creating table, we need to create
database first.

1.
2.
3.

create database sonoo;

use sonoo;
create table emp(id int(10),name varchar(40),age int(3));

Example to Connect Java Application with mysql

database
In this example, sonoo is the database name, root is the username and password.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

import java.sql.*;
class MysqlCon{
public static void main(String args[]){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/sonoo","root","root");
//here sonoo is database name, root is username and password
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
con.close();
}catch(Exception e){ System.out.println(e);}
}
}

To connect java application with the mysql database mysqlconnector.jar file is required to be
loaded.

Two ways to load the jar file:

1. paste the mysqlconnector.jar file in jre/lib/ext folder

2. set classpath

1) paste the mysqlconnector.jar file in JRE/lib/ext folder:

Download the mysqlconnector.jar file. Go to jre/lib/ext folder and paste the jar file here.

2) set classpath:
There are two ways to set the classpath:
temporary

permament

How to set the temporary classpath

1.

open comman prompt and write:

C:>set classpath=c:\folder\mysql-connector-java-5.0.8-bin.jar;.;

How to set the permanent classpath

Go to environment variable then click on new tab. In variable name write classpath and in
variable value paste the path to the mysqlconnector.jar file by appending
mysqlconnector.jar;.; as C:\folder\mysql-connector-java-5.0.8-bin.jar;.;

DriverManager class:
The DriverManager class acts as an interface between user and drivers. It keeps track of the
drivers that are available and handles establishing a connection between a database and the
appropriate driver. The DriverManager class maintains a list of Driver classes that have
registered themselves by calling the method DriverManager.registerDriver().

Commonly used methods of DriverManager class:

1) public static void registerDriver(Driver

is used to register the given driver with

driver):
2) public static void deregisterDriver(Driver
driver):

DriverManager.
is used to deregister the given driver
(drop the driver from the list) with
DriverManager.

3) public static Connection

is used to establish the connection with

getConnection(String url):

the specified url.

4) public static Connection

is used to establish the connection with

getConnection(String url,String

the specified url, username and

userName,String password):

password.

Connection interface:
A Connection is the session between java application and database. The Connection
interface is a factory of Statement, PreparedStatement, and DatabaseMetaData i.e. object
of Connection can be used to get the object of Statement and DatabaseMetaData. The
Connection interface provide many methods for transaction management like
commit(),rollback() etc.
By default, connection commits the changes after executing queries.

Commonly used methods of Connection interface:

1) public Statement createStatement(): creates a statement object that can be used
to execute SQL queries.
2) public Statement createStatement(int resultSetType,int
resultSetConcurrency): Creates a Statement object that will generate ResultSet
objects with the given type and concurrency.
3) public void setAutoCommit(boolean status): is used to set the commit status.By
default it is true.
4) public void commit(): saves the changes made since the previous commit/rollback

permanent.
5) public void rollback(): Drops all changes made since the previous commit/rollback.
6) public void close(): closes the connection and Releases a JDBC resources
immediately.

Statement interface
The Statement interface provides methods to execute queries with the database. The
statement interface is a factory of ResultSet i.e. it provides factory method to get the object
of ResultSet.

Commonly used methods of Statement interface:

The important methods of Statement interface are as follows:
1) public ResultSet executeQuery(String sql): is used to execute SELECT query. It
returns the object of ResultSet.
2) public int executeUpdate(String sql): is used to execute specified query, it may
be create, drop, insert, update, delete etc.
3) public boolean execute(String sql): is used to execute queries that may return
multiple results.
4) public int[] executeBatch(): is used to execute batch of commands.

Example of Statement interface

Lets see the simple example of Statement interface to insert, update and delete the record.
1.
2.
3.
4.
5.
6.

import java.sql.*;
class FetchRecord{
public static void main(String args[])throws Exception{

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe"
,"system","oracle");
7.
Statement stmt=con.createStatement();
8.
9.
//stmt.executeUpdate("insert into emp765 values(33,'Irfan',50000)");
10.
//int result=stmt.executeUpdate("update emp765 set name='Vimal',salary=10000 w
here id=33");
11.
int result=stmt.executeUpdate("delete from emp765 where id=33");
12.
13.
System.out.println(result+" records affected");
14.
con.close();
15.
}}

PreparedStatement interface
The PreparedStatement interface is a subinterface of Statement. It is used to execute
parameterized query.
Let's see the example of parameterized query:
1.

String sql="insert into emp values(?,?,?)";

As you can see, we are passing parameter (?) for the values. Its value will be set by calling
the setter methods of PreparedStatement.

Why use PreparedStatement?

Improves performance: The performance of the application will be faster if you use
PreparedStatement interface because query is compiled only once.

How to get the instance of PreparedStatement?

The prepareStatement() method of Connection interface is used to return the object of
PreparedStatement. Syntax:
1.

public PreparedStatement prepareStatement(String query)throws SQLException{}

Methods of PreparedStatement interface

Method

Description

public void setInt(int paramIndex, int value)

sets the integer value to the given param

public void setString(int paramIndex, String value)

sets the String value to the given parame

public void setFloat(int paramIndex, float value)

sets the float value to the given paramet

public void setDouble(int paramIndex, double value)

sets the double value to the given param

public int executeUpdate()

executes the query. It is used for create,

public ResultSet executeQuery()

executes the select query. It returns an i

The important methods of PreparedStatement interface are given below:

Example of PreparedStatement interface that inserts the record

First of all create table as given below:
1.

create table emp(id number(10),name varchar2(50));

Now insert records in this table by the code given below:

1.
2.
3.
4.
5.
6.

import java.sql.*;
class InsertPrepared{
public static void main(String args[]){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");

7.

Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe"
,"system","oracle");

8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");

stmt.setInt(1,101);//1 specifies the first parameter in the query
stmt.setString(2,"Ratan");
int i=stmt.executeUpdate();
System.out.println(i+" records inserted");
con.close();
}catch(Exception e){ System.out.println(e);}
}
}

Example of PreparedStatement interface that updates the record

1.
2.
3.
4.
5.
6.

PreparedStatement stmt=con.prepareStatement("update emp set name=? where id

=?");

stmt.setString(1,"Sonoo");//1 specifies the first parameter in the query i.e. name

stmt.setInt(2,101);
int i=stmt.executeUpdate();
System.out.println(i+" records updated");

Example of PreparedStatement interface that deletes the record

1.
2.
3.
4.
5.

PreparedStatement stmt=con.prepareStatement("delete from emp where id=?");

stmt.setInt(1,101);
int i=stmt.executeUpdate();
System.out.println(i+" records deleted");

Example of PreparedStatement interface that retrieve the records

of a table
1.
2.
3.
4.
5.

PreparedStatement stmt=con.prepareStatement("select * from emp");

ResultSet rs=stmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

Example of PreparedStatement to insert records until user press n

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.

import java.sql.*;
import java.io.*;
class RS{
public static void main(String args[])throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe"
,"system","oracle");
PreparedStatement ps=con.prepareStatement("insert into emp130 values(?,?,?)");
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
do{
System.out.println("enter id:");
int id=Integer.parseInt(br.readLine());
System.out.println("enter name:");
String name=br.readLine();
System.out.println("enter salary:");
float salary=Float.parseFloat(br.readLine());
ps.setInt(1,id);
ps.setString(2,name);
ps.setFloat(3,salary);
int i=ps.executeUpdate();
System.out.println(i+" records affected");
System.out.println("Do you want to continue: y/n");
String s=br.readLine();
if(s.startsWith("n")){
break;
}
}while(true);
con.close();
}}