Forms for add, delete, modify, search

Java Database Programming - Using forms to Add, Modify, Delete, and Search

Some basic rules:

Try to match the component on the form to the data type.

Yes/No fields should be displayed in a JCheckBox
Memo fields in a JTextArea
All others can be displayed in a JTextField.

If a field will have a pre-determined number of valid values, and this list of values is unlikely to change, then use JComboBox with the editable property set to false, and the values entered into the code itself (via forms editor interface). This prevents the user from entering invalid data while preserving screen space. Examples:

state abbreviations in addresses
days of the week
months of the year
titles (Mr, Mrs, Ms, Dr, Lord, Lady)

If a field will have a pre-determined number of valid values, and this list of values is likely to change, then use JComboBox with the editable property set to true, and the load the values from a database table. This allows the user to access a list of existing values while giving them the opportunity to enter a value that is not in the list. Examples might be:

job titles
departments or colleges
colors (red, green, blue, ....)
sizes (small, medium, large, xlarge)

If you are loading a JComboBox from a database table, then you can do this from a method called by the JFrames/JDialog's constructor and/or "Component shown" event for the combobox. You need to remember that loading the combo could take a second or two.

Basic Structure of your program

Your constructor should create the connection to the database, and exit methods should close the connection. The connection variable should be defined within the class, but not within a method so that it can be accessed by all the methods. If the connection fails, then an error message should be displayed via JOptionPane.showMessageDialog, then the program can exit.
Each button action performed method that needs to access the database should ask the connection to create a Statement or PreparedStatement object, use the object, then close it before the method returns. Note, once you close a Statement object, then any ResultSet that it created is also closed.

Creating a new database record from data on a form.

Your form should have a component of the proper type for each column in the table as well as a "Save" button.
Tasks that should be done when "Save" button is pressed:

retrieve the data from the components on the screen and save them in local variables
check the data to be sure it is valid (input verifiers can help)
create either a Statement object or a PreparedStatement object.
construct the SQL INSERT INTO command
tell the Statement or PreparedStatement object to executeUpdate()
catch SQL exceptions and check return status
report the success/failure to the user.

If a record already exists with the same "key" field data, then you will get an SQL exception (Sql Exception :General error).

Example1 - Inserting a new product into the products table using a PreparedStatement object.

Products table:

Column Name	MS Access Data Type
Name	Text
Quantity	Number-Integer
Cost	Number-Double

Code:

