JDBC Query Policy

Phases

onRequest	onResponse	onMessageRequest	onMessageResponse
X	X	Coming soon…​	Coming soon…​

Description

You can use the gravitee-policy-jdbc-query policy to query a remote database and return a (single or multiple) record set.

Note

It should go without saying that database queries are expensive operations and heavily dependent on the network connection between the API Gateway and the database service. For the best experience, implement an API Gateway close to the database service, and only deploy APIs to those API Gateways (that are close to the database service).

Note	Before using this policy (and in addition to the above note), you may want to think of other options such as storing typical responses as API Properties or using the Gravitee Dictionary feature.

The Connection String (jdbcConnectionString) supports the standard JDBC connection syntax, e.g.: jdbc:mysql://hostname:3306/database

If your query returns no results, then the policy does nothing except adding the X-JDBC: no-results header.

If your query has a SQL syntax error, then the policy also does nothing except adding X-JDBC: Error processing JDBC - SQL Syntax error header. Further error information can be discovered in the logs, or by enabling the "Debug to Headers" toggle.

A jdbcResponse object will be created from the results of your SQL statement and the header X-JDBC: success will be added. Use the jdbcResponse object to extract desired field values into Context Variables.

Important: if no Variables are configured in the Policy, then the full response will be set in the 'jdbcResponse' context attribute.

TODO: For better performance, a separate JDBC Resource should be created so that this Policy can re-use existing JDBC connections (rather than having to create for every request).

Example queries:

SELECT * FROM some_table WHERE id=1

SELECT firstName, lastName FROM some_table WHERE id=1

SELECT firstName, lastName, id, some_column FROM some_table WHERE id={#request.headers['myUserId']}

The jdbcResponse object includes a content array (regardless of the number of records retrieved). Each record is an item in the array. Each field (or column) specified in your SELECT statement is a key/value pair.

Example jdbcResponse object:

{
  "content": [
	  {
		"id": 1,
		"firstName": "Fred",
		"lastName": "Durst",
		"some_column": "some_text1"
	  },
	  {
		"id": 2,
		"firstName": "Serj",
		"lastName": "Tankian",
		"some_column": "some_text2"
	  }
  ]
}

For debugging purposes, you can enable Debug to Headers (jdbcDebugHeaders) and either the full record set results or SQL Exception will be added to the X-JDBC-DEBUG-RESPONSE header.

Included JDBC Drivers

MySQL Driver	PostgreSQL Driver	Microsoft SQL Server Driver	Oracle Driver
8.0.33+	42.7.3+	13.2.0.jre11	23.26.0.0.0

Configuration

You can configure the policy with the following options:

Property	Required	Description	Type	Default
jdbcConnectionString	X	Specify the JDBC connection string. (Supports EL)	string
jdbcConnectionUsername	X	Specify the JDBC connection username. (Supports EL)	string
jdbcConnectionPassword	X	Specify the JDBC connection password. (Supports EL)	string
jdbcQuery	X	Specify the SQL query string. (Supports EL)	string
variables		The variables to set in the execution context when a valid recordset is returned by your SQL query. (Supports EL)	List of variables	-
jdbcHeaders		If enabled, Gravitee will populate the X-JDBC header with the status/outcome of your SQL statement	boolean	true
jdbcDebugHeaders		If enabled, Gravitee will populate the X-JDBC-DEBUG-RESPONSE header with the full SQL response/recordset	boolean	false

Example configuration:

{
    "policy": "gravitee-policy-jdbc-query",
    "configuration": {
        "jdbcConnectionString": "jdbc:mysql://db4free.net:3306/dbtest_database",
        "jdbcConnectionUsername": "dbtest_user",
        "jdbcConnectionPassword": "dbtest_pass",
        "jdbcQuery": "SELECT firstName, lastName FROM some_table WHERE id={#request.headers['myUserId']}",
        "variables": [
        	{
        		"name": "FirstName",
        		"value": "{#jsonPath(#jdbcResponse.content[0], '$.firstName')}"
        	}
        ]
    }
}