Dungeoun Dungeoun - 1 month ago 6
Java Question

Java: Why all rows getting deleted when only the selected row should get deleted?

I'm trying to delete the data from database, when a row is selected from JTable and the button

BtnDelete1
clicked. The operation should delete the rows from different tables. But when I click the delete button, the selected row is getting deleted from all the tables except "EMAIL_ADDRESSES" table, where all the rows are getting deleted.

Here's my Stored Procedure for Deleting the rows where
fnumber
is given:

CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_contact`(IN fnumber int(2))
BEGIN

DELETE FROM PHONE_NUMBERS
WHERE F_number = fnumber;

DELETE FROM PHYSICAL_ADDRESS
WHERE F_number = fnumber;

DELETE FROM EMAIL_ADDRESSES
WHERE FNumber = fnumber;

DELETE FROM APPOINTMENTS
WHERE Fr_No = fnumber;

DELETE FROM FRIEND
WHERE F_no = fnumber;

END


Here's my button
BtnDelete1
code:

public class BtnDelete1 extends AbstractAction {


private FirstSwingApp mainGui;

public BtnDelete1(FirstSwingApp mainGui) {
super("Press Me");
putValue(MNEMONIC_KEY, KeyEvent.VK_P);
this.mainGui = mainGui;
}

public void actionPerformed(ActionEvent e) {

Object cell = mainGui.getSelectedCell();

int fnumber = ((int) noText.getText().charAt(0));

CallableStatement dstmt = null;
CallableStatement cstmt = null;

ResultSet rs;


try {

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Contact_Manager?user=root");


String SQL = "{call delete_contact (?)}";

String disQuery = "\n" +
"select FRIEND.F_Name, FRIEND.F_no,FRIEND.Birth_date,FRIEND.Gender,FRIEND.City,FRIEND.State, PHONE_NUMBERS.Extension, PHONE_NUMBERS.Ph_No, PHYSICAL_ADDRESS.Address_Line1, PHYSICAL_ADDRESS.Address_Line2, EMAIL_ADDRESSES.Email_Address, EMAIL_ADDRESSES.Platform, APPOINTMENTS.Notes, APPOINTMENTS.Meeting,APPOINTMENTS.Calls\n" +
"from FRIEND,PHONE_NUMBERS, PHYSICAL_ADDRESS, EMAIL_ADDRESSES, APPOINTMENTS\n" +
"where FRIEND.F_no = PHONE_NUMBERS.F_number = PHYSICAL_ADDRESS.F_number = EMAIL_ADDRESSES.FNumber = APPOINTMENTS.Fr_No;\n";

dstmt = conn.prepareCall(disQuery);
cstmt = conn.prepareCall(SQL);

cstmt.setInt(1, fnumber);

cstmt.executeQuery();

rs = dstmt.executeQuery();

ResultSetMetaData metaData = rs.getMetaData();

// names of columns
Vector<String> columnNames = new Vector<String>();
int columnCount = metaData.getColumnCount();
for (int column = 1; column <= columnCount; column++) {
columnNames.add(metaData.getColumnName(column));
}

// data of the table
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);
}

// It creates and displays the table


model.setDataVector(data, columnNames);

// Closes the Connection

dstmt.close();
System.out.println("Success!!");
} catch (SQLException ex) {

System.out.println("Error in connection: " + ex.getMessage());
}


}

}


And here's my code where the data gets populated in the text fields when I click a particular row in JTable
table
:

table.getSelectionModel().addListSelectionListener(new ListSelectionListener() {
@Override
public void valueChanged(ListSelectionEvent e) {
int row = table.getSelectedRow();
System.out.println("Selecte table row = " + row);
if (row != -1) {
int modelRow = table.convertRowIndexToModel(row);
System.out.println("Selecte model row = " + row);

Vector data = (Vector) ((DefaultTableModel) table.getModel()).getDataVector().get(modelRow);

nameText.setText(data.get(0).toString());
noText.setText(data.get(1).toString());
bdateText.setText(data.get(2).toString());
sexText.setText(data.get(3).toString());
cityText.setText(data.get(4).toString());
stateText.setText(data.get(5).toString());
extText.setText(data.get(6).toString());
phoneText.setText(data.get(7).toString());
add1Text.setText(data.get(8).toString());
add2Text.setText(data.get(9).toString());
emailText.setText(data.get(10).toString());
platText.setText(data.get(11).toString());
notesText.setText(data.get(12).toString());
meetText.setText(data.get(13).toString());
callText.setText(data.get(14).toString());

}
}
});

Answer
DELETE FROM EMAIL_ADDRESSES
WHERE FNumber = fnumber;

SQL is case insensitive when it comes to identifiers. So FNumber = fnumber is always true. Hence this deletes every row.

The best thing to do would be to use a variable name that's different from the column name in the table.

Comments