Java

Reading and Writing Data Using JDBC and XML

For those who want to delve deeply into Java, this article explores Java Database Connectivity (JDBC), a class library that connects Java programs to relational databases. The first of three parts, it is excerpted from chapter 20 of the book Sams Teach Yourself Java 2 in 21 Days, 4th Edition, written by Rogers Cadenhead and Laura Lemay (Sams; ISBN: 0672326280).

Reading and Writing Data Using JDBC and XML - Writing Data to a Database Using SQL(Page 5 of 5 )

In the CoalTotals application, you retrieved data from a database using an SQL statement prepared as a string, like this:

SELECT * FROM Coal WHERE (Country='Swaziland')
ORDER BY YEAR

This is a common way to use SQL. You could write a program that asks a user to enter an SQL query and then displays the result (though this isn't a good idea—SQL queries can be used to delete records, tables, and even entire databases).

The java.sql package also supports another way to create an SQL statement: a prepared statement.

A prepared statement, which is represented by the PreparedStatement class, is an SQL statement that is compiled before it is executed. This enables the statement to return data more quickly and is a better choice if you are executing an SQL statement repeatedly in the same program.

Tip - Prepared statements also have another advantage on Windows systems: They make it possible to write data to a Microsoft Access database using the JDBC-ODBC driver. For several years, I've had no luck at all writing data from Java to Access using statements, but I can use prepared statements without any trouble. I can't figure out why. I'm hoping another author writes a book titled Teach Yourself Why Microsoft Access Hates My Unprepared SQL Statements in 21 Days.

To create a prepared statement, call a connection's prepareStatement(String) method with a string that indicates the structure of the SQL statement.

To indicate the structure, you write an SQL statement in which parameters have been replaced with question marks.

Here's an example for a connection object called cc:

PreparedStatement ps = cc.prepareStatement(
"SELECT * FROM Coal WHERE (Country='?') ORDER BY
YEAR");

The question marks in these SQL statements are placeholders for data. Before you can execute the statement, you must put data in each of these places using one of the methods of the PreparedStatement class.

To put data into a prepared statement, you must call a method with the position of the placeholder followed by the data to insert.

For example, to put the string "Swaziland" in the first prepared statement, call the setString(int, String) method:

ps.setString(1, "Swaziland");

The first argument indicates the position of the placeholder, numbered from left to right. The first question mark is 1, the second is 2, and so on.

The second argument is the data to put in the statement at that position.

The following methods are available:

setAsciiStream(int, InputStream, int)—At the position indicated by the first argument, inserts the specified InputStream, which represents a stream of ASCII characters. The third argument indicates how many bytes from the input stream to insert.

setBinaryStream(int, InputStream, int)—At the position indicated by the first argument, inserts the specified InputStream, which represents a stream of bytes. The third argument indicates the number of bytes to insert from the stream.

setCharacterStream(int, Reader, int)—At the position indicated by the first argument, inserts the specified Reader, which represents a character stream. The third argument indicates the number of characters to insert from the stream.

setBoolean(int, boolean)—Inserts a boolean value at the position indicated by the integer.

setByte(int, byte)—Inserts a byte value at the indicated position.

setBytes(int, byte[])—Inserts an array of bytes at the indicated position.

setDate(int, Date)—Inserts a Date object (from the java.sql package) at the indicated position.

setDouble(int, double)—Inserts a double value at the indicated position.

setFloat(int, float)—Inserts a float value at the indicated position.

setInt(int, int)—Inserts an int value at the indicated position.

setLong(int, long)—Inserts a long value at the indicated position.

setShort(int, short)—Inserts a short value at the indicated position.

setString(int, String)—Inserts a String value at the indicated position.

There's also a setNull(int, int) method that stores SQL's version of a null (empty) value at the position indicated by the first argument.

The second argument to setNull() should be a class variable from the Types class in java.sql to indicate what kind of SQL value belongs in that position.

There are class variables for each of the SQL data types. This list, which is not complete, includes some of the most commonly used variables: BIGINT, BIT, CHAR, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, SMALLINT, TINYINT, and VARCHAR.

The following code puts a null CHAR value at the fifth position in a prepared statement called ps:

ps.setNull(5, Types.CHAR);

The next project demonstrates the use of a prepared statement to add stock quote data to a database. Quotes are collected from the Yahoo! Web site.

As a service to people who follow the stock market, Yahoo! offers a Download Spreadsheet link on its main stock quote page for each ticker symbol.

To see this link, look up a stock quote on Yahoo! or go directly to a page such as this one:

You can click this link to open the file or save it to a folder on your system. The file, which is only one line long, contains the stock's price and volume data saved at the last market close. Here's an example of what Sun's data looked like on March 8, 2004:

"SUNW",4.66,"3/8/2004","4:00pm",-0.14,4.76,4.79,
4.58,80934008

The fields in this data, in order, are the ticker symbol, closing price, date, time, price change since yesterday's close, daily low, daily high, daily open, and volume.

The QuoteData application uses each of these fields except one—the time, which isn't particularly useful because it's always the time the market closed.

The following takes place in the program:

The ticker symbol of a stock is taken as a command-line argument.

A QuoteData object is created with the ticker symbol as an instance variable called ticker.

The object's retrieveQuote() method is called to download the stock data from Yahoo! and return it as a String.

The object's storeQuote() method is called with that String as an argument. It saves the stock data to a database using a JDBC-ODBC connection.

The last task requires a stock quote database, which can be reached through JDBC-ODBC, set up to collect this data.

Windows users can download quotedata.mdb, a Microsoft Access 2000 database created to hold Yahoo!'s stock quote data, from the book's Web site. Visit http://www.java21days.com and open the Day 20 page. After you download the database (or create one of your own), use the ODBC Data Source administrator to create a new data source associated with the database. This application assumes that the name of the source is QuoteData.

Enter the text of Listing 20.2 into your editor and save the file as QuoteData.java.

After you compile the QuoteData application, connect to the Internet and run the program. Remember to specify a valid ticker symbol as a command-line argument. To load the current quote for SUNW (Sun Microsystems):

java QuoteData SUNW

The retrieveQuote() method (lines 13–33) downloads the quote data from Yahoo! and saves it as a string. The techniques used in this method were covered on Day 17, "Communicating Across the Internet."

The storeQuote() method (lines 35–66) uses the SQL techniques covered in this section.

The method begins by splitting up the quote data into a set of string tokens, using the , character as the delimiter between each token. The tokens are then stored in a String array with nine elements.

The array contains the same fields as the Yahoo! data in the same order: ticker symbol, closing price, date, time, price change, low, high, open, and volume.

Next, a data connection to the QuoteData data source is created using the JDBC-ODBC driver (lines 41–45).

This connection is then used to create a prepared statement (lines 46–50). This statement uses the INSERT INTO SQL statement, which causes data to be stored in a database. In this case, the database is quotedata.mdb, and the INSERT INTO statement refers to the Stocks table in that database.

Eight placeholders are in the prepared statement. Only eight are needed, instead of nine, because the application does not use the time field from the Yahoo! data.

A series of setString() methods puts the elements of the String array into the prepared statement, in the same order that the fields exist in the database: ticker symbol, closing price, date, price change, low, high, open, and volume (lines 51–58).

Some fields in the Yahoo! data are dates, floating-point numbers, and integers, so you might think that it would be better to use setDate(), setFloat(), and setInt() for that data.

However, Microsoft Access 2000 does not support some of these methods when you are using SQL to work with the database, even though they exist in Java. If you try to use an unsupported method, such as setFloat(), an SQLException error occurs.

It's easier to send Access strings and let the database program convert them automatically into the right format. This is likely to be true when you are working with other databases; the level of SQL support varies based on the product and ODBC driver involved.

After the prepared statement has been prepared and all the placeholders are filled, the statement's executeUpdate() method is called (line 59). This either adds the quote data to the database or throws an SQL error. The private method stripQuotes() is used to remove quotation marks from Yahoo!'s stock data. This method is called on line 39 to take care of three fields that contain extraneous quotes: the ticker symbol, date, and time.