JDBC

What is 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 library includes APIs for each of the tasks commonly associated with database
usage:
• Making a connection to a database
• Creating SQL or MySQL statements
• Executing that SQL or MySQL queries in the database
• Viewing & Modifying the resulting records
What is JDBC?
• Fundamentally, JDBC is a specification that provides a complete set of interfaces that
allows for portable access to an underlying database. Java can be used to write different
types of executables, such as:
• Java Applications
• Java Applets
• Java Servlets
• Java ServerPages (JSPs)
• Enterprise JavaBeans (EJBs)
• All of these different executables are able to use a JDBC driver to access a database and
take advantage of the stored data.
• JDBC provides the same capabilities as ODBC(Open Database Connectivity), allowing Java
programs to contain database-independent code.
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:
JDBC Architecture:

jdbc driver api

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 subprotocol.
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.
The JDBC 4.0 Packages:
• The [Link] and [Link] are the primary packages for JDBC 4.0. It offers the main classes for
interacting with your data sources.
• The new features in these packages include changes in the following areas:
• Automatic database driver loading
• Exception handling improvements
• Enhanced BLOB/CLOB functionality
• Connection and statement interface enhancements
• National character set support
• SQL ROWID access
• SQL 2003 XML data type support
• Annotations
JDBC Process
• Loading the JDBC driver
• Connecting to the DBMS
• Creating and executing a statement
• Processing data returned by the DBMS
• Terminating the connection with the DBMS
 • Loading the JDBC driver
• Connecting to the DBMS
• Creating and executing a statement
JDBC Process • Processing data returned by the DBMS
• Terminating the connection with the DBMS
JDBC Driver Types:
• 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.

• JDBC Drivers Types:

• JDBC driver implementations vary because of the wide variety of operating systems and hardware
platforms in which Java operates.
• It has divided the implementation types into four categories, Types 1, 2, 3, and 4.
Type 1: JDBC-ODBC Bridge Driver:

• The JDBC type 1 driver, also known as the

JDBC-ODBC bridge is a database driver
implementation that employs the ODBC ( Open
Data Base Connectivity) driver to connect to
the database.
• The driver converts JDBC method calls into
ODBC function calls. The bridge is usually used
when there is no pure-Java driver available for
a particular database.
• When Java first came out, this was a useful
driver because most databases only supported
ODBC access but now this type of driver is
recommended only for experimental use or
when no other alternative is available.

Use the ODBC Data Source Administrator to create the

association between the database and the JDBC/ODBC bridge
Functions:
• Translates query obtained by JDBC into corresponding ODBC query,which is then handled by the
ODBC driver.
• Sun provides a JDBC-ODBC Bridge driver.
• [Link]. This driver is native code and not Java, and is closed source.
• Client -> JDBC Driver -> ODBC Driver -> Database
• There is some overhead associated with the translation work to go from
JDBC to ODBC.

Advantages:
• Almost any database for which ODBC driver is installed, can be accessed.

Disadvantages:
• Performance overhead since the calls have to go through the JDBC overhead bridge to the ODBC
driver, then to the native database connectivity interface.
• The ODBC driver needs to be installed on the client machine.
• Considering the client-side software needed, this might not be suitable for applets.
 Type 2: JDBC-Native API:
• The JDBC type 2 driver, also known as the
Native-API driver is a database driver
implementation that uses the clientside
libraries of the side database. The driver
converts JDBC method calls into native calls of
the database API.
• These drivers typically provided by the
database vendors and used in the same
manner as the JDBC-ODBC Bridge, the vendor-
specific driver must be installed on each client
machine.
• If we change the Database we have to change
the native API as it is specific to a database and
they are mostly obsolete now but you may
realize some speed increase with a Type 2
driver, because it eliminates ODBC's overhead.
The JDBC Type 2 driver (Native-API driver) uses the database's own
client-side libraries to convert JDBC calls into the database's native calls,
so that it can communicate with the database in its own native language.
Functions:
• This type of driver converts JDBC calls into calls to the client API for that database.
Client -> JDBC Driver -> Vendor Client DB Library -> Database

Advantage:
• Better performance than Type 1 since no jdbc to odbc translation is needed.

Disadvantages
• The vendor client library needs to be installed on the client machine.
• Not all databases give the client side library.
 CHECK FLOW AFTER 2 SLIDES
