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

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/hpdlzu80100/article/details/70249793
无数据,不应用。现实生活中的很多系统都离不开数据库,而数据的增删改查则是最基本的功能。
这几天实现了以下个人在技术上的突破:
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 application
package 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 book
package 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



 

程序运行效果:
展开阅读全文

没有更多推荐了,返回首页