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!
