JDBC Query

Introduction

Valve for performing database (RDBMS) queries using JDBC.

Supports any JDBC compatible statements (SELECT, INSERT, UPDATE, DELETE, etc.) and the use of query parameters.

Rows returned by the statement are added/merged to the current flows item set using a configurable identifier. Native JSON types are preserved other types are converted to strings.

If a statement just modifies rows in the DB (i.e. INSERT, UPDATE, DELETE) item set will not be modified.

If a statement does neither return or modify rows, valve will fail ("No data") unless "allow_no_data": true is set.

Prerequisites

Before using this valve the JdbcClient module must be configured and deployed.

Configuration

Note

Valve name: JdbcQuery | JDBCQuery

Properties

Name	Description	Default	Mandatory	Expanded
destination	JdbcClient instance to use.	"default"
query	The JDBC query/statement.
row_id	Name of column containing the row identifier. Value of this column is used as item id when rows are added/merged to item set.	"id"
query_params	Query parameters descriptor (See below)
allow_no_data	Flag for allowing no data queries. By default this valve fails if a query returns/affects no rows. Use this flag to turn off this behaviour.	false

Example

{
  "name" : "JdbcQuery",
  "config" : {
    "destination" : "default",
    "query" : "SELECT * FROM users WHERE username=?",
    "row_id" : "username",
    "query_params" : "username"
  }
}

Query parameters

Query parameters are represented as ? in the query:

SELECT * FROM users WHERE username=? AND type=?

Before the query is executed each parameter is replaced with an actual value based on the query parameter descriptor.

The query parameter description can be either a simple string containing a comma separated list of parameters, an array of strings containing parameters or an array of parameter objects.

Caution

Elements of the descriptor must match the order and the number of parameters in the query.

When simple strings are used the parameters are assumed to be pipe input parameter names. Values are extracted from the request and added to the statement as is and in the order the params are defined.

{
  "query_params": "username"
}

{
  "query_params": ["id", "username", "created"]
}

When parameter objects are used, parameters can be expanded and type converted. By default parameters are not expanded ("expand" : false) and uses the supplied type/no conversion ("type": "NONE").

Type conversion are used for non-native JSON types that are encoded into strings.

Properties

Name	Description
value	Parameter value. This could be the name of or path to a pipe request parameter containing the actual value or an expansion expression evaluating to the actual value. (Mandatory)
expand	Flag turning on property expansion on value. (Default: false)
type	(Default: "NONE")

Example

{
  "query_params": [
    {
        "value" : "username",
        "expand" : false,
        "type": "NONE"
    },
    {
        "value" : "created",
        "expand" : false,
        "type": "INSTANT"
    }
  ]
}

Note: that expansion requires an expansion expression and that expansion always produces strings.

Examples

Examples below use the following table:

CREATE TABLE users (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    created TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (id)
)

A simple SELECT with a single parameter

Valve with statement to select a user from the users table.

{
    "name" : "JdbcQuery",
    "config" : {
        "destination" : "default",
        "query" : "SELECT * FROM users WHERE id=?",
        "row_id" : "username",
        "query_params" : [ {
            "value" : "id",
            "expand" : false
        } ]
    }
}

A SELECT using query parameter type conversion

Valve with statement to select all users created in a specific time interval.

Since JSON doesn't support instants, type conversion must be used to convert the instant string value to an actual Instant object (that can be converted to a JDBC TIMESTAMP by the JdbcClient).

{
  "name" : "JdbcQuery",
  "config" : {
    "destination" : "default",
    "query" : "SELECT * FROM users WHERE created BETWEEN ? AND ?",
    "row_id" : "username",
    "query_params" : [ {
      "value" : "not_before",
      "expand" : false,
      "type" : "INSTANT"
    }, {
      "value" : "${now}",
      "expand" : true,
      "type" : "INSTANT"
    } ]
  }
}

An authentication SELECT with username and password

Valve with statement to authenticate a user using username and password.

This is an example of how to verify that a user exists and that the user password is correct in a single statement not returning the stored password (like the LDAP bind operation). If user doesn't exist or an incorrect password is used, statement will return an empty result which should be treated as an error by the pipe. If user exist and password is correct, one row/item is returned (success).

Example also demonstrates the use of expansion for query params.

Note

Expansion is not necessary for request parameters since query params defaults to request parameters when expansion is off (default). Skipping expansion also leads to better performance. Use expansion to access values in session or item.

Caution

To detect invalid username/password combinations allow_no_data must be turned on and pipe must validate item set (no row/item if incorrect username or password is supplied).

Danger

Never store passwords in clear text.

