Apache JMeter Tutorial => Parameterization using databases

Example

Another way to parametrize your performance scripts is to use database data through JDBC. JDBC is an application programming interface that defines how a client can access a database.

First of all, download the JDBC driver to your database (refer to the database vendor). For example, mysql driver can be found here. Then, you can add it by adding the .jar file to the test-plan by using the form below:

But it is better to add the Jar file in lib folder and restart JMeter.

After that, configure the database connection by using the ‘JDBC Connection Configuration’ element. Like this: Right click on Thread Group -> Add -> Config Element -> JDBC Connection Configuration

‘JDBC Connection Configuration’ parameters:

Name - name of the connection configuration that will be shown in the thread group tree
Variable Name - name that will be used as unique identifier for the db connection (multiple connections can be used and each one will be tied to a different name)
Max Number of Connections - maximum number of connections allowed in the connection pool. In case of 0, each thread will get its own pool with a single connection in it
Max Wait (ms) - pool throws an error if the specified timeout is exceeded during db connection
Time Between Eviction Runs (ms) - number of milliseconds to pause between runs of the thread that evicts unused connections from the db pool
Auto Commit - yes to turn auto commit for related db connections
Test While Idle - check idle connections before an effective request is detected. More details: BasicDataSource.html#getTestWhileIdle
Soft Min Evictable Idle Time(ms) - period of time during which a specified connection might be idle in the db pool before it can be evicted. More details: BasicDataSource.html#getSoftMinEvictableIdleTimeMillis
Validation Query - healthcheck query that will be used to verify if the database is still responding
Database URL - JDBC connection string for the database. See here for examples
JDBC Driver class - appropriate name of driver class (specific for each db). For example, ‘com.mysql.jdbc.Driver’ for MySql db
Username - database username
Password - database password (will be stored unencrypted in the test plan)

In our case we need to setup the mandatory fields only:

Variable name Bound to Pool.
Database URL
JDBC Driver class
Username
Password

The rest of the fields in the screen can be left as defaults:

Let’s assume that we store test user credentials in the database:

Now when the database connection is configured, we can add the JDBC request itself and use its query to get all the credentials from database: Right click on Thread Group -> Add -> Sample -> JDBC Request

By using the ‘Select Statement’ query and ‘Variable Names’ we can parse the response to custom variables.

We will now have JMeter variables that can be used further in subsequent requests. Specified variables will be created with incremental suffix (email_1, email_2, email_3…..).

To use these variables in the ‘Login request’, we need to add a counter that will be used to access the right values from the JDBC query response. To add the ‘Counter’ element in JMeter: Right click on Thread Group -> Add -> Config Element -> Counter

After that, we can update the ‘Login request’ using the __V function. This returns the result of evaluating a variable name expression and can be used to evaluate nested variable references: