An Introduction to JDBC

This example, shown in Listing 1, assumes you have some familiarity with Java. The Java code loads the JDBC driver class, establishes a connection with a database, builds an SQL statement, submits the statement and retrieves the results. A database and a table populated with some data must exist.

In the first executable line, the class object for the JDBC driver is loaded by passing its fully qualified name to the Class.forName method. This method loads the class, if it is not already loaded, and returns a Class object for it. In the next line, the database URL string is constructed in the form jdbc:subprotocol_name: hostname:port/database_name/other parameters. The subprotocol name is mysql, since we are using the MySQL database. The host name is localhost in this example, but can also be an Internet host name or IP address. The port number for the MySQL server is 3306 and the name of the database is test. Other parameters can be passed in the database URL, such as user ID and password.

A connection object is obtained via a call to the getConnection method of the driver manager, allowing use of the JDBC driver to manage queries. The user ID and password are in clear text in the file. The password is encrypted by the JDBC driver before passing the information to the MySQL server. A statement object is required to issue a query. The statement object is obtained by calling the createStatement method of the connection object.

The SQL query is stored in a string and passed to the executeQuery method of the statement object, which returns a ResultSet object containing the results of the query. The next method of the resultSet object moves the current row forward by one. It returns false after the last row. This method must be called to advance to the first row, and can be called in a loop to retrieve data from all matching rows. The resultSet object contains a number of methods to extract data from a row. For example, to retrieve a string, the getString method is used. Similarly, to retrieve an integer, the getInt method is used. Other methods to retrieve a byte, short, long, float, double boolean, date, time and a blob are included. The getBytes method can be used to retrieve a binary large object (blob). The parameter to these methods is either an integer or a string. The integer is the column number of the row retrieved. Not all columns of a table need to be retrieved. The string is the name of the column label.

Once data has been extracted from the resultSet object, it is closed. Another SQL query can be issued and the resultSet object can be reused. The statement object can also be reused. The statement and connection objects are closed when database retrieval is complete. This simple example illustrates the process of retrieving data from a database table. It is also possible to update tables and obtain information about tables. When updating tables, the executeUpdate method of the statement object is used. For example:

String query = "update test_table set phone =
        999-9999 ";
query += "where name = \"John Smith\"";
stmt.executeUpdate( query );

JDBC can be used to obtain information about the structure of a database and its tables. For example, you can get a list of tables in a particular database and the column names for any table. This information is useful when programming for any database. The structure of a database may not be known to the programmer, but it can be obtained by using metadata statements—SQL statements used to describe the database and its parts.

Two types of metadata can be retrieved with JDBC. The first type describes the database and the second type describes a result set. The DatabaseMetaData class contains over a hundred methods to inquire about the database, some of which are quite exotic. A common method is the getTables method.

DatabaseMetaData dmd = con.getMetaData();
ResultSet rs = dmd.getTables( null, null, null,
        new String[] {"TABLE"} );

The parameters passed to getTables are, in order, a catalog (group of related schemas), a schema (group of related tables) pattern, a table name pattern and a type array. Some of the types include table, view and system table. If null is passed, no pattern is used to limit the metadata information retrieved. Some of the other methods include getDataProductVersion, getTablePrivileges and getDriverName. The result set rs contains information about all the tables in the database. Each row contains information about a table. For example, the third column of any row of the result set is the table name string.

Useful metadata can be obtained about a result set after the execution of a query. When a result set is obtained after the execution of a query, the metadata statements can be used to extract information such as the number of columns, column types and width.

ResultSet rs = stmt.executeQuery("Select * from test_table");
ResultSetMetaData rsmd = rs.getMetaData();

The rsmd.getColumnCount() method returns the number of columns in the test_table and the rsmd.getColumnLabel(i) method returns the name of the ith column. Similarly, the rsmd.getColumnDisplaySize(i) method returns the width of the ith column. A number of other methods described in the JDBC API can be used to extract all types of information about a table.