CODEWITHSUNDEEP
javamysqlsqlspringtransactions
Amir Kerimov
Amir Kerimov

Reputation: 11

How can I update only changed fields in my @Transactional method in Java?

Good day Dear developers! I need to solve a couple of problems related to my method. I need to create an edit / update method that would allow me to change my object.

One of the conditions is that only the fields that the user changes should change in the database. All fields should not be overwritten. Example: the old value name - > "Jack", the new value name - > "Bob", should change . HOWEVER, if the old value is name - > " Jack "and the new value is name - > "Jack", then the field should not be overwritten, since the values are equals.

I need my method to check and change only the fields that were changed and insert changes on the database.

I tried to do the task using sql, but unfortunately I don't know how to set the values (The idea is do not perform any update if a new value is the same as in DB right now) :


    @Transactional(transactionManager = TransactionManagerConfig.TX_MANAGER_MAIN)
    public void editUserContacts(User user) {

        Connection connection = DataSourceUtils.getConnection(mainDataSource);

        String sql = "UPDATE `" + userSchema + "`.l_user " +
                " SET user_phone_number = @phoneNumber," +
                "firstname = @fullName," +
                "user_country = @userCountry," +
                "house_phone_number = @phoneAlternative," +
                " WHERE l_user_id=? , " +
                "user_phone_number != @phoneNumber OR IS NULL," +
                "firstName != @fullName OR IS NULL," +
                "user_country != @userCountry OR IS NULL," +
                "house_phone_number != @phoneAlternative OR IS NULL,;

        try (
                PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
        ) {
            statement.executeUpdate();
            statement.getUpdateCount();

        } catch (SQLException e) {
            throw new DAOException(e);

        }
    }

Maybe I need to do SELECT first, or compare each field using equals ? Please tell me on the EXAMPLE of at least one value, how can I implement my task correctly?

P.S I want to update my value if the new value is not equals the old value. but if old and new values is equals , that nothing should happen

I will be very grateful for your help!

Upvotes: 0

Views: 1083

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If I understand correctly, you ant to update the values in the table only when the new value is not NULL. However, only some of the columns might be affected. For this, use COALESCE():

UPDATE " + userSchema + ".l_user 
    SET user_phone_number = COALESCE(@phoneNumber, user_phone_number),
        firstname = COALESCE(@fullName, firstname),
        user_country = COALESCE(@userCountry, user_country),
        house_phone_number = COALESCE(@phoneAlternative, house_phone_number),
    WHERE l_user_id = ? AND
          not (user_phone_number <=> @phoneNumber and
               firstName <=> @fullName and
               user_country <=> @userCountry and
               house_phone_number <=> @phoneAlternative
              );

This will attempt to update rows only where one of the four values has changed. The <=> is the NULL-safe comparison operator.

Your query attempt has multiple errors.

  • The WHERE clause does not recognize ,. That is just a syntax error
  • OR IS NULL is not valid SQL. That is a syntax error.
  • You also have a comma before the WHERE clause, which is another syntax error.

Upvotes: 0

Akina
Akina

Reputation: 42622

Example: the old value name - > "Jack", the new value name - > "Bob", should change . HOWEVER, if the old value is name - > " Jack "and the new value is name - > "Jack", then the field should not be overwritten, since the values are equals.

Don't care about it. MySQL is smart enough, it will renew the column only when its value is really changed.

Example:

enter image description here

Look - there is 2 rows, and only in one row the value is changed. And MySQL reports

1 row affected

and

Rows matched: 2 Changed: 1

I.e. the row which' value was not altered by fact was not changed.

Upvotes: 2

Related Questions