<?xml version="1.0" encoding="UTF-8"?>
<chapter xmlns="http://docbook.org/ns/docbook" version="5.0" xml:id="jdbc"
    xmlns:xlink="http://www.w3.org/1999/xlink">
  <title>JDBC Support</title>

  <para>
      Spring Integration provides Channel Adapters for receiving and sending
      messages via database queries. Through those adapters Spring Integration 
      supports not only plain JDBC SQL Queries, but also Stored Procedure and 
      Stored Function calls.
  </para>
  <para>
      The following JDBC components are available by default:
  </para>
      <itemizedlist>
          <listitem>
              <para><emphasis><link linkend='jdbc-inbound-channel-adapter'>Inbound Channel Adapter</link></emphasis></para>
          </listitem>
          <listitem>
              <para><emphasis><link linkend='jdbc-outbound-channel-adapter'>Outbound Channel Adapter</link></emphasis></para>
          </listitem>
          <listitem>
              <para><emphasis><link linkend='jdbc-outbound-gateway'>Outbound Gateway</link></emphasis></para>
          </listitem>
          <listitem>
              <para><emphasis><link linkend='stored-procedure-inbound-channel-adapter'>Stored Procedure Inbound Channel Adapter</link></emphasis></para>
          </listitem>
          <listitem>
              <para><emphasis><link linkend='stored-procedure-outbound-channel-adapter'>Stored Procedure Outbound Channel Adapter</link></emphasis></para>
          </listitem>
          <listitem>
              <para><emphasis><link linkend='stored-procedure-outbound-gateway'>Stored Procedure Outbound Gateway</link></emphasis></para>
          </listitem>
      </itemizedlist>
  <para>
      Furthermore, the Spring Integration JDBC Module also provides a 
      <emphasis><link linkend='jdbc-message-store'>JDBC Message Store</link></emphasis>
  </para>

  <section id="jdbc-inbound-channel-adapter">
    <title>Inbound Channel Adapter</title>

    <para>The main function of an inbound Channel Adapter is to execute a SQL
    <code>SELECT</code> query and turn the result set as a message. The
    message payload is the whole result set, expressed as a
    <classname>List</classname>, and the types of the items in the list
    depend on the row-mapping strategy that is used. The default strategy is
    a generic mapper that just returns a <classname>Map</classname> for each
    row in the query result. Optionally, this can be changed by adding a reference to
    a <classname>RowMapper</classname> instance (see the
    <ulink url="http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html">Spring
    JDBC</ulink> documentation for more detailed information about row mapping).<note>
        <para>If you want to convert rows in the SELECT query result to
        individual messages you can use a downstream splitter.</para>
      </note></para>

    <para>The inbound adapter also requires a reference to either
    a <classname>JdbcTemplate</classname> instance or
    a <interfacename>DataSource</interfacename>.</para>

    <para>As well as the <code>SELECT</code> statement to generate the
    messages, the adapter above also has an <code>UPDATE</code> statement that
    is being used to mark the records as processed so that they don't show up in
    the next poll. The update can be parameterized by the list of ids from the
    original select. This is done through a naming convention by default (a
    column in the input result set called "id" is translated into a list in
    the parameter map for the update called "id"). The following example
    defines an inbound Channel Adapter with an update query and a
    <classname>DataSource</classname> reference.

    <programlisting language="xml"><![CDATA[<int-jdbc:inbound-channel-adapter query="select * from item where status=2"
    channel="target" data-source="dataSource"
    update="update item set status=10 where id in (:id)" />]]></programlisting>
    <note>
         The parameters in the update query are specified with a colon (:) prefix to the name of a parameter (which in this case is an expression to be applied to each of the rows in the polled result set). This is a standard feature of the named parameter JDBC support in Spring JDBC combined with a convention (projection onto the polled result list) adopted in Spring Integration. The underlying Spring JDBC features limit the available expressions (e.g. most special characters other than period are disallowed), but since the target is usually a list of or an individual object addressable by simple bean paths this isn't unduly restrictive.
      </note> To change the parameter generation strategy you can inject a
    <classname>SqlParameterSourceFactory</classname> into the adapter to
    override the default behavior (the adapter has a
    <code>sql-parameter-source-factory</code> attribute).</para>

    <section>
      <title>Polling and Transactions</title>

      <para>The inbound adapter accepts a regular Spring Integration poller as
      a sub element, so for instance the frequency of the polling can be
      controlled. A very important feature of the poller for JDBC usage is the
      option to wrap the poll operation in a transaction, for example:</para>

      <programlisting language="xml"><![CDATA[<int-jdbc:inbound-channel-adapter query="..."
        channel="target" data-source="dataSource" update="...">
    <int:poller fixed-rate="1000">
        <int:transactional/>
    </int:poller>
</int-jdbc:inbound-channel-adapter>]]></programlisting>

      <para><note>
           If a poller is not explicitly specified, a default value will be used (and as per normal with Spring Integration can be defined as a top level bean).
        </note>
      In this example the database is polled every 1000 milliseconds, and the update and
      select queries are both executed in the same transaction. The transaction manager
      configuration is not shown, but as long as it is aware of the data source then the
      poll is transactional. A common use case is for the downstream channels to be
      direct channels (the default), so that the endpoints are invoked in the
      same thread, and hence the same transaction. Then if any of them fail,
      the transaction rolls back and the input data is reverted to its
      original state.</para>
    </section>
  </section>

  <section id="jdbc-outbound-channel-adapter">
    <title>Outbound Channel Adapter</title>

    <para>The outbound Channel Adapter is the inverse of the inbound: its role
    is to handle a message and use it to execute a SQL query. The message
    payload and headers are available by default as input parameters to the
    query, for instance:
    </para>
    
    <programlisting language="xml"><![CDATA[<int-jdbc:outbound-channel-adapter
    query="insert into foos (id, status, name) values (:headers[id], 0, :payload[foo])"
    data-source="dataSource"
    channel="input"/>]]></programlisting>

    <para>
        In the example above, messages arriving on the channel labelled 
        <emphasis>input</emphasis> have a payload of a map with key 
        <emphasis>foo</emphasis>, so the <code>[]</code> operator dereferences 
        that value from the map. The headers are also accessed as a map.
    </para>
    
    <note>
         The parameters in the query above are bean property expressions on the 
         incoming message (not Spring EL expressions). This behavior is part of the
        <classname>SqlParameterSource</classname>
         which is the default source created by the outbound adapter. Other 
         behavior is possible in the adapter, and requires the user to inject a 
         different <classname>SqlParameterSourceFactory</classname>.
    </note>

    <para>
        The outbound adapter requires a reference to either a 
        <interfacename>DataSource</interfacename> or a 
        <classname>JdbcTemplate</classname>. It can also have a 
        <classname>SqlParameterSourceFactory</classname> injected to control 
        the binding of each incoming message to a query.
    </para>

    <para>
        If the input channel is a direct channel, then the outbound adapter runs 
        its query in the same thread, and therefore the same transaction (if
        there is one) as the sender of the message.
    </para>
    
    <para><emphasis>Passing Parameters using SpEL Expressions</emphasis></para>

    <para>
        A common requirement for most JDBC Channel Adapters is to pass parameters
        as part of Sql queries or Stored Procedures/Functions. As mentioned above,
        these parameters are by default bean property expressions, not SpEL expressions. 
        
        However, if you need to pass SpEL expression as parameters, you must inject 
        a <interfacename>SqlParameterSourceFactory</interfacename> explicitly.
    </para>
    <para>
        The following example uses a <classname>ExpressionEvaluatingSqlParameterSourceFactory</classname> 
        to achieve that requirement. 
    </para>
    
    <programlisting language="xml"><![CDATA[<jdbc:outbound-channel-adapter data-source="dataSource" channel="input"
    query="insert into MESSAGES (MESSAGE_ID,PAYLOAD,CREATED_DATE)     \
    values (:id, :payload, :createdDate)"
    sql-parameter-source-factory="spelSource"/>
        
<bean id="spelSource" 
      class="o.s.integration.jdbc.ExpressionEvaluatingSqlParameterSourceFactory">
    <property name="parameterExpressions">
        <map>
            <entry key="id"          value="headers['id'].toString()"/>
            <entry key="createdDate" value="new java.util.Date()"/>
            <entry key="payload"     value="payload"/>
        </map>
    </property>
</bean>]]></programlisting>
      <para>
          For further information, please also see 
          <xref linkend="sp-defining-parameter-sources"/>
      </para>
  </section>

  <section id="jdbc-outbound-gateway">
    <title>Outbound Gateway</title>

    <para>The outbound Gateway is like a combination of the outbound and
    inbound adapters: its role is to handle a message and use it to execute a
    SQL query and then respond with the result sending it to a reply channel.
    The message payload and headers are available by default as input
    parameters to the query, for instance:
    <programlisting language="xml"><![CDATA[<int-jdbc:outbound-gateway
    update="insert into foos (id, status, name) values (:headers[id], 0, :payload[foo])"
    request-channel="input" reply-channel="output" data-source="dataSource" />]]></programlisting></para>

    <para>The result of the above would be to insert a record into the "foos"
    table and return a message to the output channel indicating the number of
    rows affected (the payload is a map: <literal>{UPDATED=1}</literal>).</para>

    <para>If the update query is an insert with auto-generated keys, the reply
    message can be populated with the generated keys by adding
    <literal>keys-generated="true"</literal> to the above example (this is not
    the default because it is not supported by some database platforms). For
    example:</para>

    <programlisting language="xml"><![CDATA[<int-jdbc:outbound-gateway
    update="insert into foos (status, name) values (0, :payload[foo])"
    request-channel="input" reply-channel="output" data-source="dataSource"
    keys-generated="true"/>]]></programlisting>

    <para>Instead of the update count or the generated keys, you can also
    provide a select query to execute and generate a reply message from the result
    (like the inbound adapter), e.g:</para>

    <programlisting language="xml"><![CDATA[<int-jdbc:outbound-gateway
    update="insert into foos (id, status, name) values (:headers[id], 0, :payload[foo])"
    query="select * from foos where id=:headers[$id]"
    request-channel="input" reply-channel="output" data-source="dataSource"/>]]></programlisting>

    <para>As with the channel adapters, there is also the option to provide
    <classname>SqlParameterSourceFactory</classname> instances for request and
    reply. The default is the same as for the outbound adapter, so the request
    message is available as the root of an expression. If
    keys-generated="true" then the root of the expression is the generated
    keys (a map if there is only one or a list of maps if
    multi-valued).</para>

    <para>The outbound gateway requires a reference to either a DataSource or
    a JdbcTemplate. It can also have a
    <classname>SqlParameterSourceFactory</classname> injected to control the
    binding of the incoming message to the query.</para>
  </section>

  <section id="jdbc-message-store">
    <title>JDBC Message Store</title>

    <para>The JDBC module provides an implementation of the Spring Integration
    <classname>MessageStore</classname> (important in the Claim Check pattern)
    and <classname>MessageGroupStore</classname> (important in stateful
    patterns like Aggregator) backed by a database. Both interfaces are
    implemented by the JdbcMessageStore, and there is also support for
    configuring store instances in XML. For example:</para>

    <programlisting language="xml"><![CDATA[<int-jdbc:message-store id="messageStore" data-source="dataSource"/>
]]></programlisting>

    <para>A <classname>JdbcTemplate</classname> can be specified instead of a
    <classname>DataSource</classname>.</para>

    <para>Other optional attributes are show in the next example:</para>

    <para><programlisting language="xml"><![CDATA[<int-jdbc:message-store id="messageStore" data-source="dataSource"
    lob-handler="lobHandler" table-prefix="MY_INT_"/>]]></programlisting>

    Here we have specified a <classname>LobHandler</classname> for dealing with
    messages as large objects (e.g. often necessary if using Oracle) and a
    prefix for the table names in the queries generated by the store. The
    table name prefix defaults to "INT_".</para>

    <section>
      <title>Initializing the Database</title>

      <para>Spring Integration ships with some sample scripts that can be used
      to initialize a database. In the spring-integration-jdbc JAR file you
      will find scripts in the
      <classname>org.springframework.integration.jdbc</classname> package:
      there is a create and a drop script example for a range of common
      database platforms. A common way to use these scripts is to reference
      them in a <ulink
      url="http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html#d0e24182">Spring
      JDBC data source initializer</ulink>. Note that the scripts are provided
      as samples or specifications of the the required table and column names.
      You may find that you need to enhance them for production use (e.g. with
      index declarations).</para>
    </section>

    <section>
      <title>Partitioning a Message Store</title>

      <para>It is common to use a <classname>JdbcMessageStore</classname> as a
      global store for a group of applications, or nodes in the same
      application. To provide some protection against name clashes, and to
      give control over the database meta-data configuration, the message
      store allows the tables to be partitioned in two ways. One is to use
      separate table names, by changing the prefix as described above, and the
      other is to specify a "region" name for partitioning data within a
      single table. An important use case for this is when the MessageStore is
      managing persistent queues backing a Spring Integration Message Channel. The
      message data for a persistent channel is keyed in the store on the
      channel name, so if the channel names are not globally unique then there
      is the danger of channels picking up data that was not intended for
      them. To avoid this, the message store region can be used to keep data
      separate for different physical channels that happen to have the same
      logical name.</para>
    </section>
    </section>
    
    <section id="stored-procedures">
        <title>Stored Procedures</title>
        <para>
            In certain situations plain JDBC support is not sufficient. Maybe you
            deal with legacy relational database schemas or you have
            complex data processing needs, but ultimately you have to use
            <ulink url="http://en.wikipedia.org/wiki/Stored_procedure">Stored Procedures</ulink>
            or Stored Functions. Since Spring Integration 2.1, we provide
            three components in order to execute Stored Procedures or 
            Stored Functions:
        </para>
        <itemizedlist>
	        <listitem>Stored Procedures Inbound Channel Adapter</listitem>
	        <listitem>Stored Procedures Outbound Channel Adapter</listitem>
	        <listitem>Stored Procedures Outbound Gateway</listitem>
        </itemizedlist>
        
        <section id="sp-supported-databases">
            <title>Supported Databases</title>
            <para>
                In order to enable calls to <emphasis>Stored Procedures</emphasis>
                and <emphasis>Stored Functions</emphasis>, the Stored Procedure
                components use the <ulink
                url="http://static.springsource.org/spring/docs/current/javadoc-api/org/springframework/jdbc/core/simple/SimpleJdbcCall.html"><classname>org.springframework.jdbc.core.simple.SimpleJdbcCall</classname></ulink>
                class. Consequently, the following databases are fully supported
                for executing Stored Procedures:</para>
            <itemizedlist>
                <listitem>Apache Derby</listitem>
                <listitem>DB2</listitem>
                <listitem>MySQL</listitem>
                <listitem>Microsoft SQL Server</listitem>
                <listitem>Oracle</listitem>
                <listitem>PostgreSQL</listitem>
                <listitem>Sybase</listitem>
            </itemizedlist>
            <para>
                If you want to exute Stored Functions instead, the following
                databases are fully supported:
            </para>
            <itemizedlist>
                <listitem>MySQL</listitem>
                <listitem>Microsoft SQL Server</listitem>
                <listitem>Oracle</listitem>
                <listitem>PostgreSQL</listitem>
            </itemizedlist>
        </section>
        <note>
            <para>
                Even though your particular database may not be fully supported,
                chances are, that you can use the Stored Procedure Spring Integration
                components quite successfully anyway, provided your RDBMS supports
                Stored Procedures or Functions.</para>
            <para>
                As a matter of fact, some of the provided integration tests use
                the <ulink url="http://www.h2database.com/">H2 database</ulink>. 
                Nevertheless, it is very important to thoroughly test those usage scenarios.
            </para>
        </note>
        <section id="sp-configuration">
            <title>Configuration</title>
            <para>
                The Stored Procedure components provide full XML Namespace support
                and configuring the components is similar as for the general purpose
                JDBC components discussed earlier.
            </para>
        </section>
        
        <section id="sp-common-config-params">
            <title>Common Configuration Attributes</title>
            <para>
                Certain configuration parameters are shared among all Stored Procedure
                components and are described below:
            </para>

            <para><emphasis role="bold">auto-startup</emphasis></para>
            <para>
                Lifecycle attribute signaling if this component should
                be started during Application Context startup.
                Defaults to <code>true</code>.
                <emphasis>Optional</emphasis>.
            </para>

            <para><emphasis role="bold">data-source</emphasis></para>
            <para>
                Reference to a <interfacename>javax.sql.DataSource</interfacename>, 
                which is used to access the database.
                <emphasis>Required</emphasis>.
            </para>

            <para><emphasis role="bold">id</emphasis></para>
            <para>
                Identifies the underlying Spring bean definition, which
                is an instance of either <classname>EventDrivenConsumer</classname>
                or <classname>PollingConsumer</classname>, depending
                on whether the Outbound Channel Adapter's <code>channel</code>
                attribute references a <interfacename>SubscribableChannel</interfacename>
                or a <interfacename>PollableChannel</interfacename>.
                <emphasis>Optional</emphasis>.
            </para>

            <para><emphasis role="bold">ignore-column-meta-data</emphasis></para>
            <para>
                For fully supported databases, the underlying
                <ulink url="http://static.springsource.org/spring/docs/current/javadoc-api/org/springframework/jdbc/core/simple/SimpleJdbcCall.html"><classname>SimpleJdbcCall</classname></ulink>
                class can automatically retrieve the parameter information
                for the to be invoked Stored Procedure or Function
                from the JDBC Meta-data.
            </para>
            <para>
                However, if the used database does not support meta
                data lookups or if you like to provide customized parameter
                definitions, this flag can be set to <code>true</code>. It defaults
                to <code>false</code>.
                <emphasis>Optional</emphasis>.
            </para>

            <para><emphasis role="bold">is-function</emphasis></para>
            <para>
                If <code>true</code>, a SQL Function is called. In that case the
                <code>stored-procedure-name</code> attribute defines the name of
                the called function. Defaults to <code>false</code>.
                <emphasis>Optional</emphasis>.
            </para>
            <para><emphasis role="bold">stored-procedure-name</emphasis></para>
            <para>
                The attribute specifies the name of the stored procedure. If the 
                <code>is-function</code> attribute is set to <code>true</code>, 
                this attribute specifies the function name.
                <emphasis>Required</emphasis>.
            </para>
            <para><emphasis role="bold">sql-parameter-source-factory</emphasis>
            (Not available for the Stored Procedure Inbound Channel Adapter.)</para>

            <para>
                Reference to a <interfacename>SqlParameterSourceFactory</interfacename>.

                By default bean properties of the passed in
                <interfacename>Message</interfacename> payload will be used
                as a source for the Stored Procedure's input parameters
                using a <classname>BeanPropertySqlParameterSourceFactory</classname>.
            </para>
            <para>
                This may be sufficient for basic use cases. For more
                sophisticated options, consider passing in one or more
                <classname>ProcedureParameter</classname>. Please also refer to 
                <xref linkend="sp-defining-parameter-sources"/>.
                <emphasis>Optional</emphasis>.
            </para>
            
            <para><emphasis role="bold">use-payload-as-parameter-source</emphasis>            
            (Not available for the Stored Procedure Inbound Channel Adapter.)</para>
            
            <para>
                If set to <code>true</code>, the payload of the Message 
                will be used as a source for providing parameters. 
                If false, however, the entire Message will be available as a 
                source for parameters.
             </para>
             <para>   
                If no Procedure Parameters are passed in, this property
                will default to <code>true</code>. This means that using a default 
                <classname>BeanPropertySqlParameterSourceFactory</classname> 
                the bean properties of the payload will be used as a 
                source for parameter values for the to-be-executed 
                Stored Procedure or Stored Function.
             </para>
             <para>         
                However, if Procedure Parameters are passed in, then 
                this property will by default evaluate to <code>false</code>. 
                <classname>ProcedureParameter</classname> allow for 
                SpEL Expressions to be provided and therefore it is 
                highly beneficial to have access to the entire Message. The property
                is set on the underlying <classname>StoredProcExecutor</classname>.
                <emphasis>Optional</emphasis>.
            </para>             
        </section>
        
        <section id="sp-common-config-subelements">
            <title>Common Configuration Sub-Elements</title>
            <para>
                The Stored Procedure components share a common set of sub-elements
                to define and pass parameters to Stored Procedures or Functions.
                The following elements are available:
            </para>

            <itemizedlist>
                <listitem>parameter</listitem>
                <listitem>returning-resultset</listitem>
                <listitem>sql-parameter-definition</listitem>
                <listitem>poller</listitem>
            </itemizedlist>

            <para><emphasis role="bold">parameter</emphasis></para>
            <para>
                Provides a mechanism to provide Stored Procedure parameters.
                Parameters can be either static or provided using a SpEL Expressions.
                <emphasis>Optional</emphasis>.
            </para>

            <programlisting language="xml"><![CDATA[<int-jdbc:parameter name=""    ]]><co id="sp-parameter-sub-xml01-co" linkends="sp-parameter-sub-xml01" /><![CDATA[
                    type=""    ]]><co id="sp-parameter-sub-xml02-co" linkends="sp-parameter-sub-xml02" /><![CDATA[
                    value=""/> ]]><co id="sp-parameter-sub-xml03-co" linkends="sp-parameter-sub-xml03" /><![CDATA[

<int-jdbc:parameter name=""
                    expression=""/>]]><co id="sp-parameter-sub-xml04-co" linkends="sp-parameter-sub-xml04" /></programlisting>

            <para>
                <calloutlist>
                    <callout arearefs="sp-parameter-sub-xml01-co" id="sp-parameter-sub-xml01">
                        <para>
                            The name of the parameter to be passed into the
                            Stored Procedure or Stored Function.
                            <emphasis>Required</emphasis>.
                        </para>
                    </callout>
                    <callout arearefs="sp-parameter-sub-xml02-co" id="sp-parameter-sub-xml02">
                        <para>
                            This attribute specifies the type of the value. If
                            nothing is provided this attribute will default to
                            <classname>java.lang.String</classname>. This attribute
                            is only used when the <code>value</code> attribute is
                            used.
                            <emphasis>Optional</emphasis>.
                        </para>
                    </callout>
                    <callout arearefs="sp-parameter-sub-xml03-co" id="sp-parameter-sub-xml03">
                        <para>
                            The value of the parameter. You have to provider either
                            this attribute or the <code>expression</code> attribute must be
                            provided instead.
                            <emphasis>Optional</emphasis>.
                        </para>
                    </callout>
                    <callout arearefs="sp-parameter-sub-xml04-co" id="sp-parameter-sub-xml04">
                        <para>
                            Instead of the <code>value</code> attribute, you can
                            also specify a SpEL expression for passing the value
                            of the parameter. If you specify the <code>expression</code>
                            the <code>value</code> attribute is not allowed.
                            <emphasis>Optional</emphasis>.
                        </para>
                    </callout>
                </calloutlist>
            </para>

            <para><emphasis role="bold">returning-resultset</emphasis></para>
            <para>
                Stored Procedures may return multiple resultsets. By setting one
                or more <code>returning-resultset</code> elements, you can specify
                <interfacename>RowMappers</interfacename> in order to convert
                each returned <classname>ResultSet</classname> to meaningful objects.
                <emphasis>Optional</emphasis>.
            </para>
            <programlisting language="xml"><![CDATA[<int-jdbc:returning-resultset name="" row-mapper="" />]]></programlisting>

            <para><emphasis role="bold">sql-parameter-definition</emphasis></para>

            <para>
                If you are using a database that is fully supported, you typically
                don't have to specify the Stored Procedure parameter definitions.
                Instead, those parameters can be automatically derived from the
                JDBC Meta-data. However, if you are using databases that are not
                fully supported, you must set those parameters explicitly using the
                <code>sql-parameter-definition</code> sub-element.
            </para>
            <para>
                You can also choose to turn off any processing of parameter meta
                data information obtained via JDBC using the <code>ignore-column-meta-data</code>
                attribute.
            </para>

            <programlisting language="xml"><![CDATA[<int-jdbc:sql-parameter-definition name=""         ]]><co id="sp-parameter-definition-xml01-co" linkends="sp-parameter-definition-xml01" /><![CDATA[
                                    direction="IN"  ]]><co id="sp-parameter-definition-xml02-co" linkends="sp-parameter-definition-xml02" /><![CDATA[
                                    type="STRING"   ]]><co id="sp-parameter-definition-xml03-co" linkends="sp-parameter-definition-xml03" /><![CDATA[
                                    scale=""/>      ]]><co id="sp-parameter-definition-xml04-co" linkends="sp-parameter-definition-xml04" /></programlisting>
            <para>
	            <calloutlist>
                    <callout arearefs="sp-parameter-definition-xml01-co" id="sp-parameter-definition-xml01">
                        <para>
                            Specifies the name of the SQL parameter.
                            <emphasis>Required</emphasis>.
                        </para>
                    </callout>
	                <callout arearefs="sp-parameter-definition-xml02-co" id="sp-parameter-definition-xml02">
	                    <para>
	                        Specifies the direction of the SQL parameter definition.
	                        Defaults to <code>IN</code>. Valid values are:
	                        <code>IN</code>,
	                        <code>OUT</code> and
	                        <code>INOUT</code>. 
	                        If your procedure is returning ResultSets,
	                        please use the <code>returning-resultset</code> element.
	                        <emphasis>Optional</emphasis>.
	                    </para>
	                </callout>
	                <callout arearefs="sp-parameter-definition-xml03-co" id="sp-parameter-definition-xml03">
	                    <para>
	                        The SQL type used for this SQL parameter definition. Will translate
	                        into the integer value as defined by java.sql.Types. Alternatively
	                        you can provide the integer value as well. If this attribute is
	                        not explicitly set, then it will default to 'VARCHAR'.
	                        <emphasis>Optional</emphasis>.
	                    </para>
	                </callout>
	                <callout arearefs="sp-parameter-definition-xml04-co" id="sp-parameter-definition-xml04">
	                    <para>
	                        The scale of the SQL parameter. Only used for numeric and decimal
	                        parameters.
	                        <emphasis>Optional</emphasis>.
	                    </para>
	                </callout>
				</calloutlist>
			</para>

            <para><emphasis role="bold">poller</emphasis></para>
            <para>
                Allows you to configure a Message Poller if this endpoint is a
                <classname>PollingConsumer</classname>.
                <emphasis>Optional</emphasis>.
            </para>

        </section>

        <section id="sp-defining-parameter-sources">
            <title>Defining Parameter Sources</title>
            <para>
                Parameter Sources govern the techniques of retrieving and mapping the
                Spring Integration Message properties to the relevant Stored Procedure
                input parameters. The Stored Procedure components follow certain rules.
            </para>
            <para>
                By default bean properties of the passed in 
                <interfacename>Message</interfacename> payload will be used as a 
                source for the Stored Procedure's input parameters. In that case a
                <classname>BeanPropertySqlParameterSourceFactory</classname> will 
                be used. This may be sufficient for basic use cases. The following 
                example illustrates that default behavior.
            </para>
            <important>
                Please be aware that for the "automatic" lookup of bean properties
                using the <classname>BeanPropertySqlParameterSourceFactory</classname> 
                to work, your bean properties must be defined in lower case. 
                This is due to the fact that in 
                <classname>org.springframework.jdbc.core.metadata.CallMetaDataContext</classname> 
                (method matchInParameterValuesWithCallParameters()), the retrieved 
                Stored Procedure parameter declarations are converted to lower case.
                
                As a result, if you have camel-case bean properties such as "lastName", 
                the lookup will fail. In that case, please provide an explicit 
                <classname>ProcedureParameter</classname>.
            </important>              
            <para>
                Let's assume we have a payload that consists of a simple bean with
                the following three properties: <emphasis>id</emphasis>, 
                <emphasis>name</emphasis> and <emphasis>description</emphasis>. 
                Furthermore, we have a simplistic Stored Procedure called <emphasis>INSERT_COFFEE</emphasis>
                that accepts three input parameters: 
                <emphasis>id</emphasis>,
                <emphasis>name</emphasis> and
                <emphasis>description</emphasis>. We also use a fully supported 
                database. In that case the following configuration for a Stored
                Procedure Oubound Adapter will be sufficient: 
            </para>
            <programlisting><![CDATA[<int-jdbc:stored-proc-outbound-channel-adapter data-source="dataSource"
                                        channel="insertCoffeeProcedureRequestChannel" 
                                        stored-procedure-name="INSERT_COFFEE"/>]]></programlisting>   
            <para>    
                For more sophisticated options consider passing in one or more 
                <classname>ProcedureParameter</classname>.
            </para>
            <para>    
                If you do provide <classname>ProcedureParameter</classname> explicitly, 
                then as default an <classname>ExpressionEvaluatingSqlParameterSourceFactory</classname>
                will be used for parameter processing in order to enable the full
                power of SpEL expressions.
            </para>
            <para>
                Furthermore, if you need even more control over how parameters are
                retrieved, consider passing in a custom implementation of a 
                <interfacename>SqlParameterSourceFactory</interfacename> using the 
                <code>sql-parameter-source-factory</code> attribute.
            </para>
        </section>
        <section id="stored-procedure-inbound-channel-adapter">
	        <title>Stored Procedure Inbound Channel Adapter</title>

	        <programlisting language="xml"><![CDATA[<int-jdbc:stored-proc-inbound-channel-adapter
                                   channel=""                                   ]]><co id="sp-inbound-xml01-co" linkends="sp-inbound-xml01" /><![CDATA[
                                   stored-procedure-name=""
                                   data-source=""
                                   auto-startup="true"
                                   id=""
                                   ignore-column-meta-data="false"
                                   is-function="false"
                                   max-rows-per-poll=""                         ]]><co id="sp-inbound-xml02-co" linkends="sp-inbound-xml02" /><![CDATA[
                                   skip-undeclared-results=""                   ]]><co id="sp-inbound-xml03-co" linkends="sp-inbound-xml03" /><![CDATA[
    <int:poller/>
    <int-jdbc:sql-parameter-definition name="" direction="IN"
                                               type="STRING"
                                               scale=""/>
    <int-jdbc:parameter name="" type="" value=""/>
    <int-jdbc:parameter name="" expression=""/>
    <int-jdbc:returning-resultset name="" row-mapper="" />
</int-jdbc:stored-proc-inbound-channel-adapter>]]></programlisting>
            <para>
                <calloutlist>
                    <callout arearefs="sp-inbound-xml01-co" id="sp-inbound-xml01">
                        <para>
                            Channel to which polled messages will be sent. If the stored
                            procedure or function does not return any data, the payload
                            of the Message will be Null.
                            <emphasis>Required</emphasis>.
                        </para>
                    </callout>
                    <callout arearefs="sp-inbound-xml02-co" id="sp-inbound-xml02">
                        <para>
                            Limits the number of rows extracted per query. Otherwise
                            all rows are extracted into the outgoing message.
                            <emphasis>Optional</emphasis>.
                        </para>
                    </callout>
                    <callout arearefs="sp-inbound-xml03-co" id="sp-inbound-xml03">
                        <para>
                             If this attribute is set to <code>true</code>, then 
                             all results from a stored procedure call that don't 
                             have a corresponding <classname>SqlOutParameter</classname> 
                             declaration will be bypassed.
                         </para>
                         <para>
                             E.g. Stored Procedures may return an update count value,
                             even though your Stored Procedure only declared a single
                             result parameter. The exact behavior depends on the used
                             database. The value is set on the underlying 
                             <classname>JdbcTemplate</classname>.
                         </para>
                         <para>                   
                             Few developers will probably ever want to process
                             update counts, thus the value defaults to <code>true</code>.
                             <emphasis>Optional</emphasis>.
                         </para>
                    </callout>
              </calloutlist></para>
	    </section>
        <section id="stored-procedure-outbound-channel-adapter">
            <title>Stored Procedure Outbound Channel Adapter</title>

            <programlisting language="xml"><![CDATA[<int-jdbc:stored-proc-outbound-channel-adapter channel=""                       ]]><co id="sp-outbound-xml01-co" linkends="sp-outbound-xml01" /><![CDATA[
                                               stored-procedure-name=""         
                                               data-source=""                   
                                               auto-startup="true"              
                                               id=""                            
                                               ignore-column-meta-data="false"  
                                               order=""                         ]]><co id="sp-outbound-xml02-co" linkends="sp-outbound-xml02" /><![CDATA[
                                               return-value-required="false"    ]]><co id="sp-outbound-xml03-co" linkends="sp-outbound-xml03" /><![CDATA[
                                               sql-parameter-source-factory=""
                                               use-payload-as-parameter-source=""> 
    <int:poller fixed-rate=""/>
    <int-jdbc:sql-parameter-definition name=""/>
    <int-jdbc:parameter name=""/>

</int-jdbc:stored-proc-outbound-channel-adapter>]]></programlisting>
            <para>
                <calloutlist>
                    <callout arearefs="sp-outbound-xml01-co" id="sp-outbound-xml01">
                        <para>
                            The receiving Message Channel of this endpoint.
                            <emphasis>Required</emphasis>.
                        </para>
                    </callout>
                    <callout arearefs="sp-outbound-xml02-co" id="sp-outbound-xml02">
                        <para>
                             Specifies the order for invocation when this endpoint
                             is connected as a subscriber to a channel. This is
                             particularly relevant when that channel is using a
                             <emphasis>failover</emphasis> dispatching strategy.
                             It has no effect when this endpoint itself is a
                             Polling Consumer for a channel with a queue.
                            <emphasis>Optional</emphasis>.
                        </para>
                    </callout>
                    <callout arearefs="sp-outbound-xml03-co" id="sp-outbound-xml03">
                        <para>
                            Indicates whether this procedure's return value
                            should be included.
                            <emphasis>Optional</emphasis>.
                        </para>
                    </callout>
              </calloutlist></para>
        </section>
        <section id="stored-procedure-outbound-gateway">
            <title>Stored Procedure Outbound Gateway</title>

		    <programlisting language="xml"><![CDATA[<int-jdbc:stored-proc-outbound-gateway request-channel=""                       ]]><co id="sp-gateway-xml01-co" linkends="sp-gateway-xml01" /><![CDATA[
                                       stored-procedure-name=""                 
                                       data-source=""                           
                                   auto-startup="true"                         
                                   id=""                                        
                                   ignore-column-meta-data="false"              
                                   is-function="false"                          
                                   order=""                                     
                                   reply-channel=""                             ]]><co id="sp-gateway-xml02-co" linkends="sp-gateway-xml02" /><![CDATA[
                                   reply-timeout=""                             ]]><co id="sp-gateway-xml03-co" linkends="sp-gateway-xml03" /><![CDATA[
                                   return-value-required="false"                ]]><co id="sp-gateway-xml04-co" linkends="sp-gateway-xml04" /><![CDATA[
                                   skip-undeclared-results=""                   ]]><co id="sp-gateway-xml05-co" linkends="sp-gateway-xml05" /><![CDATA[
                                   sql-parameter-source-factory=""
                                   use-payload-as-parameter-source="">             
<int-jdbc:sql-parameter-definition name="" direction="IN"
                                   type=""
                                   scale="10"/>
<int-jdbc:sql-parameter-definition name=""/>
<int-jdbc:parameter name="" type="" value=""/>
<int-jdbc:parameter name="" expression=""/>
<int-jdbc:returning-resultset name="" row-mapper="" />]]></programlisting>

		    <para>
		        <calloutlist>
			        <callout arearefs="sp-gateway-xml01-co" id="sp-gateway-xml01">
			            <para>
			                The receiving Message Channel of this endpoint.
			                <emphasis>Required</emphasis>.
			            </para>
			        </callout>
		            <callout arearefs="sp-gateway-xml02-co" id="sp-gateway-xml02">
		                <para>
		                    Message Channel to which replies should be sent,
                            after receiving the database response.
                            <emphasis>Optional</emphasis>.
                        </para>
		            </callout>
		            <callout arearefs="sp-gateway-xml03-co" id="sp-gateway-xml03">
		                <para>
		                    Allows you to specify how long this gateway will wait 
		                    for the reply message to be sent successfully before 
		                    throwing an exception. Keep in mind that when sending 
		                    to a <classname>DirectChannel</classname>, the invocation 
		                    will occur in the sender's thread so the failing of the 
		                    send operation may be caused by other components further
		                    downstream. 
		                    
		                    By default the Gateway will wait indefinitely. The 
		                    value is specified in milliseconds.
		                    <emphasis>Optional</emphasis>.
		                </para>
		            </callout>
		            <callout arearefs="sp-gateway-xml04-co" id="sp-gateway-xml04">
		                <para>
		                    Indicates whether this procedure's return value
                            should be included.
                            <emphasis>Optional</emphasis>.
                        </para>
		            </callout>
                    <callout arearefs="sp-gateway-xml05-co" id="sp-gateway-xml05">
                        <para>
                             If the <code>skip-undeclared-results</code> attribute 
                             is set to <code>true</code>, then all results from 
                             a stored procedure call that don't have a 
                             corresponding <classname>SqlOutParameter</classname> 
                             declaration will be bypassed.
                         </para>
                         <para>
                             E.g. Stored Procedures may return an update count value,
                             even though your Stored Procedure only declared a single
                             result parameter. The exact behavior depends on the used
                             database. The value is set on the underlying 
                             <classname>JdbcTemplate</classname>.
                         </para>
                         <para>                   
                             Few developers will probably ever want to process
                             update counts, thus the value defaults to <code>true</code>.
                             <emphasis>Optional</emphasis>.
                         </para>
                    </callout>                     		            
		      </calloutlist>
		  </para>
        </section>
        <section id="sp-examples">
            <title>Examples</title>
            <para>
                In the following two examples we call <ulink url="http://db.apache.org/derby/">Apache Derby</ulink>
                Stored Procedures. The first procedure will call a Stored Procedure that
                returns a <classname>ResultSet</classname>, and using a <interfacename>RowMapper</interfacename>
                the data is converted into a domain object, which then becomes the
                Spring Integration message payload.
            </para>
            <para>
                In the second sample we call a Stored Procedure that uses 
                Output Parameters instead, in order to return data.
            </para>
            <note>
                <para>
                    Please have a look at the <emphasis>Spring Integration Samples</emphasis>
                    project, located at 
                    <ulink url="https://github.com/SpringSource/spring-integration-samples"/>
                </para>
                <para>
                    The project contains the Apache Derby example referenced
                    here, as well as instruction on how to run it. The 
                    <emphasis>Spring Integration Samples</emphasis> project also 
                    provides an  
                    <ulink url="https://github.com/SpringSource/spring-integration-samples/tree/master/intermediate/stored-procedures-oracle">example</ulink> 
                    using Oracle Stored Procedures.
                </para>
            </note>
            <para>
                In the first example, we call a Stored Procedure named 
                <emphasis>FIND_ALL_COFFEE_BEVERAGES</emphasis> that does not 
                define any input parameters but which returns a <classname>ResultSet</classname>. 
            </para>
            <para>
                In Apache Derby, Stored Procedures are implemented using Java. Here 
                is the method signature followed by the corresponding Sql:
            </para>  
            <programlisting language="java"><![CDATA[public static void findAllCoffeeBeverages(ResultSet[] coffeeBeverages)
            throws SQLException {
    ...
}]]></programlisting>

	        <programlisting language="xml"><![CDATA[CREATE PROCEDURE FIND_ALL_COFFEE_BEVERAGES() \
PARAMETER STYLE JAVA LANGUAGE JAVA MODIFIES SQL DATA DYNAMIC RESULT SETS 1 \
EXTERNAL NAME 'org.springframework.integration.jdbc.storedproc.derby.DerbyStoredProcedures.findAllCoffeeBeverages';
]]></programlisting>

            <para>
                In Spring Integration, you can now call this Stored Procedure using 
                e.g. a <code>stored-proc-outbound-gateway</code>
            </para>
            <programlisting language="xml"><![CDATA[<int-jdbc:stored-proc-outbound-gateway id="outbound-gateway-storedproc-find-all" 
                                       data-source="dataSource"
                                       request-channel="findAllProcedureRequestChannel" 
                                       expect-single-result="true"
                                       stored-procedure-name="FIND_ALL_COFFEE_BEVERAGES">
    <int-jdbc:returning-resultset name="coffeeBeverages" 
        row-mapper="org.springframework.integration.support.CoffeBeverageMapper"/>
    </int-jdbc:stored-proc-outbound-gateway>]]></programlisting>
    
            <para>
                In the second example, we call a Stored Procedure named 
                <emphasis>FIND_COFFEE</emphasis> that has one input parameter. Instead
                of returning a ResultSet, an output parameter is used:
            </para>
            
            <programlisting language="java"><![CDATA[public static void findCoffee(int coffeeId, String[] coffeeDescription)
            throws SQLException {
    ...
}]]></programlisting>

		<programlisting><![CDATA[CREATE PROCEDURE FIND_COFFEE(IN ID INTEGER, OUT COFFEE_DESCRIPTION VARCHAR(200)) \
PARAMETER STYLE JAVA LANGUAGE JAVA EXTERNAL NAME \
'org.springframework.integration.jdbc.storedproc.derby.DerbyStoredProcedures.findCoffee';]]></programlisting>

        <para>
            In Spring Integration, you can now call this Stored Procedure using 
            e.g. a <code>stored-proc-outbound-gateway</code>
        </para>  
        <programlisting language="xml"><![CDATA[<int-jdbc:stored-proc-outbound-gateway id="outbound-gateway-storedproc-find-coffee" 
                                       data-source="dataSource"
                                       request-channel="findCoffeeProcedureRequestChannel" 
                                       skip-undeclared-results="true"
                                       stored-procedure-name="FIND_COFFEE"
                                       expect-single-result="true">
    <int-jdbc:parameter name="ID" expression="payload" />
</int-jdbc:stored-proc-outbound-gateway>]]></programlisting>
            
        </section>
    </section>
</chapter>
