Using JDBC to Connect to a Database

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, use the forName method of the Class class, specifying the package and class name of the driver. 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 username, 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, 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.

Note: In the examples that follow, I assume the root password is PassWord , but you should use whatever 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 = "PassWord";
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;
}

You can find these classes — and the other classes for working with SQL databases — in the java.sql package. As a result, you have to include an import statement that specifies this package in any program that uses JDBC.

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 .”)

TABLE 4-1 Connection and Statement Methods

Connection Class Method	Description
`void close()`	Closes the connection.
`Statement createStatement()`	Creates a `Statement` object that can execute a SQL statement on the database connected by the connection.
`Statement createStatement(int type, int concur)`	Creates a `Statement` object that can execute a SQL statement on the database connected by the connection.
Statement Interface Methods	Description
`ResultSet executeQuery(String sql)`	Executes the `select` statement contained in the string parameter and returns the result data as a `ResultSet` object.
`ResultSet executeQuery(String sql)`	Executes the `select` statement contained in the string parameter and returns the result data as a `ResultSet` object.
`int executeUpdate(String sql)`	Executes the `insert` , `update` , or `delete` statements contained in the string parameter and returns the result data as a `ResultSet` object.

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 access only 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.

TABLE 4-2 Navigation Methods of the ResultSet Interface

Method	Description
`void close()`	Closes the result set.
`void last()`	Moves the cursor to the last row.
`int getRow()`	Gets the current row number.
`boolean next()`	Moves to the next row.

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.

TABLE 4-3 Get Methods of the ResultSet Interface

Method	Description
`BigDecimal getBigDecimal(String columnName)`	Gets the value of the specified column as a `BigDecimal` .
`BigDecimal getBigDecimal(int columnIndex)`	Gets the value of the specified column as a `BigDecimal` .
`boolean getBoolean(String columnName)`	Gets the value of the specified column as a `boolean` .
`boolean getBoolean(int columnIndex)`	Gets the value of the specified column as a `boolean` .
`Date getDate(String columnName)`	Gets the value of the specified column as a `Date` .
`Date getDate(int columnIndex)`	Gets the value of the specified column as a `Date` .
`double getDouble(String columnName)`	Gets the value of the specified column as a `double` .
`double getDouble(int columnIndex)`	Gets the value of the specified column as a `double` .
`float getFloat(String columnName)`	Gets the value of the specified column as a `float` .
`float getFloat(int columnIndex)`	Gets the value of the specified column as a `float` .
`int getInt(String columnName)`	Gets the value of the specified column as an `int` .
`int getInt(int columnIndex)`	Gets the value of the specified column as an `int` .
`long getLong(String columnName)`	Gets the value of the specified column as a `long` .
`long getLong(int columnIndex)`	Gets the value of the specified column as a `long` .
`short getShort(String columnName)`	Gets the value of the specified column as a `short` .
`short getShort(int columnIndex)`	Gets the value of the specified column as a `short` .
`String getString(String columnName)`	Gets the value of the specified column as a `String` .
`String getString(int columnIndex)`	Gets the value of the specified column as a `String` .

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 0.

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 Wonderful Life ($16.45)
1974: Young Frankenstein ($18.65)
1977: Star Wars ($19.75)
1987: The Princess Bride ($18.65)
1989: Glory ($16.45)
1997: The Game ($16.45)
1998: Shakespeare in Love ($21.95)
2009: Zombieland ($20.85)
2010: The King's Speech ($19.64)
2013: Star Trek Into Darkness ($21.95)

Note that you may also get a message suggesting that you should install an SSL certificate to provide more secure access to the MySQL database. For the purpose of testing JDBC, you can ignore this message.

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 = "PassWord";
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:

→6 The main method begins by calling the getMovies method to get a ResultSet object that contains the movies to be listed. Then a while loop reads each row of the result set. The getMovie method is called to create a Movie object from the data in the current row. Then an output string is created and sent to the console. The loop is contained in a try/catch statement because the next method may throw SQLException .
→28 The getMovies method is responsible for getting a database connection, and then querying the database to get the movies. The first task is delegated to the getConnection method. Then a Statement is created and executed with the following select statement:
select title, year, price from movie order by year

Then the result set is returned to the main method.
→46 The getConnection method creates a Connection object to the database. Note that the user ID and password are hard-coded into this method. In a real application, you get this information from the user or from a configuration file.
→70 The getMovie method extracts the title, year, and price from the current row and uses these values to create a Movie object.
→86 The Movie class is created as an inner class. To keep this application simple, this class uses public fields and a single constructor that initializes the fields with the values passed as parameters.

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 and 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.

TABLE 4-4 Methods for Scrollable Result Sets

Method	Description
`boolean absolute(int row)`	Moves the cursor to the given row number in this `ResultSet` object.
`void afterLast()`	Moves the cursor to the end of this `ResultSet` object, just after the last row.
`void beforeFirst()`	Moves the cursor to the front of this `ResultSet` object, just before the first row.
`boolean first()`	Moves the cursor to the first row in this `ResultSet` object.
`boolean last()`	Moves the cursor to the last row in this `ResultSet` object.
`boolean next()`	Moves the cursor down one row from its current position.
`boolean previous()`	Moves the cursor to the previous row in this `ResultSet` object.
`boolean relative(int rows)`	Moves the cursor a relative number of rows, either positive or negative.

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.

TABLE 4-5 Methods for Updatable Result Sets

Method	Description
`void cancelRowUpdates()`	Cancels the updates made to the current row in this `ResultSet` object.
`void deleteRow()`	Deletes the current row from this `ResultSet` object and from the underlying database.
`void insertRow()`	Inserts the contents of the insert row into this `ResultSet` object and into the database.
`void moveToCurrentRow()`	Moves the cursor to the remembered cursor position, usually the current row.
`void moveToInsertRow()`	Moves the cursor to the insert row.
`void refreshRow()`	Refreshes the current row with its most recent value in the database.
`void updateRow()`	Updates the underlying database with the new contents of the current row of this `ResultSet` object.

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. 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);
}

TABLE 4-6 Update Methods of the ResultSet Interface

Update by Column Name	Update by Column Index
`void updateBigDecimal(String columnName, BigDecimal value)`	`void updateBigDecimal(int columnIndex, BigDecimal value)`
`void updateBoolean(String columnName, boolean value)`	`void updateBoolean(int columnIndex, boolean value)`
`void updateDate(String columnName, Date value)`	`void updateDate(int columnIndex, Date value)`
`void updateDouble(String columnName, double value)`	`void updateDouble(int columnIndex, double value)`
`void updateFloat(String columnName, float value)`	`void updateFloat(int columnIndex, float value)`
`void updateInt(String columnName, int value)`	`void updateInt(int columnIndex, int value)`
`void updateLong(String columnName, long value)`	`void updateLong(int columnIndex, long value)`
`void updateShort(String columnName, short value)`	`void updateShort(int columnIndex, short value)`
`void updateString(String columnName, String value)`	`void updateString(int columnIndex, String value)`

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, call the moveToInsertRow method to move the cursor to the insert row. Then 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, 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);
}