Java源码——一个简单的数据库应用程序（通讯录）

无数据，不应用。现实生活中的很多系统都离不开数据库，而数据的增删改查则是最基本的功能。

这几天实现了以下个人在技术上的突破：

1. Java程序直连Oracle数据库，而之前的玩法是：

a. 直接使用Oracle Express Edition创建示例数据库应用程序 （好像用的是.NET）

b. 用Java连接MySQL和Derby (JavaDB)

2. 在JHTP教材示例的基础上，增加了两个删除记录和更新数据的功能，得益于：

a. 自己对SQL有了更深刻的理解，简单的PreparedStatement不在话下（呵呵）

b. 对Java程序的前后端交互有了完整的接触（之前写了很多小程序，没有打通数据库与代码交互的这一环节）

多说无益，分享代码如下：

1. PersonQueries类（用于通讯录程序所使用的PreparedStatement）

// Fig. 28.31: PersonQueries.java// PreparedStatements used by the Address Book applicationpackage ch24;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.ArrayList;public class PersonQueries {   private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";   private static final String USERNAME = "c##scott";   private static final String PASSWORD = "scott";   private Connection connection; // manages connection   private PreparedStatement selectAllPeople;    private PreparedStatement selectPeopleByLastName;    private PreparedStatement insertNewPerson;    private PreparedStatement deletePerson;    private PreparedStatement updatePerson;        // constructor   public PersonQueries()   {      try       {         connection =             DriverManager.getConnection(URL, USERNAME, PASSWORD);         // create query that selects all entries in the AddressBook         selectAllPeople =             connection.prepareStatement("SELECT * FROM Addresses");                  // create query that selects entries with a specific last name         selectPeopleByLastName = connection.prepareStatement(            "SELECT * FROM Addresses WHERE LastName = ?");                  // create insert that adds a new entry into the database         insertNewPerson = connection.prepareStatement(            "INSERT INTO Addresses " +             "(AddressID, FirstName, LastName, Email, PhoneNumber)" +             "VALUES (id_sequence.nextval, ?, ?, ?, ?)");                  // create delete statement that delete current entry from database         deletePerson = connection.prepareStatement(            "DELETE FROM Addresses where AddressID = ?");                           // create update statement that delete current entry from database         updatePerson = connection.prepareStatement(            "UPDATE Addresses SET "+             "FirstName = ?, LastName = ?, Email = ?, PhoneNumber = ?" +             "WHERE AddressID = ?");      }      catch (SQLException sqlException)      {         sqlException.printStackTrace();         System.exit(1);      }   } // end PersonQueries constructor      // select all of the addresses in the database   public List< Person > getAllPeople()   {      List< Person > results = null;      ResultSet resultSet = null;            try       {         // executeQuery returns ResultSet containing matching entries         resultSet = selectAllPeople.executeQuery();          results = new ArrayList< Person >();                  while (resultSet.next())         {            results.add(new Person(               resultSet.getInt("addressID"),               resultSet.getString("firstName"),               resultSet.getString("lastName"),               resultSet.getString("email"),               resultSet.getString("phoneNumber")));         }       }       catch (SQLException sqlException)      {         sqlException.printStackTrace();               }       finally      {         try          {            resultSet.close();         }          catch (SQLException sqlException)         {            sqlException.printStackTrace();                     close();         }      }            return results;   }    // select person by last name      public List< Person > getPeopleByLastName(String name)   {      List< Person > results = null;      ResultSet resultSet = null;      try       {         selectPeopleByLastName.setString(1, name); // specify last name         // executeQuery returns ResultSet containing matching entries         resultSet = selectPeopleByLastName.executeQuery();          results = new ArrayList< Person >();         while (resultSet.next())         {            results.add(new Person(resultSet.getInt("addressID"),               resultSet.getString("firstName"),               resultSet.getString("lastName"),               resultSet.getString("email"),               resultSet.getString("phoneNumber")));         }       }       catch (SQLException sqlException)      {         sqlException.printStackTrace();      }       finally      {         try          {            resultSet.close();         }         catch (SQLException sqlException)         {            sqlException.printStackTrace();                     close();         }      }             return results;   }       // add an entry   public int addPerson(      String fname, String lname, String email, String num)   {      int result = 0;            // set parameters, then execute insertNewPerson      try       {     insertNewPerson.setString(1, fname);         insertNewPerson.setString(2, lname);         insertNewPerson.setString(3, email);         insertNewPerson.setString(4, num);         // insert the new entry; returns # of rows updated         result = insertNewPerson.executeUpdate();       }      catch (SQLException sqlException)      {         sqlException.printStackTrace();         close();      }             return result;   }       // delete a person   public int deletePerson(      String addressID)   {      int result = 0;            // set parameters, then execute insertNewPerson      try       {     deletePerson.setString(1, addressID);         // insert the new entry; returns # of rows updated         result = deletePerson.executeUpdate();       }      catch (SQLException sqlException)      {         sqlException.printStackTrace();         close();      }             return result;   }       // update a person   public int updatePerson(      String fname, String lname, String email, String num, String addressID)   {      int result = 0;            // set parameters, then execute insertNewPerson      try       {     updatePerson.setString(1, fname);     updatePerson.setString(2, lname);     updatePerson.setString(3, email);     updatePerson.setString(4, num);     updatePerson.setString(5, addressID);         // update current entry; returns # of rows updated         result = updatePerson.executeUpdate();       }      catch (SQLException sqlException)      {         sqlException.printStackTrace();         close();      }             return result;   }       // close the database connection   public void close()   {      try       {         connection.close();      }       catch (SQLException sqlException)      {         sqlException.printStackTrace();      }    } } // end class PersonQueries 

