Java: вставьте несколько строк в MySQL с PreparedStatement

Я хочу вставить несколько строк в таблицу MySQL сразу с помощью Java. Количество строк является динамическим. В прошлом я этим занимался...

for (String element : array) {
    myStatement.setString(1, element[0]);
    myStatement.setString(2, element[1]);

    myStatement.executeUpdate();
}

Я хотел бы оптимизировать это, чтобы использовать синтаксис, поддерживаемый MySQL:

INSERT INTO table (col1, col2) VALUES ('val1', 'val2'), ('val1', 'val2')[, ...]

но с PreparedStatement Я не знаю, как это сделать, так как я не знаю заранее, сколько элементов array будет содержать. Если это невозможно с помощью PreparedStatement, как еще я могу это сделать (и все еще экранировать значения в массиве)?

6 72
javascript mysql jdbc prepared-statement batch-insert

6 ответов:

вы можете создать пакет с PreparedStatement#addBatch() и выполнить его с помощью PreparedStatement#executeBatch().

вот пример старта:

public void save(List<Entity> entities) throws SQLException {
    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT);
    ) {
        int i = 0;

        for (Entity entity : entities) {
            statement.setString(1, entity.getSomeProperty());
            // ...

            statement.addBatch();
            i++;

            if (i % 1000 == 0 || i == entities.size()) {
                statement.executeBatch(); // Execute every 1000 items.
            }
        }
    }
}

он выполняется каждые 1000 элементов, потому что некоторые драйверы JDBC и/или DBs могут иметь ограничение на длину пакета.

см. также:

146

при использовании драйвера MySQL вы должны установить соединение param rewriteBatchedStatements истина ( jdbc:mysql://localhost:3306/TestDB?**rewriteBatchedStatements=true**).

С помощью этого параметра оператор перезаписывается для массовой вставки, когда таблица блокируется только один раз и индексы обновляются только один раз. Так что это гораздо быстрее.

без этого param только преимущество чище исходный код.

21

Если вы можете создать свой оператор sql динамически вы можете сделать следующее решение:

    String myArray[][] = { { "1-1", "1-2" }, { "2-1", "2-2" },
            { "3-1", "3-2" } };

    StringBuffer mySql = new StringBuffer(
            "insert into MyTable (col1, col2) values (?, ?)");

    for (int i = 0; i < myArray.length - 1; i++) {
        mySql.append(", (?, ?)");
    }

    myStatement = myConnection.prepareStatement(mySql.toString());

    for (int i = 0; i < myArray.length; i++) {
        myStatement.setString(i, myArray[i][1]);
        myStatement.setString(i, myArray[i][2]);
    }
    myStatement.executeUpdate();
5

в случае, если у вас есть автоинкремент в таблице и нужно получить к нему доступ.. вы можете использовать следующий подход... Сделать тест перед использованием, потому что getGeneratedKeys() в инструкции, потому что это зависит от используемого драйвера. Приведенный ниже код тестируется на Maria DB 10.0.12 и Maria JDBC driver 1.2

помните, что увеличение размера пакета улучшает производительность только в определенной степени... для моей установки увеличение размера партии выше 500 фактически ухудшало производительность.

public Connection getConnection(boolean autoCommit) throws SQLException {
    Connection conn = dataSource.getConnection();
    conn.setAutoCommit(autoCommit);
    return conn;
}

private void testBatchInsert(int count, int maxBatchSize) {
    String querySql = "insert into batch_test(keyword) values(?)";
    try {
        Connection connection = getConnection(false);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = true;
        int[] executeResult = null;
        try {
            pstmt = connection.prepareStatement(querySql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < count; i++) {
                pstmt.setString(1, UUID.randomUUID().toString());
                pstmt.addBatch();
                if ((i + 1) % maxBatchSize == 0 || (i + 1) == count) {
                    executeResult = pstmt.executeBatch();
                }
            }
            ResultSet ids = pstmt.getGeneratedKeys();
            for (int i = 0; i < executeResult.length; i++) {
                ids.next();
                if (executeResult[i] == 1) {
                    System.out.println("Execute Result: " + i + ", Update Count: " + executeResult[i] + ", id: "
                            + ids.getLong(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            success = false;
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (connection != null) {
                if (success) {
                    connection.commit();
                } else {
                    connection.rollback();
                }
                connection.close();
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
3

@Ali Shakiba ваш код нуждается в некоторой модификации. Ошибка часть:

for (int i = 0; i < myArray.length; i++) {
     myStatement.setString(i, myArray[i][1]);
     myStatement.setString(i, myArray[i][2]);
}

обновленный код:

String myArray[][] = {
    {"1-1", "1-2"},
    {"2-1", "2-2"},
    {"3-1", "3-2"}
};

StringBuffer mySql = new StringBuffer("insert into MyTable (col1, col2) values (?, ?)");

for (int i = 0; i < myArray.length - 1; i++) {
    mySql.append(", (?, ?)");
}

mysql.append(";"); //also add the terminator at the end of sql statement
myStatement = myConnection.prepareStatement(mySql.toString());

for (int i = 0; i < myArray.length; i++) {
    myStatement.setString((2 * i) + 1, myArray[i][1]);
    myStatement.setString((2 * i) + 2, myArray[i][2]);
}

myStatement.executeUpdate();
3

мы можем отправить несколько обновлений вместе в JDBC для отправки пакетных обновлений.

мы можем использовать Statement, PreparedStatement и CallableStatement объекты для обновления bacth с отключить autocommit

addBatch() и executeBatch() функции доступны со всеми объектами оператора, чтобы иметь BatchUpdate

здесь метод addBatch () добавляет набор операторов или параметров к текущему пакету.

0