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.
Adding a DBLookup mediator
Go to Advanced
and click DBLookup
to add a DBLookup mediator. Figure 1
illustrate this,
Figure 1: Adding a DBLookup mediator
DBLookup mediator options
Clicking on the DBLookup mediator created as in figure 1 will open up
DBLookup mediator specific options. Figure 2 illustrate this,
Figure 2: 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.
Adding properties
Figure 3 shows the screen after clicking Add Property,
Figure 3: 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
- Click Add Statement link shown in figure 3 and it'll open
up
the screen where you could enter SQL statement option. This is shown in
figure 4.
- 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 4: Adding SQL statements