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.

    image from book
    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.

    image from book
    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;
}
  REMEMBER 

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
Open table as spreadsheet

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

Table 4-2: Navigation Methods of the ResultSet Interface
Open table as spreadsheet

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.

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.

Table 4-3: Get Methods of the ResultSet Interface
Open table as spreadsheet

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 a int.

int getInt (int columnIndex)

Gets the value of the specified column as a 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.

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:

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.


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.

  Tip 

While you're testing database code that executes SQL statements constructed from strings like this, throw in a System.out.println call to print the statement to the console. That way, you can verify that the statement is being created properly.


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.

Table 4-4: Methods for Scrollable Result Sets
Open table as spreadsheet

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
Open table as spreadsheet

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. 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);
}
Table 4-6: Update Methods of the ResultSet Interface
Open table as spreadsheet

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)

Update by Column Name

Update by Column Index

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


Book I - Java Basics

Book II - Programming Basics

Book III - Object-Oriented Programming

Book IV - Strings, Arrays, and Collections

Book V - Programming Techniques

Book VI - Swing

Book VII - Web Programming

Book VIII - Files and Databases

Book IX - Fun and Games



Java All-In-One Desk Reference For Dummies
Java All-In-One Desk Reference For Dummies
ISBN: 0470124512
EAN: 2147483647
Year: 2004
Pages: 332

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net