Nav

To Execute Bulk Operations

The insert, update, and delete operations we saw above are fine for the cases in which each input parameter can take only one value.

For example, when deleting, many rows could match the criteria and get deleted, but only one criteria (POSITION = X) is provided. The same concept applies for update, if you do UPDATE PRODUCTS set PRICE = PRICE * 0.9 where PRICE > :price, you may be applying a 10% discount on many products, but the price input parameter will only take one value.

What happens if we want to apply different discount rates on products that have different prices? Well, we could do it by executing many operations. For example, assume you have a payload which is a list of object of the following structure { price : number, discountRate: number}, then, we could do this:


       
    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<foreach>
  <db:update config-ref="dbConfig">
    <db:input-parameters>
     #[
      {
        'discountRate' : payload.discountRate,
        'price' : payload.price,
      }
    ]
    </db:input-parameters>
    <db:sql>
      UPDATE PRODUCTS set PRICE = PRICE * :discountRate where PRICE > :price
    </db:sql>
  </db:update>
</foreach>

That method would certainly get the job done; however, it is highly inefficient. One query needs to be executed per each element in the list. That means that for each element we will have to:

  • Parse the query

  • Resolve parameters

  • Grab a connection to the DB (either by getting one for the pool or establishing a new one)

  • Pay all the network overhead

  • The RBMS has to process the query and apply changes

  • Release the connection

You can avoid all of the above steps by doing a bulk operation. When you look at it, there’s only one query here; the update statement is constant, not dynamic. The only thing that changes is that, on each iteration, we supply a different set of parameters.

Bulk operations allow you to do exactly that: to run one single query using a set of parameters values. Make no mistake though, this is not just a shortcut for the same <foreach> above, this uses features on the JDBC API so that:

  • Query is parsed only once

  • Only one DB connection is required since a single statement is executed

  • Network overhead is minimized

  • RBDMS can execute the bulk operation atomically

For these use cases, the connector offers three operations, <db:bulk-insert>, <db:bulk-update> and <db:bulk-delete>.

These are pretty similar to their single counterparts, except that instead of receiving input parameters as key-value pairs, they expect them as a list of key-value pairs.

Let’s look at an example:


       
    
1
2
3
4
5
6
7
8
<db:bulk-insert config-ref="dbConfig" >
  <db:bulk-input-parameters>
    #[[{'id': 2, 'name': 'George', 'lastName': 'Costanza'}, {'id': 3, 'name': 'Cosmo', 'lastName': 'Kramer'}]]
  </db:bulk-input-parameters>
  <db:sql>
    insert into customers (id, name, lastName) values (:id, :name, :lastName)
  </db:sql>
</db:bulk-insert>