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