Sergey Horban is a QA Engineer with 3+ years of experience in automation and manual testing, bug-tracking and test management.

Learn JMeter in 5 Hours

Start Learning
Slack

Run massively scalable performance tests on web, mobile, and APIs

Feb 04 2018

How to Create Test Data in a Database with JMeter

When performance testing APIs, you sometimes have to use different test data than what is present in the database. Occasionally, the test data in the database is not enough and the testers must create more themselves. Apache JMeter™ is very suitable for these purposes, because it allows you to create, modify and delete data from a database. This blog post will explain how.

 

Adding Data to the Database

 

In order to add data to the database, you need to do the following:

 

1. Configure the connection to the database, as described here "MySQL Database and JMeter - How to Test Your Connection"

 

2. In the JDBC Request, set the following:

 

databse configuration with jmeter

 

Example code

 

INSERT INTO `sakila`.`city`(`city_id`,`city`,`country_id`,`last_update`)
VALUES(603,'New York','103',null)

 

This is a SQL query that adds the specified data to the 'city' table:

  • `sakila`.`city`: sakila - is the name of the database
  • city - is the name of table that exists in the ‘sakila’ database
  • `city_id`,`city`,`country_id`,`last_update` - are the names of the fields that contain the ‘city ’ table
  • 603,'New York','103',null - are the values that we add to the table. If we need to add other values, then we only need to replace these values with the others

 

3. Thread Group -> Add -> Listener -> View Results Tree

 

The "View Results Tree" displays the sent requests and the received responses. In our case, this element allows us to see the query being sent to the database and the response received from the database.

 

After starting our request to add data to the database, in the "View Results Tree" we will see the request itself, as shown in the image below.

 

crwating test data in databases with jmeter

 

The result of the successful addition of data will be as shown in the image below.

 

The successful result of adding data to the table is when the response contains a row of the following kind "1 updates". "1 updates" means that one row is added to the table.

 

jmeter, database api testing

 

In the database itself, our added data will look like this:

 

jmeter, mysql database, load testing

 

Updating Data in the Database

 

When specific test data already exists in the database, but is partially not suitable for the test case, it makes no sense to add new test data. You can simply change the existing.

 

For example, above, we added new data to the database. If this data is suitable for the test, but only with a different value of "city_id", then you can do the following:

 

1. In the JDBC Request, set the following

 

api testing databases with jmeter

 

Example code

 

update `sakila`.`city` 
set city_id = 7000 
where city_id = 603

 

This is a SQL query that changes the value of column city_id = 603 to city_id = 7000 in the 'city' table.

 

After starting our request to updating data to the database, in the "View Results Tree" we will see the request itself, as shown in the image below.

 

adding data to mysql database with jmeter

 

The result of the successful data change will look like this:

 

mysql database data addition with jmeter tutorial

 

In the database itself, our modified data will appear:

 

modifying database data with jmeter for api testing

 

Removing Data from the Database

 

If we need to delete the test data from the database after the test, you can do the following:

 

1. In the JDBC Request, set the following:

 

removing database data with jmeter

 

Example code

 

delete 
from `sakila`.`city`  
where city_id = 7000

 

This is a SQL query that deletes from the table 'city' the row that has the value of the column city_id = 7000

 

After starting our request to delete data in the database, in the “View Results Tree” we will see the request itself, as shown in the image below.

 

changing database data with jmeter

 

The result of successful data deletion will be shown like this:

 

how to change data in a database with jmeter

 

Using Variables in a JDBC Request

 

When you have to regularly create test data in specific tables by using different SQL queries, then in the JDBC Request you can use a variable to replace the SQL query itself.

 

In order to use a variable in a JDBC Request, you need to set up the database, and then do the following:


1. Thread Group -> Add -> Config Element -> User Defined Variables

 

jmeter database load testing

 

2. In the User Defined Variables, add the following

 

how to change database data with jmeter

 

insert_city - This is the name of the variable that will be used in the JDBC Request

 

INSERT INTO `sakila` .`city` (` city_id`, `city`,` country_id`, `last_update`) VALUES (603, 'New York', '103', null) is the value of the variable insert_city

 

In the JDBC Request, set the following:

 

working with database variables with jmeter

 

Example code

 

${insert_city}

 

This is a syntax that allows you to use a variable created in User Defined Variables

 

After starting our request that adds data to the database, in the View Results Tree we will see the request itself, as shown in the image below.

 

using user defined variables in jmeter databases

 

The result of the successful addition of data will be as shown in the image below

 

how to successfully add data to databases in jmeter

 

In the database itself, our added data will look like this:

 

jmeter api testing added data, mysql database

 

The SQL query that resides in the JDBC Request can also contain variables.

 

To do this, you need to do the following

 

1. Thread Group - Add - Config Element - User Defined Variables

 

2. In the User Defined Variables, add the following

 

mysql database testing with jmeter

 

The variables added in the image above will be used in the query to add data to the database

 

3. In the JDBC Request, set the following

 

how to modify database data with jmeter

 

Example code

 

INSERT INTO `sakila`.`city`(`city_id`,`city`,`country_id`,`last_update`)
VALUES(${city_id},'${city}','${country_id}',${last_update})

 

This is a SQL query that adds data to the 'city' table.

 

$ {city_id}, $ {city} ',' $ {country_id} ', $ {last_update} - This is a syntax that allows you to use variables created in User Defined Variables

 

After starting our request to add data to the database, in the View Results Tree we will see the request itself, as shown in the image below.

 

viewing mysql database requests in jmeter

 

The result of the successful addition of data will be as shown in the image below

 

how to successfully add data to dbs with jmeter

 

In the database itself, our added data will look as shown in the image below

 

load testing mysql databases with jmeter

 

That’s it! You now know how to create test data in a database by using JMeter. To learn more JMeter, check out our free JMeter academy.

 

Click here to subscribe to our newsletter.

 

BlazeMeter enhances JMeter abilities, so try us out by putting your URL in the box below and your test will start in minutes. Or, request a demo.

Interested in writing for our Blog?Send us a pitch!

Your email is required to complete the test. If you proceed, your test will be aborted.