Will mysql lose data when storing data?-CodePudding

When I look up some documents, those document authors usually recommend a simple check on mysql data storage operations, such as this:

package org.example.jdbc.demo;

import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;

import java.sql.*;

@SuppressWarnings("JavadocDeclaration")
public class Mysql02Test {
    private static Connection connection;

    @BeforeAll
    static void beforeAll() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://192.168.24.1/demo?useSSL=false&serverTimezone=Asia/Shanghai", "root", "root");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }

    @Test
    void insertTest() throws SQLException {
        Statement statement = connection.createStatement();
        int updateCount = statement.executeUpdate("INSERT INTO mysql_jdbc_demo(message) VALUES ('Hello World'), ('Hello World')");
        System.out.println("update: "   update);
        if (updateCount != 2) {
            System.out.println("update != 2");
        }
    }
}

In addition to declaring "INSERT IGNORE INTO" in sql to let mysql ignore duplicate data.

Is it possible that updateCount is not equal to 2?

The 2 in this example represents the amount of data that needs to be saved.

CodePudding user response：

Yes, it is possible executeUpdate does not return 2. According to the documentation, it returns the amount of affected rows by your SQL DML statement or 0 for SQL statements that return nothing[0]:

Returns:

either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

There are some pitfalls and quirks depending on the database driver so it's not 100% reliable[1].

[0] https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#executeUpdate-java.lang.String-

[1] What does it mean when Statement.executeUpdate() returns -1?

CodePudding user response：

According to the post from Diyarbakir, they state from the Does updateCount() and executeUpdate() return samething (Stack Overflow)

"From the docs:

executeUpdate()

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Returns: either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

So basically:

executeUpdate() is used for SQL INSERT, UPDATE, DELETE or a DLL statement.
execute() could be used for any SQL statement after which you would call getUpdateCount().

Since you're using an UPDATE it doesn't matter in your case. There should be no difference" [0]

If this solves your answer, please go back to the original post and give credit to Diyarbakir (Does updateCount() and executeUpdate() return samething).

[0] Does updateCount() and executeUpdate() return samething