SPEC : MYSQL 5.7.16,JDK1.7,TOMCAT 8,mysql-connector-java-6.0.4.jar,WINDOWS 10
The Code below does not update batch method to db
Preparestatement pst = null;
String[] sqlx = {
"insert to abc('col1','col2')values('first', 'data')"
"insert to abc('col1','col2')values('second','data')"
"insert to abc('col1','col2')values('third', 'data')"
};
for(String sqIn : sqlx){
pst = <jdbcConn>.preparestatement(sqIn);
pst.addBatch();
}
int[] chkSql = pst.executeBatch();
//check if chkSql consists of 0..rollback else commit for > 0
On debug mode of the code, chkSql always has '1' ..this is dizzy ?
Only 1 row is inserted successfully , others are not inserted.
Is this a bug with MYSQLDB or JAR ????
Statement stmt = null;
String[] sqlx = {
"insert into abc(col1,col2) values('first', 'data')",
"insert into abc(col1,col2) values('second','data')",
"insert into abc(col1,col2) values('third', 'data')"
};
for(String sqIn : sqlx){
stmt.addBatch(sqIn);
}
int[] chkSql = stmt.executeBatch();
try running this code. connection is your Connection object. also don't use a prepared statement if you don't want to set values at runtime.
Have a look for this kind of queries here,
and, prepared statements here
I hope this helps you to understand when to use what
It looks like you are creating a new PreparedStatement at each iteration, so the executeBatch() will only be applied to the last PreparedStatement object.
Furthermore, PreparedStatement is used to avoid SQL injection and it takes care of values escaping, when you use the ? placeholders system.
The addBatch() method you are using is meant to work with variable parameters :
void addBatch()
throws SQLException
Adds a set of parameters to this PreparedStatement object's batch of
commands.
, not with raw queries like you tried to do (for that, you would use addBatch(java.lang.String query)
void addBatch(String sql)
throws SQLException
Adds the given SQL command to the current list of commmands for this
Statement object. The commands in this list can be executed as a batch
by calling the method executeBatch.
The following example should do what you want :
String[][] data = { { "first", "data" }, { "second", "data" }, { "third", "data" } };
String sql = "insert into abc(col1,col2) values (?, ?)";// use placeholders
PreparedStatement pst = connection.prepareStatement(sql);// create a single statement
for (String[] row : data) {
// set parameters
pst.setString(1, row[0]);
pst.setString(2, row[1]);
pst.addBatch();// validate the set
}
int[] chkSql = pst.executeBatch(); // execute the batch of commands
//check if chkSql consists of 0..rollback else commit for > 0
Have no idea about JDBC/Java but your posted INSERT statement is plain wrong. You should never quote the column names else it will be considered as string literal rather a column. it should be
insert to abc(col1,col2)values('first', 'data')
As in your posted code, it should look like
String[] sqlx = {
"insert into abc(col1,col2)values('first', 'data')",
"insert into abc(col1,col2)values('second','data')",
"insert into abc(col1,col2)values('third', 'data')"
};
