SqlAttributesFilter
|
This filter uses synchronous architecture. Accessing the filter Consider the performance impact of this filter, especially for deployments with a small number of gateway units (therefore, a small number of executing threads) and a long execution time for the JDBC call. |
Executes a SQL query through a prepared statement and exposes its first result.
Parameters in the prepared statement are derived from expressions. The query
result is exposed in an object whose location is specified by the target
expression. If the query yields no result, then the resulting object is empty.
The execution of the query is performed lazily; it does not occur until the first attempt to access a value in the target. This defers the overhead of connection pool, network and database query processing until a value is first required. This also means that the parameters expressions is not evaluated until the object is first accessed.
Usage
{
"name": string,
"type": "SqlAttributesFilter",
"config": {
"dataSource": JdbcDataSource reference,
"preparedStatement": configuration expression<string>,
"parameters": [ runtime expression<string>, ... ],
"target": lvalue-expression
}
}Properties
"dataSource": JdbcDataSource reference, required-
dataSourceas a JNDI lookup name is deprecated; usedataSourceas a JdbcDataSource configuration object instead. For more information, refer to the Deprecated section of the Release Notes.The JdbcDataSource to use for connections. Configure JdbcDataSource as described in JdbcDataSource.
"preparedStatement": configuration expression<string>, required-
The parameterized SQL query to execute, with
?parameter placeholders. "parameters": array of runtime expressions<strings>, optional-
The parameters to evaluate and include in the execution of the prepared statement.
See also Expressions.
"target": <lvalue-expression>, required-
Expression that yields the target object containing the query results. For example,
${target.sql.queryresult}.Access to
targettriggers a call to the database that blocks the executing thread until the database responds.See also Expressions.
Example
Using the user’s session ID from a cookie, query the database to find the user logged in and set the profile attributes in the attributes context:
{
"name": "SqlAttributesFilter",
"type": "SqlAttributesFilter",
"config": {
"target": "${attributes.sql}",
"dataSource": "java:comp/env/jdbc/mysql",
"preparedStatement": "SELECT f.value AS 'first', l.value AS 'last', u.mail AS 'email', GROUP_CONCAT(CAST(r.rid AS CHAR)) AS 'roles' FROM sessions s INNER JOIN users u ON ( u.uid = s.uid AND u.status = 1 ) LEFT OUTER JOIN profile_values f ON ( f.uid = u.uid AND f.fid = 1 ) LEFT OUTER JOIN profile_values l ON ( l.uid = u.uid AND l.fid = 2 ) LEFT OUTER JOIN users_roles r ON ( r.uid = u.uid ) WHERE (s.sid = ? AND s.uid <> 0) GROUP BY s.sid;",
"parameters": [ "${request.cookies[keyMatch(request.cookies,'JSESSION1234')][0].value}" ]
}
}Lines are folded for readability in this example. In your JSON, keep the values
for "preparedStatement" and "parameters" on one line.