MySQL Java Tutorial

The Java Developers Kit includes a package known as the Java Database Connectivity API, or the JDBC for short. This provides a single interface for interacting with any database package that provides a JDBC driver. Most major database solutions, including MySQL, provide a JDBC driver either on their install discs or freely downloadable from their web page.

The MySQL JDBC Driver

The JDBC driver for MySQL is named MySQL :: Connector/J, and it is a very thorough implementation of JDBC API. The driver will be a JAR file which must be located on the Java classpath. The most common Java IDE's, particularly Eclipse and Netbeans, will do this for you, provided you reference the driver file as an library. In addition, in order to use commands from JDBC API, you'll need to insert the following command at the top of any class that will directly use the JDBC.

import java.sql.*;

Setup

While the JDBC attempts to make using your databases in your Java programs as simple as possible, there are a number of steps to follow to set up the database connection. Since it is possible to write a program that includes multiple database drivers, before connecting to a database you must inform the API of which driver you will be using. For this, you run the following:

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

This will return an instance of the Driver class (which is included in java.sql), and you can store the reference to that instance for later use if you like. However, it's not necessary in most circumstances. Should you receive an error, this most likely means that your class path is not configured correctly.

Creating a Connection

The database connection must be made using a URL or an IP address. If the database is running on the same machine as the Java program, this is easy: The URL is localhost, with one significant addition: the protocol (or url) identifier. A familiar one for the Web is "http://". For a MySQL JDBC database, the correct protocol identifier is "jdbc:mysql://".

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost", username, password);

Dealing With Errors

Any error concerning a connection, a statement or a result set will be of the SQLException type. For more details on the specific error, you should catch SQLException and print its error message to the console. One potential source of confusion is speed at which connections time out. There is plenty of time to connect, run a few queries and get results, but almost certainly no time to wait for user input after a connection. If you must wait for user input, it is usually a better practice to disconnect, retrieve user input and then reconnect again to run the queries.

Queries and Results

There are two key classes for interacting with the database once connected: the Statement and the ResultSet.

Statement s = conn.createStatement();
s.executeQuery("SELECT * FROM employees");
ResultSet rs = s.getResultSet();

Statement simply sends the SQL string to the database, so queries will need to be written in the native dialect of SQL for the database used. An important quality of a ResultSet is that it retrieves data from the server one row at a time, and only retrieves the data when the row is asked for. The above code has not downloaded any results yet from the server.

Because it is impossible on most systems to know the size of a result set before all the results have been retrieved, it is best to store a result set, at least temporarily, within a data structure that can be efficiently resized. Java's LinkedList class is perfect for this.

LinkedList results = new LinkedList();
while (rs.next()) {
results.add(rs.getString("firstName"));
}

And, it is always a good practice to close connections explicitly rather than let them timeout:

rs.close();
s.close();
conn.close();

References

• Java SE Technologies: Database

Resources

• MySQL: Connector/J JDBC Driver