Two ways to batch update for JDBC

For cross-table bulk queries, we can use the "UNION" keyword, the subquery can also be paged query, but for the bulk update, JDBC can not update the subquery view, as follows:

Update
--subquery View
    (SELECT * from T_security_menu) 
as menu set back_url = ' url_2 ' WHERE pk = 2
--Prompts the following error: 
  --[ERR] 1288-the target Table menu of the UPDATE is not updatable

As you can see from the above error, the JDBC Bulk update must be done on the original table, which is divided into two situations:
1. batch update on the same sheet;
2. Carry out batch update across the table;

Here, take the Namedparameterjdbctemplate interface of spring JDBC as an example, two cases are described in turn, and the other class library usage is similar. 1. batch update in same table

Batch update on the same table, if the table name is the same, and the number of parameters of the response is the same, so execute SQL must be exactly the same, but the parameters are inconsistent, you can use the BatchUpdate method, as follows:

  SQL statement contains two parameters
String sql = "Update t_security_menu set Back_url =: B1 WHERE pk =:p 1"
//  multiple argument list
MAP&L T String, object> param1 = Maps.newhashmap ();
Param1.put ("B1", "Url_1");
Param1.put ("P1", 1L);
map<string, object> param2 = Maps.newhashmap ();
Param2.put ("B1", "url_2");
Param2.put ("P1", 2L);  Create parameter list
map<string, object>[] params = new map[]{param1, param2};  Execute SQL
int[] result = This.jdbcTemplate.batchUpdate (builder.tostring (), params);

From the above code can be seen, BatchUpdate is to execute the same statement many times, just constantly changing the parameters of the content, so the disadvantage is also obvious, only in the same table. 2. batch update across tables

Sometimes it happens when a table is distributed by date, that is, the daily data is stored in a separate table, so you need to update the batch across the table.

In this case, first of all, you cannot use the table name as an argument, as in Example 1, so that an escape error occurs (the table name is enclosed in single quotes), so you must process the SQL statements for each table separately, as follows:

StringBuilder builder = new StringBuilder ();  Be sure to add a semicolon to the end of the statement
builder.append ("update t_security_menu set Back_url =: B1 WHERE pk =:p 1;");
Builder.append ("update t_security_menu set Back_url =: b2 where pk =:p 2;");
Put  all parameters into a Map object
map<string, object> param1 = Maps.newhashmap ();
Param1.put ("B1", "Url_1");
Param1.put ("P1", 1L);
Param1.put ("B2", "url_2");
Param1.put ("P2", 2L);  Be sure to invoke the Execute method
This.jdbcTemplate.execute (builder.tostring (), param1, new PreparedStatementCallback <Object> () {
    @Override public
    Object doinpreparedstatement (PreparedStatement args) throws SQLException, DataAccessException {return
        null;
    }

});

In the above example, be sure to pay attention to two points, one is the end of each SQL statement must add a semicolon, and the second is to use the Execute method (BatchUpdate method error). Conclusions

Bulk INSERT, bulk Delete support syntax is similar to the update, you can use the same solution, you must organize the SQL statement by table name.