JDBC/MySQL Notes

JDBC/MySQL Notes
— print (last updated: Jan 13, 2009) — print

The java.sql package and JDBC

JDBC is the Java API for accessing relational databases. It consists of the interfaces and classes in the package java.sql. This package consists primarily of interfaces, not classes. The idea is that JDBC is simply an API for database access, independent of the specifics of the database. It is the job of driver software to link JDBC to a specific data source, consisting of a DBMS, like MySQL, and a specific database. By linking we mean that the driver software provides the classes which instantiate the java.sql interfaces:

Array, Blob, CallableStatement, Clob, Connection, Driver,
Driver, PreparedStatement, Ref, ResultSet, ResultSetMetaData, 
SQLData, SQLInput, SQLOutput, Statement, Struct

There are only a few actual classes in java.sql:

Date, Time, Timestamp: extensions of java.util.Date
DriverManager: manages the driver and delivers the connection.

A relational DBMS will typically have much more specificity about the size of the data it stores that does Java. The following chart gives the mapping of common SQL types to Java types

SQL Type	Java Type
CHAR, VARCHAR, LONGVARCHAR	String
TEXT	String
TINYINT	byte
SMALLINT	short
INT (INTEGER)	int
BIGINT	long
NUMERIC, DECIMAL	java.math.BigDecimal
REAL	float
FLOAT, DOUBLE	double
BIT	boolean
BINARY, VARBINARY, LONGVARBINARY	byte[]
BLOB, MEDIUMBLOB, LARGEBLOB	byte[]
DATE	java.sql.Date
TIME	java.sql.Time
DATETIME, TIMESTAMP	java.sql.Timestamp

The JDBC calls are used in the following standard way:

load the driver
establish a connection with data source
send SQL statements
process result set, if applicable
close the connection

There are two types of drivers commonly used in practice:

JDBC-ODBC bridge driver (type 1)
JDBC pure Java driver (type 4)

JDBC-ODBC Bridge Driver

The type 1 driver uses Microsoft's ODBC (Open Database Connectivity) which is an API of C-callable functions that can connect to a variety of DBMS's such as Microsoft Access. The JDBC-ODBC bridge driver code is "native" C code used directly by Java. This was historically the first driver type used by Java in order to get jumpstart JDBC usage. The standard JDBC-ODBC driver is already built-in to the java distribution as the class:

sun.jdbc.odbc.JdbcOdbcDriver

MySQL pure Java driver

Pure Java drivers are usually associated with a DBMS in which commands are delivered through a socket interface. The MySQL DBMS is such an example. The preferred JDBC-pure driver for MySQL is called MySQL Connector/J and is freely available through the MySQL site. The main driver class is this:

com.mysql.jdbc.Driver

Usage Steps

Load the driver.

This does not need to be done explicitly. The step which establishes the connection will automatically find the driver in the MySQL driver library and load it. In the past, it was necessary to do so explicitly, usually with a call like this:

Class.forName( "com.mysql.jdbc.Driver" );

Object driver = Class.forName( "com.mysql.jdbc.Driver" ).newInstance();

Establish a connection with data source.

This is done by calling the DriverManager.getConnection function as follows:

Connection cx = DriverManager.getConnection( url, user, password );

The parameters are strings. The user and password are optional if they don't apply or are empty. The url parameter specifies the data source and how to access it. It has the following structure:

url = "jdbc:subprotocol:subname"

The subprotocol identifes the DBMS driver; in our case it is mysql. The subname identifies the data source. For MySQL the subname must identify the host (since it not assumed to be local) and the database. This is our common url specifiying the test database on our local system:

jdbc:mysql://localhost/test

Send SQL statements.

Once we have already established the connection, cx from the previous step. There are three kinds of ways of specifying an SQL statements:

Statement: simple statement
PreparedStatement: statement with parameters
CallableStatement: used to invoke SQL stored procedures

Statement

The Statement is first created from the connection:

Statement st = cx.createStatement();

We then create a String sql_op, which represents a syntactically correct SQL command and execute this SQL command. The type of execution statement we call depends on whether there are any expected results.

For SQL commands which affect on the database but do not retrieve information, such as:
```
CREATE TABLE, DROP TABLE, INSERT INTO, UPDATE, etc.
```
we call:
```
int affected_rows = st.executeUpdate( sql_op );
```
The return value of this call is the number of affected rows.
For the SELECT statement and a few others which retrieve information (rows) from the database, the execution is as follows:
```
ResultSet rs = st.executeQuery( sql_op );
```

PreparedStatement

The PreparedStatement is used to deliver parameters to an SQL statement, typically for repeated similar calls.

PreparedStatement st = cx.prepareStatement(sql_op);

The String sql_op contains zero or more ? characters which represent unspecified values. The values are specified by Java statements:

st.set<type>(pos, value);         e.g.: st.setString(pos, value);

specifies the value of the specified type to be placed in position pos (starting from 1) of the statement. After setting the values of all postions we can call either one of the (parameterless):

st.executeUpdate()      or     st.executeQuery()

Process the result set

When the executeQuery command is used (typically for a SELECT query), a ResultSet is returned. The results of such a query are specific columns from rows which matched the query. Typically we want to read these rows sequentially and process the information. This is done in a fairly standard manner:

while (rs.next()) {
   // get data from columns in the row "rs"
}

We can retrieve a specific column from rs by:

<type> info = rs.get<type>( <column name> );

<type> info = rs.get<type>( <column index> );   // starting from 1

where <type> matches the standard Java types like String, int, Timestamp, etc. The call

Object info = rs.getObject ( .. );

gives a type-independent way to retrieve the column data (in which case a field with the SQL INT type will probably return a java.lang.Long object).

If a single row is expected, say, in the search based on a key value, the while loop format can be replaced by something like this:

if (! rs.next() ) 
  throw new Exception("something unexpected");
<type> info = rs.get<type>( <column name> );

Close the connection.

This is done with the statement:

cx.close();

This is sometimes important when we update a database in some way, in order to insure that the updates take effect. If we plan to repeately access a database, it is often worthwhile to not close the connection, i.e. to allow the connection to "persist" between queries.

A DBMS such as MySQL maintains multiple concurrent client connections. A single client connection which is idle will eventually be closed by the server after some time. Instead of establishing a new connection each time, the client can test the current connection exists and is valid before re-connecting as follows:

if (cx == null || cx.isClosed())
   cx = DriverManger.getConnection( url, user, password );

JDBC/MySQL Notes — print (last updated: Jan 13, 2009) — print