MySQL Java Spring batchUpdate column name escaping

Question

I have a mysql batchUpdate using Spring named JDBC as follows:

public SqlParameterSource[] getBatchParamsArray(Collection params) {
    List<SqlParameterSource> dbParams = new ArrayList<>();
    for(Object p: params) {
       BeanPropertySqlParameterSource beanPropertySqlParameterSource = new      BeanPropertySqlParameterSource(p);
       dbParams.add(beanPropertySqlParameterSource);
     }
     return dbParams.toArray(new SqlParameterSource[0]);
}

This is used as follows:

String SQL = "INSERT INTO mytable (user_id, related_user_id,:tagName) VALUES (:userId,:relatedUserId,:taggedAt) ON DUPLICATE KEY UPDATE set :tagName=:taggedAt"
this.namedJDBC.batchUpdate(SQL, this.getBatchParamsArray(listOfPOJOs));

My problem is that batchUpdate escapes the column name resulting in a query like so:

INSERT INTO mytable (tuser_id, related_user_id,'some_tag') VALUES (1234,24542336,'2015-12-17 10:44:25') ON DUPLICATE KEY UPDATE 'some_tag'='2015-12-17 10:44:25'

The column some_tag exists, but the escaping ' makes the SQL query fail, so how can I prevent escaping and do it manually, or otherwise achieve the desired generic functionality of batch inserts

String SQL = "INSERT INTO mytable (user_id, related_user_id,"+tagName+") VALUES (:userId,:relatedUserId,:taggedAt) ON DUPLICATE KEY UPDATE set "+tagName+"=:taggedAt" — StanislavL
"+tagName+" wouldn't be replaced with the actual tag "some_tag" — Alex Sayegh

Alex Sayegh Alex Sayegh · Accepted Answer · 2015-12-29T11:56:58

So far, it seems that's not possible, I've tried also:

SET @tagName='some_tag'; INSERT INTO myTable (user_id, related_user_id,@tagName) ....

Still the same problem. So I resulted to this:

this.getConnection().setAutoCommit(false);
for(POJO t:listOfPOJOs) {
   this.namedJDBC.update(SQL.replace(":tagName",t.getTagName()), new BeanPropertySqlParameterSource(t));
}
this.getConnection().setAutoCommit(true);

As such appreciate your feedback here!

MySQL Java Spring batchUpdate column name escaping

1 Answers