The jdbcTemplate implementation in Spring executes several examples of sql statements

  • 2020-05-30 20:07:20
  • OfStack

Say 1: jdbcTemplate is used in the Spring framework to execute multiple sql statements:

Many cases when we need to deal with one thing need to multiple tables to perform multiple sql statement, taobao order entry, for example, when we confirm the payment to your bank account list of the required amount, minus the orders which need to update the bank account of the table, at the same time, need to update the taobao order table will change order status to "paid", which requires successively performing multiple sql (only for expressing perform more SQL, for example, the specific taobao are not clear how to do ~ ~ ~ ~ ~); But if this intermediate computer broken network outages and other problems, only to our bank account money deducted, order status does not change, that we are very bad, but in fact we didn't meet this kind of situation right, here I am, how to use the Spring jdbcTemplate implement under 1 execute multiple sql statements, and does not appear this kind of circumstance


@Test  
public void  To allocate (){
    //1. The client check box is passed 1 An array 1 . 2  The menu ID
    Integer[] menus =new Integer[]{1,2};
    //2. The statement sql An array of 
    String [] sql =new String [menus.length+1];
    //3. through Role_id 200  Deletes data from a table 
    sql[0] = "delete from role_link_menu where fk_role_id=200";
    //4 New data is added to the intermediate table 
    for (int i=0;i<menus.length;i++) {
      sql[i+1]="insert into role_link_menu(id,fk_role_id,fk_menu_id) values ('"+UUID.randomUUID().toString()+"',200,             "+menus[i]+")";
    }
    jdbcTemplate.batchUpdate(sql);
  }

So long as we're connected to the database

So we have three SQL statements executed by 1

1.delete from role_link_menu where fk_role_id=200

2.insert into role_link_menu(id,fk_role_id,fk_menu_id) values ('"+UUID.randomUUID().toString()+"',200,1")";

3.insert into role_link_menu(id,fk_role_id,fk_menu_id) values ('"+UUID.randomUUID().toString()+"',200,2")";

During this period, any SQL statement that has a problem will be rolled back [**] and the initial state of all statements before execution will be ^_^

Compare the following set of code and you can see the difference


@Test
  public void  Permissions are assigned to a single process () throws Exception{
    //1. through Role_id 200  Deletes data from a table 
    String sql = "delect from role_link_menu where fk_role_id=200";
    jdbcTemplate.update(sql);
    //2. The client check box is passed 1 An array 1,2  The menu id
    Integer[] menus =new Integer[]{1,2};
    //3. New data is added to the intermediate table 
    for (int i=0;i<menus.length;i++) {
      String menu_sql="insert into role_link_menu(id,fk_role_id,fk_menu_id) values ('"+UUID.randomUUID().toString()+           "',200,"+menus[i]+")";
      if(i==1){
        throw new Exception("=====");
      }
      jdbcTemplate.update(menu_sql);
    }
  }

Here, when i=1, there will be an exception to terminate the program, and a total of two statements have been executed

1.delect from role_link_menu where fk_role_id=200

2.insert into role_link_menu(id,fk_role_id,fk_menu_id) values ('"+UUID.randomUUID().toString()+ "',200,1)";

The following sentence will not be executed, but before the completion of these two execution, is equivalent to the original we buy beer to drink Fried chicken, account money deduction, the result is only to give you a can of beer, is not depressed knead, ah-ha, anyway I will not be happy

~ ~ ~

* * note:

Rollback: a transaction is a grouping of operations that compose a logical unit of work. Although errors may occur in the system, the transaction controls and maintains the integrity and integrity of each operation in the transaction.

For example, in a bank application that transfers funds from one account to another, one account credits a specified amount to one database table, while another account debits the same amount to another database table. Because the computer may fail due to power outages, network outages, etc., it is possible to update the rows in one table without updating the rows in the other table.

If the database supports transactions, the database operations can be grouped into one transaction to prevent the database from becoming non-compliant due to these events. If a failure occurs at some point in the transaction, all updates can be rolled back to the state they were in before the transaction began. If no failure occurs, the update is done by committing the transaction as a completed state.


Related articles: