Database Testing with JDBC

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page was written against Apache JMeter 5.6.3 (latest release on 2026-10-14; it runs on Java 8 and later, Java 17 or later is recommended), with the differences planned for JMeter 6.0 noted where they matter. JMeter 6.0 is not released: its planned changes come from xdocs/changes.xml on the master branch, need Java 17 or later (Java 21 recommended) and are described as planned, not as shipped behaviour. The examples use the PostgreSQL JDBC driver (pgjdbc) 42.7.14, the latest release on Maven Central on the same date, against a local PostgreSQL server. The books on the market target JMeter 2.11 to 3.0; the database elements changed a lot since then (the connection pool moved from Excalibur to Apache Commons DBCP 2 in 3.0, and most of the pool and result-set options described below arrived in 3.3 and the 5.x line), so every field, property and default on this page is taken from the official component reference, the database tutorial, the properties reference and, where the documentation is silent, the 5.6.3 source code, which is said explicitly.

JMeter’s HTTP samplers test a database indirectly: every Bookshelf search runs SQL somewhere behind the API. The JDBC elements let a test plan talk to the database directly, as the application does. That is useful in two quite different ways: to load-test the database itself (how many searches per second can PostgreSQL serve, does the new index help, where does the lock contention start), and, far more often, as a utility inside an API load test (seeding and cleaning test data, reading valid ids to feed the HTTP requests, checking that a loan really reached the table). This page covers both, starting with when each is appropriate.

Testing the database directly or through the API

A JDBC Request measures the time from asking the connection pool for a connection to the end of reading the result set. It does not see the API’s connection pool, its object-relational mapping, its caches, its serialisation, its authentication or the network hop from the user. A database test answers "how does this SQL behave under concurrency", not "how fast is Bookshelf for a reader".

flowchart LR subgraph API_test [Load test through the API] J1[JMeter HTTP Request] --> A[Bookshelf API] A --> P[API connection pool] P --> D1[(PostgreSQL)] end subgraph DB_test [Load test of the database] J2[JMeter JDBC Request] --> JP[JMeter JDBC pool] JP --> D2[(PostgreSQL)] end
Question Through the API (HTTP samplers) Directly (JDBC samplers)

What is measured

What a user experiences: application code, the application’s pool, ORM, caches, serialisation, network.

The SQL statements alone, plus the JDBC driver and JMeter’s pool.

Realism of the load

High, if the workload model is right: the API decides which queries run, how many connections are used and how transactions are scoped.

Only as realistic as the SQL you copy from the application and the concurrency you choose. It is easy to send queries the application never sends, or far more concurrent connections than its pool allows.

Best for

Acceptance criteria, capacity of the whole service, regressions between builds.

Comparing indexes or query rewrites, sizing the database server, reproducing a slow query or a lock contention seen in an API test, testing a database shared by several applications.

Isolating bottlenecks

Harder: a slow search could be the API, the pool or the database.

Easy: only the database and the driver are involved.

Risks

Low, as long as the target is a test environment.

Writing statements bypass every application rule; never aim them at a production database, and use a disposable copy of the data.

Recommended split for Bookshelf. The main plan, bookshelf-load.jmx, tests through the API and uses JDBC only for test-data utilities (later sections). A separate plan, bookshelf-db.jmx, replays the two hottest statements found in the API test (the book search and the loan insert) against a disposable copy of the database, to compare indexes and find the database’s own limit. Finding those statements is a database task: PostgreSQL’s statistics views and slow query logging are covered in Diagnosing and Fixing Slow Queries.

Match the application’s concurrency. The database never sees the API’s 50 virtual users; it sees the API’s pool, for example ten connections. A JDBC test with 50 threads and one connection each (the JMeter default) puts five times more concurrent sessions on the server than the application ever could. Use the application’s pool size as the number of JMeter threads when the goal is to reproduce what the application does, and go beyond it only when the goal is to find the database’s limit.

The Bookshelf database

The examples use the Bookshelf PostgreSQL database that the Bookshelf API runs on, published by the local Docker Compose stack on localhost:5432 with a database and a role named bookshelf. Only two tables matter here. The schema below is the one this guide assumes; adapt the column names to your own database.

CREATE TABLE books (
    id               BIGSERIAL    PRIMARY KEY,
    isbn             VARCHAR(17)  NOT NULL UNIQUE,
    title            VARCHAR(300) NOT NULL,
    author           VARCHAR(200) NOT NULL,
    available_copies INTEGER      NOT NULL DEFAULT 1
);

