Chapter 6

VI Interacting With Database

• 6.1.Introduction to JDBC ,ODBC
• 6.2.JDBC architecture:Two tier and three tier
models
• 6.3.Types of JDBC drivers Class,DriverManager
class,Connection interface,Statement
interface,PreparedStatement
interface,ResultSet Interface.
 Java Database Connectivity (JDBC)

• Java Database Connectivity (JDBC) is an application

programming interface (API) for the programming language
Java, which defines how a client may access any kind of
tabular data, especially relational database. It is part of Java
Standard Edition platform, from Oracle Corporation. It acts as
a middle layer interface between java applications and
database.
• The JDBC classes are contained in the Java
• Package java.sql and javax.sql.
JDBC helps you to write Java applications.
 ODBC
• Open Database Connectivity (ODBC) is a standard that
allows applications to access data from various databases.
• ODBC acts as a translator between an application and a
data store.
• The application makes a simple standard call to the ODBC
engine, which changes that to a form the database can
handle.
• ODBC can save a lot of time and effort because the
program doesn't need to know the details of each
database, and the database doesn't need to be configured
for every application that wants to access it.
JDBC Architecture

Two-Tier Model

In the two-tier model, a Java applet or application talks directly to the data
source. This requires a JDBC driver that can communicate with the particular
data source being accessed. A user's commands are delivered to the database or
other data source, and the results of those statements are sent back to the user.
The data source may be located on another machine to which the user is
connected via a network. This is referred to as a client/server configuration, with
the user's machine as the client, and the machine housing the data source as the
server. The network can be an intranet, which, for example, connects employees
within a corporation, or it can be the Internet.
 Three-tier Architecture
Until recently, the middle tier has often been written in languages such as
C or C++, which offer fast performance. However, with the introduction
of optimizing compilers that translate Java bytecode into efficient
machine-specific code and technologies such as Enterprise JavaBeans™,
the Java platform is fast becoming the standard platform for middle-tier
development. This is a big plus, making it possible to take advantage of
Java's robustness, multithreading, and security features.
What is JDBC Driver?

JDBC drivers implement the defined interfaces in the JDBC API,

for interacting with your database server.
For example, using JDBC drivers enable you to open database
connections and to interact with it by sending SQL or database
commands then receiving results with Java.
The Java.sql package that ships with JDK, contains various
classes with their behaviours defined and their actual
implementaions are done in third-party drivers. Third party
vendors implements the java.sql.Driver interface in their database
driver.
 JDBC Drivers
JDBC drivers are client-side adapters (installed on the client
machine, not on the server) that convert requests from Java
programs to a protocol that the DBMS can understand.

There are 4 types of JDBC drivers:

1.Type-1 driver or JDBC-ODBC bridge driver

2.Type-2 driver or Native-API driver
3.Type-3 driver or Network Protocol driver
4.Type-4 driver or Thin driver
 Type-1 driver or JDBC-ODBC
bridge driver

Type-1 driver or JDBC-ODBC bridge

driver uses ODBC driver to connect to the
database. The JDBC-ODBC bridge driver converts
JDBC method calls into the ODBC function calls.
Type-1 driver is also called Universal driver
because it can be used to connect to any of the
databases.

• As a common driver is used in order to interact

with different databases, the data transferred
through this driver is not so secured.
• The ODBC bridge driver is needed to be
installed in individual client machines.
• Type-1 driver isn’t written in java, that’s why it
isn’t a portable driver.
• This driver software is built-in with JDK so no
need to install separately.
• It is a database independent driver.
Type-2 driverThe Native API
driver
The Native API driver uses the client -side
libraries of the database. This driver converts
JDBC method calls into native calls of the
database API. In order to interact with different
database, this driver needs their local API, that’s
why data transfer is much more secure as
compared to type-1 driver.

• Driver needs to be installed separately in

individual client machines
• The Vendor client library needs to be
installed on client machine.
• Type-2 driver isn’t written in java, that’s
why it isn’t a portable driver
• It is a database dependent driver.
Type-3 driver The Network
Protocol driver
The Network Protocol driver uses middleware
(application server) that converts JDBC calls directly
or indirectly into the vendor-specific database protocol.
Here all the database connectivity drivers are present in
a single server, hence no need of individual client-side
installation.

• Type-3 drivers are fully written in Java, hence they

are portable drivers.
• No client side library is required because of
application server that can perform many tasks like
auditing, load balancing, logging etc.
• Network support is required on client machine.
• Maintenance of Network Protocol driver becomes
costly because it requires database-specific coding
to be done in the middle tier.
• Switch facility to switch over from one database to
another database.
Type-4 driver

Type-4 driver is also called native protocol

driver. This driver interact directly with
database. It does not require any native
database library, that is why it is also
known as Thin Driver.
• Does not require any native library and
Middleware server, so no client-side or
server-side installation.
• It is fully written in Java language,
hence they are portable drivers.
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().

Method Description
1) public static void is used to register the given driver with
registerDriver(Driver driver): DriverManager.
2) public static void is used to deregister the given driver
deregisterDriver(Driver 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.

1. public Statement createStatement(): creates a statement object

that can be used to execute SQL queries.
2. public void setAutoCommit(boolean status): is used to set the
commit status. By default it is true.
3. public void commit(): saves the changes made since the
previous commit/rollback permanent.
4. public void rollback(): Drops all changes made since the
previous commit/rollback.
5. 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:

• public ResultSet executeQuery(String sql): is used to execute

SELECT query. It returns the object of ResultSet.
• public int executeUpdate(String sql): is used to execute specified
query, it may be create, drop, insert, update, delete etc.
• public boolean execute(String sql): is used to execute queries that
may return multiple results.
• public int[] executeBatch(): is used to execute batch of commands.
 PreparedStatement interface
• The PreparedStatement interface is a subinterface of Statement.
It is used to execute parameterized query
Method Description
public void setInt(int paramIndex, sets the integer value to the given
int value) parameter index.
public void setString(int sets the String value to the given
paramIndex, String value) parameter index.
public void setFloat(int sets the float value to the given
paramIndex, float value) parameter index.
public void setDouble(int sets the double value to the given
paramIndex, double value) parameter index.
public int executeUpdate() executes the query. It is used for
create, drop, insert, update, delete etc.
public ResultSet executeQuery() executes the select query. It returns an
instance of ResultSet.
ResultSet interface
The object of ResultSet maintains a cursor pointing to a row of a
table. Initially, cursor points to before the first row.
1) public boolean next(): is used to move the cursor to the one row next from
the current position.
2) public boolean previous(): is used to move the cursor to the one row previous
from the current position.
3) public boolean first(): is used to move the cursor to the first row in result
set object.
4) public boolean last(): is used to move the cursor to the last row in result
set object.
7) public int getInt(int is used to return the data of specified column index
columnIndex): of the current row as int.
8) public int getInt(String is used to return the data of specified column name
columnName): of the current row as int.
9) public String getString(int is used to return the data of specified column index
columnIndex): of the current row as String.
10) public String getString(String is used to return the data of specified column name
columnName): of the current row as String.