why the update query does not update the date in Mysql database

Question

I found a problem when updating the data in the database. The log shows update successfully, but the data in the database does not change. this happens sometimes, not frequently, like 1 of 200.

below is the code:

public void updateRecharge(String serial_num, String msg, String succ_amout, String datetime) {
    try {
        con = open();
    } catch(Exception e) {
        e.printStackTrace();
        log.writeLog(serial_num, datetime, "fail to update: " + e.getMessage());
        return;
    }

    String area = new JudgeTidField().checkTidField(serial_num)[1];
    String table = new JudgeTidField().table(area);

    String query = "update " + table + " set Recharge_state=?, Recharge_money=?, Recharge_record_date=? where Serial_number='" + serial_num + "';";

    try {   
        preState = con.prepareStatement(query);
        preState.setString(1, msg);
        preState.setFloat(2, Float.valueOf(succ_amout));
        preState.setString(3, datetime);
        preState.executeUpdate();

        log.writeLog(serial_num, datetime, "success to update");
    } catch(Exception e) {
        e.getMessage();
        log.writeLog(serial_num, datetime, "fail to update: " + e.getMessage());
    } finally {
        try {
            preState.close();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }

        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

I check the log, it shows "success to update", but the data does not change. what kind of problem cause this happening?

1- You should use a ? for serial_num like the other parameters; 2- preState.executeUpdate(); returns a int value representing the number of the rows updated, you might want to check it — MadProgrammer
– MadProgrammer, Commented Jun 10, 2015 at 3:18

Steve C · Accepted Answer · 2015-06-10 04:09:15Z

2

As suggested by @MadProgrammer you should use parameter replacement for the serialNum and check the return value from executeUpdate.

Additionally, I would use java.text.DateFormat to convert datetime into a java.util.Date and call java.sql.PreparedStatement.setDate(...) to apply it to preState. This will trap any date format problems early.

Also if any parameters are null then you must explicitly call java.sql.PreparedStatement.setNull(...)

answered Jun 10, 2015 at 4:09

Steve C

19.5k5 gold badges37 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

why the update query does not update the date in Mysql database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related