| Now that we have a database and an ODBC datasource defined, we are in a position to extract data from it and display it. Listing 19.2 shows an example that does just that. Listing 19.2 The TestTicketRequest.java File /* * TestTicketRequest.java * * Created on July 9, 2002, 11:22 AM */ package ch19; import java.sql.*; import java.util.*; /** * * @author Stephen Potts * @version */ public class TestTicketRequest implements java.io.Serializable { //information about the customer private int custID; private String lastName; private String firstName; //information about the cruise private int cruiseID; private String destination; private String port; private String sailing; private int numberOfTickets; public TestTicketRequest() { } public String toString() { String outString; outString = "-------------------------------------------" + "\n"; //information about the customer outString += "custID = " + this.custID + "\n"; outString += "lastName = " + this.lastName + "\n"; outString += "firstName = " + this.firstName + "\n"; outString += "-------------------------------------------" + "\n"; //information about the cruise outString += "cruiseID = " + this.cruiseID + "\n"; outString += "destination = " + this.destination + "\n"; outString += "port = " + this.port + "\n"; outString += "sailing = " + this.sailing + "\n"; outString += "numberOfTickets = " + this.numberOfTickets + "\n"; outString += "-------------------------------------------" + "\n"; return outString; } public String retrieveFromDB() { java.sql.Connection dbConn = null; Statement statement1 = null; String createStatement; String insertStatement; try { // ============== Make connection to database ================== //load the driver class Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //Specify the ODBC data source String sourceURL = "jdbc:odbc:TicketRequest"; //get a connection to the database dbConn = DriverManager.getConnection(sourceURL); //If we get to here, no exception was thrown System.out.println("The database connection is " + dbConn); System.out.println("Making connection...\n"); //Create the statement statement1 = dbConn.createStatement(); //Populate String getString = "SELECT * FROM TicketRequest "; ResultSet results = statement1.executeQuery(getString); while (results.next()) { custID = results.getInt("custID"); lastName = results.getString("lastName"); firstName = results.getString("firstName"); cruiseID = results.getInt("cruiseID"); destination = results.getString("destination"); port = results.getString("port"); sailing = results.getString("sailing"); numberOfTickets = results.getInt("numberOfTickets"); System.out.println(this); } return "Successful Retrieval"; } catch (Exception e) { System.out.println("Exception was thrown: " + e.getMessage()); return "UnSuccessful Retrieval"; } finally { try { if (statement1 != null) statement1.close(); if (dbConn != null) dbConn.close(); } catch (SQLException sqle) { System.out.println("SQLException during close(): " + sqle.getMessage()); } } } public static void main(String[] args) { TestTicketRequest ttr = new TestTicketRequest(); System.out.println("The contents of the database:"); System.out.println(ttr.retrieveFromDB()); } } The interesting part of this example is the retrieval from the database. The first step is to connect to the datasource in exactly the same fashion as we did in the preceding example. dbConn = DriverManager.getConnection(sourceURL); The Connection object is used to create a Statement object. //Create the statement statement1 = dbConn.createStatement(); We first create the SQL as a String. //Populate the bean String getString = "SELECT * FROM TicketRequest "; Next, we create the result set by executing the query. ResultSet results = statement1.executeQuery(getString); For each row in the result set, we assign it to a variable and then call the toString() method defined previously to print out the contents of "this" class at the moment. while (results.next()) { custID = results.getInt("custID"); lastName = results.getString("lastName"); firstName = results.getString("firstName"); cruiseID = results.getInt("cruiseID"); destination = results.getString("destination"); port = results.getString("port"); sailing = results.getString("sailing"); numberOfTickets = results.getInt("numberOfTickets"); System.out.println(this); The result of running this example is sent to standard output: The contents of the database: The database connection is sun.jdbc.odbc.JdbcOdbcConnection@befab0 Making connection... ------------------------------------------- custID = 1001 lastName = Carter firstName = Joseph ----------------------------------------- cruiseID = 2001 destination = Alaska port = Vancouver sailing = 1/1/1993 numberOfTickets = 3 ----------------------------------------- ------------------------------------------- custID = 12345 lastName = Joe firstName = Cocomo ----------------------------------------- cruiseID = 3001 destination = Caribbean port = Miami sailing = 1/1/2004 numberOfTickets = 3 ----------------------------------------- ------------------------------------------- custID = 13 lastName = Beasley firstName = Demarcus ----------------------------------------- cruiseID = 3001 destination = Caribbean port = Miami sailing = 1/1/2004 numberOfTickets = 3 ----------------------------------------- ------------------------------------------- custID = 17 lastName = Glance firstName = Harvey ----------------------------------------- cruiseID = 3001 destination = Caribbean port = Miami sailing = 1/1/2004 numberOfTickets = 3 ----------------------------------------- ------------------------------------------- custID = 29 lastName = White firstName = Byron ----------------------------------------- cruiseID = 20010 destination = South America port = San Juan sailing = 10/3/02 numberOfTickets = 3 ----------------------------------------- Successful Retrieval Notice that the data is sent to output as soon as it is extracted from the result set. If we want to extract just one row from the database, we have to modify our program to be more selective. Listing 19.3 shows an example that retrieves only a single row based on a customer ID. Listing 19.3 The TestTicketRequest.java File /* * TestTicketRequest2.java * * Created on July 9, 2002, 11:22 AM */ package ch19; import java.sql.*; import java.util.*; /** * * @author Stephen Potts * @version */ public class TestTicketRequest2 implements java.io.Serializable { //information about the customer private int custID; private String lastName; private String firstName; //information about the cruise private int cruiseID; private String destination; private String port; private String sailing; private int numberOfTickets; public TestTicketRequest2() { } public String toString() { String outString; outString = "------------------------------------------" + "\n"; //information about the customer outString += "custID = " + this.custID + "\n"; outString += "lastName = " + this.lastName + "\n"; outString += "firstName = " + this.firstName + "\n"; outString += "------------------------------------------" + "\n"; //information about the cruise outString += "cruiseID = " + this.cruiseID + "\n"; outString += "destination = " + this.destination + "\n"; outString += "port = " + this.port + "\n"; outString += "sailing = " + this.sailing + "\n"; outString += "numberOfTickets = " + this.numberOfTickets + "\n"; outString += "------------------------------------------" + "\n"; return outString; } public String retrieveFromDB() { java.sql.Connection dbConn = null; Statement statement1 = null; String createStatement; String insertStatement; try { // ============== Make connection to database ================== //load the driver class Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //Specify the ODBC data source String sourceURL = "jdbc:odbc:TicketRequest"; //get a connection to the database dbConn = DriverManager.getConnection(sourceURL); //If we get to here, no exception was thrown System.out.println("The database connection is " + dbConn); System.out.println("Making connection...\n"); //Create the statement statement1 = dbConn.createStatement(); custID = 17; //Populate the bean String getBeanString = "SELECT * FROM TicketRequest " + "WHERE CustID = " + custID; ResultSet results = statement1.executeQuery(getBeanString); while (results.next()) { custID = results.getInt("custID"); lastName = results.getString("lastName"); firstName = results.getString("firstName"); cruiseID = results.getInt("cruiseID"); destination = results.getString("destination"); port = results.getString("port"); sailing = results.getString("sailing"); numberOfTickets = results.getInt("numberOfTickets"); System.out.println(this); } return "Successful Retrieval"; } catch (Exception e) { System.out.println("Exception was thrown: " + e.getMessage()); return "UnSuccessful Retrieval"; } finally { try { if (statement1 != null) statement1.close(); if (dbConn != null) dbConn.close(); } catch (SQLException sqle) { System.out.println("SQLException during close(): " + sqle.getMessage()); } } } public static void main(String[] args) { TestTicketRequest2 ttr = new TestTicketRequest2(); System.out.println("The contents of the database:"); System.out.println(ttr.retrieveFromDB()); } } This example opens the database using the JDBC-ODBC driver in the same way. Instead of allowing the getString to request all the rows, it limits it to only the rows that match the custID. custID = 17; //Populate the bean String getString = "SELECT * FROM TicketRequest " + "WHERE CustID = " + custID; Notice that we still use the while statement even though we only expect one row to be returned. The reason for this is that the while statement handles the case of zero or n rows that match this criteria. If no rows are returned, no error is thrown. If multiple rows are returned, only the last one retrieved is processed. This simplifies the error-handling code and prevents the user from getting error messages. The result of running this program is shown here: The contents of the database: The database connection is sun.jdbc.odbc.JdbcOdbcConnection@befab0 Making connection... ----------------------------------------- custID = 17 lastName = Glance firstName = Harvey ----------------------------------------- cruiseID = 3001 destination = Caribbean port = Miami sailing = 1/1/2004 numberOfTickets = 3 ----------------------------------------- Successful Retrieval Note that only one row was retrieved. | 
