Friday, June 29, 2012

Java Database Connectivity - JDBC

JDBC is Java application programming interface that allows the Java programmers to access database management system from Java code. It was developed by JavaSoft, a subsidiary of Sun Microsystems. The purpose of this article is to give you an introductory overview of this JDBC concept. Before we go any further, let me reiterate that JDBC in itself is a very large topic and what you see in this chapter is just a drop in the ocean. But, this should be sufficient to get you started into using JDBC in your day to day programs.

What is Java Database Connectivity or JDBC?

The Java application programming interface provides a mechanism for dynamically loading the correct Java packages and drivers and registering them with the JDBC Driver Manager that is used as a connection factory for creating JDBC connections which supports creating and executing statements such as SQL INSERT, UPDATE and DELETE. Driver Manager is the backbone of the jdbc architecture.

Generally all Relational Database Management System supports SQL and we all know that Java is platform independent, so JDBC makes it possible to write a single database application that can run on different platforms and interact with different Database Management Systems.

Java Database Connectivity is similar to Open Database Connectivity (ODBC) which is used for accessing and managing database, but the difference is that JDBC is designed specifically for Java programs, whereas ODBC is not depended upon any language.

In short JDBC helps the programmers to write java applications that manage these three programming activities:

1. It helps us to connect to a data source, like a database.
2. It helps us in sending queries and updating statements to the database and
3. Retrieving and processing the results received from the database
Why is Java Database Connectivity Important?

Java, as you might be aware is a very powerful programming language which is widely used to create enterprise class applications. Any enterprise application that you may use has to display and manipulate data in some form and this data is invariably stored in some Relational Database (RDBMS) like Oracle, Sybase etc. So, the system or the application per say must connect and interact with the database in order to display the required information on screen. This is exactly where the JDBC layer fits in.

Without JDBC, there is no way that the application can effectively interact with a Relational Database like Oracle or Sybase and perform its functions. The latest version of the JDBC API is 4.0. Some of the new Features Added to JDBC 4.0 are:

1. Auto-loading of JDBC driver class
2. Connection management enhancements
3. Support for RowId SQL type
4. DataSet implementation of SQL using Annotations
5. SQL exception handling enhancements
6. SQL XML support

JDBC Architecture:

At a high level, the JDBC Architecture consists of two layers:

1. The JDBC API, which provides the application-to-JDBC Manager connection.
2. The JDBC Driver API, which supports the JDBC Manager-to-Driver Connection.

The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to different 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. The location of the driver manager with respect to the JDBC drivers and the Java application is shown below:

Main Components of JDBC:

There are numerous components that work together as part of the JDBC API in order to allow an enterprise java application to connect to a database. However, the following are the main components:
1. DriverManager - 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 sub-protocol under JDBC will be used to establish a database Connection.
2. Driver - The database communications link, handling all communication with the database. Normally, once the driver is loaded, the developer need not call it explicitly every time.
3. Connection - Interface with all methods for contacting a database. The connection object represents communication context, i.e., all communication with database happens through the connection object only.
4. Statement - Encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.
5. ResultSet - The ResultSet represents set of rows retrieved due to query execution which gets returned to the calling system.

A Sample Code to connect to a database using JDBC:

The following is a code snippet that you can use to connect to an Oracle database using a thin driver. The steps in connecting to a database and obtaining the output are:
1. Load the Driver Class
2. Create a Connection
3. Create a Statement
4. Execute the query and obtain the ResultSet
5. Access the output of the query from the ResultSet

Each line of code that represents each of the above steps is marked by the comment followed by a number.

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

Class.forName ("oracle.jdbc.driver.OracleDriver"); //1

Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@hostname:1526:testdb", "scott", "tiger"); //2

