JDBC Query

Valve performing JDBC queries

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

Valve name: JdbcQuery | JDBCQuery

Properties

Name	Description	Default value	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.

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.

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.

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).

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.

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.

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
    } ]
  }
}

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