Querying the Database

   

Java™ 2 Primer Plus
By Steven Haines, Steve Potts

Table of Contents
Chapter 19.  Accessing Databases with Java Database Connectivity (JDBC)


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.


       
    Top
     



    Java 2 Primer Plus
    Java 2 Primer Plus
    ISBN: 0672324156
    EAN: 2147483647
    Year: 2001
    Pages: 332

    Similar book on Amazon

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