英文:
JavaFX Address Book updating contacts with same info (SQL)
问题
以下是翻译好的内容:
我正在完成一个JavaFX的通讯录。用户可以添加、编辑、删除和查看存储在数据库中的所有联系人。然而,我有一个小问题,如果多条记录共享一条信息,当我为其中一条进行编辑时,它会更新所有相关记录。以下是一个示例:
假设我的数据库中有三个人(John、Charli和另一个John)。
假设这三个人都有不同的电话号码。我想要更新第一个John的电话号码。当我进行更改时,它会将两个John的电话号码都更新为我输入的新值。
这个问题绝对与我编写的更新功能的SQL有关。以下是我目前的写法。
public void updateData(String column, String newValue, String id) throws SQLException {
String updateQuery = "UPDATE contacts SET " + column + " = ? WHERE " + column + "= ? ";
try {
PreparedStatement psmt = DBConnect.getConnection().prepareStatement(updateQuery);
psmt.setString(1, newValue);
psmt.setString(2, id);
psmt.executeUpdate();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
updateData是由我想要更改字段的方法调用的,就像这样。
public void changePhoneNumberCellEvent(CellEditEvent editedCell) throws SQLException {
Person person = table_contact.getSelectionModel().getSelectedItem();
String oldPhoneNumber = person.getPhone_number();
person.setPhone_number(editedCell.getNewValue().toString());
updateData("phone_number", editedCell.getNewValue().toString(), oldPhoneNumber);
}
现在我知道我的SQL是出问题了,因为它在first_name列中找到了多个相同的值?有没有办法我可以编辑这个SQL,使它只更改我想要更改的人的信息?我进行了一些研究,提到了DISTINCT关键字,但我认为它可能无法帮助,因为两个人可能共享相同的名字、姓氏等。
谢谢。
英文:
So I am finishing up an Address book in JavaFX. The user can add, edit, delete and view all the contacts that are stored in the database. However I have a small problem, if multiple records share one piece of information, when I edit something for one of them it updates it for everything. Here's an example:
Let's say there's three people in my DB (John, Charli, and another John)
And let's say all three people have different phone numbers. I want to update the first John's phone number. When I change it, it edits both John's phone number to the new value I entered.
This issue 100% has something to do with how I wrote my SQL for the update function. Here's what I have now.
public void updateData(String column, String newValue, String id) throws SQLException {
String updateQuery = "UPDATE contacts SET " + column + " = ? WHERE " + column + "= ? ";
try {
PreparedStatement psmt = DBConnect.getConnection().prepareStatement(updateQuery);
psmt.setString(1, newValue);
psmt.setString(2, id);
psmt.executeUpdate();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
UpdateData is called by methods whose fields I want to change, like so.
public void changePhoneNumberCellEvent(CellEditEvent editedCell) throws SQLException {
Person person = table_contact.getSelectionModel().getSelectedItem();
String oldPhoneNumber = person.getPhone_number();
person.setPhone_number(editedCell.getNewValue().toString());
updateData("phone_number", editedCell.getNewValue().toString(), oldPhoneNumber);
}
Now I know my SQL is breaking because it finds multiple of the same values in the first_name column for example? Is there a way I can edit this SQL so it only changes the info of person I want to change? I did some research and the DISTINCT keyword was brought up but I don't think that could help because 2 people could share the same first name/last name, etc.
Thanks.
答案1
得分: 2
始终将主键用作数据集的唯一标识符。在更新和删除时,您应始终使用该唯一标识符。否则,您的查询将引起副作用。例如,删除共享同一列中相同数据的多个数据集。
英文:
Always use a primary key as a unique identifier for the dataset. When updating, deleting you should then always use that unique identifier. Otherwise your queries will cause side effects. For example deleting multiple datasets that share the same data in a column.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论