DB Lookup 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.

DBLookup mediator options

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

Properties can be added by 'Add Property' link.

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

SQL Statements can by added by clicking the 'Add Statement' link.
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: Add Statements