Web programming Units WEB1P and WEB2P

JDBC

Introduction

Java Database Connectivity (JDBC) is an API (included in both J2SE and J2EE releases) that provides cross-DBMS connectivity to a wide range of SQL databases and access to other tabular data sources, such as spreadsheets or flat files. With a JDBC technology-enabled driver, you can connect all corporate data even in a heterogeneous environment.

JDBC is really two things:

1. If you are a database user, you will use its API to access your database
2. If you are a database implementor (like Oracle, for example), you will construct a driver that implements the API for your particular database

The diagram (left, from http://java.sun.com/products/jdbc/overview.html) shows a Java applet or application (or servlet) using the JDBC API to connect to a suitable JDBC driver. That driver may connect to the database directly (as on the left side) or use some database middleware (probably also provided by the database vendor) to mediate the connection (as on the right side).

To use JDBC, you need to be familiar with SQL.

Using JDBC in a Java program

There are a number of JDBC tutorials available at http://java.sun.com/products/jdbc/learning/tutorial/index.html. Refer to those for more details on the following. The JDBC API is documented at http://java.sun.com/j2se/1.5.0/docs/api/java/sql/package-summary.html and http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/package-summary.html

Obtaining drivers

Before using JDBC to connect to a database, you need to have a suitable driver for the database you want to connect to. These are usually freely available: examples Oracle (class oracle.jdbc.driver.OracleDriver), MySQL (class com.mysql.jdbc.Driver). A JDBC-ODBC bridge (class sun.jdbc.odbc.JdbcOdbcDriver) that can be used to access a Microsoft Access or SQLServer database is included in the basic JDBC package. To use it in a web application, the JAR file should normally be copied into the /WEB-INF/lib directory. (NetBeans will do this for you if you put the JAR file in your project's library.) If you create a DataSource resource in your web application's context.xml file (or Tomcat's server.xml file), you may need to put the JAR file in Tomcat's library folder. This is <tomcat>/common/lib where <tomcat> is the base directory of your Tomcat installation.

Connecting to the database

The first step to perform in your application is to establish a connection to your database. There are a number of ways of doing this - they vary on the extent to which you need to hardwire code in your program.

DriverManager connection

The "old" way to establish a connection to a database is to use the java.sql.DriverManager class. This is in two steps: first create an instance of the driver class, then get the DriverManager to make a connection using it.

Class.forName(DriverClassName).newInstance();
Connection con = DriverManager.getConnection(URL,Username,Password);

The URL above is a special JDBC URL which (in a database-specific way) defines the location of the database and how to connect to it. An example URL for accessing an Oracle database on my local machine is

jdbc:oracle:thin:@localhost:1521:JIM

The URL for an analogous MySQL database might be

jdbc:mysql://localhost:3306/JIM

(In MySQL, if the hostname is not specified, it defaults to '127.0.0.1'. If the port is not specified, it defaults to '3306', the default port number for MySQL servers.)

Datasource connection

The "new" way (which gives more flexibility, portability and maintainability) is to use a javax.sql.DataSource. A DataSource represents a real world datasource, but can be defined in a number of different ways. Once defined, you call its getConnection method to create a new database connection. See http://java.sun.com/products/jdbc/articles/package2.html for details.

The most convenient way to set up a DataSource in a web application is to use your servlet container's standard resource factories to create one. That sounds more complicated than it is. The Tomcat documentation shows you how to set it up - see the section on JDBC resources in http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html. One suggestion - it tells you to configure Tomcat's resource factory by adding an elements to the $CATALINA_HOME/conf/server.xml file. Easier is to put those settings in your webapps META-INF/context.xml file - Tomcat will deploy this with your webapp such that it has the effect of overriding the server.xml file.

Statements and ResultSets

Once you have a connection to your database, you can use it to perform SQL commands. There are two kinds of object in the JDBC API for doing this: statements and result sets.

Given a Connection object called conn, you can create a new Statement object by

Statement stmt = conn.createStatement();

To then execute a query, you can simply pass an SQL command as a string to an appropriate method. There are several different execute methods defined, but a simple example is

ResultSet result = stmt.executeQuery( "SELECT Entry, Customer, DOW, Cups, Type " + "FROM JJJJData " + "ORDER BY Cups DESC");

This returns an object that provides access to the results of the query. Typically you iterate through it, viz:

while(result.next())

and the columns can be accessed either by position (result.getXXX(1)) or name (result.getXXX("Entry")). There are a variety of getter methods - XXX can be one of a number of types such as String, Int or Object, corresponding to the type of the database column.

If your program executes the same statement repeatedly (with the same or different parameters), it is often worth creating a PreparedStatement object. This is a statement that is only compiled once, but can be executed multiple times.