Reading and Writing Data Using JDBC and XML - Retrieving Data from a Database Using SQL
(Page 4 of 5 )
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.
Caution - It's beyond the scope of today's lesson to teach SQL, a rich data retrieval and storage language that has its own book in this series: Sams Teach Yourself SQL in 21 Days by Richard Waymire and Rick Sawtell (ISBN 0-67232-469-5). Although you need to learn SQL to do any extensive work with it, much of the language is easy to pick up from any examples you can find, such as those you will work with today.
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.
Note - To add records to a database instead of retrieving them, the statement's executeUpdate() method should be called. You will work with this later.
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:
getDate(String)—Returns the Date value stored in the specified field name (using the Date class in the java.sql package, not java.util.Date)
getDouble(String)—Returns the double value stored in the specified field name
getFloat(String)—Returns the float value stored in the specified field name
getInt(String)—Returns the int value stored in the specified field name
getLong(String)—Returns the long value stored in the specified field name
getString(String)—Returns the String stored in the specified field name
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 20.1 contains the CoalTotals 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.
Listing 20.1 The Full Text of
CoalTotals.java 1: import java.sql.*;
2:
3: public class CoalTotals {
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\tCOUNTRY
\t\tYEAR\t" +
18: "ANTHRACITE PRODUCTION");
19: while(rec.next()) {
20: System.out.println(rec.getString(1) +
"\t"
21: + rec.getString(2) + "\t\t"
22: + rec.getString(3) + "\t"
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 SDK:
java CoalTotals 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.
Next: Writing Data to a Database Using SQL >>
More Java Articles
More By Sams Publishing
|
 This article 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). Check it out today at your favorite bookstore. Buy this book now.
|
|
