Please create in JAVA and mySQLite OR SQL. Which ever is more convienents. Please note if you used SQL OR mySQLite.

Please provide 1 or 2 screen shots so i know what to expect when i run the program.

Thank you!

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.

*****The code and classes are at the bottom to modify and for a reference*****

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. Thats 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 thats 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?

****Here is code from previous mySQLite program*****

//Person.java

public class Person { private long ssn; private String firstName; private String lastName; private int age; private long creditCard;

public Person(String firstName, String lastName, int age, long creditCard, long ssn) { this.ssn = ssn; this.firstName = firstName; this.lastName = lastName; this.age = age; this.creditCard = creditCard; }

public Person() { }

public long getSsn() { return ssn; }

public void setSsn(long ssn) { this.ssn = ssn; }

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 int getAge() { return age; }

public void setAge(int age) { this.age = age; }

public long getCreditCard() { return creditCard; }

public void setCreditCard(long creditCard) { this.creditCard = creditCard; }

@Override public boolean equals(Object o) { if (this == o) return true; if (!(o instanceof Person)) return false;

Person person = (Person) o;

return getSsn() == person.getSsn();

}

@Override public int hashCode() { return (int) (getSsn() ^ (getSsn() >>> 32)); }

@Override public String toString() { return "Person{" + "firstName='" + firstName + '\'' + ", lastName='" + lastName + '\'' + ", age=" + age + ", creditCard=" + creditCard + ", ssn=" + ssn + '}'; } }

===================================================================================================

//Main.java

import java.sql.*;

import java.util.ArrayList;

public class Main {

public static void main(String[] args) {

Person person = new Person("Chase", "Sin", 34, 5136112, 12345);

Person person1 = new Person("Pat", "jeater", 66, 5136113, 12346);

Person person2 = new Person("Holly", "martin", 62, 5136114, 12347);

Person person3 = new Person("Mickey", "Mouse", 100, 5136115, 12348);

Person person4 = new Person("Donald", "Duck", 78, 5136116, 12349);

Person person5 = new Person("Whiley", "Cyote", 79, 5136117, 12350);

createTable();

insertPerson(person);

insertPerson(person1);

insertPerson(person2);

insertPerson(person3);

insertPerson(person4);

insertPerson(person5);

System.out.println(selectPerson(4));

for (Person p : findAllPeople()) {

System.out.println(p);

}

deletePerson(4);

for (Person p : findAllPeople()) {

System.out.println(p);

}

}

public static Connection deletePerson(int id) {

Connection connection = null;

Statement stmt = null;

try {

connection = getConnection();

connection.setAutoCommit(false);

stmt = connection.createStatement();

String sql = "DELETE from MARK where ID=" + id + ";";

stmt.executeUpdate(sql);

connection.commit();

connection.close();

System.out.println("(Deleted Person " + id + ") done successfully");

} catch (Exception e) {

System.out.println(e);

connection = null;

}

return connection;

}

public static ArrayList findAllPeople() {

Connection connection = null;

Statement statement = null;

ArrayList person = new ArrayList<>();

try {

connection = getConnection();

connection.setAutoCommit(false);

statement = connection.createStatement();

ResultSet rs = statement.executeQuery("SELECT * FROM MARK;");

while (rs.next()) {

person.add(new Person(rs.getString("firstname"), rs.getString("lastname"), rs.getInt("age"),

rs.getInt("creditcard"), rs.getInt("ssn")));

}

rs.close();

statement.close();

connection.close();

System.out.println("(Find All People) done successfully");

} catch (Exception e) {

System.out.println(e);

person = null;

}

return person;

}

public static Person selectPerson(int id) {

Connection connection = null;

Statement statement = null;

Person person = new Person();

try {

connection = getConnection();

connection.setAutoCommit(false);

statement = connection.createStatement();

ResultSet rs = statement.executeQuery("SELECT * FROM MARK where ID=" + id + ";");

while (rs.next()) {

person.setFirstName(rs.getString("firstname"));

person.setLastName(rs.getString("lastname"));

person.setAge(rs.getInt("age"));

person.setCreditCard(rs.getInt("creditcard"));

person.setSsn(rs.getInt("ssn"));

}

rs.close();

statement.close();

connection.close();

System.out.println("(Select Person " + id + ") done successfully");

} catch (Exception e) {

System.out.println(e);

person = null;

}

return person;

}

public static Connection insertPerson(Person person) {

Connection connection = null;

Statement statement = null;

try {

connection = getConnection();

connection.setAutoCommit(false);

statement = connection.createStatement();

String sql = "INSERT INTO MARK (FIRSTNAME,LASTNAME,AGE,CREDITCARD,SSN) " + "VALUES (" + "'"

+ person.getFirstName() + "'," + "'" + person.getLastName() + "'," + person.getAge() + ","

+ person.getCreditCard() + "," + person.getSsn() + " );";

statement.executeUpdate(sql);

statement.close();

connection.commit();

connection.close();

System.out.println(

"(Insert Person " + person.getFirstName() + " " + person.getLastName() + ") done successfully");

} catch (Exception e) {

System.out.println(e);

connection = null;

}

return connection;

}

public static Connection createTable() {

Connection connection = null;

Statement statement = null;

try {

connection = getConnection();

statement = connection.createStatement();

String sql = "CREATE TABLE MARK " + "(ID INTEGER PRIMARY KEY AUTOINCREMENT, "

+ "FIRSTNAME CHAR(30) NOT NULL, " + "LASTNAME CHAR(30) NOT NULL, "

+ "AGE INT , " + "CREDITCARD BIGINT , "

+ "SSN BIGINT ) ";

statement.executeUpdate(sql);

statement.close();

connection.close();

System.out.println("Table created successfully");

} catch (Exception e) {

System.out.println(e);

connection = null;

}

return connection;

}

public static Connection getConnection() {

Connection connection = null;

try {

Class.forName("org.sqlite.JDBC");

connection = DriverManager.getConnection("jdbc:sqlite:MyDatabase.db");

System.out.println("Opened database successfully");

} catch (Exception e) {

System.out.println(e);

connection = null;

}

return connection;

}

}