Using JDBC to Connect to a Database

JDBC-Java Database Connectivity-is a Java feature that lets you connect to almost any relational database system, execute SQL commands, and process the results all from within a Java program. In this chapter, you set up JDBC and use it to access data in a MySQL database.

If you aren't familiar with the basics of SQL, read the previous chapter before you tackle this chapter.

Setting Up a Driver

Before you can write a Java program to access a database via JDBC, you must first install a driver that links Java's database API classes to an actual database. Getting the driver set up right can be tricky, but once you get it working, accessing the database is easy.

The following sections describe two basic approaches to setting up a driver to connect to a database: ODBC or a database connector.

Setting up an ODBC data source

ODBC is a generic database connection standard that almost every database program available can speak to. It's inherently inefficient, but it is easy to set up and performs adequately for small applications and for testing purposes. If you're using Microsoft Access files for your database, ODBC is the way to go.

Assuming you have created a database in Access that you want to access from a Java program, you can follow these steps to create an ODBC data source for the Access database:

1. Open the Control Panel and double-click Administrative Tools.

   A window with icons for various administrative tools comes up.

2. Double click Data Sources (ODBC).

   The ODBC Data Source Administrator dialog box opens, as shown in Figure 4-1.

   Figure 4-1: The ODBC Data Source Administrator dialog box.

3. Click the System DSN tab, and then click Add.

   A dialog box listing a bunch of ODBC drivers appears.

4. Choose Microsoft Access Driver, and then click Finish.

   The Finish button is strangely named, but this is when the real configuration actually begins. The dialog box shown in Figure 4-2 now appears.

   Figure 4-2: Configuring an Access data source.

5. Type a name for the data source.

   You use this name in your Java program to access the data source, so choose it wisely.

6. Click the Select button, and then choose the database you want to connect to and click OK.

   A Select Database dialog box appears. From this dialog box, you can navigate to the folder that contains your Access data file to select it.

7. Click OK.

   The data source is added to the list of configured ODBC data sources.

8. Click OK to dismiss the ODBC Data Source Administrator.

   You're all done.

Setting up the MySQL JDBC connector

An alternative to using ODBC is to use a database connector, which is a driver provided by your database vendor. Database connectors are designed to work exclusively with a specific type of database. As a result, they're considerably more efficient and powerful than ODCB.

You have to obtain the JDBC connector for the database you're using from the company that makes the database server you're using. For example, you can get a JDBC connector for MySQL from the MySQL Web site at http://www.mysql.com. Along with the driver, you get detailed instructions on how to set it up. But the following procedure works for a simple testing environment:

1. Download the driver from http://www.mysql.com/products/connector and unzip it.

   The driver you're looking for is called MySQL Connector/J. After you download it from MySQL's Web site, unzip the files to any folder you wish. (I suggest using one with a simple pathname, such as c:MySql.)

2. Add the driver's .jar file to your ClassPath variable.

   To change the ClassPath, open Control Panel and double-click System. Then click the Advanced tab, and then click Environment Variables. You can then click New to add a new environment variable. The ClassPath variable has to specify the complete path for the connector's jar file. For example, here's a sample ClassPath variable for a driver located in c:mysql:

   .;c:mysqlmysql-connector-java-5.0.4-bin.jar

   Notice that the ClassPath variable starts with a period and a semicolon. This ensures that Java can find classes that are in the current directory.

   Tip

   If the ClassPath variable already exists, just add the connector's jar file to the end of the existing text.

That's all you have to do. You can now connect to MySQL from a Java program.

Connecting to a Database

Before you can use JDBC to access a SQL database, you must first establish a connection to the database. The first step to establishing a connection involves registering the driver class so the class is available. To do that, you use the forName method of the Class class, specifying the package and class name of the driver. For example, to register the MySQL connector, use this statement:

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

To register the standard ODBC driver, use this statement instead:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Note that the forName method throws ClassNotFoundException, so you have to enclose this statement in a try/catch block that catches ClassNotFoundException.

After you register the driver class, you can call the static getConnection method of the DriverManager class to open the connection. This method takes three String parameters: the database URL, the user name, and a password. Here's an example:

String url = "jdbc:mysql://localhost/Movies"; String user = "root"; String pw = "pw"; con = DriverManager.getConnection(url, user, pw);

The URL parameter has the following syntax:

jdbc:subprotocol:subname

where subprotocol is mysql for a MySQL database and odbc for an ODBC driver. The subname is the database name. For a MySQL database, this can be a complete URL, but for a database on your own computer, you just specify //localhost/ plus the name of the database.

For ODBC, you use the name you used when you created the data source. For example

String url = "jdbc:odbc:Movies";

