Create a 3 layer application in Java, where the top layer is the User Interface,
ID: 3822791 • Letter: C
Question
Create a 3 layer application in Java, where the top layer is the User Interface, the middle layer is the ‘business’ layer, and the bottom layer is the database connectivity. The user interface will present a GUI to the user, asking them to enter some information, and to interact with the stored data. Refer to your previous assignments. You can use your code or start from scratch. (The exact functionality provided to the user is deliberately vague here.) Create a connection to a relational database using SQLite or MySQL. Create a single database table to hold information. Go back to the previous assignments, and re-use your classes, or modify them as you like. Recall that you will create the DB only once. You can just comment out the code that creates it if you like. Some people find a way to detect if the DB is already there. That’s fine, but not required. These requirements are taken from the previous assignment. This time the user is to be presented with a GUI that allows these things to be done. So the data is not hard coded, the user will fill in a GUI click a button to interact with the DB. When you retrieve the data from the database it should populate the GUI to demonstrate that it is working. Demonstrate the insertion of a record into the database. Create a method in the Data layer that takes a Person as a parameter, and puts that data in the database. Insert several records. Demonstrate the retrieval of information from the database. Use SQL Select statements. Write a method called getPerson that returns a Person object. This method retrieves the data for a Person from the database. We also need to pass a parameter to identify what person. You can use ‘name’ if you like, or if you find it easier to use the database generated ID that’s fine too. This method returns the object that represents that person. This will require that you extract the data that is returned from the database, and call the Person constructor. Note that this is the data-exchange between the relational database and the business layer. Write a method called findAllPeople that returns an ArrayList of objects containing all the people in the database. Write a method called deletePerson that removes a person from the database. The parameters will be first name and last name. Print out on the console the data from the record that is being deleted. Use your findAllPeople method to verify that that person has been removed from the database. Consider what this method should return. Suppose the person is not found, should the method return that information somehow?
*****My eclipse is not properly set up with sql please attach screenshot of program outputting the GUI and the expected output please :(
Explanation / Answer
PersonDBManager.java
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;
public class PersonDBManager {
// Database layer object
private PersonDBConnect connection;
public PersonDBManager(String dbName) throws SQLException, ClassNotFoundException {
connection = new PersonDBConnect(dbName);
// People table is created only if it doesn't exist.
connection.createTableUnique("People",
"(ID INT PRIMARY KEY NOT NULL, "
+ "First_Name TEXT NOT NULL, "
+ "Last_Name TEXT NOT NULL, "
+ "Number TEXT NOT NULL, "
+ "Email TEXT NOT NULL, "
+ "Student INT NOT NULL, "
+ "Employed INT NOT NULL)");
}
public PersonDBConnect getConnection() {
return connection;
}
public void setConnection(PersonDBConnect connection) {
this.connection = connection;
}
public int generatePersonID() throws SQLException {
int genID = 0;
ResultSet rs = connection.queryDatabase("SELECT ID FROM People ORDER BY ID DESC");
if (rs.next()) {
genID = rs.getInt(1) + 1;
}
return genID;
}
public void addPersonToDB(Person person) throws SQLException {
int studentBooleanValue = (person.isStudent()) ? 1 : 0;
int employedBooleanValue = (person.isEmployed()) ? 1 : 0;
String sqlString =
"INSERT INTO People (ID, First_Name, Last_Name, "
+ "Number, Email, Student, Employed) VALUES ("
+ "'"
+ person.getId()
+ "', '"
+ person.getFirstName()
+ "', '"
+ person.getLastName()
+ "', '"
+ person.getNumber()
+ "', '"
+ person.getEmail()
+ "', "
+ studentBooleanValue + ", " + employedBooleanValue + ")";
connection.updateDatabase(sqlString);
}
public Person getPerson(int personID) throws SQLException {
String query = "SELECT * FROM People WHERE ID = " + personID;
ResultSet rs = connection.queryDatabase(query);
// Initialize
String firstName = "";
String lastName = "";
String number = "";
String email = "";
boolean isStudent = false;
boolean isEmployed = false;
while (rs.next()) {
firstName = rs.getString(2);
lastName = rs.getString(3);
number = rs.getString(4);
email = rs.getString(5);
isStudent = (rs.getInt(6) == 1);
isEmployed = (rs.getInt(7) == 1);
}
return new Person(personID, firstName, lastName, number, email, isStudent, isEmployed);
}
public ArrayList<Person> findAllPeople() throws SQLException {
String query = "SELECT * FROM People";
ResultSet set = connection.queryDatabase(query);
ArrayList<Person> resultArrayList = new ArrayList<Person>();
// Fill the ArrayList
while (set.next()) {
resultArrayList.add(getPerson(set.getInt(1)));
}
return resultArrayList;
}
public boolean deletePerson(String firstName, String lastName) throws SQLException {
boolean successful = false;
String query = "SELECT ID FROM People WHERE First_Name = '" + firstName + "' AND Last_Name = '" + lastName + "'";
ResultSet rs = connection.queryDatabase(query);
// Initialize
int personID = 0;
// Get the Person ID
while (rs.next()) {
personID = rs.getInt(1);
}
//Stores the Person object before it gets deleted.
Person deletedPerson = getPerson(personID);
connection.updateDatabase("DELETE FROM People WHERE ID = " + personID);
/*
* If the deleted person is not in the findAllPeople()
* ArrayList then the deletion was successful.
*/
if (!findAllPeople().contains(getPerson(personID))) {
successful = true;
System.out.println("Recored Deleted: " + deletedPerson.toString());
} else {
System.out.println("Error Deleting Person ID: " + personID);
successful = false;
}
return successful;
}
public DefaultTableModel buildTableModel() throws SQLException {
// Execute the Query
ResultSet rs = connection.queryDatabase("SELECT * FROM People");
ResultSetMetaData metaData = rs.getMetaData();
Vector<String> columnNames = new Vector<String>();
int columnCount = metaData.getColumnCount();
// Add the column names to the columnNames object
for (int column = 1; column <= columnCount; column++) {
columnNames.add(metaData.getColumnName(column));
}
// Table Data
Vector<Vector<Object>> data = new Vector<Vector<Object>>();
while (rs.next()) {
Vector<Object> vector = new Vector<Object>();
for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
vector.add(rs.getObject(columnIndex));
}
data.add(vector);
}
return new DefaultTableModel(data, columnNames);
}
}
Person.java
public class Person {
private int id;
private String firstName;
private String lastName;
private String number;
private String email;
private boolean isStudent;
private boolean isEmployed;
public Person(int id, String firstName, String lastName,String number, String email, boolean isStudent,boolean isEmployed) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.number = number;
this.email = email;
this.isStudent = isStudent;
this.isEmployed = isEmployed;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public boolean isStudent() {
return isStudent;
}
public void setStudent(boolean isStudent) {
this.isStudent = isStudent;
}
public boolean isEmployed() {
return isEmployed;
}
public void setEmployed(boolean isEmployed) {
this.isEmployed = isEmployed;
}
@Override
public String toString() {
String result;
result = "ID: " + id + " Name: " + firstName + lastName
+ " Number: " + number + " Email: " + email;
return result;
}
}
PersonDBConnect.java
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class PersonDBConnect {
private Connection connection;
public PersonDBConnect(String dbName) throws ClassNotFoundException, SQLException {
/*
* SQLite .jar file should be a library added to the build path
*/
Class.forName("org.sqlite.JDBC");
connection = DriverManager.getConnection("jdbc:sqlite:" + dbName + ".db");
}
public ResultSet queryDatabase(String queryString) throws SQLException {
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(queryString);
return rs;
}
public void createTableUnique(String tableName, String tableFields) throws SQLException {
Statement statement = connection.createStatement();
statement.executeUpdate("CREATE TABLE IF NOT EXISTS " + tableName + tableFields);
}
public void updateDatabase(String sqlString) throws SQLException {
Statement statement = connection.createStatement();
statement.executeUpdate(sqlString);
}
public void closeConnection() throws SQLException {
connection.close();
}
}
PersonDBInterface.java
import java.awt.Component;
import java.awt.EventQueue;
import javax.swing.ButtonGroup;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JTabbedPane;
import javax.swing.JPanel;
import javax.swing.JRadioButton;
import javax.swing.JTextField;
import javax.swing.JComboBox;
import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JTable;
import javax.swing.border.BevelBorder;
import javax.swing.text.JTextComponent;
import javax.swing.JScrollPane;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.io.File;
import java.io.FileFilter;
import java.sql.SQLException;
import java.awt.Font;
public class PersonDBInterface {
private PersonDBManager personDBM;
private JFrame frmPersonDatabase;
// Displays the connection status
private JLabel statusBarLabel;
// Container for the 4 main panels
private JTabbedPane tabbedPane;
// Panel to establish DB connections
private JPanel connectPanel;
private JLabel lblConnectTitle;
private JTextField createDBTextbox;
private JComboBox<String> existingDBComboBox;
private JRadioButton rdbtnUseExistingDB;
private JRadioButton rdbtnCreateDB;
private ButtonGroup radioGroup;
// Displays any connection errors
private JLabel lblError;
private JButton btnConnect;
// Panel to add records to the table
private JPanel addPanel;
private JButton btnClearAll;
private JButton btnAdd;
private JTextField firstNameTextBox;
private JTextField lastNameTextBox;
private JTextField numberTextbox;
private JTextField emailTextbox;
private JCheckBox chckbxStudent;
private JCheckBox chckbxEmployed;
// Displays database insertion errors
private JLabel lblAddError;
// Shows all elements in the table
private JPanel browsePanel;
private JButton btnShowAll;
private JTable browseTable;
// Displays database query errors
private JLabel lblBrowseError;
// Deletes a given person from the DB
private JPanel deletePanel;
private JComboBox<String> nameComboBox;
private JButton btnDelete;
private JLabel lblDeleteError;
// Displays row deletion errors
private JLabel lblDeleteTitle;
private JScrollPane scrollPane;
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
// Call the constructor for this class
PersonDBInterface window = new PersonDBInterface();
//Set it visible
window.frmPersonDatabase.setVisible(true);
} catch (Exception e) {
e.printStackTrace();
}
}
});
}
public PersonDBInterface() {
initialize();
}
public void getDBConnection() {
// Holds the DB name
String dbName = null;
// Create or use existing DB
if (rdbtnCreateDB.isSelected()) {
dbName = createDBTextbox.getText();
} else if (rdbtnUseExistingDB.isSelected()) {
// Only get the substring before ".db"
dbName = ((String) existingDBComboBox.getSelectedItem()).split(".db")[0];
}
try {
// Establishes connection to the database
personDBM = new PersonDBManager(dbName);
lblError.setText("Connected Successfully!");
System.out.println("Connected Successfully!");
statusBarLabel.setText("Connected to " + dbName);
// Allow user to use buttons
toggleAllButtons(true);
} catch (ClassNotFoundException cnfe) {
// Display Error message and lock buttons
lblError.setText("Connect Error - " + cnfe.getMessage());
System.out.println("Connect Error - " + cnfe.getMessage());
statusBarLabel.setText("Not Connected");
toggleAllButtons(false);
} catch (SQLException sqle) {
// Display Error message and lock buttons
lblError.setText("ERROR - " + sqle.getMessage());
System.out.println("Connect Error - " + sqle.getMessage());
statusBarLabel.setText("Not Connected");
toggleAllButtons(false);
}
}
public boolean isValidInput() {
boolean result = false;
// All Fields need to be filled to be valid
if ((!firstNameTextBox.getText().isEmpty() && !lastNameTextBox.getText().isEmpty())
&& (!numberTextbox.getText().isEmpty() && !emailTextbox.getText().isEmpty())) {
result = true;
} else {
lblAddError.setText("Input for this person is not valid. Please fill in all fields");
}
return result;
}
public void toggleAllButtons(boolean state) {
btnAdd.setEnabled(state);
btnClearAll.setEnabled(state);
btnShowAll.setEnabled(state);
btnDelete.setEnabled(state);
}
public void populateExistingDBComboBox() {
// Reset Items
existingDBComboBox.removeAllItems();
// Read the names of all files that end in ".db"
File parent = new File(System.getProperty("user.dir"));
File[] children = parent.listFiles(new FileFilter() {
public boolean accept(File file) {
return file.getName().endsWith(".db");
}
});
for (int i = 0; i < children.length; i++) {
existingDBComboBox.insertItemAt(children[i].getName(), i);
}
}
public void populateNameComboBox() {
nameComboBox.removeAllItems();
try {
for (Person p : personDBM.findAllPeople()) {
nameComboBox.addItem(p.getLastName() + ", " + p.getFirstName());
}
} catch (SQLException sqle) {
lblDeleteError.setText("Delete Error - " + sqle.getMessage());
}
// Lock the Delete button if there's nothing to delete
if (nameComboBox.getItemCount() == 0) {
btnDelete.setEnabled(false);
} else {
btnDelete.setEnabled(true);
}
}
private void initialize() {
// Create the main Frame
frmPersonDatabase = new JFrame();
frmPersonDatabase.setTitle("Person Database");
frmPersonDatabase.setBounds(100, 100, 497, 353);
frmPersonDatabase.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frmPersonDatabase.getContentPane().setLayout(null);
// Create the Tabbed Panel over the main Frame
tabbedPane = new JTabbedPane(JTabbedPane.TOP);
tabbedPane.setBounds(0, 5, 495, 293);
frmPersonDatabase.getContentPane().add(tabbedPane);
// Create the Connect Panel
connectPanel = new JPanel();
tabbedPane.addTab("Connect", null, connectPanel, null);
connectPanel.setLayout(null);
// Create a Title label for the Connect Panel
lblConnectTitle = new JLabel("Connect to a Person Database");
lblConnectTitle.setBounds(109, 5, 217, 15);
connectPanel.add(lblConnectTitle);
// Add Radiobuttons on the Connect Panel
rdbtnUseExistingDB = new JRadioButton("Use Existing DB");
rdbtnUseExistingDB.setBounds(73, 92, 149, 23);
rdbtnUseExistingDB.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
existingDBComboBox.setEnabled(true);
createDBTextbox.setEnabled(false);
btnConnect.setEnabled(true);
}
});
connectPanel.add(rdbtnUseExistingDB);
rdbtnCreateDB = new JRadioButton("Create DB");
rdbtnCreateDB.setBounds(73, 44, 149, 23);
rdbtnCreateDB.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
existingDBComboBox.setEnabled(false);
createDBTextbox.setEnabled(true);
btnConnect.setEnabled(true);
}
});
connectPanel.add(rdbtnCreateDB);
radioGroup = new ButtonGroup();
radioGroup.add(rdbtnCreateDB);
radioGroup.add(rdbtnUseExistingDB);
// Add Textbox to Connect Panel
createDBTextbox = new JTextField();
createDBTextbox.setBounds(246, 49, 177, 19);
createDBTextbox.setEnabled(false);
connectPanel.add(createDBTextbox);
createDBTextbox.setColumns(10);
// Add Combobox to Connect Panel
existingDBComboBox = new JComboBox<String>();
existingDBComboBox.setBounds(246, 92, 177, 24);
existingDBComboBox.setEnabled(false);
// Populate Combobox with Existing Databases
populateExistingDBComboBox();
connectPanel.add(existingDBComboBox);
// Add Connect Button to the Connect Panel
btnConnect = new JButton("Connect");
btnConnect.setBounds(306, 144, 117, 25);
btnConnect.setEnabled(false);
btnConnect.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (personDBM != null) {
try {
// Close the connection and open a new one
personDBM.getConnection().closeConnection();
personDBM = null;
getDBConnection();
} catch (SQLException sqle) {
lblError.setText("Error disconnecting from DB - " + sqle.getMessage());
}
} else {
getDBConnection();
}
populateExistingDBComboBox();
populateNameComboBox();
}
});
connectPanel.add(btnConnect);
// Add a error label to the connect panel
lblError = new JLabel("");
lblError.setFont(new Font("Dialog", Font.BOLD, 10));
lblError.setBounds(67, 181, 356, 45);
connectPanel.add(lblError);
// Add status label to the bottom of the connect panel
statusBarLabel = new JLabel("Not Connected");
statusBarLabel.setBounds(0, 303, 428, 21);
frmPersonDatabase.getContentPane().add(statusBarLabel);
// Add the Add Person Panel to the Tabbed Panel
addPanel = new JPanel();
tabbedPane.addTab("Add", null, addPanel, null);
addPanel.setLayout(null);
JLabel lblSubtitle = new JLabel("Please enter a Person's information.");
lblSubtitle.setBounds(106, 14, 314, 15);
addPanel.add(lblSubtitle);
JLabel lblFirstName = new JLabel("First Name");
lblFirstName.setBounds(48, 41, 100, 15);
addPanel.add(lblFirstName);
JLabel lblNumber = new JLabel("Number");
lblNumber.setBounds(48, 101, 70, 15);
addPanel.add(lblNumber);
JLabel lblEmail = new JLabel("Email");
lblEmail.setBounds(48, 134, 70, 15);
addPanel.add(lblEmail);
JLabel lblLastName = new JLabel("Last Name");
lblLastName.setBounds(48, 74, 100, 15);
addPanel.add(lblLastName);
chckbxStudent = new JCheckBox("Student");
chckbxStudent.setBounds(254, 165, 89, 23);
addPanel.add(chckbxStudent);
chckbxEmployed = new JCheckBox("Employed");
chckbxEmployed.setBounds(150, 165, 100, 23);
addPanel.add(chckbxEmployed);
btnClearAll = new JButton("Clear All");
btnClearAll.setBounds(202, 229, 117, 25);
btnClearAll.addActionListener(new ActionListener() {
/*
* Clears all the text fields and checkboxes.
*/
public void actionPerformed(ActionEvent e) {
for (Component C : addPanel.getComponents()) {
if (C instanceof JTextField) {
((JTextComponent) C).setText("");
}
if (C instanceof JCheckBox) {
((JCheckBox) C).setSelected(false);
}
}
}
});
addPanel.add(btnClearAll);
btnAdd = new JButton("Add");
btnAdd.setBounds(342, 229, 117, 25);
btnAdd.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (isValidInput()) {
try {
personDBM.addPersonToDB(new Person(
personDBM.generatePersonID(),
firstNameTextBox.getText(),
lastNameTextBox.getText(),
numberTextbox.getText(),
emailTextbox.getText(),
chckbxStudent.isSelected(),
chckbxEmployed.isSelected()));
lblAddError.setText(
"Person successfully added to Database.");
browseTable.removeAll();
populateNameComboBox();
for (Component C : addPanel.getComponents()) {
if (C instanceof JTextField) {
((JTextComponent) C).setText("");
}
if (C instanceof JCheckBox) {
((JCheckBox) C).setSelected(false);
}
}
} catch (SQLException sqle) {
lblAddError.setText(sqle.getMessage());
}
}
}
});
addPanel.add(btnAdd);
firstNameTextBox = new JTextField();
firstNameTextBox.setBounds(150, 39, 309, 19);
addPanel.add(firstNameTextBox);
firstNameTextBox.setColumns(10);
lastNameTextBox = new JTextField();
lastNameTextBox.setColumns(10);
lastNameTextBox.setBounds(150, 72, 309, 19);
addPanel.add(lastNameTextBox);
numberTextbox = new JTextField();
numberTextbox.setBounds(150, 101, 309, 19);
addPanel.add(numberTextbox);
numberTextbox.setColumns(10);
emailTextbox = new JTextField();
emailTextbox.setBounds(150, 132, 309, 19);
addPanel.add(emailTextbox);
emailTextbox.setColumns(10);
lblAddError = new JLabel("");
lblAddError.setFont(new Font("Dialog", Font.BOLD, 10));
lblAddError.setBounds(48, 186, 411, 36);
addPanel.add(lblAddError);
// Creates a Delete Panel to the Tabbed Panel
deletePanel = new JPanel();
tabbedPane.addTab("Delete", null, deletePanel, null);
deletePanel.setLayout(null);
nameComboBox = new JComboBox<String>();
nameComboBox.setBounds(27, 79, 246, 24);
deletePanel.add(nameComboBox);
lblDeleteError = new JLabel("");
lblDeleteError.setFont(new Font("Dialog", Font.BOLD, 10));
lblDeleteError.setBounds(26, 160, 427, 40);
deletePanel.add(lblDeleteError);
btnDelete = new JButton("Delete");
btnDelete.setBounds(27, 123, 117, 25);
btnDelete.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
String firstName =
((String) nameComboBox.getSelectedItem()).split(", ")[1];
String lastName =
((String) nameComboBox.getSelectedItem()).split(", ")[0];
try {
if (personDBM.deletePerson(firstName, lastName)) {
browseTable.removeAll();
populateNameComboBox();
lblDeleteError.setText(firstName
+ "'s record has been deleted successfully.");
}
} catch (SQLException sqle) {
lblDeleteError.setText("Delete Error - "
+ sqle.getMessage());
System.out.println("Delete Error - " +
sqle.getMessage());
}
}
});
deletePanel.add(btnDelete);
lblDeleteTitle = new JLabel(
"Select for a Person's name to delete.");
lblDeleteTitle.setBounds(26, 41, 324, 15);
deletePanel.add(lblDeleteTitle);
// Add a Browse Panel to the Tabed Panel
browsePanel = new JPanel();
tabbedPane.addTab("Browse", null, browsePanel, null);
browsePanel.setLayout(null);
btnShowAll = new JButton("Show All Persons");
btnShowAll.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
try {
browseTable.setModel(personDBM.buildTableModel());
} catch (SQLException sqle) {
lblBrowseError.setText("Error - "
+ sqle.getMessage());
System.out.println("Error - "
+ sqle.getMessage());
}
}
});
btnShowAll.setBounds(12, 27, 169, 25);
browsePanel.add(btnShowAll);
toggleAllButtons(false);
scrollPane = new JScrollPane();
scrollPane.setBounds(12, 64, 466, 162);
browsePanel.add(scrollPane);
browseTable = new JTable();
browseTable.setFillsViewportHeight(true);
browseTable.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
scrollPane.setViewportView(browseTable);
browseTable.setBorder(new BevelBorder(BevelBorder.LOWERED,null, null, null, null));
lblBrowseError = new JLabel("");
lblBrowseError.setBounds(22, 229, 401, 25);
browsePanel.add(lblBrowseError);
}
}