I am trying to update mysql record using Java Swing. Seems like there is some problem with MySql Update statement. I have pasted the code as under:
conDBase = getConnection();
stmt = conDBase.createStatement();
String sql = "update user set role = " + jComboBox1.getSelectedItem()+ "where userID =" + jComboBox2.getSelectedItem();
stmt.executeUpdate(sql);
conDBase.close();
JOptionPane.showMessageDialog(null, "User Role Updated");
this.dispose();
new Admin().setVisible(true);
Well, there are at least two problems with your code:
I suspect it's the latter problem which you're noticing right now, but you should definitely fix the code to use prepared statements as well.
Oh, and you probably shouldn't be doing any of this on the UI thread (or accessing the UI controls from a non-UI thread) either...
I'm not sure what error message you are getting (perhaps you could post some details up).
However, the following line needs to be altered:
String sql = "update user set role = " + jComboBox1.getSelectedItem()+ " where userID =" + jComboBox2.getSelectedItem() + ";";
Notice the space before the "where" and the semi-colon at the end of the statement.
A better version would look like:
public int updateRoleForUser (String role, Integer userId) throws Exception {
int i = 0;
String sql =
" update user "
+ " set role = ?"
+ " where userId = ?;";
try {
conDBase = getConnection();
ps = conDBase.prepareStatement(sql);
ps.setString(1, role);
ps.setInt(2, userId);
i = ps.executeUpdate();
} catch (Exception e) {
// do something with Exception here. Maybe just throw it up again
} finally {
closeConnection();
return i;
}
};
Then call the method using the values from your Combo Boxes.
You should really put the whole lot into a method within a DAO class of some description and then pass in the role and userId values as parameters;
You must put an space between the quote and the "where". Like this:
...getSelectedItem() + " where...
because, if not, you'll get something like:
...set role = 25where userId...
