Java: Insert batched data in MySQL database with Prepared Statements

Question

I have created a custom function to insert data in my MySQL database. The functions first creates a query based on the input given. The query wil look like INSERT INTO tableName (columnName1, ..., columnNamei) VALUES (?, ..., ?), ..., (?, ...,?). After that, the PreparedStatement needs to made, which contains the real values. These need to be added to a batch, because I want to add multiple rows at once (as showed here: Java: Insert multiple rows into MySQL with PreparedStatement). Here is the code:

insertData() Function

public static void insertData(String table, List<HashMap<String, Object>> list) throws SQLException {

    //Create query: make sure all of the rows in the table get the same amount of values passed
    //Prepare colnames string
    String colNamesParsed = "";
    int counter = 1;

    //Iterate over only the first hashmap of the list (THATS WHY ALL THE ROWS NEED TO HAVE THE SAME AMOUNT OF VALUES PASSED)
    for (String colName : list.get(0).keySet()) {

        //Check if it is the last col name
        if (counter != list.get(0).keySet().size()) {
            colNamesParsed = colNamesParsed + colName+", ";
        }
        else {
            colNamesParsed = colNamesParsed + colName;
        }

        counter++;
    }

    //Now create the place holder for the query variables
    String queryVariablesPlaceholder = "";
    int rowSize = 0;
    for (HashMap<String, Object> row : list) {

        //This part is to check if all row sizes are equal
        if (rowSize == 0) {
            rowSize = row.values().size();
        }
        else {
            //Check if the rowsize is equal for all rows
            if (row.values().size() != rowSize) {
                System.out.println("The rows of the arrays are from a different size");
                return;
            }
        }

        String queryVariablesRow = "(?, ";
        for (int j = 1; j < (row.values().size()-1); j++) {
            queryVariablesRow = queryVariablesRow+"?, ";
        }
        queryVariablesRow = queryVariablesRow+"?)";

        //Make sure the query does not start with a comma
        if (queryVariablesPlaceholder.equals("")) {
            queryVariablesPlaceholder = queryVariablesRow;
        }
        else {
            queryVariablesPlaceholder = queryVariablesPlaceholder+", "+queryVariablesRow;
        }
    }

    //The MySQL query needs to be built now
    String query = "INSERT INTO "+table+" ("+colNamesParsed+") VALUES "+queryVariablesPlaceholder+";";
    System.out.println(query);

    //Init prepared statement
    PreparedStatement statement = con.prepareStatement(query);

    for (HashMap<String, Object> map : list) {

        int varCounter = 1;
        //Iterate over all values that need to be inserted
        for (Object object : map.values()) {

            if (object instanceof Integer) {
                statement.setInt(varCounter, Integer.parseInt(object.toString()));
            }
            else if (object instanceof String) {
                statement.setString(varCounter, object.toString());
            }
            else if (object instanceof Timestamp) {
                statement.setTimestamp(varCounter, parseStringToTimestamp(object.toString()));
            }
            else if (object instanceof Double) {
                statement.setDouble(varCounter, Double.parseDouble(object.toString()));
            }
            System.out.println(varCounter);
            varCounter++;
        }

        //Add row to the batch
        try {
            statement.addBatch();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

    }
    //Execute the query, which is in fact the batch
    statement.executeBatch();
}

When I want to insert some data in the database, I execute the following code:

Functional part

List<HashMap<String, Object>> list = new ArrayList<>();
    for (Object object : listOfObjects) {
        HashMap<String, Object> map = new HashMap<>();
        map.put("columnName1", object.getSomeValue());
        /....../
        map.put("columnName2", object.getSomeOtherValue());

        list.add(map);
    }

    Functions.insertData("tableName", list);

Creating the dynamic query seems to work perfectly. However, I can't get the statement.addBatch() to work. It keeps giving me the following error:

java.sql.SQLException: No value specified for parameter 9

I don't get it, because I only have 8 parameters to pass in every unit of the batch. My target table has 9 columns, so I tried to add a value for that column, but then it says: No value specified for parameter 10, so it seems like it isn't closing the 'batch unit' or something.

What am I missing here?

Any help is greatly appreciated!

Answer 1

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.