Java Database Connectivity

From SwinBrain

The Java Database Connectivity, or JDBC, API provides interfaces that can be used to interact with a databases from Java source code. In order to remain DBMS neutral, JDBC consists only of interface definitions. The JDBC interfaces themselves are implemented by DBMS providers, allowing them to provide access to databases managed by their DBMS products. This page outlines the basic architecture of JDBC, and provides some links to additional material.

Introduction: The material in this page aims to introduce important concepts rather than provide a detailed examination of the topic.

JDBC Architecture

The content of this section was created for JDBC Version 1. This may not be relevant for different versions of this software.
The role of JDBC drivers

Using a database in a program requires the ability to connect to a database. This requires a component within the system that understands how to communicate with the DBMS, allowing the program to instruct the DBMS. Java calls this component a driver (similar to a managed provider in ADO.NET). The role of the driver is to provide the means of connecting to a DBMS, there are four kinds of drivers in JDBC. The first step in a JDBC program is to register the required drivers with the JDBC Driver Manager.

With the driver in place you can create a connection to the database. The connection is requested from the DriverManager and is configured using a URL. The URL starts with "jdbc:" and is then followed by a subprotocol indicating the driver to use for example "jdbc:derby:" indicates a jdbc URL with the derby protocol. The remainder of the URL will indicate the database to connect to and any other DBMS specific options, for example the URL "jdbc:derby:Test;create=true" indicates a jdbc URL to derby connecting to a Test database that will be created if it does not exist.

Getting the connection from the Driver Manager

Once you have the URL, the next step is to ask the Driver Manager to create a connection using the URL. This creates a JDBC Connection that connects to the database specified in the URL. The protocol information in the URL will also identify the driver to be used to communicate with the DBMS. The connection uses the driver and the remaining URL information to connect to the database. At this point we have created a connection to the database through which we can pass requests, and receive responses.

Execute SQL via a JDBC Statement

Once you have a JDBC Connection you need a JDBC Statement to allow you to execute SQL statements via the connection. The Statement is created from the connection using createStatement. This creates a new statement object, which is linked to the creating Connection. The Statement object can be used to execute SQL statements on the database. When you create a statement in this manner, you get an object that is capable of running a number of different SQL statements. The Statement object has the following methods used to execute SQL over the connection.

Executing a JDBC statement, and reading the ResultSet
  • execute(string): Executes the passed in SQL string on the database.
  • executeQuery(string): Executes the passed in SQL SELECT query, giving easy access to the results.
  • executeUpdate(string): Executes the passed in SQL statment that will be modifying the database. This can be used for INSERT, UPDATE, and DELETE statements.

When SQL is executed via the Statement it executes on the database and results (if any) are returned via a ResultSet object. The executeQuery method is used to easily gain access to the first result set returned from the query, while the executeUpdate method is used when no result set will be returned. Once a Result Set is fetched you query it for each row, and the values of the columns within that row.

JDBC 2

The content of this section was created for JDBC 2. This may not be relevant for different versions of this software.
The role of the Data Source, providing both the database and the driver details

JDBC 2 introduced the DataSource, which replaces the role of the DriverManager. Rather than registering the driver class and then retrieving your connection from the DriverManager, you create or retrieve a DBMS specific DataSource object. The DataSource object is created from a DBMS specific class and therefore knows how to communicate with its database. In addition to this driver information, the DataSource knows the database that it connects to.

Each DBMS will provide its own DataSource classes, all of which implement the DataSource interface. Creating and configuring the DataSource is then dependent upon the DBMS's DataSource implementation. Typically different class will be provided to gain certain features, though they will all include methods for setting the location of the database.

Using a DataSource to create a connection

Now that we have a DataSource object that knows both the type of driver to use and the location of the database, it can be used to create the connection. The JDBC DataSource objects provide a getConnection method that returns the JDBC connection object used to connect to the database.

Once you have the JDBC Connection the remainder of the programming model does not change from that described above. You use the Connection to create Statements that are capable of running SQL over the connection. The results of these statements (if any) can be read using a ResultSet.

Links

SwinBrain links related to database programming with Java.

External links related to database programming with Java.

[edit]Links: Topic Brains
Return to Swin Brain