***JAVA*** I\'m having difficulties removing a customer out of MySQL databse thr
ID: 3719099 • Letter: #
Question
***JAVA***
I'm having difficulties removing a customer out of MySQL databse through the JTable (.getSelectedRow()) method, I want to use my 'Delete' button to simply remove a highlighted row from both my JTable and the linked MySQL databse.
My button Action Event
public void actionPerformed(ActionEvent e) {
if ("Add".equals(e.getActionCommand())) {
CustomerForm form = new CustomerForm(db, null, this);
db = form.getDB();
}
else if ("Edit".equals(e.getActionCommand())) {
int index = CustomerManagerFrame.this.jt.getSelectedRow();
if (index != -1) {
Customer selCustomer = db.getCustomer(index + 1);
CustomerForm form = new CustomerForm(db, selCustomer, this);
db = form.getDB();
}
}
//this is what is supposed to happen if the 'Delete' button is pressed
else {
int index = CustomerManagerFrame.this.jt.getSelectedRow();
if (index != -1)
try {
db.deleteCustomer(index);
} catch (SQLException ex) {
}
displayTable(db);
}
}
This is my DB class with methods.
The addCustomer works correctly and it adds the customer to the JTable and inputs them into the SQL db. I've commented out portions of the delete method I was trying to implement, currently it will only delete the customer from the JTable.
public class CustomerDB {
ArrayList<Customer> customers;
public CustomerDB() throws SQLException {
customers = new ArrayList<>();
Connection toDB = DriverManager.getConnection("jdbc:MySQL://localhost:3306/mma", "mma_user", "sesame");
Statement newStatement = toDB.createStatement();
ResultSet results = newStatement.executeQuery("SELECT * FROM customer_list");
while (results.next()){
String Email = results.getString("Email");
String FirstName = results.getString("FirstName");
String LastName = results.getString("LastName");
customers.add(new Customer(FirstName, LastName, Email));
}
}
public void addCustomer(Customer c) throws SQLException {
Connection toDB = DriverManager.getConnection("jdbc:MySQL://localhost:3306/mma", "mma_user", "sesame");
try (PreparedStatement ps = toDB.prepareStatement("INSERT INTO Customer_List (Email, FirstName, LastName) VALUES (?, ?, ?)")) {
customers.add(c);
ps.setString(1, c.getEmail());
ps.setString(2, c.getFirstName());
ps.setString(3, c.getLastName());
ps.executeUpdate();
}
}
public Customer getCustomer(int id) {
for (int i = 0; i < customers.size(); i++) {
if (customers.get(i).Id == id) {
return customers.get(i);
}
}
return null;
}
public void updateCustomer(Customer c) {
for (int i = 0; i < customers.size(); i++) {
if (customers.get(i).Id == c.Id) {
customers.remove(i);
customers.add(i, c);
}
}
}
public void deleteCustomer(int i) throws SQLException {
//Connection toDB = DriverManager.getConnection("jdbc:MySQL://localhost:3306/mma", "mma_user", "sesame");
//String sql = "DELETE FROM Customer_List " + "WHERE Email = ?";
//PreparedStatement ps = toDB.prepareStatement(sql);
customers.remove(i);
//ps.setInt(1, (i));
//ps.executeUpdate();
}
public ArrayList getCustomers() {
return customers;
}
}
Explanation / Answer
Hi there is a small mistake in delete method.......your try to remove the Customer object in a customer list(you declared at top) by using parmeter(Index i) is totally wrong......first get the object by parmeter using for loop or for each loop and delete that object from the list of coustomers and db too....steps are under below,
for(Customer cust : customers){
if(cust.getId()==i){
customers.remove(cust); //removes the object from list
}
}
In prepared statement you your comparing string to int how could it delete just change that to id or something equalized one....that two errors are there please see and modify carefully.
In for each also try to equalize the approiprate parmeters that you passed in the method otherwise it wont delete....the main thing is just by using parameter get the object and delete it from list using remove() method.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.