Java: Insert batched data in MySQL database with Prepared Statements -

Java: Insert batched data in MySQL database with Prepared Statements -

i have created custom function insert info in mysql database. functions first creates query based on input given. query wil insert tablename (columnname1, ..., columnnamei) values (?, ..., ?), ..., (?, ...,?). after that, preparedstatement needs made, contains real values. these need added batch, because want add together multiple rows @ 1 time (as showed here: java: insert multiple rows mysql preparedstatement). here code:

insertdata() function

public static void insertdata(string table, list<hashmap<string, object>> list) throws sqlexception { //create query: create sure of rows in table same amount of values passed //prepare colnames string string colnamesparsed = ""; int counter = 1; //iterate on first hashmap of list (thats why rows need have same amount of values passed) (string colname : list.get(0).keyset()) { //check if lastly col name if (counter != list.get(0).keyset().size()) { colnamesparsed = colnamesparsed + colname+", "; } else { colnamesparsed = colnamesparsed + colname; } counter++; } //now create place holder query variables string queryvariablesplaceholder = ""; int rowsize = 0; (hashmap<string, object> row : list) { //this part check if row sizes equal if (rowsize == 0) { rowsize = row.values().size(); } else { //check if rowsize equal rows if (row.values().size() != rowsize) { system.out.println("the rows of arrays different size"); return; } } string queryvariablesrow = "(?, "; (int j = 1; j < (row.values().size()-1); j++) { queryvariablesrow = queryvariablesrow+"?, "; } queryvariablesrow = queryvariablesrow+"?)"; //make sure query not start comma if (queryvariablesplaceholder.equals("")) { queryvariablesplaceholder = queryvariablesrow; } else { queryvariablesplaceholder = queryvariablesplaceholder+", "+queryvariablesrow; } } //the mysql query needs built string query = "insert "+table+" ("+colnamesparsed+") values "+queryvariablesplaceholder+";"; system.out.println(query); //init prepared statement preparedstatement statement = con.preparestatement(query); (hashmap<string, object> map : list) { int varcounter = 1; //iterate on values need inserted (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 batch seek { statement.addbatch(); } grab (sqlexception e) { e.printstacktrace(); } } //execute query, in fact batch statement.executebatch(); }

when want insert info in database, execute next code:

functional part

list<hashmap<string, object>> list = new arraylist<>(); (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 dynamic query seems work perfectly. however, can't statement.addbatch() work. keeps giving me next error:

java.sql.sqlexception: no value specified parameter 9

i don't it, because have 8 parameters pass in every unit of batch. target table has 9 columns, tried add together value column, says: no value specified parameter 10, seems isn't closing 'batch unit' or something.

what missing here?

any help appreciated!

this

insert tablename (columnname1, ..., columnnamei) values (?, ..., ?), ..., (?, ...,?)

is not standard sql syntax. if utilize jdbc parameter each "?" in query.

use:

insert tablename (columnname1, ..., columnnamei) values (?, ..., ?)

and add together every statement batch.

java mysql database prepared-statement batch-processing