Type 3: JDBC-Net pure Java:
• In a Type 3 driver, a three-tier approach is
used to accessing databases. T he JDBC clients
use standard network sockets to
communicate with an middleware application
server.
• T he socket information is then translated by
the middleware application server into the
call format required by the DBMS, and
forwarded to the database server.
• This kind of driver is extremely flexible, since
it requires no code installed on the client and
a sing le driver can actually provide access to
multiple databases.
1. **Three-tier approach**: In a Type 3 driver, there's an extra middle layer (called the middleware server) between the client and the database, unlike Type 2, where the client
connects directly to the database.
2. **Clients use network sockets**: The client (your computer) communicates with the middleware server over the network, using a common communication method called
sockets.
3. **Middleware translates to DB format**: The middleware server receives the client's requests, converts them into the specific format needed by the database, and sends them
to the database server.
4. **No client-side code**: The client doesn’t need any special database drivers installed, making it simpler to manage.
5. **Access to multiple databases**: A single Type 3 driver can connect to multiple databases, so you don’t need separate drivers for each one, making it very flexible.
Functions:
• Follows a three tier communication approach.
• Can interface to multiple databases - Not vendor specific.
• The JDBC Client driver written in java, communicates with middleware-net-server using a
database independent protocol, and then this net server translates this request into database
commands for that database.
• Thus the client driver to middleware communication is database independent.
• Client -> JDBC Driver -> Middleware-Net Server ->Database

Advantages:
• Since the communication between client and the middleware server is database independent,
there is no need for the vendor db library on the client machine. Also the client to middleware
need'nt be changed for a new database.
• The Middleware Server (Can be a full fledged J2EE Application server) can provide typical
middleware services like caching (connections, query results, and so on), load balancing, logging,
auditing etc..
 • eg. for the above include jdbc driver features in Weblogic.
• Can be used in internet since there is no client side software needed.
• At client side a single driver can handle any database.(It works provided the middlware supports
that database!!)

Disadvantages:
• Requires database-specific coding to be done in the middle tier.
• An extra layer added may result in a timebottleneck. But typically this is overcome by providing
efficient middleware services described above.

Client application: Your Java application uses JDBC to send database queries.

JDBC Type 3 Driver: The JDBC Type 3 driver (installed on the client) sends the requests over the network to a middleware server. This
driver doesn’t directly talk to the database.

Middleware server: The middleware server receives the request and translates it into a format the specific database can understand.

Database server: The middleware server forwards the translated request to the actual database server, which processes the query and
sends the response back to the middleware.

Response flow: The middleware server then sends the database's response back to the JDBC driver, which finally returns it to your client
application.
Type 4: 100% pure Java:
• In a T ype 4 driver, a pure Java-based driver
that communicates directly with vendor's
database through socket connection. This is
the highest performance driver available for
the database and is usually provided by the
vendor itself.
• This kind of driver is extremely flexible, you
don't need to install special software on the
client or server. Further, these drivers can be
downloaded dynamically.
• MySQL's Connector/J driver is a Type 4 driver.
Because of the proprietary nature of their
network protocols, database vendors usually
supply type 4 drivers.
Functions:
• Type 4 drivers are entirely written in Java that communicate directly with a vendor's database
through socket connections.
• No translation or middleware layers, are required, improving performance.
• The driver converts JDBC calls into the vendor-specific database protocol so that client applications
can communicate directly with the database server.
• Completely implemented in Java to achieve platform independence.
• e.g include the widely used Oracle thin driver -
• [Link]. OracleDriver which connect to jdbc:oracle:thin URL format.
• Client Machine -> Native protocol JDBC Driver -> Database
Advantages:
• These drivers don't translate the requests into db request to ODBC or pass it to client api for the db,
nor do they need a middleware layer for request indirection. Thus the performance is considerably
improved.

Disadvantage:
• At client side, a separate driver is needed for each database.
Which Driver should be used?
• If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver
type is 4.
• If your Java application is accessing multiple types of databases at the same time, type 3 is the
preferred driver.
• Type 2 drivers are useful in situations where a type 3 or type 4 driver is not available yet for your
database.
• The type 1 driver is not considered a deployment-level driver and is typically used for
development and testing purposes only.
Refer PPT J2EE Database Concepts from slide 21 onwards
for other concepts.
Jtable → Implementing [Link]
• The TableModel interface enables a Java Swing application to manage data in a JTable object.
Thank You