Cernunnos Manual

<sql-upsert>

Description:

Executes the specified UPDATE statement. If zero rows are affected, executes the specified INSERT statement. By default, this task looks for a Connection object as a request attribute under the name OpenConnectionTask.DEFAULT_ATTRIBUTE_NAME.

Reagents:

Name XPath Description Reagent Type Expected Type Required
DATA_SOURCE @data-source The DataSource to use for executing the SQL. If omitted the request attribute under the name 'SqlAttributes.DATA_SOURCE' will be used PHRASE javax.sql.DataSource No
CONNECTION @connection **DEPRECATED: Use DATA_SOURCE instead.** Optional Connection object. The default is the value of the 'SqlAttributes.CONNECTION' request attribute (if specified) or null. PHRASE java.sql.Connection No
UPDATE_SQL update-statement The SQL statement that performs the Update portion of the 'Upsert' operation. PHRASE java.lang.String Yes
INSERT_SQL insert-statement The SQL statement that performs the Insert portion of the 'Upsert' operation. PHRASE java.lang.String Yes
PARAMETERS parameter/@value The parameters (if any) for the PreparedStatement objects that will perform this upsert. WARNING: Parameters must appear in the same order as the associated SQL. NODE_LIST java.util.List No
UPDATE_PARAMETERS update-parameter/@value If provided, UPDATE_PARAMETERS will override the PARAMETERS list for the 'update' operation only. Use UPDATE_PARAMETERS and INSERT_PARAMETERS instead of PARAMETERS if update and insert parameters must differ in number or order. NODE_LIST java.util.List No
INSERT_PARAMETERS insert-parameter/@value If provided, INSERT_PARAMETERS will override the PARAMETERS list for the 'insert' operation only. Use UPDATE_PARAMETERS and INSERT_PARAMETERS instead of PARAMETERS if update and insert parameters must differ in number or order. NODE_LIST java.util.List No

Examples:

Loads a Spring bean container defined in 'datasourceContext.xml' which contains a DataSource, then begins a transaction, then queries that DataSource for the 'user_id' associated with the 'admin' account, then performs an 'upsert' operation on the 'up_user_profile' table:

 
       
        <spring-beans location="datasourceContext.xml"> 
          <with-attribute key="SqlAttributes.DATA_SOURCE" value="${PortalDb}"> 
            <sql-transaction> 
              <sql-upsert> 
                <update-statement>UPDATE up_user_profile SET profile_name = 'HTML browser profile' WHERE user_id = ?</update-statement>  
                <insert-statement>INSERT INTO up_user_profile(user_id, profile_id, profile_name, description, layout_id, structure_ss_id, theme_ss_id) VALUES(?, 1, 'HTML browser profile', 'A sample profile for common web browsers', 1, 1 1)</insert-statement>  
                <parameter value="${sql(SELECT user_id FROM up_user WHERE user_name = 'admin')}"/> 
              </sql-upsert> 
            </sql-transaction> 
          </with-attribute> 
        </spring-beans>