Description

The SQLBatchUpdateReceiver connects to a database and performs batched updates using a configuration file that contains one or more SQL update statements to tables in one or more databases. This Receiver is useful when executing synthetic data replacement of values on multiple tables within one or more databases to guarantee data security.


In This Article


Required Parameters

The SQLBatchUpdateReceiver requires that the following parameters are defined:

  • configPath - Defines the path to the configuration file where database connection details and Update statements are defined.
  • configFileName* - Defines the name of the configuration file.


Optional Parameters

  • configSubDir - Defines a subdirectory under the configPath directory where configuration files are stored.


Receiver Attribute Property Keys

No property keys are defined for this Receiver.


Example Configuration File

The following is an example configuration file showing the XML format that defines multiple JDBC connections and SQL Update queries.  In this example XML configuration, there are three databases that will be connected to by reading their respective JDBC properties file. 


The databases are:

  • alpha
  • beta
  • gamma


The respective JDBC properties files are:

  • configAlpha.properties

  • configBeta.properties

  • configGamma.properties


Within each <database><updateQueries>, one to many <updateQuery> elements may be defined. Each <updateQuery> element contains an SQL update statement.  The SQL update statement may access the values of the primary Domain within a given Scenario. 


<databaseConfig>
  <databases>
    <database>
      <jdbcPath>/Users/jdoe/resources/configAlpha.properties</jdbcPath>
      <updateQueries>
        <updateQuery><![CDATA[update user set first_name="#{firstName}" where id="#{id}";]]></updateQuery>
        <updateQuery><![CDATA[update user set last_name="#{lastName}" where id="#{id}";]]></updateQuery>
      </updateQueries>
    </database>
    <database>
      <jdbcPath>/Users/jdoe/resources/configBeta.properties</jdbcPath>
      <updateQueries>
        <updateQuery><![CDATA[update user set first_name="#{firstName}" where id="#{id}";]]></updateQuery>
        <updateQuery><![CDATA[update user set middle_initial="#{middleInitial}" where id="#{id}";]]></updateQuery>
      </updateQueries>
    </database>
     <database>
      <jdbcPath>/Users/jdoe/resources/configGamma.properties</jdbcPath>
      <updateQueries>
        <updateQuery><![CDATA[update user set last_name="#{lastName}" where id="#{id}";]]></updateQuery>
        <updateQuery><![CDATA[update user set middle_initial="#{middleInitial}" where id="#{id}";]]></updateQuery>
      </updateQueries>
    </database>
  </databases>
</databaseConfig>


Example JDBC Property File

The example JDBC property files connect to three MySQL databases, alpha, beta, and gamma:


ConfigAlpha.properties

  • <jdbcPath>/Users/jdoe/resources/configAlpha.properties</jdbcPath>


driver=com.mysql.jdbc.Driver
user=root
password=admin
url=jdbc:mysql://localhost:3306/alpha
batchCount=1000


ConfigBeta.properties

  • <jdbcPath>/Users/jdoe/resources/configBeta.properties</jdbcPath>


driver=com.mysql.jdbc.Driver
user=root
password=admin
url=jdbc:mysql://localhost:3306/beta
batchCount=1000


ConfigGamma.properties

  • <jdbcPath>/Users/jdoe/resources/configGamma.properties</jdbcPath>


driver=com.mysql.jdbc.Driver
user=root
password=admin
url=jdbc:mysql://localhost:3306/gamma
batchCount=1000


Example Domain

The following example User Domain is used in a Scenario to generate test data;  its id, firstName, lastName, and middleInitial attributes are used in the update queries described below. 



Example SQL Update Statements


SQL Updates on the alpha Database 

The two SQL update statements update the first_name and last_name of the user table in the alpha database.


update user set first_name="#{firstName}" where id="#{id}";
update user set last_name="#{lastName}" where id="#{id}";


SQL Updates on the beta Database 

The two SQL update statements update the first_name and middle_initial of the user table in the beta database.


update user set first_name="#{firstName}" where id="#{id}";
update user set middle_initial="#{middleInitial}" where id="#{id}";


SQL Updates on the gamma Database 

The two SQL update statements update the last_name and middle_initial of the user table in the gamma database.


update user set last_name="#{lastName}" where id="#{id}";
update user set middle_initial="#{middleInitial}" where id="#{id}";