Dynamic Data from JMeter JDBC Requests

A common requirement when load testing is to populate subsequent requests with dynamic data from previous requests. If you’re thinking about using a database to store static test data, such as usernames, paswords, credit card numbers etc then read on to find out how to extract this data at runtime and populate JMeter variables.

In this example we’ll need to add a JDBC Connection Configuration to our test plan. This will define and control the way in which JMeter will access your database.

Notice I’ve left the defaults for connection pool properties, although you may need to tweak this depending on how you’ll access the data. I’ve used a JDBC connection URL that accesses a local mysql server listening on port 3306. The database in use is called watirgrid

I’ve also defined the JDBC Driver class and specified the username / password to connect with.

Now we can make a JDBC request using the JDBC Request sampler. It will look something like this:

The Variable Name matches the JDBC Connection Config Variable Name i.e. mysql

I’m using a select statement that will print out results in a JSON-like format. That query is as follows:

SELECT
CONCAT("[",
GROUP_CONCAT(
CONCAT("{email:'",email,"'"),
CONCAT(",login_count:'",login_count),"'}")
,"]")
AS json FROM users

In doing this, it will return a result set that looks like this:

+-------------------------------------------------+
| json                                            |
+-------------------------------------------------+
| [{email:'tim.koops@gmail.com',login_count:'1'}] |
+-------------------------------------------------+
1 row in set (0.00 sec)

Now we need to add a Post Processor Regex Extractor to the JDBC request as follows:

The reason we do this is so we can extract the results from the JSON string returned by the database query. For example, to extract and populate a variable called ${email} our PPRE would look like this:

Now we’re cooking! Add a Debug Sampler to confirm everything works as you’d expect. You should see results like this in the response data

email=tim.koops@gmail.com
email_g=1
email_g0=email:'tim.koops@gmail.com'
email_g1=tim.koops@gmail.com
mysql=org.apache.jmeter.protocol.jdbc.config.DataSourceElement$DataSourceComponentImpl@10d69502

Attached is a demo test plan: jdbc_example.jmx

5 thoughts on “Dynamic Data from JMeter JDBC Requests

  1. Still learning how to use JMeter, I don’t quite understand how this example might work if I have a large dataset and want each iteration to use a different value. But then, there are lots of things I still don’t understand about JMeter…

    • This example returns a single row resultset each time the JDBC Request is executed.

      If you wanted to iterate, the query can be parametized using standard JMeter variables.

      If you just wanted to populate a single large (multi-row) dataset then I would just use a CSV Data Set Config and populate that as part of your pre-test build e.g. via ant or the command line.

      Really just depends on how you intend on using the data i.e. static data, transactional data etc

  2. Yeah, that’s what I thought. Trying to avoid proliferation of libraries (and digging into the main app’s source) at the moment so was seeing if I could use JMeter to extract the data…It would work if JMeter had an ‘ignore header row’ option on it’s CSV Data Set.

  3. Iam receiving the following error while running the existing script, please explain where the problem might be with :

    java.net.ConnectException: Connection refused: connect
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(Unknown Source)
    at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
    at java.net.PlainSocketImpl.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at java.net.Socket.connect(Unknown Source)
    at sun.net.NetworkClient.doConnect(Unknown Source)
    at sun.net.www.http.HttpClient.openServer(Unknown Source)
    at sun.net.www.http.HttpClient.openServer(Unknown Source)
    at sun.net.www.http.HttpClient.(Unknown Source)
    at sun.net.www.http.HttpClient.(Unknown Source)
    at sun.net.www.http.HttpClient.New(Unknown Source)
    at sun.net.www.http.HttpClient.New(Unknown Source)
    at sun.net.www.http.HttpClient.New(Unknown Source)
    at sun.net.www.protocol.http.HttpURLConnection.plainConnect(Unknown Source)
    at sun.net.www.protocol.http.HttpURLConnection.connect(Unknown Source)
    at org.apache.jmeter.protocol.http.sampler.HTTPSampler.sample(HTTPSampler.java:431)
    at org.apache.jmeter.protocol.http.sampler.HTTPSamplerBase.sample(HTTPSamplerBase.java:1021)
    at org.apache.jmeter.protocol.http.sampler.HTTPSamplerBase.sample(HTTPSamplerBase.java:1007)
    at org.apache.jmeter.threads.JMeterThread.run(JMeterThread.java:290)
    at java.lang.Thread.run(Unknown Source)

  4. Hi. I read a few of your other posts and i wanted to say thank you for the informative posts.

Comments are closed.