gpt4 book ai didi

java - 为什么当我点击更新按钮时,数据库中的所有数据都改变了?

转载 作者:行者123 更新时间:2023-12-02 08:58:15 25 4
gpt4 key购买 nike

我正在制作一个图书馆系统,用户可以在其中过滤数据库中的数据(使用 SQLite)。截至目前,我在更新数据库时遇到问题。当我在数据库中选择一项然后更新它时,所有数据都会发生变化。如何仅更改所选项目?

这是我的代码:

package librarySystemPackage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
import net.proteanit.sql.DbUtils;

public class LibrarySystem extends javax.swing.JFrame {

Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;

public LibrarySystem() {
initComponents();
conn = DbConnection.ConnecrDb();
updateTable();
}

private void updateTable() {
String sql = "select * from LibrarySystemDatabase";
try {
pst = conn.prepareStatement (sql);
rs = pst.executeQuery();
tblTable.setModel(DbUtils.resultSetToTableModel (rs));
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
} finally {
try {
rs.close();
pst.close();
} catch (Exception e) {
}
}
}

private void clearFields() {
txtNo.setText(null);
txtTitle.setText(null);
txtAuthor.setText(null);
txtGenre.setText(null);
txtLexile.setText(null);
txtPoints.setText(null);
}

@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">
private void initComponents() {

jLabel1 = new javax.swing.JLabel();
jLabel2 = new javax.swing.JLabel();
jLabel3 = new javax.swing.JLabel();
jLabel4 = new javax.swing.JLabel();
jLabel5 = new javax.swing.JLabel();
jLabel6 = new javax.swing.JLabel();
txtTitle = new javax.swing.JTextField();
txtNo = new javax.swing.JTextField();
txtAuthor = new javax.swing.JTextField();
txtGenre = new javax.swing.JTextField();
txtLexile = new javax.swing.JTextField();
txtPoints = new javax.swing.JTextField();
btnInsert = new javax.swing.JButton();
jScrollPane1 = new javax.swing.JScrollPane();
tblTable = new javax.swing.JTable();
btnDeselect = new javax.swing.JButton();
btnUpdate = new javax.swing.JButton();

setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

jLabel1.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
jLabel1.setText("Title:");

jLabel2.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
jLabel2.setText("Accession Number:");

jLabel3.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
jLabel3.setText("Author:");

jLabel4.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
jLabel4.setText("Lexile:");

jLabel5.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
jLabel5.setText("Genre:");

jLabel6.setFont(new java.awt.Font("Tahoma", 0, 20)); // NOI18N
jLabel6.setText("Points:");

btnInsert.setFont(new java.awt.Font("Tahoma", 0, 24)); // NOI18N
btnInsert.setText("Insert");
btnInsert.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
btnInsertActionPerformed(evt);
}
});

tblTable.setModel(new javax.swing.table.DefaultTableModel(
new Object [][] {
{null, null, null, null},
{null, null, null, null},
{null, null, null, null},
{null, null, null, null}
},
new String [] {
"Title 1", "Title 2", "Title 3", "Title 4"
}
));
tblTable.addMouseListener(new java.awt.event.MouseAdapter() {
public void mouseClicked(java.awt.event.MouseEvent evt) {
tblTableMouseClicked(evt);
}
});
tblTable.addKeyListener(new java.awt.event.KeyAdapter() {
public void keyReleased(java.awt.event.KeyEvent evt) {
tblTableKeyReleased(evt);
}
});
jScrollPane1.setViewportView(tblTable);

btnDeselect.setFont(new java.awt.Font("Tahoma", 0, 24)); // NOI18N
btnDeselect.setText("Deselect");
btnDeselect.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
btnDeselectActionPerformed(evt);
}
});

btnUpdate.setFont(new java.awt.Font("Tahoma", 0, 24)); // NOI18N
btnUpdate.setText("Update");
btnUpdate.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
btnUpdateActionPerformed(evt);
}
});

javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(50, 50, 50)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
.addGroup(layout.createSequentialGroup()
.addComponent(jLabel6, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(txtPoints, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
.addComponent(jLabel4, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(txtLexile, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
.addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(txtGenre, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
.addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(txtAuthor, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
.addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 175, javax.swing.GroupLayout.PREFERRED_SIZE)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(txtNo, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
.addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 170, javax.swing.GroupLayout.PREFERRED_SIZE)
.addGap(26, 26, 26)
.addComponent(txtTitle, javax.swing.GroupLayout.PREFERRED_SIZE, 187, javax.swing.GroupLayout.PREFERRED_SIZE)))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 42, Short.MAX_VALUE)
.addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 548, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGroup(layout.createSequentialGroup()
.addGap(16, 16, 16)
.addComponent(btnInsert, javax.swing.GroupLayout.PREFERRED_SIZE, 123, javax.swing.GroupLayout.PREFERRED_SIZE)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addComponent(btnDeselect)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addComponent(btnUpdate)
.addGap(0, 0, Short.MAX_VALUE)))
.addGap(28, 28, 28))
);
layout.setVerticalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGap(122, 122, 122)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(txtNo, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(txtTitle, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(txtAuthor, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGap(6, 6, 6)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, 37, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(txtGenre, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel4, javax.swing.GroupLayout.PREFERRED_SIZE, 37, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(txtLexile, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(jLabel6, javax.swing.GroupLayout.PREFERRED_SIZE, 37, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(txtPoints, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE)))
.addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 259, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGap(28, 28, 28)
.addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
.addComponent(btnDeselect)
.addComponent(btnInsert)
.addComponent(btnUpdate))
.addContainerGap(224, Short.MAX_VALUE))
);

pack();
}// </editor-fold>

