JDBCDataSink

The JDBCDataSink is used to write record into a table of an SQL database. It creates an insert, update or delete SQL statement based on the provided table name and column names. An existence test is carried out based on an adjustable WHERE clause. Such a WHERE clause may contain question marks ? for every parameter bound at runtime. The #? syntax extension is known from the JDBCDataSource. This module is tested with the JDBC drivers for Oracle, PostgreSQL and MariaDB databases, so it officially only supports MariaDB and Oracle databases.

Configuration

Name	Type, usage constraints, defaults	Description
table	required: paraVal default: none type: string	The name of the database table.
operation	optional: paraVal default: create type: export operation	Defines the operation used to export the object. The following operations are supported: create, update, createOrUpdate, and delete. See the table DataSink operations for a more detailed description of the supported operations.
columns	optional: paraList default: none type: string	The names of the table columns if not determined from the attributes of the output object.
whereClause	optional: paraVal default: none type: string	The SQL WHERE clause (excluding the WHERE keyword) used to identify the records in the database. Question marks can be used for substitution with parameter values. In addition the #? syntax is supported.
whereClauseParameter	optional: paraList default: none type: list of values	Values for every question mark parameter given in the WHERE clause.
datasource	required: paraVal default: none type: javax.sql.DataSource	A reference to a javax.sql.DataSource object. It defines either a JDBCConnectionPool defined in some initialization section of the configuration file or it refers a javax.sql.DataSource provided programmatically by the client application and which is put into the configuration registry.
autoCommit	optional: paraVal default: false type: boolean	Determines whether a transaction is automatically committed after the export of the entity.
updateMode	optional: paraVal default: full type: update mode	Determines how null values are treated. Update mode: full: set null attributes to NULL in the database partial: ignore columns with null values.

Example

<dataSink type="JDBCDataSink">
    <dp:paraVal name="operation" value="update" />
    <dp:paraVal name="datasource" value="${inst.ds}"/>
    <dp:paraVal name="table" value="PERSON" />
    <dp:paraList name="columns">
        <value>FIRSTNAME</value>
    </dp:paraList>
    <dp:paraVal name="whereClause" value="STATUS = ?" />
    <dp:paraList name="whereClauseParameter">
        <value>20</value>
    </dp:paraList>
</dataSink>