Reading Data Using JDBC

   

As you saw in the previous example, a Connection represents a session to a database. When it becomes necessary to read data from the database, the instance of the Connection is used, but indirectly. The main class that is used in conjunction with the Connection class is the java.sql.Statement class.

The Statement Class

The Statement class is used to execute static SQL statements and possibly read the results that were returned by the query. As the name implies, the Statement class represents a single SQL statement that needs to be executed against the database. You don't create a new instance of the Statement class directly. You use the instance of the connection as sort of a Statement Factory. You do this by calling the createStatement method that exists on the Connection class. This method will return an instance of the Statement class that you can use to execute SQL statements.

There are several other similar methods that also exist on the Connection class that are used for such things as creating an instance of a java.sql.PreparedStatement. A PreparedStatement is used for preparing a SQL statement for execution at a later time. This is more efficient that creating a new statement each time.

You'll see more of this class and others later in the "Using Prepared Statements" section.

For now, take a look at an example using the Statement class. The following example shows how to read a set of records from the employee database and display those records to the console. The DatabaseManager class from the previous example is reused throughout the examples to make it easier and more consistent when acquiring the initial connection to the database.

Note

You will see that creating a DatabaseManager type of class for your Java applications that access the database to be a very valuable idea. The complete functionality might be slightly different for various applications, but it makes sense to isolate the connection creation to a factory type of object. This makes it easier in the future if you have to support different databases, because all the connection information is located in one area, which tends to decrease the coupling your application has on any particular database platform.


Listing 26.5 shows the class called ReadEmployeeRecords. This class gets a connection to the database through the DatabaseManager class and then reads all the Employee records in the database. It then iterates through the records, printing out certain pieces of information about each employee. Listing 26.6 shows the source code for Employee.java that is used within the ReadEmployeeRecords.java example.

