Java: Insert Multiple Rows into MySQL with Preparedstatement

Java: Insert multiple rows into MySQL with PreparedStatement

You can create a batch by PreparedStatement#addBatch() and execute it by PreparedStatement#executeBatch().

Here's a kickoff example:

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

It's executed every 1000 items because some JDBC drivers and/or DBs may have a limitation on batch length.

See also:

• JDBC tutorial - Using PreparedStatement
• JDBC tutorial - Using Statement Objects for Batch Updates

Insert multiple rows into two tables in a single query using JDBC in JAVA

Use a PreparedStatement and batch insert:

List<Student> students = ...
Connection con = ...
String insertSql = "INSERT INTO student VALUES (?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(insertSql);
for (Student student : students) {
    pstmt.setString(1, student.getId()); //not sure if String or int or long
    pstmt.setString(2, student.getName());
    pstmt.setString(3, student.getCity());
    pstmt.addBatch();
}
pstmt.executeBatch();
//close resources...

Similar for your Teachers.

More info:

• Using Prepared Statements
• Difference between Statement and PreparedStatement
• Reusing a PreparedStatement multiple times

JDBC insert multiple rows

First of all, with query string concatenation you not only lose the type conversion native to PreparedStatement methods, but you also get vulnerable to malicious code being executed in the database.

Second, PreparedStatements are previously cached in the very database itself, and this already gives a very good performance improvement over plain Statements.

Java: Insert batched data in MySQL database with Prepared Statements

This

INSERT INTO tableName (columnName1, ..., columnNamei) VALUES (?, ..., ?), ..., (?, ...,?) 

is not standard SQL syntax.
If you use this JDBC will a parameter for each "?" in your query.

Use:

INSERT INTO tableName (columnName1, ..., columnNamei) VALUES (?, ..., ?)

and add every statement to a batch.

insert multiple values using prepared statement using loop java

You need :

cnx.setAutoCommit(false) 

at the beginning

and

cnx.commit();

at the end

---

Related Topics