Home arrow Java arrow Page 4 - Reading and Writing Data Using JDBC and XML

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

Author Info:
By: Sams Publishing
Rating: 4 stars4 stars4 stars4 stars4 stars / 9
June 08, 2006
  1. · Reading and Writing Data Using JDBC and XML
  2. · Database Drivers
  3. · Connecting to an ODBC Data Source
  4. · Retrieving Data from a Database Using SQL
  5. · Writing Data to a Database Using SQL

print this article

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)由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.

Listing 20.1 The Full Text of CoalTotals.java
 1: import java.sql.*;
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:

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.

blog comments powered by Disqus

- Java Too Insecure, Says Microsoft Researcher
- Google Beats Oracle in Java Ruling
- Deploying Multiple Java Applets as One
- Deploying Java Applets
- Understanding Deployment Frameworks
- Database Programming in Java Using JDBC
- Extension Interfaces and SAX
- Entities, Handlers and SAX
- Advanced SAX
- Conversions and Java Print Streams
- Formatters and Java Print Streams
- Java Print Streams
- Wildcards, Arrays, and Generics in Java
- Wildcards and Generic Methods in Java
- Finishing the Project: Java Web Development ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials