Java and JDBC (MySQL & mSQL)

14.1. What Is JDBC?

Like all Java APIs, JDBC is a set of classes and interfaces that work together to support a specific set of functionality. In the case of JDBC, this functionality is naturally database access. The classes and interfaces that make up the JDBC API are thus abstractions from concepts common to database access for any kind of database. A Connection , for example, is a Java interface representing a database connection. Similarly, a ResultSet represents a result set of data returned from a SQL SELECT statement. Java puts the classes that form the JDBC API together in the java.sql package which Sun introduced in JDK 1.1.

The details of database access naturally differ from vendor to vendor. JDBC does not actually deal with those details. Most of the classes in the java.sql package are in fact interfaces -- and thus no implementation details. Individual database vendors provide implementations of these interfaces in the form of something called a JDBC driver. As a database programmer, however, you need to know only a few details about the driver you are using -- the rest you manage via the JDBC interfaces. The vendor specific information you need in order to use JDBC includes:

The JDBC URL for the driver
The name of the class that implementsjava.sql.Driver

NOTE

The new JDBC 2.0 specification adds an optional standard extension API for vendors to implement. If your JDBC vendor implements this standard extension, you do not even need to know the JDBC URL or Driver class implementation. It prescribes a DataSource class that you can look up by a configurable name in a JNDI [22]-supported directory.

[22]JNDI is the Java Naming and Directory Interface API. It lets you store Java objects in a naming and directory service like an Lightweight Directory Access Protocol (LDAP) server and then look them up by name.

Both of these items can be supplied at runtime, either on the command line or in a properties file. Your code never needs to mention these two implementation-dependent pieces. We will cover what the JDBC URL and Driver class do in a few paragraphs when we cover database connections. Figure 14-1 diagrams the interfaces of JDBC.

Figure 14-1. The classes and interfaces of the JDBC API

14.1.1. The Database Connection

Your first step is to connect to the database. One of the few implementation classes in the java.sql.package is the DriverManager class. It maintains a list of JDBC implementations and provides you with database connections based on JDBC URLs you provide it. A JDBC URL comes in the form of jdbc:protocol:subprotocol. It tells a DriverManager which database engine you wish to connect to and it provides the DriverManager with enough information to make a connection.

NOTE

JDBC uses the word "driver" in multiple contexts. In the lower-case sense, a JDBC driver is the collection of classes that together implement all of the JDBC interfaces and provide an application with access to at least one database. In the upper-case sense, the Driver is the class that implements java.sql.Driver. Finally, JDBC provides a DriverManager that can be used to keep track of all of the different Driver implementations.

The protocol part of the URL refers to a given JDBC driver. In the case of MySQL and mSQL, the protocol is mysql and msql, respectively. The subprotocol provides the implementation-specific connection data. Both MySQL and mSQL require a host name and database name in order to make a connection. Optionally, they may require a port if your database engine is not running as root. The full mSQL URL therefore looks like: jdbc:msql://athens.imaginary.com:1114/test. It says that the DriverManager should find the mSQL JDBC driver and connect to the database test at athens.imaginary.com on port 1114. All of this is done via a single call to the DriverManager getConnection() method. Example 14-1 shows how to make a connection to an mSQL database.

Example 14-1. A Code Snippet from the Examples that Come with the Imaginary JDBC Driver for mSQL Showing How to Make a Connection

import java.sql.*;

public class Connect {
    public static void main(String argv[]) {
        Connection con = null;

        try {
            // here is the JDBC URL for this database
            String url = "jdbc:msql://athens.imaginary.com:1114/db_test";
            // more on what the Statement and ResultSet classes do later
            Statement stmt;
            ResultSet rs; 

            // either pass this as a property, i.e.
            // -Djdbc.drivers=com.imaginary.sql.msql.MsqlDriver
            // or load it here like we are doing in this example
            Class.forName("com.imaginary.sql.msql.MsqlDriver");
            // here is where the connection is made   
            con = DriverManager.getConnection(url, "borg", ""); 
        }
        catch( SQLException e ) {
            e.printStackTrace();
        }
        finally {
            if( con != null ) {
                try { con.close(); }
                catch( Exception e ) { }
            }
        }
    }
}

The line con = DriverManager.getConnection(url, "borg", "") makes the database connection in this example. In this case, the JDBC URL and Driver implementation class names are actually hard coded into this application. The only reason this is acceptable is because this application is a demo for the mSQL-JDBC driver. For a serious application, you would want to load this information from a properties file, pass it as command line arguments, or pass it as system properties. The Driver implementation will automatically be loaded if you pass it as the system property jdbc.drivers -- in other words, you do not have to call Class.forName().newInstance(driver_name) when you pass the driver name as the jdbc.drivers system property. The second and third arguments to getConnection() are the user ID and password to use for the connection. Because mSQL does not use passwords for user authentication, this example just uses an empty string. In MySQL, however, you will need to provide a password.[23]

[23]MySQL actually has several JDBC drivers. At least one of them allows you to specify the user ID and password as part of the URL.

14.1.2. Maintaining Portability Using Properties Files

Though our focus is on two specific databases, it is good Java programming practice to make your applications completely portable. To most people, portability means that you do not write code that will run on only one platform. In the Java world, however, the word "portable" is a much stronger term. It means no hardware resource dependencies, and that means no database dependencies.

We discussed how the JDBC URL and Driver name are implementation dependent, but we did not discuss how to avoid hard coding them. Because both are simple strings, you can pass them on the command line as runtime arguments or as parameters to applets. While that solution works, it is hardly elegant since it requires command line users to remember long command lines. A similar solution might be to prompt the user for this information; but again, you are requiring that the user remember a JDBC URL and a Java class name each time they run an application.

A more elegant solution than either of the above solutions would be to use a properties file. Properties files are supported by the java.util.ResourceBundle and its subclasses to enable an application to extract runtime specific information from a text file. For a JDBC application, you can stick the URL and Driver name in the properties file, leaving the details of the connectivity up to an application administrator. Example 14-1 shows a properties file that provides connection information.

Example 14-1. The SelectResource.properties File with Connection Details for a Connection

Driver=com.imaginary.sql.msql.MsqlDriver
URL=jdbc:msql://athens.imaginary.com:1114/db_test

Example 14-2 shows the portable Select class.

Example 14-2. Specific Information

import java.sql.*;
import java.util.*;

public class Connect {
    public static void main(String argv[]) {
        Connection con = null;
        ResourceBundle bundle = ResourceBundle.getBundle("SelectResource");

        try {
            String url = bundle.getString("URL");
            Statement stmt;
            ResultSet rs; 

             Class.forName(bundle.getString("Driver"));
            // here is where the connection is made   
            con = DriverManager.getConnection(url, "borg", ""); 
        }
        catch( SQLException e ) {
            e.printStackTrace();
        }
        finally {
            if( con != null ) {
                try { con.close(); }
                catch( Exception e ) { }
            }
        }
    }
}

We have gotten rid of anything specific to mSQL in the sample connection code. One important issue still faces portable JDBC developers -- one that stings mSQL developers in particular. JDBC requires any driver to support SQL2 entry level. This is an ANSI standard for minimum SQL support. As long as you use SQL2 entry level SQL in your JDBC calls, your application will be 100% portable to other database engines. Unfortunately, while MySQL is SQL2 entry level, mSQL is not. Applications you write for mSQL will very likely port to other databases without issue, but applications written to use the full range of SQL92 entry level will not port back to mSQL without pain.