Batch Statements vs Multiple Value Inserts

Question

JDBC has a feature of group multiple queries in one single unit and pass it in a single network trip to the database.

As shown in this code example:

String[] EMPLOYEES = new String[]{"Zuck","Mike","Larry","Musk","Steve"};
String[] DESIGNATIONS = new String[]{"CFO","CSO","CTO","CEO","CMO"};

String insertEmployeeSQL = "INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) VALUES (?,?,?)";
PreparedStatement employeeStmt = connection.prepareStatement(insertEmployeeSQL);

for(int i = 0; i < EMPLOYEES.length; i++){
    String employeeId = UUID.randomUUID().toString();
    employeeStmt.setString(1,employeeId);
    employeeStmt.setString(2,EMPLOYEES[i]);
    employeeStmt.setString(3,DESIGNATIONS[i]);
    employeeStmt.addBatch();
}
employeeStmt.executeBatch();

But, with SQL we have the feature of using INSERTs with many VALUES groups, just like at:

String[] EMPLOYEES = new String[] {"Zuck","Mike","Larry","Musk","Steve"};
String[] DESIGNATIONS = new String[] {"CFO","CSO","CTO","CEO","CMO"};

StringBuilder insertEmployeeSQL = "INSERT INTO EMPLOYEE(ID, NAME, DESIGNATION) VALUES (?,?,?)"
    + ", (?, ?, ?)".repeat(EMPLOYEES.length - 1);

PreparedStatement employeeStmt = connection.prepareStatement(insertEmployeeSQL.toString());

int columnIndex = 0;
for(int i = 0; i < EMPLOYEES.length; i++){
    String employeeId = UUID.randomUUID().toString();
    employeeStmt.setString(++columnIndex, employeeId);
    employeeStmt.setString(++columnIndex, EMPLOYEES[i]);
    employeeStmt.setString(++columnIndex, DESIGNATIONS[i]);
}

employeeStmt.execute();

What I need to know is, what is the difference between these two approaches?

Obs:

The first example is from here https://www.baeldung.com/jdbc-batch-processing and the second edited by me. English is not my native language and this is my first post so sorry for anything.

user_5221149 · Answer

INSERT with multiple VALUES blocks, i.e. multiple (val, ...) blocks is not valid SQL syntax. Oh sure, some databases might support that extended syntax, but not all of them do.

Even if your database does, using that syntax will greatly increase the number of statement parameters, and all of the databases have a limit to the number of parameter is allowed in a single request. The actual limit varies by database. Batching doesn't have a batch size limit, other than amount of memory for storing all the values.

Batch Statements vs Multiple Value Inserts

1 Answers