Issue
I'm trying to change only 1 column and row upon the click of a button and also update mySQL according to the String. I managed to write a code but its changing the whole column into the String instead of the selected row. I'm trying to change only the value of the selected row. How can i do that?
private void jButtonBorrowActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
DefaultTableModel model = (DefaultTableModel) jTableBooks1.getModel();
int row = jTableBooks1.getSelectedRow();
String notavail = model.getValueAt(row, 2).toString();
notavail = "Not Available";
PreparedStatement ps;
String query = "UPDATE `Books` SET `Availability`=?";
try {
ps = dbConnect.getConnection().prepareStatement(query);
ps.setString(1, notavail);
ps.executeUpdate();
JOptionPane.showMessageDialog(null, "Book Borrowed Successfully!");
BookMenu bm = new BookMenu();
bm.setVisible(true);
bm.setLocationRelativeTo(null);
this.dispose();
} catch (SQLException ex) {
Logger.getLogger(Login.class.getName()).log(Level.SEVERE, null, ex);
}
}
public ArrayList<Books> booksList(){
ArrayList<Books> booksList = new ArrayList<>();
PreparedStatement ps;
ResultSet rs;
String query = "SELECT * FROM `Books`";
try {
ps = dbConnect.getConnection().prepareStatement(query);
rs = ps.executeQuery();
Books book;
while (rs.next()){
book = new Books(rs.getString("author"),rs.getString("bookname"),rs.getString("ISBN"), rs.getString("Course"),rs.getString("Year"),rs.getString("Availability"));
booksList.add(book);
}
} catch (SQLException ex) {
Logger.getLogger(Register.class.getName()).log(Level.SEVERE, null, ex);
}
return booksList;
}
public void showBooks(){
ArrayList<Books>list = booksList();
DefaultTableModel model = (DefaultTableModel)jTableBooks1.getModel();
Object[] row = new Object[6];
for(int i=0;i<list.size();i++){
row[0] =list.get(i).getAuthor();
row[1] =list.get(i).getBookname();
row[2] = list.get(i).getAvailability();
model.addRow(row);
}
As mentioned above i would like to change only the data in the selected row. Attached is a picture of how it is and how i want it to be.
Solution
String query = "UPDATE Books SET Availability = ?";
You need a WHERE
clause in your update query, else all rows get updated.
Assuming that the book name and author can be used as a primary key, this might do the job :
String author = model.getValueAt(row, 0).toString();
String bookname = model.getValueAt(row, 1).toString();
String query =
"UPDATE Books SET availability = ? WHERE author = ? AND bookname = ?";
try {
ps = dbConnect.getConnection().prepareStatement(query);
ps.setString(1, notavail);
ps.setString(2, author);
ps.setString(3, bookname);
...
Answered By - GMB
Answer Checked By - Timothy Miller (JavaFixing Admin)