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 - 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: SamsTeach 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
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)由eturns the Date value stored in the specified field name (using the Date class in the java.sql package, not java.util.Date)
getDouble(String)由eturns the double value stored in the specified field name
getFloat(String)由eturns the float value stored in the specified field name
getInt(String)由eturns the int value stored in the specified field name
getLong(String)由eturns the long value stored in the specified field name
getString(String)由eturns 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.
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.