CREATE TABLE loans (
    id         BIGSERIAL    PRIMARY KEY,
    book_id    BIGINT       NOT NULL REFERENCES books (id),
    username   VARCHAR(100) NOT NULL,
    status     VARCHAR(20)  NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE INDEX loans_username_idx ON loans (username);

-- Used by the callable-statement example
CREATE FUNCTION count_active_loans(p_username VARCHAR) RETURNS INTEGER
    LANGUAGE sql STABLE
    AS $$ SELECT count(*)::INTEGER FROM loans WHERE username = p_username AND status = 'ACTIVE' $$;

The test users are the reader0001-style accounts of users.csv (see Parameterization & Test Data), so every row a test creates can be found and removed by its username. Connection settings are passed as run properties, like threads and rampup elsewhere in this guide: db_host, db_port, db_name, db_user and db_password. The password has a placeholder value and is never written into the .jmx file.

Source: CREATE TABLE (PostgreSQL documentation)

Adding the JDBC driver

JMeter does not ship any JDBC driver. The driver jar of your database must be on JMeter’s classpath, and the easiest place for it is the lib directory (not lib/ext, which is for JMeter components and plugins).

  1. Download the driver from its vendor. For PostgreSQL, the pgjdbc jar is published on Maven Central with a SHA-1 checksum next to it.

  2. Copy it to $JMETER_HOME/lib.

  3. Restart JMeter. Jars are only scanned at start-up.

  4. In the JDBC Connection Configuration, pick or type the driver class (org.postgresql.Driver).

cd "$JMETER_HOME/lib"
curl -fLO https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.14/postgresql-42.7.14.jar
curl -fL https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.14/postgresql-42.7.14.jar.sha1; echo
shasum -a 1 postgresql-42.7.14.jar

Output (the two hashes must be identical):

0e7c94ce80cb1dd9875ff44ffe6a40a8aaa3eefd
0e7c94ce80cb1dd9875ff44ffe6a40a8aaa3eefd  postgresql-42.7.14.jar

Other ways to add the jar, all described on Installation & Configuration: the user.classpath property (a directory outside the JMeter installation), or Add directory or jar to classpath on the Test Plan element, which adds it for that plan only. In distributed mode the driver must be present on every load generator, and the Maven plugin used in CI needs it declared as a test-plan library (covered in the CI/CD page of this section).

Source: Getting Started: JDBC Driver (User Manual), Getting Started: Classpath (User Manual) and Initializing the Driver (pgjdbc)

Driver classes and URLs

The JDBC Driver class drop-down offers a list of common classes, and the Validation Query drop-down a list of common validation queries. Both lists come from properties (jdbc.config.jdbc.driver.class and jdbc.config.check.query), so they can be extended in user.properties; any other class name can be typed in.

Database Driver class (5.6.3 list) URL form

PostgreSQL

org.postgresql.Driver

jdbc:postgresql://host:port/database (the manual shows the short form jdbc:postgresql:{dbname}, which connects to localhost:5432)

MySQL

com.mysql.jdbc.Driver (old name; see The MySQL driver class and JMeter 6.0)

jdbc:mysql://host[:port]/dbname

MariaDB

org.mariadb.jdbc.Driver

per the MariaDB Connector/J documentation

Microsoft SQL Server

com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc:sqlserver://host:port;DatabaseName=dbname

Oracle

oracle.jdbc.OracleDriver

jdbc:oracle:thin:@//host:port/service

H2, HSQLDB, Derby, DB2, Firebird, SQLite, Exasol, jTDS, Ingres

listed in the drop-down

per vendor documentation

If JMeter reports No suitable driver, either the class was not found (the log then shows DataSourceElement: Could not load driver with a ClassNotFoundException), or the class was found but does not accept the URL (a typo in the URL, or the wrong class). If the server is down or unreachable, the error is a java.net.ConnectException.

Source: JDBC Connection Configuration (Component Reference) and Properties Reference: JDBC Request configuration

The MySQL driver class and JMeter 6.0

MySQL renamed the class of its driver in Connector/J 8.0: the class implementing java.sql.Driver changed from com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver, and the old name is deprecated. JMeter 5.6.3 still uses the old name everywhere: in the default value of jdbc.config.jdbc.driver.class, in the component reference, and in the database tutorial, whose example also names the long-gone mysql-connector-java-X.X.X-bin.jar.

The 6.0.0 section of changes.xml on master lists, under Changes / General, "Change references to old MySQL driver to new class com.mysql.cj.jdbc.Driver`" (pull request 6274). It is not in the list of incompatible changes: it changes what JMeter suggests and documents, not what a test plan may contain. A plan with the old class name keeps whatever behaviour the Connector/J version in `lib gives that deprecated name. Typing the new name now works in 5.6.3 too, and removes the dependency on a deprecated alias:

# user.properties: put the current MySQL class first in the 5.6.3 drop-down (6.0 is planned to do this itself)
jdbc.config.jdbc.driver.class=com.mysql.cj.jdbc.Driver|org.postgresql.Driver|org.mariadb.jdbc.Driver|\
    com.microsoft.sqlserver.jdbc.SQLServerDriver|oracle.jdbc.OracleDriver|org.h2.Driver

Source: changes.xml on master and Changes in the Connector/J API (MySQL documentation)

JDBC Connection Configuration

The JDBC Connection Configuration creates a pool of database connections, through Apache Commons DBCP 2 (BasicDataSource), and binds it to a name. Every JDBC element (Request, PreProcessor, PostProcessor) names the pool it uses; nothing else connects it to the configuration element. Several configurations can exist side by side, each with its own name, for example one for the Bookshelf database and one for a reporting replica.

Add → Config Element → JDBC Connection Configuration (test class JDBCDataSource, a TestBean element, so its panel is generated from its properties and grouped into the four sections below).

Where it sits and when it runs. The pool is set up once, when the test starts (testStarted in the 5.6.3 source), before any thread runs, and samplers find it by name. Its settings are read at that moment, so they can use properties and functions (${__P(db_host,localhost)}) but not variables set by threads, such as CSV Data Set Config variables. The element accepts no children. Put it directly under the Test Plan, or under the Thread Group that uses it, and keep the name unique across the plan: with two configurations of the same name, only one is kept, and JMeter logs JDBC data source already defined for: ....

Source: JDBC Connection Configuration (Component Reference)

Every field

Defaults and JMX property names are from the 5.6.3 source (DataSourceElementBeanInfo and its resource bundle); descriptions follow the component reference.

Field Meaning Default JMX property

Variable Name Bound to Pool

Variable Name for created pool

The name the pool is bound to. Every JDBC Request names it in Variable Name of Pool declared in JDBC Connection Configuration. Required; must be unique.

(empty)

dataSource

Connection Pool Configuration

Max Number of Connections

Maximum number of connections in the pool. The manual’s advice: in most cases set it to 0, which gives each thread its own pool with a single connection, so connections are not shared between threads. With a positive value, one pool of that size is shared by all threads; the manual suggests making it equal to the number of threads so that threads do not wait for each other.

0

poolMax

Max Wait (ms)

How long a thread waits for a free connection before the pool throws an error (DBCP maxWaitMillis). Only matters with a shared pool smaller than the number of threads.

10000

timeout

Time Between Eviction Runs (ms)

Sleep time of DBCP’s idle-connection evictor thread; non-positive means no evictor. In 5.6.3 it is only applied when Test While Idle is on.

60000

trimInterval

Auto Commit

Whether connections commit every statement automatically. Turn it off only for explicit transactions with the Commit and Rollback query types.

True

autocommit

Transaction Isolation

The isolation level set on each connection: DEFAULT (leave the driver’s default, which for PostgreSQL is read committed), TRANSACTION_NONE, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE, or the integer value of a java.sql.Connection constant. Cannot be an expression.

DEFAULT

transactionIsolation

Pool Prepared Statements

Prepared-statement pooling per connection. -1 disables it; 0 means unlimited. Since 5.5. In the 5.6.3 source, any value of 0 or more switches DBCP pooling on with a fixed limit of 10 open statements per connection, so the number itself is not used.

-1

poolPreparedStatements

Preinit Pool

Open the pool immediately at test start instead of on the first request. With False, the first JDBC Requests on the pool include the time to connect. See the pitfall below about per-thread pools. Since 5.2.

False

preinit

Init SQL statements separated by new line

SQL statements run once on every new physical connection, when the pool creates it, for example session settings. Since 5.1.

(empty)

initQuery

Connection Validation by Pool

Test While Idle

Let the evictor validate idle connections with the validation query. If off, Soft Min Evictable Idle Time and Validation Query are ignored.

True

keepAlive

Soft Min Evictable Idle Time(ms)

How long a connection may sit idle before the evictor may close it, as long as at least minIdle connections remain (JMeter sets minIdle to 0).

5000

connectionAge

Validation Query

A simple query that proves the database still answers. Empty (default since 3.3) means the driver’s isValid() method, suitable for most databases including PostgreSQL. The drop-down offers common queries, for example select 1 for PostgreSQL, MySQL and SQL Server, select 1 from dual for Oracle and values 1 for Derby. The manual notes that DBCP also runs it when the pool is created.

(empty)

checkQuery

Database Connection Configuration

Database URL

JDBC connection string, defined by the driver vendor.

(empty)

dbUrl

JDBC Driver class

Fully qualified driver class; the jar must be on JMeter’s classpath.

(empty)

driver

Username

Database user. If empty, no user or password is passed.

(empty)

username

Password

Password. The manual warns that it is stored unencrypted in the test plan; pass it as a property instead.

(empty)

password

Connection Properties

Driver properties set when a connection is established, in DBCP’s format name=value;name=value, for example ApplicationName=jmeter-bookshelf for pgjdbc. Since 5.2.1.

(empty)

connectionProperties

Source: JDBC Connection Configuration (Component Reference), BasicDataSource Configuration (Apache Commons DBCP) and Connection parameters (pgjdbc)

Two properties tune the drop-downs and the sampler globally:

Property Meaning Default

jdbc.config.check.query

Validation queries offered in the drop-down, separated by a vertical bar.

the list above

jdbc.config.jdbc.driver.class

Driver classes offered in the drop-down, separated by a vertical bar.

the list above

jdbcsampler.nullmarker

The string that means SQL NULL in Parameter values.

]NULL[

jdbcsampler.max_retain_result_size

Maximum bytes kept from a CLOB or BLOB.

65536

Source: Properties Reference: JDBC Request configuration

Per-thread connections or a shared pool

The choice of Max Number of Connections changes what the test measures.

0 (default): one connection per thread N > 0: one shared pool of N connections

Connections opened on the database

One per JMeter thread that uses the pool, opened lazily on its first request.

Up to N, created at start if Preinit Pool is on, otherwise on demand.

Waiting

Never: each thread owns its connection.

A thread waits up to Max Wait when all N are busy; that wait is part of the sample time.

Transactions over several requests

Work: every request of the thread uses the same connection.

Unsafe: each request borrows a connection and returns it, so Autocommit(false), the statements and Commit may run on different connections.

Models

Clients that each hold a connection (batch jobs, desktop clients).

An application server with its own pool, such as the Bookshelf API with ten connections.

Worked numbers. With the canonical load profile of this guide (50 threads, 60 s ramp-up, 660 s duration) and the default 0, PostgreSQL receives up to 50 new sessions during the first minute, about one every 1.2 s, each a separate server process. PostgreSQL’s max_connections is typically 100, and the Bookshelf API holds ten of them, so 50 JMeter threads fit; 120 would make connection attempts fail once the server’s limit is reached. To model the API instead, use poolMax = 10 with 50 threads: at most ten statements run at once, and the other threads queue in JMeter’s pool for up to 10 s, as requests do in the API’s pool.

Source: JDBC Connection Configuration (Component Reference) and Connections and Authentication: max_connections (PostgreSQL documentation)

Example: the Bookshelf pool

Add → Config Element → JDBC Connection Configuration under the Test Plan, then:

<JDBCDataSource guiclass="TestBeanGUI" testclass="JDBCDataSource" testname="Bookshelf DB" enabled="true">
  <stringProp name="dataSource">bookshelfDb</stringProp>
  <stringProp name="poolMax">0</stringProp>
  <stringProp name="timeout">10000</stringProp>
  <stringProp name="trimInterval">60000</stringProp>
  <boolProp name="autocommit">true</boolProp>
  <stringProp name="transactionIsolation">DEFAULT</stringProp>
  <stringProp name="poolPreparedStatements">-1</stringProp>
  <boolProp name="preinit">false</boolProp>
  <stringProp name="initQuery">SET statement_timeout = '5s'</stringProp>
  <boolProp name="keepAlive">true</boolProp>
  <stringProp name="connectionAge">5000</stringProp>
  <stringProp name="checkQuery"></stringProp>
  <stringProp name="dbUrl">jdbc:postgresql://${__P(db_host,localhost)}:${__P(db_port,5432)}/${__P(db_name,bookshelf)}</stringProp>
  <stringProp name="driver">org.postgresql.Driver</stringProp>
  <stringProp name="username">${__P(db_user,bookshelf)}</stringProp>
  <stringProp name="password">${__P(db_password,)}</stringProp>
  <stringProp name="connectionProperties">ApplicationName=jmeter-bookshelf</stringProp>
</JDBCDataSource>
<hashTree/>

statement_timeout makes PostgreSQL cancel any statement running longer than five seconds, and ApplicationName labels the JMeter sessions in pg_stat_activity, so they can be told apart from the API’s sessions while the test runs. The password property has no default; it is supplied at run time:

jmeter -n -t bookshelf-db.jmx -Jthreads=10 -Jrampup=10 -Jduration=300 -Jdb_host=localhost -Jdb_password=change-me -l results/db.jtl

A -J value is visible in the shell history and the process list; for anything but a local disposable database, put db_password in a properties file that is not committed and pass it with -q.

Source: JDBC Connection Configuration (Component Reference), Getting Started: Command-line options and Client Connection Defaults: statement_timeout (PostgreSQL documentation)

JDBC Request

The JDBC Request sampler sends one SQL statement through a named pool and records the result as a sample. Its fields are also the fields of the JDBC PreProcessor and PostProcessor.

Add → Sampler → JDBC Request (test class JDBCSampler, a TestBean element).

Every field

Field Meaning Default JMX property

Name

Shown in the tree and used as the sample label in results.

JDBC Request

testname attribute

Variable Name of Pool declared in JDBC Connection Configuration

Must match the Variable Name for created pool of a JDBC Connection Configuration. Required.

(empty)

dataSource

Query Type

The kind of statement, see the next table. Required.

Select Statement

queryType

SQL Query

The statement. No trailing semicolon. For callable statements { } around the call is usually unnecessary, but can be used when the database has a non-standard syntax; the driver converts it.

(empty)

query

Parameter values

For prepared and callable statements: comma-separated values, one per ? placeholder, including OUT parameters (give any value). ]NULL[ means SQL NULL. Enclose a value in double quotes if it contains a comma or a double quote, and double any embedded double quote: "Dbl-Quote: "" and Comma: ,". Variables and functions are allowed.

(empty)

queryArguments

Parameter types

One type per placeholder: a field name of java.sql.Types (VARCHAR, INTEGER, BIGINT, DATE, TIMESTAMP, DOUBLE…) or its integer value, for driver-specific types (Oracle’s CURSOR is -10). Prefix OUT or INOUT for callable statement output parameters; IN is assumed.

(empty)

queryArgumentsTypes

Variable Names

Comma-separated variable names, one per column of the result (skip a column with an empty name), or one per OUT parameter of a callable statement, in order.

(empty)

variableNames

Result Variable Name

If set, an object variable holding the whole result as a List of Map objects, one map per row, with the column label as key.

(empty)

resultVariable

Query timeout(s)

Statement timeout in seconds. Empty means 0, which means no timeout; -1 means "do not call setQueryTimeout at all", for drivers that do not support it.

(empty), meaning 0

queryTimeout

Limit ResultSet

Maximum number of rows to read. Empty means -1, no limit. Since 5.6.3 the limit is also passed to the driver with Statement.setMaxRows, so extra rows are not fetched from the database. It also limits what is stored in variables and counted. Since 5.2.

(empty), meaning -1

resultSetMaxRows

Handle ResultSet

What to do with a ResultSet returned as an OUT parameter of a callable statement: Store as String (default; the variable gets its text form; CLOB and BLOB converted to strings), Store as Object (the ResultSet is kept as an object for a later script; BLOB as a byte array), or Count Records (iterate and store the row count). CLOB and BLOB values are cut at jdbcsampler.max_retain_result_size bytes.

Store as String

resultSetHandler

Source: JDBC Request (Component Reference) and java.sql.Types (Java SE 21 API)

Query types

Query Type JDBC call and use Response data

Select Statement

Statement.executeQuery. A plain query with no placeholders; values must be written into the SQL text.

A header line of column labels and one line per row, values separated by tabs.

Update Statement

Statement.executeUpdate. INSERT, UPDATE, DELETE and DDL without placeholders.

N updates

Callable Statement

CallableStatement.execute. Stored procedures and functions, with IN, OUT and INOUT parameters; can return several result sets and update counts.

Each result set and update count, then Output variables by position: and the OUT values.

Prepared Select Statement

PreparedStatement.executeQuery with ? placeholders. The normal choice for queries.

Same as Select Statement.

Prepared Update Statement

PreparedStatement.executeUpdate with placeholders. The normal choice for writes.

N updates.

Commit, Rollback

Connection.commit() or rollback(). The SQL Query is ignored.

Commit or Rollback

AutoCommit(false), AutoCommit(true)

Connection.setAutoCommit(...). The SQL Query is ignored; only the connection’s state changes.

AutoCommit(false) or AutoCommit(true)

Edit

A variable reference that evaluates to one of the types above.

As the resolved type.

The queryType value in the .jmx file is the English label (Prepared Select Statement, AutoCommit(false) and so on); the source warns that these strings must not change because plans store them.

Prefer the prepared types. A Select Statement with ${term} pasted into the SQL text is sent to the database as a new statement text on every request: the database parses and plans each one, which is not what an application using prepared statements does, and a term containing a quote breaks the SQL. With Prepared Select Statement, the SQL text is constant and the values travel as parameters.

Source: JDBC Request (Component Reference)

What the sample records

From the 5.6.3 source (JDBCSampler):

  • Elapsed time runs from the request for a connection to the end of reading the whole result set into text.

  • Connect time is the time to obtain a connection from the pool: near zero with a warm pool, the full TCP and authentication handshake on the first request of a per-thread pool, and the queueing time with a busy shared pool.

  • Latency ends when the statement returns, before the result set is converted to text. (The component reference still says that latency is set from the time to acquire a connection; since 3.1, connect time and latency are recorded separately.)

  • Response code is 200 and the message OK on success. On an SQLException, the code is the SQLState followed by the vendor error code, the message is the exception text, and the sample fails. Any other exception gives code 000.

  • Response headers hold a description of the connection; response data the text shown in the table above, encoded as UTF-8.

A JDBC sample fails only when the statement throws. A query that returns no rows, or an update that changes nothing, is a success; assertions are needed to catch those (see below).

Source: JDBC Request (Component Reference) and Changes history (3.1: "JDBC Request …​ computes latency and connect time")

Variables from a result

With Variable Names set, every row of a select (plain or prepared) sets numbered variables, and a count variable ends in _#. For a search that returns two rows of id, title, author and the variable list book_id,,author:

Variable Value

book_id_#

2 (number of rows)

book_id_1, book_id_2

the id of rows 1 and 2

author_#, author_1, author_2

the same for the third column

The second column has an empty name, so it is skipped. If the query returns no rows, the count variables are 0 and no numbered variables are set. Numbered variables left over from an earlier, longer result are removed, so book_id_3 does not survive from a previous iteration. The count variable is what a ForEach Controller or an If Controller reads (see Logic Controllers & Transactions).

With Result Variable Name = bookRows, the same result is also stored as a list of maps, for a script:

// JSR223 PostProcessor, child of the JDBC Request
def rows = vars.getObject('bookRows')            // List<Map<String, Object>>
if (rows) {
    def pick = rows[new Random().nextInt(rows.size())]
    vars.put('bookId', pick.get('id').toString())
}

Source: JDBC Request (Component Reference) and JSR223 PostProcessor (Component Reference)

Example: the Bookshelf search as a prepared select

Menu path: select the Thread Group, Add → Sampler → JDBC Request, and fill in:

Field Value

Name

DB search books

Variable Name of Pool

bookshelfDb

Query Type

Prepared Select Statement

SQL Query

SELECT id, title, author FROM books WHERE title ILIKE ? ORDER BY title LIMIT 20

Parameter values

"%${term}%" (quoted, because a search term may contain a comma)

Parameter types

VARCHAR

Variable Names

book_id,book_title,book_author

Query timeout(s)

5

Limit ResultSet

20

 
<JDBCSampler guiclass="TestBeanGUI" testclass="JDBCSampler" testname="DB search books" enabled="true">
  <stringProp name="dataSource">bookshelfDb</stringProp>
  <stringProp name="queryType">Prepared Select Statement</stringProp>
  <stringProp name="query">SELECT id, title, author FROM books WHERE title ILIKE ? ORDER BY title LIMIT 20</stringProp>
  <stringProp name="queryArguments">"%${term}%"</stringProp>
  <stringProp name="queryArgumentsTypes">VARCHAR</stringProp>
  <stringProp name="variableNames">book_id,book_title,book_author</stringProp>
  <stringProp name="resultVariable"></stringProp>
  <stringProp name="queryTimeout">5</stringProp>
  <stringProp name="resultSetMaxRows">20</stringProp>
  <stringProp name="resultSetHandler">Store as String</stringProp>
</JDBCSampler>
<hashTree/>

The term variable comes from the same search-terms.csv CSV Data Set Config as the HTTP plan. The response data shown in the View Results Tree during a one-thread check looks like this (titles illustrative):

id	title	author
17	Dune	Frank Herbert
42	Dune Messiah	Frank Herbert

Source: JDBC Request (Component Reference)

Example: create a loan and read back its id

PostgreSQL returns generated values with RETURNING, so the insert runs as a Prepared Select Statement and its result sets a variable like any query:

Field Value

Name

DB create loan

Query Type

Prepared Select Statement

SQL Query

INSERT INTO loans (book_id, username, status) VALUES (?, ?, 'ACTIVE') RETURNING id

Parameter values

${book_id_1},${username}

Parameter types

BIGINT,VARCHAR

Variable Names

loanId

After the sample, loanId_1 holds the new id and loanId_# is 1. With Auto Commit on, the insert is committed when the statement completes. A plain Prepared Update Statement would also work, but only returns 1 updates..

Source: JDBC Request (Component Reference) and INSERT (PostgreSQL documentation)

Example: a callable statement with an OUT value

The count_active_loans function returns the number of active loans of a reader. With the JDBC escape syntax for a function result, the first placeholder is the OUT value; it still needs a parameter value and a type:

Field Value

Name

DB count active loans

Query Type

Callable Statement

SQL Query

{? = call count_active_loans(?)}

Parameter values

0,${username}

Parameter types

OUT INTEGER,VARCHAR

Variable Names

activeLoans

The response data ends with Output variables by position: followed by [1] 3, and activeLoans is 3. For OUT parameters, the variable names are matched to the OUT parameters in order, without the _1 suffix.

Source: JDBC Request (Component Reference) and Calling Stored Functions and Procedures (pgjdbc)

Example: an explicit transaction

Borrowing a book in the database is two writes that must succeed together: insert the loan and decrement available_copies. With auto commit on, each statement commits on its own. The simplest way to get a real transaction is a second pool whose connections do not auto-commit, used only by the transactional requests, with the whole unit inside a Transaction Controller so it is also measured:

Test Plan
├── JDBC Connection Configuration "Bookshelf DB (tx)"   Variable Name: bookshelfTx, Max Number of Connections: 0,
│                                                        Auto Commit: False, other fields as bookshelfDb
└── Thread Group                                          Action after a Sampler error: Start Next Thread Loop
    └── Transaction Controller "DB borrow book"           Generate parent sample: off, Include timers: off
        ├── JDBC Request "reset"           Query Type: Rollback
        ├── JDBC Request "insert loan"     Prepared Update Statement
        │     INSERT INTO loans (book_id, username, status) VALUES (?, ?, 'ACTIVE')
        │     values: ${book_id_1},${username}   types: BIGINT,VARCHAR
        ├── JDBC Request "take copy"       Prepared Update Statement
        │     UPDATE books SET available_copies = available_copies - 1 WHERE id = ? AND available_copies > 0
        │     values: ${book_id_1}   types: BIGINT
        └── JDBC Request "commit"          Query Type: Commit
<TransactionController guiclass="TransactionControllerGui" testclass="TransactionController" testname="DB borrow book">
  <boolProp name="TransactionController.parent">false</boolProp>
  <boolProp name="TransactionController.includeTimers">false</boolProp>
</TransactionController>
<hashTree>
  <JDBCSampler guiclass="TestBeanGUI" testclass="JDBCSampler" testname="reset">
    <stringProp name="dataSource">bookshelfTx</stringProp>
    <stringProp name="queryType">Rollback</stringProp>
    <stringProp name="query"></stringProp>
  </JDBCSampler>
  <hashTree/>
  <JDBCSampler guiclass="TestBeanGUI" testclass="JDBCSampler" testname="insert loan">
    <stringProp name="dataSource">bookshelfTx</stringProp>
    <stringProp name="queryType">Prepared Update Statement</stringProp>
    <stringProp name="query">INSERT INTO loans (book_id, username, status) VALUES (?, ?, 'ACTIVE')</stringProp>
    <stringProp name="queryArguments">${book_id_1},${username}</stringProp>
    <stringProp name="queryArgumentsTypes">BIGINT,VARCHAR</stringProp>
  </JDBCSampler>
  <hashTree/>
  <JDBCSampler guiclass="TestBeanGUI" testclass="JDBCSampler" testname="take copy">
    <stringProp name="dataSource">bookshelfTx</stringProp>
    <stringProp name="queryType">Prepared Update Statement</stringProp>
    <stringProp name="query">UPDATE books SET available_copies = available_copies - 1 WHERE id = ? AND available_copies &gt; 0</stringProp>
    <stringProp name="queryArguments">${book_id_1}</stringProp>
    <stringProp name="queryArgumentsTypes">BIGINT</stringProp>
  </JDBCSampler>
  <hashTree/>
  <JDBCSampler guiclass="TestBeanGUI" testclass="JDBCSampler" testname="commit">
    <stringProp name="dataSource">bookshelfTx</stringProp>
    <stringProp name="queryType">Commit</stringProp>
    <stringProp name="query"></stringProp>
  </JDBCSampler>
  <hashTree/>
</hashTree>

Three conditions make this correct:

  • One connection per thread. The transactional pool must use Max Number of Connections = 0. With a shared pool, each request borrows a connection and returns it, so the four requests can run on different connections (this follows from the 5.6.3 source, where every sample gets a connection and closes it).

  • Discard a half-finished transaction. If insert loan fails, Start Next Thread Loop skips the rest of the iteration and leaves the transaction open on the thread’s connection; JMeter’s pool does not roll back when a connection is returned (the 5.6.3 source sets DBCP’s rollbackOnReturn to false). The reset request at the start of the next iteration rolls it back. On a clean connection it is an empty rollback, cheap but still a sample; give it a label that the analysis can filter out.

  • Set Include timers explicitly. The plan writes TransactionController.includeTimers as false, as everywhere in this guide, because a missing property is read as true by 5.6.3.

The AutoCommit(false) and AutoCommit(true) query types do the same on a pool that auto-commits, but switching back to auto commit with a transaction still open commits it, so a failed iteration is harder to clean up than with a dedicated pool.

Source: JDBC Request (Component Reference), Transaction Controller (Component Reference), Thread Group (Component Reference) and Connection.setAutoCommit (Java SE 21 API)

Isolation levels and what each guarantees are explained in Transactions in the SQL section.

Checking JDBC results

Because an empty result is a success, add the cheapest assertion that proves the answer is useful:

  • Rows returned. A Response Assertion with Apply to: JMeter Variable Name to use = book_id_#, Field to Test = Text Response, Not + Equals, pattern 0, fails the search when no book matched.

  • Row written. A Response Assertion on the Text Response of a Prepared Update Statement, Substring, pattern 1 updates, fails take copy when no copy was available.

  • Duration. A Duration Assertion with ${__P(search_sla_ms,800)} works on JDBC samples exactly as on HTTP samples.

The assertion types and their costs are described in Assertions.

Source: Response Assertion (Component Reference)

JDBC PreProcessor and JDBC PostProcessor

The JDBC PreProcessor runs an SQL statement just before the sampler it is attached to; the JDBC PostProcessor runs one just after. The component reference gives their purpose: the PreProcessor for data a sample needs and that cannot be prepared in a setUp Thread Group, the PostProcessor to put back the state a sample changed. The official demo plan JDBC-Pre-Post-Processor.jmx creates a row with a stored procedure in a PreProcessor and deletes it in a PostProcessor.

Add → Pre Processors → JDBC PreProcessor and Add → Post Processors → JDBC PostProcessor (test classes JDBCPreProcessor and JDBCPostProcessor). Their fields are exactly those of the JDBC Request, from Variable Name of Pool to Handle ResultSet, with the same JMX property names, and they can set variables in the same way.

Behaviour that differs from a sampler (5.6.3 source, AbstractJDBCProcessor):

  • They produce no sample: their time is not recorded, and it does not count in the sampler’s elapsed time. A Transaction Controller includes it only when Include duration of timer and pre-post processors in generated sample is ticked.

  • Errors do not fail anything. An SQLException is logged as a warning (SQL Problem in ...) and the test goes on. Check jmeter.log after a run that uses them, or verify their effect with a sampler.

  • They follow the usual scoping: as a child of a sampler they run for that sampler only; at Thread Group level they run around every sampler in scope, which is rarely intended.

Source: JDBC PreProcessor (Component Reference), JDBC PostProcessor (Component Reference) and Test Plan using JDBC Pre/Post Processor (demo)

Example: remove the loan that an HTTP request created

In the Bookshelf API journey, POST /api/loans creates a loan and the JSON Extractor stores its id in loanId (see Correlation & Extractors). A JDBC PostProcessor on the last request of the journey, GET /api/loans/{id}, deletes it again, so a long soak test does not fill the loans table and slow every later query:

<JDBCPostProcessor guiclass="TestBeanGUI" testclass="JDBCPostProcessor" testname="Delete test loan" enabled="true">
  <stringProp name="dataSource">bookshelfDb</stringProp>
  <stringProp name="queryType">Prepared Update Statement</stringProp>
  <stringProp name="query">DELETE FROM loans WHERE id = ? AND username = ?</stringProp>
  <stringProp name="queryArguments">${loanId},${username}</stringProp>
  <stringProp name="queryArgumentsTypes">BIGINT,VARCHAR</stringProp>
  <stringProp name="variableNames"></stringProp>
  <stringProp name="resultVariable"></stringProp>
  <stringProp name="queryTimeout">5</stringProp>
  <stringProp name="resultSetMaxRows"></stringProp>
  <stringProp name="resultSetHandler">Store as String</stringProp>
</JDBCPostProcessor>
<hashTree/>

The username condition makes sure the statement can only delete a row of a test user. The trade-off: the delete adds one database round trip per journey that real users do not cause, and it runs while the load test is measuring. At the canonical 15.6 requests per second, with five requests per journey, that is at most about three extra deletes per second. That is negligible for Bookshelf, but for a database close to its limit, prefer a single clean-up in a tearDown Thread Group:

-- tearDown Thread Group, JDBC Request "Clean test loans", Query Type: Update Statement
DELETE FROM loans WHERE username LIKE 'reader%'

Source: JDBC PostProcessor (Component Reference) and tearDown Thread Group (Component Reference)

Example: feed the HTTP plan with real book ids

Instead of a hand-written list of ids that drifts from the data, a setUp Thread Group can read available books before the load starts and write them to a CSV file for the main Thread Group. Variables do not cross Thread Groups, so the file is the hand-over.

  1. Add → Threads (Users) → setUp Thread Group, one thread, one loop, Action to be taken after a Sampler error: Stop Test.

  2. Under it, Add → Sampler → JDBC Request "Read available books": Prepared Select Statement, SQL SELECT id FROM books WHERE available_copies > ? ORDER BY id, values 0, types INTEGER, Result Variable Name bookRows, Limit ResultSet 500.

  3. Under the request, Add → Post Processors → JSR223 PostProcessor (Groovy, cache compiled script ticked):

    import org.apache.jmeter.services.FileServer

def rows = vars.getObject('bookRows') ?: []
if (rows.isEmpty()) {
    prev.setSuccessful(false)
    prev.setResponseMessage('No available books: seed the database first')
    return
}
def base = new File(FileServer.getFileServer().getBaseDir())
def target = new File(base, 'data/generated/book-ids.csv')
target.parentFile.mkdirs()
target.withWriter('UTF-8') { w ->
    w << 'bookId\n'
    rows.each { w << it.get('id') << '\n' }
}
log.info("wrote ${rows.size()} book ids")

  4. In the main Thread Group, a CSV Data Set Config reads data/generated/book-ids.csv into bookId.

The query runs once, before any measured traffic, so it costs the test nothing.

Source: setUp Thread Group (Component Reference), JDBC Request (Component Reference) and CSV Data Set Config (Component Reference)

The database tutorial adapted to Bookshelf

The user manual’s Building a Database Test Plan creates 50 users that each send two SQL requests 100 times, 10,000 JDBC requests in total, against a MySQL database, and reads the results in a Summary Report. The same steps, with the Bookshelf PostgreSQL database and the conventions of this guide, give bookshelf-db.jmx.

Step 1: add users

Select the Test Plan, Add → Threads (Users) → Thread Group, and name it JDBC Users. The tutorial uses 50 users, a 10 s ramp-up (one new user every 0.2 s) and a loop count of 100. The Bookshelf version keeps the run properties of this guide, so the same plan serves smoke and load runs:

Field Tutorial Bookshelf

Name

JDBC Users

JDBC Users

Number of Threads (users)

50

${__P(threads,1)}

Ramp-up period (seconds)

10

${__P(rampup,1)}

Loop Count

100

Infinite, with Specify Thread lifetime ticked

Duration (seconds)

(none)

${__P(duration,60)}

Step 2: add the connection and the requests

  1. Select JDBC Users, Add → Config Element → JDBC Connection Configuration, and fill it in as in the Bookshelf pool example above (variable name bookshelfDb, org.postgresql.Driver, URL built from db_host, db_port, db_name). Where the tutorial types a user name and password, the Bookshelf plan uses ${__P(db_user,bookshelf)} and ${__P(db_password,)}.

  2. Add → Config Element → CSV Data Set Config for search-terms.csv (variable term).

  3. Add → Sampler → JDBC Request "DB search books", as in the prepared-select example.

  4. Add → Sampler → JDBC Request "DB loans of a reader": Prepared Select Statement, SELECT id, book_id, status FROM loans WHERE username = ? ORDER BY created_at DESC LIMIT 10, values ${username} from users.csv, types VARCHAR. It replaces the tutorial’s second query, which differed from the first only by its parameter value.

The tutorial’s two requests are both prepared selects with one VARCHAR parameter; Bookshelf keeps that shape but reads two different tables, so the dashboard shows the search and the loan lookup separately.

Step 3: add a listener and run

The tutorial adds a Summary Report and runs from the GUI. For a measured run, keep the GUI for a one-thread check and run the load in CLI mode, writing a results file:

# smoke: one thread, 30 s
jmeter -n -t bookshelf-db.jmx -Jthreads=1 -Jrampup=1 -Jduration=30 -Jdb_password=change-me -l results/db-smoke.jtl

# load: ten threads (the size of the API's pool), five minutes
jmeter -n -t bookshelf-db.jmx -Jthreads=10 -Jrampup=10 -Jduration=300 -Jdb_password=change-me -l results/db-load.jtl -e -o results/db-dashboard

The summariser prints lines of this form (numbers illustrative):

summary +  29870 in 00:00:28 = 1066.8/s Avg:     8 Min:     1 Max:   154 Err:     0 (0.00%) Active: 10 Started: 10 Finished: 0
summary +  32514 in 00:00:30 = 1083.8/s Avg:     9 Min:     1 Max:   201 Err:     0 (0.00%) Active: 10 Started: 10 Finished: 0

The dashboard is written to results/db-dashboard/index.html.

Source: Building a Database Test Plan (User Manual) and Getting Started: CLI mode

Worked numbers: closed loop or a fixed rate

The plan above has no timer, so each of the 10 threads sends its next query as soon as the previous one returns. With an average of 9 ms per sample, one thread does about 1 / 0.009 ≈ 111 queries per second and ten threads about 1,100, which is what the illustrative summariser shows: the database is driven as hard as ten connections can drive it. That is the right shape for finding the database’s limit with a given concurrency (raise threads step by step and watch when throughput stops growing and the average rises).

To reproduce the load the API puts on the database instead, measure the statement rates during an API test (for example from PostgreSQL’s statement statistics) and pace the JDBC plan to them with a Constant Throughput Timer. If the API test at 15.6 requests per second produced 6 searches per second, a timer with a target of 360 samples per minute, Calculate Throughput based on: all active threads in current thread group (shared), placed as a child of "DB search books", reproduces it. By Little’s Law the database then holds on average 6 × 0.009 ≈ 0.05 search statements at a time, which is why a database that looks idle under the API test can still be saturated by an unthrottled JDBC test. Timers and the throughput timers are covered in Timers & Think Time and Open Model & Throughput Control.

Source: Constant Throughput Timer (Component Reference)

Cost on the load generator

JDBC samplers are cheap compared with HTTP requests with large bodies, but three things grow with the load:

  • Result text. Every sample converts the whole result set to text in memory and keeps it as the response data until the sample is processed. A SELECT * over a large table at a few hundred samples per second can use more heap than everything else in the plan. Select only the needed columns, add LIMIT in the SQL, and set Limit ResultSet, which since 5.6.3 also stops the driver from fetching extra rows. The pgjdbc documentation notes that the driver fetches all rows of a result at once by default.

  • Result variables. Result Variable Name keeps every row as objects for the thread, in addition to the text, and Store as Object keeps result sets alive. Use them only for small results, and never in the measured part of a load test without a reason.

  • Connections. Each per-thread connection holds a socket and buffers on the JMeter side and a server process on the PostgreSQL side. Hundreds of threads mean hundreds of sessions, which usually tests the database’s connection handling more than its queries.

As for every protocol, do not keep the View Results Tree in a load run; it holds every response.

Source: Best Practices (User Manual), Changes (5.6.3: "Pass JDBCSampler.maxRows to Statement.setMaxRows") and Issuing a Query and Processing the Result (pgjdbc)

Pitfalls

  • Connection time in the first samples. With per-thread pools, each thread’s pool is created by its first JDBC request, so that sample includes connecting and authenticating. Preinit Pool does not help here (in the 5.6.3 source it only takes effect for a shared pool, which is created at test start). Exclude the ramp-up from the analysis, or start each thread with a warm-up select 1 request under a Once Only Controller and leave its label out of the results.

  • No pool found named: '...'. The request’s pool name does not match any configuration’s Variable Name for created pool, or the configuration is disabled. Names are case-sensitive.

  • Pool names that clash with variables. The pool is stored as a JMeter variable. A CSV column or User Defined Variable with the same name replaces it, and the sampler reports that the object stored under the name is not a data source.

  • CSV variables in the connection. The URL, user and password are read when the test starts, before any CSV line is read, so they can only use properties and functions.

  • Too many parameters or types. The counts of parameter values, parameter types and ? placeholders must be equal, OUT parameters included. A missing value or type fails the sample.

  • Commas in values. %${term}% without quotes splits into two parameters as soon as a term contains a comma. Quote values that come from data.

  • Trailing semicolons. Some drivers reject them; the manual says not to write them.

  • Write tests that leave data behind. Inserts accumulate during long runs and change the query plans and timings the test is supposed to measure. Clean up with a PostProcessor or a tearDown Thread Group, or restore the database from a snapshot between runs.

  • Auto commit off with a shared pool. Uncommitted work stays on a connection that another thread may borrow next. Use per-thread connections for explicit transactions.

  • Silent PreProcessor and PostProcessor failures. They only log a warning.

  • Password in the plan. A literal password is saved unencrypted in the .jmx file and ends up in version control. Use ${__P(db_password,)}.

Source: JDBC Request (Component Reference) and JDBC Connection Configuration (Component Reference)

What changed between versions

Area JMeter 2.x and 3.0 (the books' era) JMeter 5.6.3, and 6.0 as planned

Connection pool

Excalibur datasource until 2.13; 3.0 replaced it with Apache Commons DBCP 2 and asked users to check their validation query.

DBCP 2. 3.1 uses the driver’s isValid() when no validation query is set; 3.3 makes the empty validation query and 0 connections (one per thread) the defaults and adds the driver-class and validation-query drop-downs.

Pool options

Max connections, max wait, eviction, auto commit, isolation (2.6), keep-alive.

Init SQL statements (5.1), Preinit Pool (5.2), Connection Properties (5.2.1), Pool Prepared Statements (5.5).

Sampler

Select, update, callable, prepared, commit, rollback, auto commit; Handle ResultSet (2.13); query timeout (2.10).

Separate connect time and latency (3.1); drivers without query-timeout support (-1, 5.1); Limit ResultSet (5.2), passed to the driver with setMaxRows (5.6.3).

Pre- and post-processors

JDBC PreProcessor and PostProcessor since 2.6.

Unchanged.

MySQL driver

com.mysql.jdbc.Driver and mysql-connector-java jars.

5.6.3 still lists com.mysql.jdbc.Driver. Planned for 6.0: references changed to com.mysql.cj.jdbc.Driver (pull request 6274).

Runtime

Java 7 or 8.

5.6.3 on Java 8 or later (17 recommended). Planned for 6.0: Java 17 or later, and commons-pool2 2.13.1 among the updated libraries.

Source: Changes history, Changes and changes.xml on master

References