Statement stmt = conn.createStatement(); //3
ResultSet rs = stmt.executeQuery("select 'Hi' from dual"); //4
while (
System.out.println (rs.getString(1)); // 5

JDBC Driver Categories/Types:

If you saw the above code example correctly, we have loaded a driver class as the first step before we established the database connection. JDBC has 4 different categories and all drivers will invariably fit into either of these 4 categories. The four JDBC driver categories are:

1. Type 1 – JDBC-ODBC Bridge
2. Type 2 – Native API (Partly Java) Driver
3. Type 3 – Net-Protocol Fully Java Driver
4. Type 4 – Pure Java Driver

Type 1: JDBC/ODBC Bridge requires an ODBC (Open Database Connectivity) driver for the database to be installed. This type of driver works by translating the submitted queries into equivalent ODBC queries and forwards them via native API calls directly to the ODBC driver. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important.

Type2: Native API (partly-Java driver) type of driver uses a vendor-specific driver or database API to interact with the database. An example of such an API is Oracle OCI (Oracle Call Interface). It also provides no host redirection. A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.

Type 3: A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products. This type of driver can access any database.

Type 4: Proprietary Protocol-Net (pure Java driver) has the same configuration as a type 3 driver but uses a wire protocol specific to a particular vendor and hence can access only that vendor's database. Again this is all transparent to the client. A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.

Note: Type 4 JDBC driver is most preferred kind of approach in JDBC.

Types of Statements you can use in a Java Program:

As you may have seen in the code example section, a Statement is used to pass or rather send the SQL Query that we wish to execute to the database. In our example we saw a simple “Statement”. The JDBC API has 3 types of statements that you can use in your Java Programs. They are:
1. Statement
2. PreparedStatement &
3. CallableStatement

Each of these types of statements has a different purpose and has to be used differently. Both Statement & PreparedStatement are used to execute basic SQL Queries while the CallableStatement is used to execute or invoke Stored Procedures.

A standard Statement is used to create a Java representation of a literal SQL statement and execute it on the database. The Statement has to verify its metadata against the database every time. If you want to execute the SQL statement once go for STATEMENT.

A PreparedStatement is a precompiled statement. This means that when the PreparedStatement is executed, the RDBMS can just run the PreparedStatement SQL statement without having to compile it first. If you want to execute a single SQL statement multiple number of times, then go for PREPAREDSTATEMENT. PreparedStatement objects can be reused with passing different values to the queries.

A CallableStatement extends the PreparedStatement Interface. It can be used to call Stored Procedures from java code.

Types of Result Sets you can use in a Java Program:

As you may have seen in the code example section, a ResultSet is the output that we will get once our query is executed. Typically, we will iterate through the result set and obtain all the values that are returned as the output of execution of the Statement. The JDBC API gives us an option to use 3 different types of Result Sets and this value can be defined while establishing the Connection. They are:

1. TYPE_FORWARD_ONLY - specifies that a resultset is not scrollable, that is, rows within it can be advanced only in the forward direction.
2. TYPE_SCROLL_INSENSITIVE - specifies that a resultset is scrollable in either direction but is insensitive to changes committed by other transactions or other statements in the same transaction.
3. TYPE_SCROLL_SENSITIVE - specifies that a resultset is scrollable in either direction and is affected by changes committed by other transactions or statements within the same transaction.

The TYPE_FORWARD_ONLY resultset is the default type of resultset that will get created and it is always insensitive.

I hope this chapter gave you a basic overview of the JDBC concepts. As I had said at the beginning of the chapter, JDBC is an ocean and we haven’t even scratched the surface of that vast topic. If you have any specific questions related to JDBC, visit the article on “JDBC Interview Questions” to see if it has already been answered. If you can’t find an answer to your question, either leave a comment in this page or on the “JDBC Interview Questions” page and I will try to answer them.

JDBC Interview Questions - Part 1

JDBC Interview Questions - Part 2


  1. whatt "JDBC interview questions" article?
    couldn't find it with search.
    ps: glad to see you're posting again!

    1. Sorry michee. The article on interview questions is work in progress. So will be posting soon.

      Thanks :-) glad to be back



© 2013 by All rights reserved. No part of this blog or its contents may be reproduced or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without prior written permission of the Author.