I need to update a record in mySQL using JDBC statements

Question

But an exception is thrown: "java.sql.SQLException: Can not issue data manipulation statements with executeQuery()".

public void updateClientsMoney(String name, String password, Long transactValue) throws SQLException {
        System.out.println("updateClientsMoney");

        String query = "";
        if(transactValue < 0) {
            query = String.format("UPDATE bank_client SET money %s where name = '%s'", transactValue, name);
        } else{
            query = String.format("UPDATE bank_client SET money %s where name = '%s'", transactValue, name);
        }

        try(Statement statement = connection.createStatement()){
            statement.executeQuery(query);
        }
    }

Use `executeUpdate()` instead of `executeQuery()` – sinclair Apr 18 '20 at 07:00 — sinclair, Apr 18 '20 at 07:00

Nowhere Man · Answer 1 · 2020-04-18T07:46:26.140

Apart from the reported issue this code

searches the client by name which is unlikely to be a unique identifier - so this query will update money for all namesakes,
has unused variable password (possibly it should have been used along with the name),
does not use PreparedStatement which is a common best practice protecting from SQL injection,
builds incorrect query which sets money to transactValue making if meaningless

Thus, it may be refactored this way:

public void updateClientsMoney(String clientId, Long transactValue) throws SQLException {
    System.out.println("updateClientsMoney");
    String query = "UPDATE bank_client SET money = money + ? WHERE id = ?";

    try (PreparedStatement statement = connection.prepareStatement(query)) {
        statement.setLong  (1, transactValue.longValue());
        statement.setString(2, clientId);
        statement.executeUpdate();
    }
}

Update: identifying client by name AND password

public void updateClientsMoney(String name, String password, Long transactValue) throws SQLException {
    System.out.println("updateClientsMoney");
    String query = "UPDATE bank_client SET money = money + ? WHERE name = ? AND password = ?";

    try (PreparedStatement statement = connection.prepareStatement(query)) {
        statement.setLong  (1, transactValue.longValue());
        statement.setString(2, name);
        statement.setString(3, password);
        statement.executeUpdate();
    }
}

score 1 · Answer 2 · answered Apr 18 '20 at 07:05

You are using executeQuery method for a DML statement i.e. Data Manipulation Statement. The operations such as INSERT, UPDATE and DELETE fall in this category. You need to update your code like below

public void updateClientsMoney(String name, String password, Long transactValue) throws SQLException {
    System.out.println("updateClientsMoney");

    String query = "";
    if(transactValue < 0) {
        query = String.format("UPDATE bank_client SET money %s where name = '%s'", transactValue, name);
    } else{
        query = String.format("UPDATE bank_client SET money %s where name = '%s'", transactValue, name);
    }

    try(Statement statement = connection.createStatement()){
        statement.executeUpdate(query);
    }
}

I need to update a record in mySQL using JDBC statements

2 Answers2