An SQL statement is represented in Java by a Statement object. Statement is an interface, so it can’t be instantiated directly. However, an object that implements the interface is returned by the createStatement() method of a Connection object, as in the following example:

Statement lookSee = payday.CreateStatement();

After you have a Statement object, you can use it to conduct an SQL query by calling the object’s executeQuery(String) method. The String argument should be an SQL query that follows the syntax of that language.

The following is an example of an SQL query that could be used on the Coal table of the world20.mdb database:

SELECT Country, Year, 'Anthracite Production' FROM Coal
    WHERE (Country Is Not Null) ORDER BY Year

This SQL query retrieves several fields for each record in the database for which the Country field is not equal to null. The records returned are sorted according to their Country field, so Afghanistan would precede Burkina Faso.

The following Java statement executes that query on a Statement object named looksee:

ResultSet set = looksee.executeQuery(
    "SELECT Country, Year, 'Anthracite Production' FROM Coal "
    + "WHERE (Country Is Not Null) ORDER BY Year");

If the SQL query has been phrased correctly, the executeQuery() method returns a ResultSet object holding all the records that have been retrieved from the data source.

When a ResultSet is returned from executeQuery(), it is positioned at the first record that has been retrieved. The following methods of ResultSet can be used to pull information from the current record:

These are just the simplest methods available in the ResultSet interface. The methods you should use depend on the form that the field data takes in the database, although methods such as getString() and getInt() can be more flexible in the information they retrieve from a record.

You also can use an integer as the argument to any of these methods, such as getString(5), instead of a string. The integer indicates which field to retrieve (1 for the first field, 2 for the second field, and so on).

An SQLException is thrown if a database error occurs as you try to retrieve information from a resultset. You can call this exception’s getSQLState() and getErrorCode() methods to learn more about the error.

After you have pulled the information you need from a record, you can move to the next record by calling the next() method of the ResultSet object. This method returns a false Boolean value when it tries to move past the end of a resultset.

Normally, you can move through a resultset once from start to finish, after which you can’t retrieve its contents again.

When you’re finished using a connection to a data source, you can close it by calling the connection’s close() method with no arguments.

Listing 18.1 contains the CoalReporter application, which uses the JDBC-ODBC bridge and an SQL statement to retrieve some records from an energy database. Four fields are retrieved from each record indicated by the SQL statement: FIPS, Country, Year, and Anthracite Production. The resultset is sorted according to the Year field, and these fields are displayed to standard output.

 1: import java.sql.*;
 2:
 3: public class CoalReporter {
 4:     public static void main(String[] arguments) {
 5:        String data = "jdbc:odbc:WorldEnergy";
 6:        try {
 7:            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 8:            Connection conn = DriverManager.getConnection(
 9:                data, "", "");
10:            Statement st = conn.createStatement();
11:            ResultSet rec = st.executeQuery(
12:                "SELECT * " +
13:                "FROM Coal " +
14:                "WHERE " +
15:                "(Country='" + arguments[0] + "') " +
16:                "ORDER BY Year");
17:            System.out.println("FIPS	COUNTRY		YEAR	" +
18:                "ANTHRACITE PRODUCTION");
19:            while(rec.next()) {
20:                System.out.println(rec.getString(1) +  "	"
21:                        + rec.getString(2) + "		"
22:                        + rec.getString(3) + "	"
23:                        + rec.getString(4));
24:            }
25:            st.close();
26:        } catch (SQLException s) {
27:            System.out.println("SQL Error: " + s.toString() + " "
28:                + s.getErrorCode() + " " + s.getSQLState());
29:       } catch (Exception e) {
30:           System.out.println("Error: " + e.toString()
31:               + e.getMessage());
32:       }
33:    }
34: }

This program must be run with a single argument specifying the Country field in the database from which to pull records, as in this example for the JDK:

java CoalReporter Poland

If the application were run with an argument of Poland, the output from the sample database would be the following:

FIPS   COUNTRY    YEAR    ANTHRACITE PRODUCTION
PL     Poland     1990    0.0
PL     Poland     1991    0.0
PL     Poland     1992    0.0
PL     Poland     1993    174.165194805424
PL     Poland     1994    242.50849909616
PL     Poland     1995    304.237935229728
PL     Poland     1996    308.64718066784
PL     Poland     1997    319.67029426312
PL     Poland     1998    319.67029426312

Try running the program with other countries that produce anthracite, such as France, Swaziland, and New Zealand. For any country that has a space in the name, remember to put quotation marks around the country name when running the program.

In the CoalReporter 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.

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");

Here’s another example with more than one question mark:

PreparedStatement ps = cc.prepareStatement(
    "INSERT INTO BOOKDATA VALUES(?, ?, ?, ?, ?, ?, ?)");

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:

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! website.

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:

http://quote.yahoo.com/q?s=sunw&d=v1

