Java Tutorial - Java Script :
The
The JDBC-ODBC Bridge
Retrieving Data from a Database Using SQL
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 YearThis 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
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:
· 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 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.
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
Try running the program with other countries that produce anthracite, such as France , Swaziland , and New Zealand