2. AddressBookDisplay类（主程序，包括前端页面的及用户交互的定义）

// Fig. 28.32: AddressBookDisplay.java// A simple address bookpackage ch24;import java.awt.event.ActionEvent;import java.awt.event.ActionListener;import java.awt.event.WindowAdapter;import java.awt.event.WindowEvent;import java.awt.FlowLayout;import java.awt.GridLayout;import java.util.List; import javax.swing.JButton;import javax.swing.Box;import javax.swing.JFrame;import javax.swing.JLabel;import javax.swing.JPanel;import javax.swing.JTextField;import javax.swing.WindowConstants;import javax.swing.BoxLayout;import javax.swing.BorderFactory;import javax.swing.JOptionPane;public class AddressBookDisplay extends JFrame{   private Person currentEntry;   private PersonQueries personQueries;   private List<Person> results;      private int numberOfEntries = 0;   private int currentEntryIndex;   private JButton browseButton;   private JLabel emailLabel;   private JTextField emailTextField;   private JLabel firstNameLabel;   private JTextField firstNameTextField;   private JLabel idLabel;   private JTextField idTextField;   private JTextField indexTextField;   private JLabel lastNameLabel;   private JTextField lastNameTextField;   private JTextField maxTextField;   private JButton nextButton;   private JLabel ofLabel;   private JLabel phoneLabel;   private JTextField phoneTextField;   private JButton previousButton;   private JButton queryButton;   private JLabel queryLabel;   private JPanel queryPanel;   private JPanel navigatePanel;   private JPanel displayPanel;   private JTextField queryTextField;   private JButton insertButton;   private JButton updateButton;   private JButton deleteButton;      // constructor   public AddressBookDisplay()   {      super("Address Book");             // establish database connection and set up PreparedStatements      personQueries = new PersonQueries();             // create GUI      navigatePanel = new JPanel();      previousButton = new JButton();      indexTextField = new JTextField(2);      ofLabel = new JLabel();      maxTextField = new JTextField(2);      nextButton = new JButton();      displayPanel = new JPanel();      idLabel = new JLabel();      idTextField = new JTextField(10);      firstNameLabel = new JLabel();      firstNameTextField = new JTextField(10);      lastNameLabel = new JLabel();      lastNameTextField = new JTextField(10);      emailLabel = new JLabel();      emailTextField = new JTextField(10);      phoneLabel = new JLabel();      phoneTextField = new JTextField(10);      queryPanel = new JPanel();      queryLabel = new JLabel();      queryTextField = new JTextField(10);      queryButton = new JButton();      browseButton = new JButton();      insertButton = new JButton();      updateButton = new JButton();      deleteButton = new JButton();      setLayout(new FlowLayout(FlowLayout.CENTER, 10, 10));      setSize(400, 355);      setResizable(false);      navigatePanel.setLayout(         new BoxLayout(navigatePanel, BoxLayout.X_AXIS));      previousButton.setText("Previous");      previousButton.setEnabled(false);      previousButton.addActionListener(         new ActionListener()         {            public void actionPerformed(ActionEvent evt)            {               previousButtonActionPerformed(evt);            }         }      ); // end call to addActionListener      navigatePanel.add(previousButton);      navigatePanel.add(Box.createHorizontalStrut(10));      indexTextField.setHorizontalAlignment(         JTextField.CENTER);      indexTextField.addActionListener(         new ActionListener()         {            public void actionPerformed(ActionEvent evt)            {               indexTextFieldActionPerformed(evt);            }          }      ); // end call to addActionListener      navigatePanel.add(indexTextField);      navigatePanel.add(Box.createHorizontalStrut(10));      ofLabel.setText("of");      navigatePanel.add(ofLabel);      navigatePanel.add(Box.createHorizontalStrut(10));      maxTextField.setHorizontalAlignment(         JTextField.CENTER);      maxTextField.setEditable(false);      navigatePanel.add(maxTextField);      navigatePanel.add(Box.createHorizontalStrut(10));      nextButton.setText("Next");      nextButton.setEnabled(false);      nextButton.addActionListener(         new ActionListener()         {            public void actionPerformed(ActionEvent evt)            {               nextButtonActionPerformed(evt);            }         }      ); // end call to addActionListener      navigatePanel.add(nextButton);      add(navigatePanel);      displayPanel.setLayout(new GridLayout(5, 2, 4, 4));      idLabel.setText("Address ID:");      displayPanel.add(idLabel);      idTextField.setEditable(false);      displayPanel.add(idTextField);      firstNameLabel.setText("First Name:");      displayPanel.add(firstNameLabel);      displayPanel.add(firstNameTextField);      lastNameLabel.setText("Last Name:");      displayPanel.add(lastNameLabel);      displayPanel.add(lastNameTextField);      emailLabel.setText("Email:");      displayPanel.add(emailLabel);      displayPanel.add(emailTextField);      phoneLabel.setText("Phone Number:");      displayPanel.add(phoneLabel);      displayPanel.add(phoneTextField);      add(displayPanel);      queryPanel.setLayout(         new BoxLayout(queryPanel, BoxLayout.X_AXIS));      queryPanel.setBorder(BorderFactory.createTitledBorder(         "Find an entry by last name"));      queryLabel.setText("Last Name:");      queryPanel.add(Box.createHorizontalStrut(5));      queryPanel.add(queryLabel);      queryPanel.add(Box.createHorizontalStrut(10));      queryPanel.add(queryTextField);      queryPanel.add(Box.createHorizontalStrut(10));      queryButton.setText("Find");      queryButton.addActionListener(         new ActionListener()         {            public void actionPerformed(ActionEvent evt)            {               queryButtonActionPerformed(evt);            }          }      ); // end call to addActionListener      queryPanel.add(queryButton);      queryPanel.add(Box.createHorizontalStrut(5));      add(queryPanel);      browseButton.setText("Browse All Entries");      browseButton.addActionListener(         new ActionListener()         {            public void actionPerformed(ActionEvent evt)            {               browseButtonActionPerformed(evt);            }          }       ); // end call to addActionListener      add(browseButton);      insertButton.setText("Insert New Entry");      insertButton.addActionListener(         new ActionListener()         {            public void actionPerformed(ActionEvent evt)            {               insertButtonActionPerformed(evt);            }          }       ); // end call to addActionListener   add(insertButton);        deleteButton.setText("Delete this Entry");      deleteButton.addActionListener(         new ActionListener()         {            public void actionPerformed(ActionEvent evt)            {               deleteButtonActionPerformed(evt);            }          }       ); // end call to addActionListener   add(deleteButton);     updateButton.setText("Update this Entry");      updateButton.addActionListener(         new ActionListener()         {            public void actionPerformed(ActionEvent evt)            {               updateButtonActionPerformed(evt);            }          }       ); // end call to addActionListener   add(updateButton);         addWindowListener(         new WindowAdapter()          {              public void windowClosing(WindowEvent evt)            {               personQueries.close(); // close database connection               System.exit(0);            }          }       ); // end call to addWindowListener      setVisible(true);   } // end constructor   // handles call when previousButton is clicked   private void previousButtonActionPerformed(ActionEvent evt)   {      currentEntryIndex--;            if (currentEntryIndex < 0)         currentEntryIndex = numberOfEntries - 1;            indexTextField.setText("" + (currentEntryIndex + 1));      indexTextFieldActionPerformed(evt);     }    // handles call when nextButton is clicked   private void nextButtonActionPerformed(ActionEvent evt)    {      currentEntryIndex++;            if (currentEntryIndex >= numberOfEntries)         currentEntryIndex = 0;            indexTextField.setText("" + (currentEntryIndex + 1));      indexTextFieldActionPerformed(evt);   }   // handles call when queryButton is clicked   private void queryButtonActionPerformed(ActionEvent evt)   {      results =          personQueries.getPeopleByLastName(queryTextField.getText());      numberOfEntries = results.size();            if (numberOfEntries != 0)      {         currentEntryIndex = 0;         currentEntry = results.get(currentEntryIndex);         idTextField.setText("" + currentEntry.getAddressID());         firstNameTextField.setText(currentEntry.getFirstName());         lastNameTextField.setText(currentEntry.getLastName());         emailTextField.setText(currentEntry.getEmail());         phoneTextField.setText(currentEntry.getPhoneNumber());         maxTextField.setText("" + numberOfEntries);         indexTextField.setText("" + (currentEntryIndex + 1));         nextButton.setEnabled(true);         previousButton.setEnabled(true);      }       else         browseButtonActionPerformed(evt);   }       // handles call when a new value is entered in indexTextField   private void indexTextFieldActionPerformed(ActionEvent evt)   {      currentEntryIndex =          (Integer.parseInt(indexTextField.getText()) - 1);            if (numberOfEntries != 0 && currentEntryIndex < numberOfEntries)      {         currentEntry = results.get(currentEntryIndex);         idTextField.setText("" + currentEntry.getAddressID());         firstNameTextField.setText(currentEntry.getFirstName());         lastNameTextField.setText(currentEntry.getLastName());         emailTextField.setText(currentEntry.getEmail());         phoneTextField.setText(currentEntry.getPhoneNumber());         maxTextField.setText("" + numberOfEntries);         indexTextField.setText("" + (currentEntryIndex + 1));      }     }   // handles call when browseButton is clicked   private void browseButtonActionPerformed(ActionEvent evt)   {      try      {         results = personQueries.getAllPeople();         numberOfEntries = results.size();               if (numberOfEntries != 0)         {            currentEntryIndex = 0;            currentEntry = results.get(currentEntryIndex);            idTextField.setText("" + currentEntry.getAddressID());            firstNameTextField.setText(currentEntry.getFirstName());            lastNameTextField.setText(currentEntry.getLastName());            emailTextField.setText(currentEntry.getEmail());            phoneTextField.setText(currentEntry.getPhoneNumber());            maxTextField.setText("" + numberOfEntries);            indexTextField.setText("" + (currentEntryIndex + 1));            nextButton.setEnabled(true);            previousButton.setEnabled(true);         }       }       catch (Exception e)      {         e.printStackTrace();      }    }    // handles call when insertButton is clicked   private void insertButtonActionPerformed(ActionEvent evt)    {      int result = personQueries.addPerson(firstNameTextField.getText(),         lastNameTextField.getText(), emailTextField.getText(),         phoneTextField.getText());            if (result == 1)         JOptionPane.showMessageDialog(this, "Person added!",            "Person added", JOptionPane.PLAIN_MESSAGE);      else         JOptionPane.showMessageDialog(this, "Person not added!",            "Error", JOptionPane.PLAIN_MESSAGE);                browseButtonActionPerformed(evt);   }         // handles call when deleteButton is clicked   private void deleteButtonActionPerformed(ActionEvent evt)    {      int result = personQueries.deletePerson(idTextField.getText());            if (result == 1)         JOptionPane.showMessageDialog(this, "Person deleted!",            "Person deleted", JOptionPane.PLAIN_MESSAGE);      else         JOptionPane.showMessageDialog(this, "Person not deleted!",            "Error", JOptionPane.PLAIN_MESSAGE);                browseButtonActionPerformed(evt);   }         // handles call when updateButton is clicked   private void updateButtonActionPerformed(ActionEvent evt)    {      int result = personQueries.updatePerson(firstNameTextField.getText(),             lastNameTextField.getText(), emailTextField.getText(),             phoneTextField.getText(), idTextField.getText());            if (result == 1)         JOptionPane.showMessageDialog(this, "Person updated!",            "Person updated", JOptionPane.PLAIN_MESSAGE);      else         JOptionPane.showMessageDialog(this, "Person not updated!",            "Error", JOptionPane.PLAIN_MESSAGE);                browseButtonActionPerformed(evt);   }      // main method   public static void main(String args[])   {      new AddressBookDisplay();   } } // end class AddressBookDisplay 

程序运行效果：