private
void saveButtonActionPerformed(java.awt.event.ActionEvent evt) {

        //

        // retrieve and convert data
into local variables

        //

        String name =
nameField.getText();

        String strquantity =
quantityField.getText();

        String strcost =
costField.getText();

        short quantity = 0;;

        double cost = 0;

        try{

          quantity =
Short.parseShort(strquantity);

        }

        catch(NumberFormatException
e)

        {

        
messageLabel.setText("Invalid entry in quantity field, must be a
number");

         return;

        }

        try{

          cost =
Double.parseDouble(strcost);

        }

        catch(NumberFormatException
e)

        {

        
messageLabel.setText("Invalid entry in cost field, must be a number");

         return;

        }

        //

        // test data for consistency
and accuracy

        //

        if(cost < 0)

        {

        
messageLabel.setText("Cost must be greater than or equal to zero");

         return;

        }

        if(quantity < 0)

        {

        
messageLabel.setText("Quantity must be greater than or equal to zero");

         return;

        }

        if(name.length() <= 0)

        {

        
messageLabel.setText("Name cannot be blank");

         return;

        }

        //

        //  create and execute
PreparedStatement

        //

        try{

         
PreparedStatement prep = con.prepareStatement("INSERT INTO products
(Name, Quantity, Cost)
Values(?, ?, ?)");

         
prep.setString(1,name);

         
prep.setShort(2,quantity);

         
prep.setDouble(3,cost);

          int result =
prep.executeUpdate();

          if(result == 1)

            
messageLabel.setText(name+" added to the Products table");

          else

            
messageLabel.setText("Unable to add "+name+" to Products table");

          prep.close(); //
close the PreparedStatement

         }

         catch (SQLException e)
      

         {

         
messageLabel.setText("Unable to add "+name+" to Products table :
"+e.getMessage());

         }

   }

Example2 - Inserting a new product into the products table using a Statement object.

Note: using a PreparedStatement can be easier to create and maintain because you don't have to create a complex String addition line remembering all the single quotes and other data notation marks.


    .... same as previous example ....

      

        //

        //  create and execute
Statement

        //

        try{

          Statement stat =
con.createStatement();

          

          String
sqlCommand = "INSERT INTO products (Name, Quantity, Cost) Values('" +

                              
name+"',"+quantity+", "+cost+")";

           

          int result =
stat.executeUpdate(sqlCommand);

          if(result == 1)

            
messageLabel.setText(name+" added to the Products table");

          else

            
messageLabel.setText("Unable to add "+name+" to Products table");

          stat.close(); //
close the PreparedStatement

         }

         catch (SQLException e)
      

         {

         
messageLabel.setText("Unable to add "+name+" to Products table :
"+e.getMessage());

         }

}

Locating and loading a record from a table using key field data

Your form should have a component of the proper type for each column in the table as well as a "Find" button.
Tasks that should be done when "Find" button is pressed:

Retrieve value(s) of key field data from screen and store in local variables
Check to be sure user input was valid for the fied(s)
Blank out non key data on screen
Create a Statement or PreparedStatement Object with a SELECT command with a WHERE clause testing for equality on all key fields. If there is more than one key field, then use an AND to form the WHERE clause. e.g.
SELECT * FROM products WHERE name = ?
Tell the Statement or PreparedStatement to executeQuery
Process results, if key fields are used with equality test in the WHERE clause, then there should be nore more than 1 record in ResultSet.
If the record existed, retrieve values from result set into local variables
Copy/format values from local variables onto screen
If record was not found, then display error message.
Close Statement / PreparedStatement object.
Catch SQL errors

If the a record is not found, then you will get an empty result set. This is indicated by rs.next() returning false on the first call.

Example: Finding a product record using product name

 private void
findButtonActionPerformed(java.awt.event.ActionEvent
evt) {

     //

     // retrieve data used in search

     //

     String name = nameField.getText();

     //

     // verify data

     //

     if(name.length() <= 0)

     {

      messageLabel.setText("Name field cannot
be
blank");

      return;

     }

      costField.setText("");

      quantityField.setText("");

  

     //

     //  create and execute a prepared
statement

     //

  

      try{

        PreparedStatement prep =
con.prepareStatement("SELECT
* FROM products WHERE name = ?");

        prep.setString(1, name);

        ResultSet rs =
prep.executeQuery();

        if(rs.next())  // was
at
least one record found?

        {

         // get record data
from
result set into local variables

         name = rs.getString(1);

         short quantity =
rs.getShort(2);

         double cost =
rs.getDouble(3);

         //

         // display data onto
screen

         //

        
nameField.setText(name);

        
quantityField.setText(""+quantity);

        
costField.setText(""+cost);

        
messageLabel.setText(""); 
// clear out message label

        }

        else

         
messageLabel.setText(name+"
not found in Products table");      

        prep.close();

       }

       catch(SQLException sqle)

       {

        messageLabel.setText("Error
during
lookup :
"+sqle.getMessage());        

       }

        

    }

Deleting a record from a table using key field data

Your form should have a component of the proper type for each key column in the table as well as a "Delete" button.
Tasks that should be done when "Delete" button is pressed:

Retrieve value(s) of key field data from screen and store in local variables
Check to be sure user input was valid for the fied(s)
Create a Statement or PreparedStatement Object with a DELETE command with a WHERE statement containing keyfield data. If there is more than one key field, then use an AND to form the WHERE clause, e.g.
DELETE FROM products WHERE name = ?
Tell the Statement or PreparedStatement to executeUpdate
Process results, since you are deleting with a key field(s) in the where statement, then executeUpdate should return 1 (if the record was present)
If executeUpdate returned a 0, then display an error message
Close Statement / PreparedStatement object.
Catch SQL errors

If the a record is not found, then you will get a return value of 0, if it is, then you will get a return value of 1.

Example: Deleting a product record using product name

private void
deleteButtonActionPerformed(java.awt.event.ActionEvent
evt) {

        // Add your handling code
here:

     //

     // retrieve data used in search

     //

     String name = nameField.getText();

     //

     // verify data

     //

     if(name.length() <= 0)

     {

      messageLabel.setText("Name field cannot
be
blank");

      return;

     }

 

     //

     //  create and execute a prepared
statement

     //

        try{

        PreparedStatement prep =
con.prepareStatement("DELETE
FROM products WHERE name = ?");

        prep.setString(1, name);

        int result =
prep.executeUpdate();

        if(result == 1)  // was
at least one record deleted?

        {

         //

         // clear data on screen

         //

         nameField.setText("");

        
quantityField.setText("");

         costField.setText("");

        
messageLabel.setText(name+"
successfully deleted from products");  // clear out message label

        }

        else

         
messageLabel.setText("Unable
to delete "+name+" from Products table");     
      

        prep.close();

       }

       catch(SQLException sqle)

       {

        messageLabel.setText("Error
during
delete :
"+sqle.getMessage());        

       }

        

    }

Modifying an existing database record from data on a form using key field data.

Your form should have a component of the proper type for each column in the table as well as a "Modify" button.
Tasks that should be done when "Modify" button is pressed:

retrieve the data from the components on the screen and save them in local variables
check the data to be sure it is valid (input verifiers can help)
create either a Statement object or a PreparedStatement object.
construct the SQL UPDATE command, be sure to include a WHERE clause. If there is more than one key field, then use an AND to form the WHERE clause. Your UPDATE command should include all non key columns in the SET clause and all key columns in the WHERE clause. e.g.
UPDATE products SET quantity = ?, cost = ? WHERE name = ?
tell the Statement or PreparedStatement object to executeUpdate()
catch SQL exceptions and check return status
report the success/failure to the user.

If the record did not already exist, then executeUpdate will return a 0 (for 0 records modified). If it did exist, then executeUpdate will return a 1

Example - Modifyin an existing product in the products table using a PreparedStatement object.

  private void
modifyButtonActionPerformed(java.awt.event.ActionEvent
evt) {

          //

        // retrieve and convert data
into local variables

        //

        String name =
nameField.getText();

        String strquantity =
quantityField.getText();

        String strcost =
costField.getText();

        short quantity = 0;;

        double cost = 0;

        try{

          quantity =
Short.parseShort(strquantity);

        }

        catch(NumberFormatException
e)

        {

        
messageLabel.setText("Invalid
entry in quantity field, must be a number");

         return;

        }

        try{

          cost =
Double.parseDouble(strcost);

        }

        catch(NumberFormatException
e)

        {

        
messageLabel.setText("Invalid
entry in cost field, must be a number");

         return;

        }

        //

        // test data for consistency
and accuracy

        //

        if(cost < 0)

        {

        
messageLabel.setText("Cost
must be greater than or equal to zero");

         return;

        }

        if(quantity < 0)

        {

        
messageLabel.setText("Quantity
must be greater than or equal to zero");

         return;

        }

        if(name.length() <= 0)

        {

        
messageLabel.setText("Name
cannot be blank");

         return;

        }

        //

        //  create and execute
a
prepared statement

        //

        try{

         
PreparedStatement
prep = con.prepareStatement("UPDATE products SET quantity = ?, cost = ?
WHERE
name = ?");

         
prep.setShort(1,quantity);

         
prep.setDouble(2,cost);

         
prep.setString(3,
name);

          int result =
prep.executeUpdate();

          if(result == 1)

            
messageLabel.setText(name+" modified in Products table");

          else

            
messageLabel.setText("Unable to modify "+name+" in Products table");

          prep.close(); //
close the PreparedStatement

         }

         catch (SQLException e)
      

         {

         
messageLabel.setText("Unable
to modify "+name+" in Products table : "+e.getMessage());

         }

    }

Finding records using non key data

The Update, Find, Delete commands shown above work fine if the key data is something that is readily known and can be typed into a textfield, e.g. a social security number, telephone number, or drivers license number. However, many tables use autonumber key fields or account numbers that no one should be expected to remember.
In these cases a facility to locate records using non key data such as last name, or partial key data such as part of a license plate number should be provided for.
If non key data or partial key data is used, then there is a good chance that multiple records will be found. If that is the case, then a facility needs to be designed to allow the user to select the proper record, then add/update, or delete that record.
Using a SELECT statement that uses the LIKE keyword in the WHERE clause on multiple fields is the best way to locate data based on non key values.
Displaying the results in a JTable is the best way to show them. This JTable need not display all the data for every record found, but it should display at a minimum the key fields and enough information to allow the user to know which record they want.
Steps to implement a search using non key or partial key data

Create a form with a field for each data item that you would like to be used in the search. Provide a "find" button.
When the find button is pressed

Retrieve search data from the form
On text fields, trim off leading/trailing spaces using the trim() method of Strings
On data fields, convert to the data type, check for valid values. If not convertable or invalid, either use a default value or display an error message.
Construct a Statement or Prepared statement to do the query. Text fields should use the LIKE keyword in the clause and append a "%" to whatever the user typed in. Numeric fields can use the user input as a minimum or maximum value to be used in the search. E.g. SELECT * FROM Products WHERE name LIKE ? AND cost > ?
Use executeQuery to perform the SELECT statement and get a result set
Display the results in a JTable
Catch SQL exceptions

Example : Use JTable in a JSplitPane to perform searchs on the Product table

private void
nonKeyFindButtonActionPerformed(java.awt.event.ActionEvent
evt) {

     //

     // retrieve data used in search

     //

     String strcost = minCostField.getText();

     String name = partialNameField.getText();

     double cost = 0;

     try{

       cost = Double.parseDouble(strcost);

     }

     catch(NumberFormatException e)

     {

      cost = 0.0;

      minCostField.setText("0.0");

     }

     name = name.trim();

     //

     //  create and execute a prepared
statement

     //

  

      try{

         PreparedStatement prep
=
con.prepareStatement("SELECT
* FROM Products WHERE name like ? AND cost > ? ");

        prep.setString(1, name+"%");

        prep.setDouble(2, cost);

        ResultSet rs =
prep.executeQuery();

   
DefaultTableModel tm = (DefaultTableModel)resultTable.getModel();

        tm.setRowCount(0);  //
clear the table

        int rows = 0;

        while(rs.next())

        {

         rows = rows+1;

         tm.setRowCount(rows);

        
resultTable.setValueAt(rs.getString("name"),rows-1,0);  // put
name in column 0

        
resultTable.setValueAt(rs.getString("quantity"),rows-1,1);  // put
qry in column 1

        
resultTable.setValueAt(rs.getString("cost"),rows-1,2);  // put
name in column 2

        }

 

        prep.close();

       }

       catch(SQLException sqle)

       {

        messageLabel.setText("Error
during
lookup :
"+sqle.getMessage());        

       }

    }

Transfering data from a search screen to a form

Once the user has been able to find the desired record using a partial key/partial data search, then you can provide one of two means for them to access and modify the record:

first, leave it up to them to write down the key field data, then go back to the edit form and enter it.
or, better, provide a mechanism that allows them to select one of the results and transfers the data for them.

Steps to implement a "transfer" button

Ask the results JTable for the row number that is selected.
Retrieve the key column data from the table
Load the key column data into the JTextFields on the editing form
Tell the JScrollPane to make the editing form (instead of the search form) visible
execute the editing form's find button routine

 private void
transferButtonActionPerformed(java.awt.event.ActionEvent
evt) {

        // Add your handling code
here:

        int row =
resultTable.getSelectedRow();
 // get the selected row.

        if(row < 0)  // row
selected
??

          return;

        String selectedName =
resultTable.getValueAt(row,0);
 // get the key data

       
nameField.setText(selectedName);
 // place it on the edit form

       
centerTabbedPane.setSelectedIndex(1);
// tell the tabbed panel to show the edit form

        findButton.doClick();
// execute the find button code from the edit form

    }