Java+ MySql Workbeanch Operation: This application begins by displaying a table

Question

Java+ MySql Workbeanch

Operation:

This application begins by displaying a table of customer data.

If the user clicks the Add button, the application allows the user to add customer data to the table (and the underlying database).

If the user selects a customer row and clicks the Edit button, the application allows the user to update the data for the selected customer row in the table (and the database).

If the user selects a customer row and clicks the Delete button, the application deletes the selected customer row from the table (and the database).

Specifications:

Create a table in the mma database described in chapter 19 to store the necessary data. To do that, you can use the SQL script stored in the create_customer_table.sql file that’s supplied. If this script isn’t supplied, you can create your own SQL script.

Create a class named Customer that stores data for the user’s id, email address, first name, and last name.

Create a class named CustomerDB that contains the methods necessary to get an array list of Customer objects, to get a Customer object for the customer with the specified id, and to add, update, or delete the specified customer.

Create a CustomerManagerFrame class like the one shown above. This frame should display a table of customer data as well as the Add, Edit, and Delete buttons. This class should use the Customer and CustomerDB classes to work with the customer data.

Create a CustomerForm class that allows the user to add or edit customer data.

This is .sql file with cutomer data.

USE mma;

DROP TABLE IF EXISTS Customer;

CREATE TABLE Customer
(
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
EmailAddress VARCHAR(50)
);

INSERT INTO Customer VALUES
(3, 'John', 'Smith', 'johnsmith@hotmail.com'),
(4, 'Frank', 'Jones', 'frankjones@yahoo.com'),
(5, 'Cynthia', 'Green', 'seagreen@levi.com'),
(6, 'Wendy', 'Kowolski', 'wendyk@warners.com');

Customer Manager Email frankiones nes@yahoo.com johnsmith@hotmail.com seagreen@levi.com wendyk@warners.com First Name Last Name Frank Jones John Smith Green Cynthia Wendy Kowolski Add Eidt Delete

Explanation / Answer

Class to create the connection with the database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionFactory {
   //static reference to itself
   private static ConnectionFactory instance = new ConnectionFactory();
   public static final String URL = "jdbc:mysql://localhost/jdbcdb";
   public static final String USER = "username";
   public static final String PASSWORD = "password";
   public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
  
   //private constructor
   private ConnectionFactory() {
       try {
           Class.forName(DRIVER_CLASS);
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       }
   }
  
   private Connection createConnection() {
       Connection connection = null;
       try {
           connection = DriverManager.getConnection(URL, USER, PASSWORD);
       } catch (SQLException e) {
           System.out.println("ERROR: Unable to Connect to Database.");
       }
       return connection;
   }  
  
   public static Connection getConnection() {
       return instance.createConnection();
   }
}

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbUtil {

   public static void close(Connection connection) {
       if (connection != null) {
           try {
               connection.close();
           } catch (SQLException e) {
               /*Ignore*/
           }
       }
   }

   public static void close(Statement statement) {
       if (statement != null) {
           try {
               statement.close();
           } catch (SQLException e) {
               /*Ignore*/
           }
       }
   }

   public static void close(ResultSet resultSet) {
       if (resultSet != null) {
           try {
               resultSet.close();
           } catch (SQLException e) {
               /*Ignore*/
           }
       }
   }
}ss

1)The Customer class to stores data for the user’s id, email address, first name, and last name.
public class Customer {
   static int customerID = 0;
   static String FirstName = null;
   static String LastName = null;
   static String EmailAddress = null;
   public static int getCustomerID() {
       return customerID;
   }
   public static void setCustomerID(int customerID) {
       Customer.customerID = customerID;
   }
   public static String getFirstName() {
       return FirstName;
   }
   public static void setFirstName(String firstName) {
       FirstName = firstName;
   }
   public static String getLastName() {
       return LastName;
   }
   public static void setLastName(String lastName) {
       LastName = lastName;
   }
   public static String getEmailAddress() {
       return EmailAddress;
   }
   public static void setEmailAddress(String emailAddress) {
       EmailAddress = emailAddress;
   }
}

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CustomerDB {
   private Connection connection;
   private Statement statement;

   public CustomerDB() { }

   public Customer getCustomer(int customerID) throws SQLException {
       String query = "SELECT * FROM employee WHERE emp_id=" + customerID;
       ResultSet rs = null;
       Customer customer = null;
       try {
           connection = ConnectionFactory.getConnection();
           statement = connection.createStatement();
           rs = statement.executeQuery(query);
           if (rs.next()) {
               customer = new Customer();
               customer.setCustomerID(rs.getInt("customerID"));
               customer.setFirstName(rs.getString("FirstName"));
               customer.setLastName(rs.getString("LastName"));
               customer.setEmailAddress(rs.getString("EmailAddress"));
           }
       } finally {
           DbUtil.close(rs);
           DbUtil.close(statement);
           DbUtil.close(connection);
       }
       return customer;
   }
}

Related Questions

Navigate

• Browse (All)
• Browse J
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.