SqlAttributesFilter
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": string,
"parameters": [ runtime expression<string>, ... ],
"target": lvalue-expression
}
}Properties
"dataSource": JdbcDataSource reference, requiredThe JdbcDataSource to use for connections. Configure JdbcDataSource as described in "JdbcDataSource".
"preparedStatement": string, requiredThe parameterized SQL query to execute, with
?parameter placeholders."parameters": array of runtime expressions<string>, optionalThe parameters to evaluate and include in the execution of the prepared statement.
See also "Expressions".
"target": lvalue-expression, requiredExpression that yields the target object that will contain the query results.
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.