PingGateway 2024.11

SqlAttributesFilter

This filter uses synchronous architecture by default. Accessing the filter target triggers a call to the database that blocks the executing thread until the database responds.

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 an SQL query through a prepared statement and collects the first result. Parameters in the prepared statement are derived from expressions.

PingGateway populates the SqlAttributesContext and the optional target (deprecated) with key-value pairs from the query. If no matching row is found, the context and optional target are empty.

When the deprecated target setting is configured, PingGateway executes the query lazily on the first attempt to access its results. This defers the overhead of connection pool, network, and database query processing until a value is first required. PingGateway doesn’t evaluate the parameters expression until the results are first accessed. PingGateway executes the query synchronously, potentially impacting performance.

When target isn’t set, PingGateway executes the query asynchronously when calling this filter. In this case, place this filter immediately before the entity reading the data from the context.

Usage

{
  "name": string,
  "type": "SqlAttributesFilter",
   "config": {
     "dataSource": JdbcDataSource reference,
     "preparedStatement": configuration expression<string>,
     "parameters": [ runtime expression<string>, ... ],
     "target": lvalue-expression, // deprecated
     "executorService": ScheduledExectutorService reference
   }
}

Properties

"dataSource": JdbcDataSource reference, required

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.

Learn more in Expressions.

"target": <lvalue-expression>, optional
This property is deprecated. Use the SqlAttributesContext instead. For more information, refer to the Deprecated section of the Release Notes.

Expression that yields the target object containing the query results; for example, ${target.sql.queryresult}.

Access to target triggers a call to the database that blocks the executing thread until the database responds.

Also refer to Expressions.

"executorService": ScheduledExecutorService reference, optional

A ScheduledExecutorService to execute the query.

Default: The default ScheduledExecutorService in the heap

Example

Using the user’s session ID from a cookie, query the database to find the user and set the profile attributes in the SqlAttributesContext:

{
        "name": "SqlAttributesFilter",
        "type": "SqlAttributesFilter",
        "config": {
              "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.

More information

org.forgerock.openig.sql.SqlAttributesFilter