JDBC Introduction

Introduction to JDBC

JDBC is an API developed by Sun Microsystems that provides a standard way to access data using the Java programming language. Using JDBC, an application can access a variety of databases and run on any platform with a Java Virtual Machine. It isn't necessary to write separate applications to access different database systems (Oracle and Sybase,MySQL for example). Using JDBC allows you to write one application that can send SQL statements to different database systems. SQL is the standard language for accessing relational databases.

The JDBC API defines a set of Java interfaces that encapsulate major database functionality, such as running queries, processing results, and determining configuration information. Because JDBC applications are written in Java, applications work on any platform.

Call-level interfaces such as JDBC are programming interfaces allowing external access to SQL database manipulation and update commands. They allow the integration of SQL calls into a general programming environment by providing library routines which interface with the database. In particular, Java based JDBC has a rich collection of routines which make such an interface extremely simple and intuitive.

Here is an easy way of visualizing what happens in a call level interface: You are writing a normal Java program. Somewhere in the program, you need to interact with a database. Using standard library routines, you open a connection to the database. You then use JDBC to send your SQL code to the database, and process the results that are returned. When you are done, you close the connection.

How Does JDBC Work?

 JDBC makes it possible to do the following things within a Java application:

• Establish a connection with a data source
• Send queries and update statements to the data source
• Process the results

The following figure shows the components of the JDBC model.

The Java application calls JDBC classes and interfaces to submit SQL statements and retrieve results.

The JDBC API is implemented through the JDBC driver. The JDBC Driver is a set of classes that implement the JDBC interfaces to process JDBC calls and return result sets to a Java application. The database (or data store) stores the data retrieved by the application using the JDBC Driver.

The main objects of the JDBC API include:

• A DataSource object is used to establish connections. Although the Driver Manager can also be used to establish a connection, connecting through a DataSource object is the preferred method.
• A Connection object controls the connection to the database. An application can alter the behavior of a connection by invoking the methods associated with this object. An application uses the connection object to create statements.
• Statement, PreparedStatement, and CallableStatement objects are used for executing SQL statements. A PreparedStatement object is used when an application plans to reuse a statement multiple times. The application prepares the SQL it plans to use. Once prepared, the application can specify values for parameters in the prepared SQL statement. The statement can be executed multiple times with different parameter values specified for each execution. A CallableStatement is used to call stored procedures that return values. The CallableStatement has methods for retrieving the return values of the stored procedure.
• A ResultSet object contains the results of a query. A ResultSet is returned to an application when a SQL query is executed by a statement object. The ResultSet object provides methods for iterating through the results of the query.

Why Do We Need JDBC?

Why can't application developers use ODBC (Open Database Connectivity) on the Java platform? After all, it's an established standard API for database access. You can use ODBC; however, ODBC isn't appropriate for direct use from the Java programming language because it uses a C interface. The JDBC API was modeled after ODBC, but, because JDBC is a Java API, it offers a natural Java interface for working with SQL. JDBC is needed to provide a "pure Java" solution for application development.

Types of JDBC Drivers

Today, there are four types of JDBC drivers in use:

• Type 1: JDBC-ODBC bridge
• Type 2: partial Java driver
• Type 3: pure Java driver for database middleware
• Type 4: pure Java driver for direct-to-database

For most applications, the best choice is a pure Java driver, either Type 3 or Type 4. Type 4 drivers are the most common and are designed for a particular vendor's database. In contrast, a Type 3 driver is a single JDBC driver used to access a middleware server, which, in turn, makes the relevant calls to the database.  Type 1 drivers are used for testing JDBC applications against an ODBC data source. Type 2 drivers require a native database API to be used. Both Type 1 and Type 2 mix a Java-based API with another API.

The following figure shows a side-by-side comparison of the implementation of each JDBC driver type. All four implementations show a Java application or applet using the JDBC API to communicate through the JDBC Driver Manager with a specific JDBC driver.