The user and password parameters must also be valid for the database server you're using. For testing purposes on a MySQL database, you can use root and the password you created when you installed MySQL. For ODBC, use admin with no password for testing.

Note that the getConnection method throws SQLException, so you need to enclose it in a try/catch block statement that catches this exception.

Putting it all together, here's a method that returns a Connection object that connects to the movies database in MySQL:

private static Connection getConnection() { Connection con = null; try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost/Movies"; String user = "root"; String pw = "NuttMutt"; con = DriverManager.getConnection(url, user, pw); } catch (ClassNotFoundException e) { System.out.println(e.getMessage()); System.exit(0); } catch (SQLException e) { System.out.println(e.getMessage()); System.exit(0); } return con; }

Querying a Database

After you establish a connection to a database, you can execute select statements to retrieve data. To do so, you have to use several classes and interfaces:

• Connection: The Connection class has two methods you're likely to use. The close method closes the connection, and the createStatement method returns a Statement object, which you then use to execute statements.
• Statement: The Statement interface contains the methods necessary to send statements to the database for execution and return the results. In particular, you use the executeQuery method to execute a select statement or the executeUpdate method to execute an insert, update, or delete statement.
• ResultSet: The ResultSet interface represents rows returned from a query. It provides methods you can use to move from row to row and to get the data for a column.

Table 4-1 lists the methods of the Connection class and the Statement interface you use to execute queries. (You find out about the many methods of the ResultSet interface later in this chapter, in the section "Navigating through the result set.")

The first parameter of the createStatement method specifies the type of result set that is created, and can be one of the following:

ResultSet.TYPE_FORWARD_ONLY ResultSet.TYPE_SCROLL_INSENSITIVE ResultSet.TYPE_SCROLL_SENSITIVE

The second parameter indicates whether the result set is read-only or updatable, and can be one of the following:

ResultSet.CONCUR_READ_ONLY ResultSet.CONCUR_UPDATABLE

Executing a select statement

The following snippet executes a select statement and gets the result set:

Statement s = con.createStatement(); String select = "Select title, year, price " + "from movie order by year"; ResultSet rows = s.executeQuery(select);

Here the result set is stored in the rows variable.

Navigating through the result set

The ResultSet object returned by the executeQuery statement contains all the rows that are retrieved by the select statement. You can only access one of those rows at a time. The result set maintains a pointer called a cursor to keep track of the current row. You can use the methods shown in Table 4-2 to move the cursor through a result set.

For example, the following snippet shows how you can structure code that processes each row in a result set:

while(rows.next()) { // process the current row }

All you have to do is replace the comment with statements that retrieve data from the result set and process it, as described in the next section.

Getting data from a result set

Table 4-3 lists the methods of the ResultSet interface you can use to retrieve data from the current row. As you can see, each of these methods comes in two versions: One specifies the column by name, the other by index number. If you know the index number, using it to access the column values is more efficient than using the column names.

Here's a bit of code that gets the title, year, and price for the current row:

String title = row.getString("title"); int year = row.getInt("year"); double price = row.getDouble("price");

The following code does the same thing, assuming the columns appear in order:

String title = row.getString(1); int year = row.getInt(2); double price = row.getDouble(3);

Note that unlike almost every other index in Java, column indexes start with 1, not zero.

Putting it all together A program that reads from a database

Now that you've seen the various elements that make up a program that uses JDBC to query a database, Listing 4-1 shows a program that reads data from the movies database and lists it on the console. When you run this program, the following appears on the console:

1946: It's a 1965: The Great Race ($14.25) 1974: Young Frankenstein ($18.65) 1975: The Return of the Pink Panther ($13.15) 1977: Star Wars ($19.75) 1987: The Princess Bride ($18.65) 1989: Glory ($16.45) 1995: Apollo 13 ($20.85) 1997: The Game ($16.45) 2001: The Lord of the Rings: The Fellowship of the Ring ($21.95)

Listing 4-1: The Movie Listing Program

import java.sql.*; import java.text.NumberFormat; public class ListMovies { public static void main(String[] args) →6 { NumberFormat cf = NumberFormat.getCurrencyInstance(); ResultSet movies = getMovies(); try { while (movies.next()) { Movie m = getMovie(movies); String msg = Integer.toString(m.year); msg += ": " + m.title; msg += " (" + cf.format(m.price) + ")"; System.out.println(msg); } } catch (SQLException e) { System.out.println(e.getMessage()); } } private static ResultSet getMovies() →28 { Connection con = getConnection(); try { Statement s = con.createStatement(); String select = "Select title, year, price " + "from movie order by year"; ResultSet rows; rows = s.executeQuery(select); return rows; } catch (SQLException e) { System.out.println(e.getMessage()); } return null; } private static Connection getConnection() →46 { Connection con = null; try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost/Movies"; String user = "root"; String pw = "NuttMutt"; con = DriverManager.getConnection(url, user, pw); } catch (ClassNotFoundException e) { System.out.println(e.getMessage()); System.exit(0); } catch (SQLException e) { System.out.println(e.getMessage()); System.exit(0); } return con; } private static Movie getMovie(ResultSet movies) → 70 { try { String title = movies.getString("Title"); int year = movies.getInt("Year"); double price = movies.getDouble("Price"); return new Movie(title, year, price); } catch (SQLException e) { System.out.println(e.getMessage()); } return null; } private static class Movie → 86 { public String title; public int year; public double price; public Movie(String title, int year, double price) { this.title = title; this.year = year; this.price = price; } } }

The following paragraphs describe the basics of how this program works:

Updating SQL Data

Besides executing select statements, you can also use a Statement object to execute insert, update, or delete statements as well. To do that, you call the executeUpdate method instead of the executeQuery method. This method returns an int value that indicates how many rows were updated. You can test the return value to determine whether the data was properly updated.

For example, here's a method that accepts a movie ID, last name, and first name, and then inserts a row into the friend table:

private static void loanMovie(int id, String lastName, String firstName) { Connection con = getConnection(); try { Statement stmt = con.createStatement(); String insert = "insert into friend " + "(lastname, firstname, movieid) " + "values (" + """ + lastName + "", "" + firstName + "", " + + id + ")"; int i = stmt.executeUpdate(insert); if (i == 1) System.out.println("Loan recorded."); else System.out.println("Loan not recorded."); } catch (SQLException e) { System.out.println(e.getMessage()); System.exit(0); } }

The getConnection method called at the start of this method is the same getConnection method in Listing 4-1. After a connection is created, a Statement object is created, and an insert statement is constructed using the values passed via the parameters. For example, if you pass id 3, last name Haskell, and first name Eddie, the following insert statement is built:

Insert into friend (lastname, firstname, movieid) Values ("Haskell", "Eddie", 3)

Then the executeUpdate method is called to execute the insert statement. An if statement is used to determine whether or not the row is inserted.

You can execute update or delete statements in the same manner.

Using an Updatable RowSet Object

If you're using a newer JDBC driver (one that supports JDBC 2.0 or later), you have another option for updating data: with an updatable result set. With an updatable result set, you can change data in a result set row, add a row to the result set, or delete a row from the result set. When you do, the updates are automatically written back to the underlying database.

To create an updatable result set, you must specify the ResultSet. CONCUR_UPDATABLE field on the createStatement method when you create the Statement object, like this:

Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.TYPE_CONCUR_UPDATABLE);

The first parameter indicates that the result set is scrollable, which means you can move the cursor backward as well as forward through the result set. You can use the methods listed in Table 4-4 to scroll the result set. This parameter also indicates that the result set can be synchronized with the database so that any changes made by other users are reflected in the result set.

The second parameter indicates that the result set is updatable, and any changes you make to the result set are automatically written back to the database. You can use any of the methods listed in Table 4-5 to update the result set, and thus update the underlying database.

Deleting a row

To delete a row from a result set, use one of the navigation methods in Table 4-4 to move to the row you want to delete, and then use the deleteRow method to delete the row. For example, here's code that deletes the third row in the result set:

try { rs.absolute(3); rs.deleteRow(); } catch (SQLException e) { System.out.println(e.getMessage()); System.exit(0); }

Updating the value of a row column

To update the value of a row column, navigate to the row you want to update, and then use one of the updater methods listed in Table 4-6 to change one or more column values. Finally, call updateRow to apply the changes.

For example:

try { rs.absolute(6); rs.updateInt("year", 1975); rs.updateRow(); } catch (SQLException e) { System.out.println(e.getMessage()); System.exit(0); }

Here the year column of the sixth row in the result set is changed to 1975.

Inserting a row

To insert a row, you use a special row in the result set called the insert row. First, you call the moveToInsertRow method to move the cursor to the insert row. Then you use update methods to set the value for each column in the insert row. You then call the insertRow method to copy the insert row into the result set, which in turn writes a new row to the database. And finally, you call moveToCurrentRow to move back to the previous position in the result set.

Here's an example:

try { rs.moveToInsertRow(); rs.updateString("title", "Monty Python and the Holy Grail"); rs.updateInt("year", 1975); rs.updateDouble("price", 13.95); rs.insertRow(); rs.moveToCurrentRow(); } catch (SQLException e) { System.out.println(e.getMessage()); System.exit(0); }