testtablealteration.java

java2 primer plus一书源程序

/*
 * 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();
        }
    }