| 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.  Notice that the values for the Last Name and the number of tickets are changed. | 