private void btnInsertActionPerformed(java.awt.event.ActionEvent evt) {
String sql = "INSERT INTO LibrarySystemDatabase(txtNo, txtTitle, txtAuthor, txtGenre, txtLexile, txtPoints) VALUES(?,?,?,?,?,?)";
try {
pst = conn.prepareStatement (sql);
pst.setString(1, txtNo.getText());
pst.setString(2, txtTitle.getText());
pst.setString(3, txtAuthor.getText());
pst.setString(4, txtGenre.getText());
pst.setString(5, txtLexile.getText());
pst.setString(6, txtPoints.getText());
pst.execute();
JOptionPane.showMessageDialog(null, "Inserted Successfully");
updateTable();
clearFields();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
} finally {
try {
rs.close();
pst.close();
} catch (Exception e) {
}
}
}

private void tblTableMouseClicked(java.awt.event.MouseEvent evt) {
int row = tblTable.getSelectedRow();
String selection = tblTable.getModel().getValueAt(row, 0).toString();
String sql = "select * from LibrarySystemDatabase where txtNo = " + selection;
try {
pst = conn.prepareStatement (sql);
rs = pst.executeQuery();
if(rs.next()) {
txtNo.setText(rs.getString("txtNo"));
txtTitle.setText(rs.getString("txtTitle"));
txtAuthor.setText(rs.getString("txtAuthor"));
txtGenre.setText(rs.getString("txtGenre"));
txtLexile.setText(rs.getString("txtLexile"));
txtPoints.setText(rs.getString("txtPoints"));
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
} finally {
try {
rs.close();
pst.close();
} catch (Exception e) {
}
}
}

private void tblTableKeyReleased(java.awt.event.KeyEvent evt) {
int row = tblTable.getSelectedRow();
String selection = tblTable.getModel().getValueAt(row, 0).toString();
String sql = "select * from LibrarySystemDatabase where txtNo = " + selection;
try {
pst = conn.prepareStatement (sql);
rs = pst.executeQuery();
if(rs.next()) {
txtNo.setText(rs.getString("txtNo"));
txtTitle.setText(rs.getString("txtTitle"));
txtAuthor.setText(rs.getString("txtAuthor"));
txtGenre.setText(rs.getString("txtGenre"));
txtLexile.setText(rs.getString("txtLexile"));
txtPoints.setText(rs.getString("txtPoints"));
}
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
} finally {
try {
rs.close();
pst.close();
} catch (Exception e) {
}
}
}

private void btnDeselectActionPerformed(java.awt.event.ActionEvent evt) {
clearFields();
updateTable();
}

这是我的更新按钮代码

    private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {                                          
String sql = "update LibrarySystemDatabase set"
+ " txtNo = '"+txtNo.getText()+"',"
+ " txtTitle = '"+txtTitle.getText()+"',"
+ " txtAuthor = '"+txtAuthor.getText()+"',"
+ " txtGenre = '"+txtGenre.getText()+"',"
+ " txtLexile = '"+txtLexile.getText()+"',"
+ " txtPoints = '"+txtPoints.getText()+"'";
try {
pst = conn.prepareStatement (sql);
pst.execute();
JOptionPane.showMessageDialog(null, "Record Updated");
updateTable();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
} finally {
try {
rs.close();
pst.close();
} catch (Exception e) {
}
}
}
    /**
* @param args the command line arguments
*/
public static void main(String args[]) {



java.awt.EventQueue.invokeLater(new Runnable() {
public void run() {
new LibrarySystem().setVisible(true);
}
});
}

// Variables declaration - do not modify
private javax.swing.JButton btnDeselect;
private javax.swing.JButton btnInsert;
private javax.swing.JButton btnUpdate;
private javax.swing.JLabel jLabel1;
private javax.swing.JLabel jLabel2;
private javax.swing.JLabel jLabel3;
private javax.swing.JLabel jLabel4;
private javax.swing.JLabel jLabel5;
private javax.swing.JLabel jLabel6;
private javax.swing.JScrollPane jScrollPane1;
private javax.swing.JTable tblTable;
private javax.swing.JTextField txtAuthor;
private javax.swing.JTextField txtGenre;
private javax.swing.JTextField txtLexile;
private javax.swing.JTextField txtNo;
private javax.swing.JTextField txtPoints;
private javax.swing.JTextField txtTitle;
// End of variables declaration
}

谢谢

最佳答案

您的update LibrarySystemDatabase SQL语句没有WHERE子句,因此每一行都会更新为相同的值。

您的代码应该是:

private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {                                          
String sql = "UPDATE LibrarySystemDatabase"
+ " SET txtTitle = ?"
+ ", txtAuthor = ?"
+ ", txtGenre = ?"
+ ", txtLexile = ?"
+ ", txtPoints = ?"
+ " WHERE txtNo = ?";
try (PreparedStatement pst = conn.prepareStatement(sql)) {
pst.setString(1, txtTitle.getText());
pst.setString(2, txtAuthor.getText());
pst.setString(3, txtGenre.getText());
pst.setString(4, txtLexile.getText());
pst.setString(5, txtPoints.getText());
pst.setString(6, txtNo.getText());
int count = pst.executeUpdate();
JOptionPane.showMessageDialog(null, count + " Records Updated");
updateTable();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
}

所做的更改:

  • 使用WHERE txtNo 上的条款列,因为根据您的 SELECT 这似乎是主键声明。

  • 使用try-with-resources为了更简单地处理close() .

  • 调用 executeUpdate()获取更新计数并显示它。

  • 使用?参数标记,以防止 SQL 注入(inject)攻击和 SQL 语法错误,就像您已经对 INSERT 所做的那样声明。

关于java - 为什么当我点击更新按钮时,数据库中的所有数据都改变了?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/60359430/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com