{
    "name" : "JdbcQuery",
    "config" : {
      "destination" : "default",
      "query" : "SELECT id,username,lastLogin FROM users WHERE username=? AND password=?",
      "query_params" : [ {
        "value" : "username",
        "expand" : false
      }, {
        "value" : "${request.password}",
        "expand" : true
      } ],
      "row_id": "username",
      "allow_no_data" : true
    }
}

An INSERT

Valve with statement to insert a new user into the users table.

Danger

Never store passwords in clear text.

{
  "name" : "JdbcQuery",
  "config" : {
    "destination" : "default",
    "query" : "INSERT INTO users (id,username,password,created) VALUES (?,?,?,?)",
    "query_params" : [ {
      "value" : "id",
      "expand" : false
    }, {
      "value" : "username",
      "expand" : false
    }, {
      "value" : "password",
      "expand" : false
    }, {
      "value" : "created",
      "expand" : false,
      "type" : "INSTANT"
    } ]
  }
}

An UPDATE

Valve with statement to update the password of a user in the users table.

Danger

Never store passwords in clear text.

{
  "name" : "JdbcQuery",
  "config" : {
    "destination" : "default",
    "query" : "UPDATE users SET password=? WHERE id=?",
    "query_params" : [ {
      "value" : "password",
      "expand" : false
    }, {
      "value" : "id",
      "expand" : false
    } ]
  }
}

Audit

Every write publishes a raw change to the platform audit trail automatically — there is no audit flag to configure on the valve or the JdbcClient anymore. Whether anything is actually recorded depends on whether an Audit module is deployed to consume it.

The valve parses the statement with JSQLParser to determine the table (object_type), the affected row (object_id, taken from an unambiguous equality predicate on row_id in the WHERE clause, or from a written column matching row_id for INSERT), and the written columns (field/after_value). SELECT statements are never audited, and a statement matching zero rows publishes nothing.

Caution

The audit parser uses JSQLParser to analyse statements. MERGE statements are not supported and publish nothing. Use INSERT, UPDATE, and DELETE instead.

Note

Column values matching a sensitive keyword (password, pwd, secret, and similar) are masked as ***** before publishing.

INSERT

Valve config:

{
  "name": "JdbcQuery",
  "config": {
    "query": "INSERT INTO users (id, name, email) VALUES ('demo', 'Demo User', '[email protected]')",
    "row_id": "id",
    "allow_no_data": true
  }
}

Published changes (object_type users, package_type UsersCreated, object_id 'demo' — taken from the written id column since there is no WHERE clause on INSERT):

[
  { "field": "id",    "after_value": "'demo'" },
  { "field": "name",  "after_value": "'Demo User'" },
  { "field": "email", "after_value": "'[email protected]'" }
]

UPDATE

Valve config:

{
  "name": "JdbcQuery",
  "config": {
    "query": "UPDATE users SET email='[email protected]' WHERE id='demo'",
    "row_id": "id",
    "allow_no_data": true
  }
}

Published change (object_type users, package_type UsersUpdated, object_id 'demo'):

[
  { "field": "email", "after_value": "'[email protected]'" }
]

DELETE

Valve config:

{
  "name": "JdbcQuery",
  "config": {
    "query": "DELETE FROM users WHERE id='demo'",
    "row_id": "id",
    "allow_no_data": true
  }
}

Published change (object_type users, package_type UsersDeleted, object_id 'demo' — DELETE has no columns of its own, so a single sentinel change is published for the whole row):

[
  { "field": "row", "after_value": "'demo'" }
]

object_id is only published when it can be identified unambiguously — an equality predicate on row_id, with no OR anywhere in the WHERE clause. A statement whose row_id cannot be identified this way, or an INSERT that does not write the row_id column, publishes nothing for that write, since every change must have an owning object.

Only the after-value of each write is published — these valves do not capture a before-value. See Audit for how published changes are grouped into events and change packages, and for the built-in UI and REST API to view them.

Troubleshooting

Invalid query parameter type configuration (Instant)

Since Instants are not natively supported in JSON they are handled as ISO 8601 date time strings. These strings cannot be handled by JDBC but need to be converted back to Instants that can be converted to JDBC TIMESTAMPs.

A missing or invalid query parameter type will cause the following pipe failure:

Pipe failure: JDBC query failed: data exception: invalid datetime format

Invalid Instant format

Type conversion from String to Instant only support ISO 8601 ("2011-12-03T10:15:30Z"). Other formats will cause the following pipe failure::

Pipe failure: Text '2022-08-10 09:05:19.285' could not be parsed at index 10