Java : Insérer plusieurs lignes dans MySQL avec PreparedStatement

Question

Je veux insérer plusieurs lignes à la fois dans une table MySQL en utilisant Java. Le nombre de lignes est dynamique. Dans le passé, je faisais...

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

    myStatement.executeUpdate();
}

J'aimerais l'optimiser pour utiliser la syntaxe supportée par MySQL :

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

mais avec un PreparedStatement Je ne connais pas de moyen de le faire puisque je ne sais pas à l'avance combien d'éléments array contiendra. Si ce n'est pas possible avec un PreparedStatement Comment puis-je le faire autrement (tout en échappant aux valeurs du tableau) ?

Answer 1

Vous pouvez créer un lot en PreparedStatement#addBatch() et l'exécuter par PreparedStatement#executeBatch() .

Voici un exemple de coup d'envoi :

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.
            }
        }
    }
}

Elle est exécutée tous les 1000 éléments car certains pilotes JDBC et/ou bases de données peuvent avoir une limitation sur la longueur des lots.

Voir aussi :

• Tutoriel JDBC - Utilisation de PreparedStatement
• Tutoriel JDBC - Utilisation de Statement Objects pour les mises à jour par lots

Answer 2

Lorsque le pilote MySQL est utilisé, vous devez définir le paramètre de connexion. rewriteBatchedStatements à vrai ( jdbc:mysql://localhost:3306/TestDB?**rewriteBatchedStatements=true**) .

Avec ce paramètre, l'instruction est réécrite en insertion en masse lorsque la table n'est verrouillée qu'une seule fois et que les index ne sont mis à jour qu'une seule fois. Elle est donc beaucoup plus rapide.

Sans ce paramètre, le seul avantage est un code source plus propre.

Answer 3

Si vous pouvez créer votre déclaration sql dynamiquement, vous pouvez faire la solution suivante :

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();

Answer 4

Dans le cas où vous avez un auto-incrément dans la table et que vous avez besoin d'y accéder... vous pouvez utiliser l'approche suivante... Faites un test avant d'utiliser car getGeneratedKeys() dans Statement car cela dépend du driver utilisé. Le code ci-dessous a été testé sur Maria DB 10.0.12 et le pilote Maria JDBC 1.2.

Rappelez-vous que l'augmentation de la taille des lots n'améliore les performances que dans une certaine mesure... pour ma configuration, l'augmentation de la taille des lots au-delà de 500 dégradait en fait les performances.

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();
    }
}

Answer 5

@Ali Shakiba votre code a besoin de quelques modifications. Partie d'erreur :

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

Code mis à jour :

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();