CODEWITHSUNDEEP
javamysqlswing
user997611
user997611

Reputation: 51

Java Mysql Update Statement

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);

Upvotes: 0

Views: 6259

Answers (3)

helios
helios

Reputation: 13841

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...

Upvotes: 1

Tom Mac
Tom Mac

Reputation: 9853

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;

Upvotes: 4

Jon Skeet
Jon Skeet

Reputation: 1500525

Well, there are at least two problems with your code:

  • You're not using a prepared statement, which means you've left yourself open for SQL injection. (You're not even quoting the values you are using, which could potentially count as a third problem.)
  • You don't have a space before the "where" clause

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...

Upvotes: 6

Related Questions