Listing 26.5 Source Code for ReadEmployeeRecords.java
 import java.sql.*; public class ReadEmployeeRecords {   // Private reference to an instance of the Connection   private Connection conn = null;   // Default Constructor   public ReadEmployeeRecords( Connection connection )   {     conn = connection;   }   // Public method to read all the records for the employees   public void readRecords()   {     // SQL Statement to get all the employees     String READ_EMPLOYEE_SQL_STMT = "SELECT * FROM EMP";     Statement stmt = null;     ResultSet rs = null;     try     {       stmt = getConnection().createStatement();       stmt.execute( READ_EMPLOYEE_SQL_STMT );       rs = stmt.getResultSet();     }     catch( SQLException ex )     {       ex.printStackTrace();     }     // Call the public method to print the employees out     printEmployeeRecords( rs );   }   // Private accessor for the connection   private Connection getConnection()   {     return conn;   }   // Use the ResultSet and print the records to the console   public void printEmployeeRecords( ResultSet rs )   {     try     {       // While there are more records to read from the ResultSet       while( rs.next() )       {         // Get the data by using the column index from the table.         // Could also have used the column names like         // rs.getString( "firstName" );         //         String nbr = rs.getString( 1 );         String name = rs.getString( 2 );         String job = rs.getString( 3 );         String mgr = rs.getString( 4 );         Timestamp hireDate = rs.getTimestamp(5);         Employee employee = new Employee( nbr, name, job, mgr, hireDate );         System.out.println( employee.toString() );       }     }     catch( SQLException ex )     {       ex.printStackTrace();     }   }   // Method to start this class and test the examples   public static void main(String[] args)   {     // Use the previous DatabaseManager     Connection conn = DatabaseManager.getConnection();     ReadEmployeeRecords reader = new ReadEmployeeRecords( conn );     reader.readRecords();     // Always make sure to close the connection when you are finished     try     {       conn.close();     }     catch( SQLException ex )     {       ex.printStackTrace();     }     catch( Exception ex )     {       ex.printStackTrace();     }   } } 
Listing 26.6 Source Code for Employee.java
 import java.sql.Timestamp; public class Employee implements java.io.Serializable {   // Private instance variables for an employee   private String employeeNbr = null;   private String name = null;   private String job = null;   private String mgr = null;   private Timestamp hireDate = null;   // Constructor   public Employee( String nbr, String name, String job,   String mgr, Timestamp hiredDate ) {     super();     employeeNbr = nbr;     this.name = name;     this.job = job;     this.mgr = mgr;     this.hireDate = hiredDate;   }   // Override the toString() method from object to display an employee   public String toString()   {     StringBuffer strBuf = new StringBuffer();     strBuf.append( "ID: " );     strBuf.append( getEmployeeNbr() );     strBuf.append( " -" );     strBuf.append( " Name: " );     strBuf.append( getName() );     strBuf.append( " -" );     strBuf.append( " Job: " );     strBuf.append( getJob() );     strBuf.append( " -" );     strBuf.append( " Mgr: " );     strBuf.append( getMgr() );     strBuf.append( " -" );     strBuf.append( " Hire Date: " );     if ( getHireDate() != null )     {       strBuf.append( getHireDate().toString() );     }     return strBuf.toString();   }   // Public Getters for the object's state   public String getEmployeeNbr()   {     return employeeNbr;   }   public String getName()   {     return name;   }   public String getJob()   {     return job;   }   public String getMgr()   {     return mgr;   }   public Timestamp getHireDate()   {     return hireDate;   } } 

Listing 26.7 shows the output when you run the previous ReadEmployeeRecords.java example.

Listing 26.7 ReadEmployeeRecords.java Output
 C:\jdk1.3se_book\classes>java ReadEmployeeRecords ID: 7839 - Name: KING       - Job: PRESIDENT - Mgr: null - Hire Date:   1981-11-17 00:00: graphics/ccc.gif 00.0 ID: 7698 - Name: BLAKE      - Job: MANAGER   - Mgr: 7839 - Hire Date:   1981-05-01 00:00: graphics/ccc.gif 00.0 ID: 7782 - Name: CLARK      - Job: MANAGER   - Mgr: 7839 - Hire Date:   1981-06-09 00:00: graphics/ccc.gif 00.0 ID: 7566 - Name: JONES      - Job: MANAGER   - Mgr: 7839 - Hire Date:   1981-04-02 00:00: graphics/ccc.gif 00.0 ID: 7654 - Name: MARTIN     - Job: SALESMAN  - Mgr: 7698 - Hire Date:   1981-09-28 00:00: graphics/ccc.gif 00.0 ID: 7499 - Name: ALLEN      - Job: SALESMAN  - Mgr: 7698 - Hire Date:   1981-02-20 00:00: graphics/ccc.gif 00.0 ID: 7844 - Name: TURNER     - Job: SALESMAN  - Mgr: 7698 - Hire Date:   1981-09-08 00:00: graphics/ccc.gif 00.0 ID: 7900 - Name: JAMES      - Job: CLERK     - Mgr: 7698 - Hire Date:   1981-12-03 00:00: graphics/ccc.gif 00.0 ID: 7521 - Name: WARD       - Job: SALESMAN  - Mgr: 7698 - Hire Date:   1981-02-22 00:00: graphics/ccc.gif 00.0 ID: 7902 - Name: FORD       - Job: ANALYST   - Mgr: 7566 - Hire Date:   1981-12-03 00:00: graphics/ccc.gif 00.0 ID: 7369 - Name: SMITH      - Job: CLERK     - Mgr: 7902 - Hire Date:   1980-12-17 00:00: graphics/ccc.gif 00.0 ID: 7788 - Name: SCOTT      - Job: ANALYST   - Mgr: 7566 - Hire Date:   1982-12-09 00:00: graphics/ccc.gif 00.0 ID: 7876 - Name: ADAMS      - Job: CLERK     - Mgr: 7788 - Hire Date:   1983-01-12 00:00: graphics/ccc.gif 00.0 ID: 7934 - Name: MILLER     - Job: CLERK     - Mgr: 7782 - Hire Date:   1982-01-23 00:00: graphics/ccc.gif 00.0 C:\jdk1.3se_book\classes> 

You might have noticed that when you retrieve data from the database, instead of just printing out the data, an Employee class is built. The reason this was done instead of just printing it out has to do with object oriented systems and specifically Object-to-Relational Mapping frameworks. You'll learn more about this later in this chapter and the next chapter, but just know that there is a method and a purpose to the madness.

After a Statement is created from the connection instance and the execute method is called on the Statement, a ResultSet object is associated with the Statement object. The ResultSet object is explained next.

The ResultSet Class

You can think of a ResultSet as a table of data that represents the result after executing a database query. It's considered a table because there can be multiple rows and usually there are multiple columns . Each column in the ResultSet represents a table column from the database. In the simple SQL statement that was executed in the ReadEmployeeRecords.java example from Listing 26.5, the ResultSet represents all the columns in the EMP table and all the rows from the table.

A ResultSet can be thought of as an open view to a set of data. It's open because the ResultSet maintains a cursor to a specific row in the data that was returned from the Statement execution. The cursor initially points to a position before the first row in the set of data and then can be moved around by calling the various methods in the ResultSet class. It wouldn't make sense to have it point to the first row, because there might be no data returned from the execution. You must use the next method to move to the first row. The next method moves the cursor to the next row in the set.

The next method returns a boolean value. It returns true if the row is valid and false if there are no more rows to read. You can see from the ReadEmployeeRecords example, iterating through the records in done by putting the next in a while loop and then iterating until the method returns false, which causes the while loop to end. There are other methods in the ResultSet that allow the cursor to be placed at a certain row. The default behavior of the ResultSet is to only allow moving forward through the rows and never backward. However, with the JDBC 2.0 API, you can setup a ResultSet to move both forward and backward. There are also new methods that allow the ResultSet to be updatable, rather than a read-only view as it was in the 1.0 JDBC API. These new features are discussed more in the next chapter.

As you probably noticed, the next method does not retrieve the data from the ResultSet. It is used only for positioning within the set of rows. To retrieve the actual data that exists in the ResultSet, you have to use one of the many getXXX methods defined in the ResultSet class. There is a method for just about any type that can be stored in the database.

Table 26.4. getXXX Methods in java.sql.ResultSet class
Return Type Method Parameter
Array getArray (int index)
Array getArray (String name)
InputStream getArray (int index)
InputStream getAsciiStream (String name)
BigDecimal getBigDecimal (int index)
BigDecimal getBigDecimal (String name)
InputStream getBinaryStream (int index)
InputStream getBinaryStream (String name)
Blob getBlob (int index)
Blob getBlob (String name)
boolean getBoolean (int index)
boolean getBoolean (String name)
byte getByte (int index)
byte getByte (String name)
byte[] getBytes (int index)
byte[] getBytes (String name)
Reader getCharacterStream (int i)
Reader getCharacterStream (String name)
Clob getClob (int index)
Clob getClob (String name)
Date getDate (int index)
Date getDate (String name)
Double getDouble (int index)
Double getDouble (String name)
float getFloat (int index)
float getFloat (String name)
int getInt (int index)
int getInt (String name)
long getLong (int index)
long getLong (String name)
Ref getRef (int index)
Ref getRef (String name)
Short getShort (int index)
Short getShort (String name)
String getString (int index)
String getString (String name)
Time getTime (int index)
Time getTime (String name)
Timestamp getTimestamp (int index)
Timestamp getTimestamp (String name)
InputStream getUnicodeStream (int index)
InputStream getUnicodeStream (String name)

There are two ways to use the getXXX methods. First, you can specify the column name that you want to retrieve. For example, if you have a column in the EMP table called EMPNO of type varchar, you can get the value by doing this:

 String employeeNbr = rs.getString( "EMPNO" ); 

You can also get the value by specifying the index of the column. So, if the column EMPNO was the first column in the ResultSet that was returned, you can also do this:

 String employeeNbr = rs.getString( 1 ); 

Caution

You have to be careful when using the column index approach. The index that the column is in the ResultSet might be completely different from the index of the column in the table. This is because the ResultSet might have a couple of columns from different tables and the column indexes will not be the same as they are when the column is in its own table.


The JDBC documentation recommends that the index approach be used to obtain the best efficiency. If there are multiple columns with the same name in the ResultSet, the first encountered will be returned. Remember, the ResultSet can represent columns across several tables and there is a chance that there are two columns with the same name. Also, when using the getXXX that takes the column name, the name parameter is case insensitive.

Note

Although the column index works fine, it may be a better approach to use the column name over the index. This is mainly a debugging issue. It will be easier to find an error using:

 String.employeeNbr = rs.getString("WRONG_NAME") 

than it will be to see

 String.employeeNbr = rs.getString(2) 

There are many getXXX methods that return a type for just about every Java primitive and several Java object types such as the String class. You have to be careful to use the correct getXXX method for the type of Object or primitive that will be returned. If you use the incorrect one, an SQLException might be raised. In some cases, the type can be converted. For example, if you have a VARCHAR value of 2 and you use a getInt method, the value might be converted to an int value of 2. The getString might correctly convert all the standard SQL types to a valid String. It's recommended in most cases if you are not sure of the database type, to use the getString method to retrieve the data from the ResultSet.

Other methods such as the getInt() can actually retrieve any of the numeric or character types. The data will be converted to an int. For example, if the database type is a varchar, JDBC will attempt to parse an int out of the value. If it can't, it will throw an SQLException.

   


Special Edition Using Java 2 Standard Edition
Special Edition Using Java 2, Standard Edition (Special Edition Using...)
ISBN: 0789724685
EAN: 2147483647
Year: 1999
Pages: 353

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net