2. 4. Logging

Chapter 2 - Guidelines

2. 6. JMS

Database Independency

Introduction

The JDBC API is a Java API for accessing virtually any kind of tabular data. The API consists of a set of classes and interfaces written in the Java programming language that provide a standard API for tool/database developers and makes it possible to write industrial strength database applications using an all-Java API.

Note JDBC is the trademarked name and is not an acronym. Nevertheless, JDBC is often thought of as standing for Java Database Connectivity.

The JDBC API makes it easy to send SQL statements to relational database systems and supports all dialects of SQL.

The value of the JDBC API is that an application can access virtually any data source and run on any platform with a Java Virtual Machine. In other words, with the JDBC API, it isn't necessary to write one program to access a Sybase database, another program to access an Oracle database, another program to access an IBM DB2 database, and so on. One can write a single program using the JDBC API, and the program will be able to send SQL or other statements to the appropriate data source. And, with an application written in the Java programming language, one doesn't have to worry about writing different applications to run on different platforms. The combination of the Java platform and the JDBC API lets a programmer write once and run anywhere.

For an application to be platform and database independent some rules need to be taken into account. The sections that follow provide an overview of the things to look out for.

JDBC Basics

Packages

The standard JDBC classes can be found in the java.sql package. Since JDK 1.4 some JDBC extension are also included in the core J2SE distributions. These extensions are located in the javax.sql package.

Database Driver

If you want to use a database in your application the first thing you need to do is to establish a connection with it. Creating a database connection (java.sql.Connection) is delegated to a database driver (java.sql.Driver). The drivers themselves are managed by the java.sql.DriverManager. See the section about DriverManager.

The java.sql.Driver concept is the starting point for writing platform and database independent applications. All database vendors have specific java.sql.Driver implementations that can be used to connect to their database(s) using the JDBC API.

Tip The JDBC driver database (http://servlet.java.sun.com/products/jdbc/drivers) on the java sun website allows you to look for a specific driver for your database.

The JDBC API defines 4 driver categories (referred to as types). The types were defined based on:

• The fact if the driver uses native (OS specific) code or is written completely in java
• The fact if the connection to the database is direct or indirect

Table 2.7. JDBC Driver Types

Tip The type of driver you choose only affects the level of platform independence. Types 4 and 3 are the preferred way to access databases using the JDBC API. Type 4 being completely platform independent and type 3 allowing for client platform independency.

Database Driver Management

The java.sql.DriverManager is responsible for managing the available java.sql.Drivers and is used to create the actual database connection.

The only method in the DriverManager class that needs to be used is getConnection. As its name implies, this method gets a connection to the database.

Tip An application may call the DriverManager methods getDriver, getDrivers and registerDriver as well as the Driver.connect method connect, but in most cases it is better to let the DriverManager class manage the details of establishing a connection.

One of the arguments in DriverManager.getConnection method is the database URL. This URL specifies which database to connect to. Each java.sql.Driver has its own URL syntax. The DriverManager takes the URL and tries to locate the Driver that needs to be used to establish the connection.

For the DriverManager to be able to create the connection, it must know which JDBC drivers are available. Registering JDBC drivers with the manager can be done in two ways:

• Setting the jdbc.drivers system property. The value for this property is a colon-separated list containing the driver class names that need to be loaded by the DriverManager.
• Loading the driver at runtime using Class.forName("driverClassName")

Tip The Class.forName approach for registering JDBC drivers is the preferred one.

Creating A Connection

Creating a connection to a database involves the following steps:

1. Loading the database driver
2. Asking the DriverManager to create a connection using a database URL that is conforming the syntax used by the JDBC driver.

Example that connects to a database using the JDBC_ODBC bridge driver:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:MyDb",
"myLogin","myPassword");

Using DataSource

Since JDBC 2.0 the use of javax.sql.DataSource provides an alternative to creating a database connection. If available, this technique is preferred over the DriverManager.getConnection approach.

The DataSource interface is implemented by a driver vendor, the same way the driver vendor has a specific JDBC driver implementation.

Table 2.8. DataSource Types

Important Not all data sources support all of these 3 implementations. Check the driver vendor's documentation.

An object that implements the DataSource interface will typically be registered with a naming service based on the Java Naming and Directory (JNDI) API. Clients that want to connect to the database simply have to lookup the correct DataSource in the JNDI tree and ask it for a connection.

Executing SQL Statements

Once you have obtained a connection to the database you can start executing statements. The steps for executing a statement are:

1. Ask the connection to create a statement
2. Execute the statement

Table 2.9. Statements Types

Statement stat =
    connection.createStatement();
stat.executeQuery(
    "SELECT name FROM person")

PreparedStatement stat =
    connection.prepareStatement(
        "SELECT name FROM person "
        + "WHERE dob=?");
stat.setDate(1, new Date());
stat.executeQuery();

CallableStatement stat =
    connection.prepareCall(
        "call findPerson(?)");
stat.setDate(1, new Date());
stat.execute();

Tip It is recommended to use the PreparedStatement as much as possible. Not only for the fact that they are precompiled and can be reused but also because the PreparedStatement hides the complexity of formatting values like dates and strings that are used in the statement. The driver handles the formatting. This is very important when you want to keep your code database independent. The formatting of a date (or timestamp) can be (and probably is) database dependent.

Important Prepared statements are linked to the connection on which they were created. Closing the connection will render the use of prepared statements on that connection useless. JDBC 3.0 defines support for caching prepared statements. An application may find out whether a data source supports statement pooling by calling the DatabaseMetaData.supportsStatementPooling. If the return value is true, the application can then choose to use PreparedStatement objects knowing that they are being pooled.

Important In many cases, reusing SQL statements is a significant optimization. This is especially true for complex prepared statements. However, it should also be noted that leaving large numbers of statements open could have an adverse impact on the use of resources.

Processing Results

The base interface for interpreting results that were returned after executing a query is the java.sql.ResultSet. After a result set is initially returned the cursor is positioned before the first result. Calling the next method the first time will give you access to the first result.

The ResultSet interface provides some other methods that can be used to navigate through the ResultSet's contents. Other methods include:

• afterLast
• beforeFirst
• previous
• first
• last

The next method is always available the other navigation methods are optional and their availability depends on the JDBC driver being used. The availability of the optional navigation methods can be checked using the getType method on the ResultSet. If the return value is ResultSet.TYPE_FORWARD_ONLY none of the additional navigation methods are available.

Once you are positioned on a record in the ResultSet you can start retrieving values. For each type of value the ResultSet has two getters: one for accepting an index as an argument and one for accepting a column name as an argument:

• getString(int index) // numbering starts at 1
• getString(String name)

Errors And Warnings

The JDBC API uses the java.sql.SQLException to report errors it has encountered. The SQLException is a chainable exception meaning that one exception can contain a link to another SQLException. See also rule JDBC_010: Check For A Nested SQLException.

Besides exceptions, the JDBC API is also knows the notion of warnings (java.sql.SQLWarning). Warnings are linked to almost every main JDBC interface, such as:

• java.sql.Connection
• java.sql.Statement
• java.sql.ResultSet

Warnings can be retried using the getWarning method on any of those interfaces. Just like the SQLException, the SQLWarning is also chainable.

Except for logging the warning there is not much you can do with it, unless you start examining the warning code (which is database dependant).

Once you have treated the warning you can clear it by calling the clearWarnings method.

Use Of SQL

When you use the JDBC API to access a database you will end up having to write SQL statements. The most common SQL standard (today) is SQL-92 but often database vendors provide some database specific extensions to the standard SQL syntax.

Important Keep in mind that once you start using the extensions offered by the database vendor you are depended of their database.

Note JDBC 3.0 did implement a subset of the SQL-99 standard but the chances are that the database (or the driver) does not support it yet.

Know Your Target Database(s)

Even if you follow all basic conventions mentioned here (not using vendor specific classes, sticking to standard SQL, etc.), your application may still not be portable across different database platforms.

Why? Each database has its own special features. In order to find out what these features are and how they can affect your code you must study the database. Unfortunately there are no strict guidelines that specify which topics you should examine in detail.

There are however some general topics that should be taken into account, such as:

• Is the database case sensitive when it comes to executing SQL? For example are column names case sensitive?
• How does the database deal with null values? For example does the database treat an empty string or zero number as being null?
• Which character set does the database use? This is important for internationalized applications.
• How does the database (and its driver) handle a CLOB or BLOB?
• Are there database specific performance issues?
• What about data conversions, such as double, float, BigDecimal, etc.?
• What about data precision? For example are the nanoseconds for a TimeStamp stored?

Architecture

Use An Existing Persistence Strategy

Using external data stores to store data is not something new. Almost every application needs it. Today a lot of existing persistence strategies exist. Some examples include:

• Container Managed Persistence Entity Beans (CMP EJB)
• JDO
• Hibernate
• TopLink

If possible try to use one of these existing implementations. It will make life a lot easier and allow you to concentrate on the application's logic and not so much on the plumbing.

Note With the release of JEE5 the persistence API of choice is of course the Java Persistence API (JPA). JPA is the standardized consolidation based on the work of Hibernate, JDO and Entity Beans. Very important to know is that JPA doesn't need a JEE5 container to work! Very similar to Hibernate, JPA can work out-side of a container allowing you to easily write Unit tests. In addition you can use JPA in a standalone Java client or directly from the web tier as many lightweight enterprise projects already did with Hibernate.

Introduce A Persistence Layer

Writing portable JDBC code implies that you don't write database specific code. Unfortunately this is not always possible. Sometimes you are forced to use vendor specific classes or SQL. In this case it is important that you isolate your database access code in a persistence layer.

If you ever you need to migrate to or support another database then you only have to change (or extend) the code in the persistence layer. The most common design pattern that is used in such a persistence layer is the Data Access Object (DAO). The DAO is one of the J2EE design patterns J2EEDP but can also be applied to non J2EE environments.

The DAO contains all code that is used to access stored data. The persistence layer can contain one or more DAOs. When you need to migrate to another database the only code you have to change is located in these DAOs. You could think of a scenario where you have more then one version of the same DAO but for different databases. In this last case you could determine at runtime which DAO to use depending on the database you need to connect to.

Even if you use an existing persistence strategy you might still be forced to do some manual SQL coding, for example DAOs in combination with CMP EJB.

Tip You could consider using the DAO template support from the Spring Framework. Spring DAO provides templates for JDBC, Hibernate, JDO and recently even for JPA and will reduce the complexity (and number of lines of code) of your DAO's dramatically!

2. 4. Logging

Chapter 2 - Guidelines

2. 6. JMS