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Как еще я могу это сделать (и все же экранировать значения в массиве)?

Источник

7 ответов

Решение

Вы можете создать партию 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 и / или БД могут иметь ограничение по длине пакета.

Смотрите также:

Источник

Когда используется драйвер MySQL, вы должны установить параметры подключения rewriteBatchedStatements к истине ( jdbc:mysql://localhost:3306/TestDB?**rewriteBatchedStatements=true**),

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

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

Источник

Если вы можете создать свой 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();
Источник

@ Али Шакиба, ваш код нуждается в некоторой модификации. Часть ошибки:

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();
Источник

Если у вас есть автоинкремент в таблице и вам нужен доступ к нему... вы можете использовать следующий подход... Выполните тестирование перед использованием, потому что getGeneratedKeys() в Statement, потому что это зависит от используемого драйвера. Приведенный ниже код протестирован на 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();
    }
}
Источник

Это может быть полезно в вашем случае передачи массива в PreparedStatement.

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

      String sql= "INSERT INTO table (col1,col2)  VALUES (?,?)";
String array[][] = new String [10][2];
for(int i=0;i<array.size();i++){
     //Assigning the values in individual rows.
     array[i][0] = "sampleData1";   
     array[i][1] = "sampleData2";
}
try{
     DBConnectionPrepared dbcp = new DBConnectionPrepared();            
     if(dbcp.putBatchData(sqlSaveAlias,array)==1){
        System.out.println("Success"); 
     }else{
        System.out.println("Failed");
     }
}catch(Exception e){
     e.printStackTrace();
}

putBatchData (sql, 2D_Array)

      public int[] putBatchData(String sql,String args[][]){
        int status[];
        try {
            PreparedStatement stmt=con.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                for(int j=0;j<args[i].length;j++){
                    stmt.setString(j+1, args[i][j]);
                }            
                stmt.addBatch();
                stmt.executeBatch();
                stmt.clearParameters();
            }
            status= stmt.executeBatch();             
        } catch (Exception e) {
            e.printStackTrace();
        } 
        return status;
    }
Источник

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

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

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

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

Источник
Другие вопросы по тегам