Below the price chart, you can find a Download Data link. Here’s what the link looks like for Sun Microsystems:

http://download.finance.yahoo.com/d/quotes.csv?s=SUNW&f=sl1d1t1c1ohgv&e=.csv

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 Feb. 23, 2007:

"SUNW",6.27,"2/23/2007","4:00pm",0.00,6.30,6.31,6.22,50254356

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 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, an Access 2000 database created to hold Yahoo!’s stock quote data, from the book’s website. Visit http://www.java21days.com and open the Day 18 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 18.2 into your editor and save the file as QuoteData.java.

 1: import java.io.*;
 2: import java.net.*;
 3: import java.sql.*;
 4: import java.util.*;
 5:
 6: public class QuoteData {
 7:     private String ticker;
 8:
 9:     public QuoteData(String inTicker) {
10:        ticker = inTicker;
11:    }
12:
13:    private String retrieveQuote() {
14:        StringBuffer buf = new StringBuffer();
15:        try {
16:            URL page = new URL("http://quote.yahoo.com/d/quotes.csv?s=" +
17:               ticker + "&f=sl1d1t1c1ohgv&e=.csv");
18:            String line;
19:            URLConnection conn = page.openConnection();
20:            conn.connect();
21:            InputStreamReader in= new InputStreamReader(
22:                conn.getInputStream());
23:            BufferedReader data = new BufferedReader(in);
24:            while ((line = data.readLine()) != null) {
25:                buf.append(line + "
");
26:            }
27:        } catch (MalformedURLException mue) {
28:            System.out.println("Bad URL: " + mue.getMessage());
29:        } catch (IOException ioe) {
30:            System.out.println("IO Error:" + ioe.getMessage());
31:        }
32:        return buf.toString();
33:     }
34:
35:     private void storeQuote(String data) {
36:         StringTokenizer tokens = new StringTokenizer(data, ",");
37:         String[] fields = new String[9];
38:         for (int i = 0; i < fields.length; i++) {
39:             fields[i] = stripQuotes(tokens.nextToken());
40:         }
41:         String datasource = "jdbc:odbc:QuoteData";
42:         try {
43:             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
44:             Connection conn = DriverManager.getConnection(
45:                 datasource, "", "");
46:             PreparedStatement prep2 = conn.prepareStatement(
47:                 "INSERT INTO " +
48:                 "Stocks(ticker, price, quoteDate, change, open, " +
49:                 "high, low, volume) " +
50:                 "VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
51:             prep2.setString(1, fields[0]);
52:             prep2.setString(2, fields[1]);
53:             prep2.setString(3, fields[2]);
54:             prep2.setString(4, fields[4]);
55:             prep2.setString(5, fields[5]);
56:             prep2.setString(6, fields[6]);
57:             prep2.setString(7, fields[7]);
58:             prep2.setString(8, fields[8]);
59:             prep2.executeUpdate();
60:             conn.close();
61:         } catch (SQLException sqe) {
62:             System.out.println("SQL Error: " + sqe.getMessage());
63:         } catch (ClassNotFoundException cnfe) {
64:             System.out.println(cnfe.getMessage());
65:         }
66:     }
67:
68:     private String stripQuotes(String input) {
69:         StringBuffer output = new StringBuffer();
70:         for (int i = 0; i < input.length(); i++) {
71:             if (input.charAt(i) != '"') {
72:                 output.append(input.charAt(i));
73:             }
74:         }
75:         return output.toString();
76:     }
77:
78:     public static void main(String[] arguments) {
79:         if (arguments.length < 1) {
80:             System.out.println("Usage: java QuoteData tickerSymbol");
81:             System.exit(0);
82:         }
83:         QuoteData qd = new QuoteData(arguments[0]);
84:         String data = qd.retrieveQuote();
85:         qd.storeQuote(data);
86:     }
87: }

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

Some versions of Access, including Access 2000, do 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 correct 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.

The default behavior of resultsets permits one trip through the set using its next() method to retrieve each record.

By changing how statements and prepared statements are created, you can produce resultsets that support these additional methods:

These actions are possible when the resultset’s policies have been specified as arguments to a database connection’s createStatement() and prepareStatement() methods.

Normally, createStatement() takes no arguments, as in this example:

Connection payday = DriverManager.getConnection(
    "jdbc:odbc:Payroll", "Doc", "1rover1");
Statement lookSee = payday.CreateStatement();

For a more flexible resultset, call createStatement() with three integer arguments that set up how it can be used. Here’s a rewrite of the preceding statement:

Statement lookSee = payday.CreateStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY,
    ResultSet.CLOSE_CURSORS_AT_COMMIT);

The same three arguments can be used in the prepareStatement(String, int, int, int) method after the text of the statement.

The ResultSet class includes other class variables that offer more options in how sets can be read and modified.