Altering the Data Using JDBC and a GUI

   

Java™ 2 Primer Plus
By Steven Haines, Steve Potts

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


Another common JDBC task is altering or updating the data in a table. Most of the systems that you will write will require that the user not only be able to insert and query data, but make changes to it as well. In addition, it is very useful if these changes can be made using a GUI. Listing 19.6 shows a program where a user can do this.

Listing 19.6 The TestTableAlteration.java File
 /*   * TestTableAlteration.java   *   * Created on July 30, 2002, 11:35 AM   */  package ch19;  import javax.swing.*;  import java.awt.*;  import java.awt.event.*;   import java.sql.*;  /**   *   * @author  Stephen Potts   * @version   */  public class TestTableAlteration extends JFrame implements ActionListener  {      //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;      JTextField tfCustID;      JTextField tfLastName;      JTextField tfFirstName;      JTextField tfCruiseID;      JTextField tfDestination;       JTextField tfPort;      JTextField tfSailing;      JTextField tfNumberOfTickets;      JLabel lCustID = new JLabel("CustID");      JLabel lLastName = new JLabel("LastName");      JLabel lFirstName = new JLabel("FirstName");      JLabel lCruiseID = new JLabel("CruiseID");      JLabel lDestination = new JLabel("Destination");      JLabel lPort = new JLabel("Port    ");      JLabel lSailing = new JLabel("Sailing");      JLabel lNumberOfTickets = new JLabel("Number of Tickets");      JButton btnRetrieve;      JButton btnStore;      java.sql.Connection dbConn = null;      Statement statement1 = null;      String createStatement;      String insertStatement;      /** Creates new TestTableAlteration */      public TestTableAlteration()      {          tfCustID = new JTextField(20);           tfLastName = new JTextField(20);          tfFirstName = new JTextField(20);          tfCruiseID = new JTextField(20);          tfDestination = new JTextField(20);          tfPort = new JTextField(20);          tfSailing = new JTextField(20);          tfNumberOfTickets = new JTextField(15);          btnRetrieve = new JButton("Retrieve");          btnStore = new JButton("Store");          this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);           JPanel p1 = new JPanel();          p1.add(lCustID);          p1.add(tfCustID);          p1.add(lLastName);          p1.add(tfLastName);          p1.add(lFirstName);          p1.add(tfFirstName);          p1.add(lCruiseID);          p1.add(tfCruiseID);          p1.add(lDestination);          p1.add(tfDestination);           p1.add(lPort);          p1.add(tfPort);          p1.add(lSailing);          p1.add(tfSailing);          p1.add(lNumberOfTickets);          p1.add(tfNumberOfTickets);          p1.add(btnRetrieve);          p1.add(btnStore);          btnRetrieve.addActionListener(this);          btnStore.addActionListener(this);          getContentPane().add(p1);          setTitle("Altering Table Data");          setBounds( 100, 100, 315, 300);          setVisible(true);      }      public void actionPerformed(ActionEvent ae)      {          if (ae.getActionCommand().equals("Retrieve"))          {              System.out.println("btnRetrieve clicked");              if (tfCustID.getText().equals(""))                  System.out.println("please enter a CustID");              else              {                  System.out.println("CustID = " + tfCustID.getText());                  this.custID = Integer.parseInt(tfCustID.getText());                  retrieveFromDB();                  tfLastName.setText(lastName);                  tfFirstName.setText(firstName);                  tfCruiseID.setText(Integer.toString(cruiseID));                  tfDestination.setText(destination);                  tfPort.setText(port);                  tfSailing.setText(sailing);                  tfNumberOfTickets.setText(Integer.toString(numberOfTickets));              }          }           if (ae.getActionCommand().equals("Store"))          {              System.out.println("btnStore clicked");              if (tfCustID.getText().equals(""))                  System.out.println("please enter a CustID");              else              {                  updateDB();              }          }      }      public String updateDB()      {          try          {              // ============== Make connection to database ==================              connectToDB();              lastName = tfLastName.getText();              firstName = tfFirstName.getText();              String strCruiseID = tfCruiseID.getText();              destination = tfDestination.getText();              port = tfPort.getText();              sailing = tfSailing.getText();              String strNumberOfTickets = tfNumberOfTickets.getText();              //Update Last Name              String updateString =              "Update TicketRequest " +              "SET lastName = '" + lastName + "' " +              "WHERE CustID = " + custID;              System.out.println(updateString);              statement1.executeUpdate(updateString);              //Update First Name              updateString =              "Update TicketRequest " +              "SET firstName = '" + firstName + "' " +              "WHERE CustID = " + custID;              System.out.println(updateString);              statement1.executeUpdate(updateString);              //Update Cruise ID              updateString =              "Update TicketRequest " +              "SET cruiseID= " + strCruiseID + " " +              "WHERE CustID = " + custID;              System.out.println(updateString);              statement1.executeUpdate(updateString);               //Update destination              updateString =              "Update TicketRequest " +              "SET destination = '" + destination + "' " +              "WHERE CustID = " + custID;              System.out.println(updateString);              statement1.executeUpdate(updateString);              //Update port              updateString =              "Update TicketRequest " +              "SET port = '" + port + "' " +              "WHERE CustID = " + custID;              System.out.println(updateString);              statement1.executeUpdate(updateString);              //Update sailing              updateString =              "Update TicketRequest " +              "SET sailing = '" + sailing + "' " +              "WHERE CustID = " + custID;              System.out.println(updateString);              statement1.executeUpdate(updateString);              //Update number of tickts               updateString =              "Update TicketRequest " +              "SET numberOfTickets = " + strNumberOfTickets + " " +              "WHERE CustID = " + custID;              System.out.println(updateString);              statement1.executeUpdate(updateString);              return "Successful Update";          } 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 String retrieveFromDB()          {              try              {                  // ============== Make connection to database ==================                  connectToDB();                  //Populate                  String getString =                  "SELECT * FROM TicketRequest " +                  "WHERE CustID = " + custID;                  ResultSet results = statement1.executeQuery(getString);                  lastName = "record not found";                  firstName = "";                  cruiseID = 0;                  destination = "";                  port = "";                  sailing = "";                  numberOfTickets = 0;                  while (results.next())                  {                      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");                  }                  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());                  }               }          }          private void connectToDB()          {              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();              } catch (Exception e)              {                  System.out.println("Exception was thrown: " + e.getMessage());              }          }          public static void main(String[] args)          {              TestTableAlteration tta = new TestTableAlteration();          }      }  } 

This example has a GUI, so we need to implement a listener interface.

 public class TestTableAlteration extends JFrame implements ActionListener 

We define two buttons to trigger action.

 JButton btnRetrieve;  JButton btnStore; 

The actionPerformed() method is where the JDBC processing is called from.

 public void actionPerformed(ActionEvent ae)  { 

The "Retrieve" button loads the custID, and then calls the retrieveFromDB() method to get the data.

 if (ae.getActionCommand().equals("Retrieve"))  {      System.out.println("btnRetrieve clicked");      if (tfCustID.getText().equals(""))          System.out.println("please enter a CustID");      else      {          System.out.println("CustID = " + tfCustID.getText());          this.custID = Integer.parseInt(tfCustID.getText());          retrieveFromDB(); 

After this method returns, we load the data from the class-level variables into the text fields.

 tfLastName.setText(lastName);  tfFirstName.setText(firstName);  tfCruiseID.setText(Integer.toString(cruiseID));  tfDestination.setText(destination);  tfPort.setText(port);  tfSailing.setText(sailing);  tfNumberOfTickets.setText(Integer.toString(numberOfTickets)); 

The "Store" button causes the updateDB() method to be called.

     if (ae.getActionCommand().equals("Store"))      {          System.out.println("btnStore clicked");          if (tfCustID.getText().equals(""))              System.out.println("please enter a CustID");          else          {              updateDB();          }      }  } 

The updateDB() method is where the data is stored to the DB.

 public String updateDB() 

First, we connect to the database.

 connectToDB(); 

Next, we update each of the class variables with the data from the text fields.

 lastName = tfLastName.getText();  firstName = tfFirstName.getText();  String strCruiseID = tfCruiseID.getText();  destination = tfDestination.getText();  port = tfPort.getText();  sailing = tfSailing.getText();  String strNumberOfTickets = tfNumberOfTickets.getText(); 

Next, we update every column because we don't know which of them contains changes. If the table was large, we would want to determine whether a field has been changed, but for a small table, this is not necessary.

             //Update Last Name              String updateString =              "Update TicketRequest " +              "SET lastName = '" + lastName + "' " +              "WHERE CustID = " + custID;              System.out.println(updateString);              statement1.executeUpdate(updateString);  .  .  .              //Update number of tickts               updateString =              "Update TicketRequest " +              "SET numberOfTickets = " + strNumberOfTickets + " " +              "WHERE CustID = " + custID;              System.out.println(updateString);              statement1.executeUpdate(updateString); 

The result of running this program is shown here in Figure 19.9.

Figure 19.9. You can update the database using JDBC and the SQL Update statement.

graphics/19fig09.gif

Notice that the values for the Last Name and the number of tickets are changed.


       
    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