DBLookup Mediator

The dblookup mediator is capable of executing an arbitrary SQL select statement, and then set some resulting values as local message properties on the message context. The DB connection used maybe looked up from an external DataSource or specified in-line, in which case an Apache DBCP connection pool is established and used.

In-lined Data Source

  • driver - Database driver class name
  • url - Database URL
  • user - User name for access Database
  • password Password for access Database

This new Datasource is based on Apache DBCP connection pools. This connection pool support the following configuration properties:

  • autocommit = true | false
  • isolation = Connection.TRANSACTION_NONE | Connection.TRANSACTION_READ_COMMITTED | Connection.TRANSACTION_READ_UNCOMMITTED | Connection.TRANSACTION_REPEATABLE_READ | Connection.TRANSACTION_SERIALIZABLE
  • initialsize = int
  • maxactive = int
  • maxidle = int
  • maxopenstatements = int
  • maxwait = long
  • minidle = int
  • poolstatements = true | false
  • testonborrow = true | false
  • testonreturn = true | false
  • testwhileidle = true | false
  • validationquery = String

External Data Source

  • dsName - The name of the Datasource to be looked up
  • icClass - Initial context factory class. The corresponding java environment property is java.naming.factory.initial
  • url - The naming service provider url . The corresponding java environment property is java.naming.provider.url
  • user - This is corresponding to the java environment property java.naming.security.principal
  • password - This is corresponding to the java environment property This is corresponding to the java environment property java.naming.security.principal

SQL Statements

  • sql - One or more SQL Statements
  • parameter - Specify how the values of parameter in the SQL would be calculated. A value can be static literal text and can be calculated at runtime based on the given expression.
  • result - Specify how to deal with rerun result from Database query execution.

More than one statement may be specified, and the SQL statement may specify parameters which could be specified as values or XPath expressions. The types of parameters could be any valid SQL types. Only the first row of a result set will be considered and any others are ignored. The single <result> element contains the 'name' and the 'column' attributes. The 'name' attribute defines the name under which the result is stored in the Synapse message context, and the column attribute specifies a column number or name .

Syntax

<dblookup>
   <connection>
     <pool>
      (
       <driver/>
       <url/>
       <user/>
       <password/>
     |
       <dsName/>
       <icClass/>
       <url/>
       <user/>
       <password/>
     )
       <property name="name" value="value"/>*
     </pool>
   </connection>
   <statement>
     <sql>select something from table where something_else = ?</sql>
     <parameter [value="" | expression=""] type="CHAR|VARCHAR|LONGVARCHAR|NUMERIC|DECIMAL|BIT|TINYINT|SMALLINT|INTEGER|BIGINT|REAL|FLOAT|DOUBLE|DATE|TIME|TIMESTAMP"/>*
     <result name="string" column="int|string"/>*
   </statement>+
</dblookup>

UI Configuration

Figure 1: DBLookup mediator options

DBLookup mediator options field descriptions,

  • Connection Information - Specify whether the connection is taken from a connection pool or from a data source
  • Driver - Database driver
  • Url - JDBC URL of the database that data will be looked up.
  • User - Username that's used to connect to the database.
  • Password - Password used to connect to the database.

Data sources

Carbon Datasources

If existing data source is choosen, clicking "Load Data Sources" will give the available datasource. Then a data source can be selected.

Figure 2: Carbon Datasources

External Datasources

Like in Pool connection data source configuration properties should be specified.

Figure 3: External Datasources

Adding properties

Figure 4: Adding a property

Property field descriptions,

  • autocommit = true | false
  • isolation = Connection.TRANSACTION_NONE | Connection.TRANSACTION_READ_COMMITTED | Connection.TRANSACTION_READ_UNCOMMITTED | Connection.TRANSACTION_REPEATABLE_READ | Connection.TRANSACTION_SERIALIZABLE
  • initialsize = int
  • maxactive = int
  • maxidle = int
  • maxopenstatements = int
  • maxwait = long
  • minidle = int
  • poolstatements = true | false
  • testonborrow = true | false
  • testwhileidle = true | false
  • validationquery = String

Adding SQL statements

  1. Click Add Statement link shown in figure 2 and it'll open up the screen where you could enter SQL statement option. This is shown in figure 3.
  2. More than one statement may be specified.
  • The SQL statement may specify parameters which could be specified as values or XPath expressions.
  • The types of parameters could be any valid SQL types. Only the first row of a result set will be considered and any others are ignored.

Figure 5: Adding SQL statements

Example


<dblookup xmlns="http://ws.apache.org/ns/synapse">
    <connection>
        <pool>
            <driver>org.apache.derby.jdbc.ClientDriver</driver>
            <url>jdbc:derby://localhost:1527/esbdb;create=false</url>
            <user>esb</user>
            <password>esb</password>
        </pool>
    </connection>
    <statement>
        <sql>select * from company where name =?</sql>
        <parameter expression="//m0:getQuote/m0:request/m0:symbol"
                   xmlns:m0="http://services.samples/xsd" type="VARCHAR"/>
        <result name="company_id" column="id"/>
    </statement>
</dblookup>

In this example, when a message arrives at dblookup mediator, it opens a connection to the database and executes the SQL query. The SQL query use '?' character for attributes that will be filled at runtime. The parameters define how to calculate the value of those attributes at runtime. In this sample a dblookup mediator has been used to extract 'id' of the company from the company database using the symbol which is evaluated using an xpath against the SOAP envelope.