When testing your APIs, web service or other system parts, you might need to record or retrieve data from a database. The purpose of this interaction is to check the correct record of specific data in the DB or to prepare test data for the tests by adding specific records to the database. This article will show you how to check, update, and add entries to your database by using Apache JMeter™. Based on these examples, each tester can then perform the appropriate interaction with the database, during their further testing.

5a1c140065d46.jpg

Make sure there is a user who has permission to connect and perform common actions CRUD in the database
Make sure the database is available for remote or local access
Install and choose the right properties for JMeter and the Java Development Kit

Part 1 - Configuring the DB on JMeter

Installing the Connector/J 5.1.42 Driver for DB Interaction

To interact with the database it is necessary to:

  1. Download the Connector/J 5.1.42 driver available from this link.

  2. Enter the downloaded archive.

Increase imagemysql databse connection jmeter

  1. Copy the mysql-connector-java-5.1.42-bin.jar to ..\apache-jmeter-3.2\lib folder

DB Connection Configuration

  1. Boot JMeter and add the JDBC Connection Configuration element. Right click on Thread Group -> Add -> Config Element -> JDBC Connection Configuration

Increase imagejmeter connection to mysql database

The JDBC Connection Configuration is used to configure JMeter connections to the database.

  1. Fill in the Variable Name field. The value of this field is used to associate a specific connection configuration (JDBC Connection Configuration) to the database and a specific request (JDBC Request) sent by JMeter.

If the field isn’t filled, the request won’t be sent and the JMeter Console would show the following message: java.lang.IllegalArgumentException: Variable Name must not be empty for element:JDBC Connection Configuration. This can be seen in the screenshot below:

Increase imagejmeter connection configuration mysql database

  1. Configure the JDBC Connection Configuration. Anyone can use the following configuration by changing the value of localhost and the

The rest of the fields can remain as set by default in JMeter.

Increase imagehow to connect my sqldb to jmeter

Part 2 - Sending a Request to the DB

Sending a Data Request

  1. Add a Thread Group element

Increase imageconnecting mysqldb to jmeter

After adding an element, leave all the parameters filled by default (as JMeter itself provides).

  1. Add a JDBC Request as a child element to the Thread Group. The request will allow sending requests to the DB.

Increase imageconnecitong mysql db to jmeter

  1. Configure the JDBC Request
    Fill in the Variable Name field. I filled in “JDBC Request No. 1” because it is a meaningful name to the request installed by the JDBC Connection Configuration.
    Set the Query Type field to Select Statement. The value of Select Statement indicates that a request will be made to retrieve data from the database
    Set the variable name in the Result variable name field. This variable will store all fields and field values received from the database. The name of this variable must be unique within the Thread Group. If the name of this variable is used in several requests to the database (JDBC Request), then it will store the fields and field values received from the last query.

  2. Add a View Results Tree Listener and run the test. The item View Results Tree Listener allows you to see the sent requests and received responses as shown in the picture below.

Increase imagehow to connect your db to jmeter if you have a mysql db

Note: Database requests can be built according to SQL language syntax, which supports a particular database. JMeter does not impose any restrictions on their design.

Asserting the DB Response

In most of the cases when working with a DB, data receiving is complete with respect to the given conditions. If the data corresponding to the request exists in the database, we will get the results.

But what happens when the DB doesn’t have the data we were testing? In such a case, we will get an empty response, but JMeter won’t report it as an error. It will look like this:

Increase imagejmeter to connect to mysql db and test the data

To check if the response was correct, we can take the next steps:

  1. Add the BeanShell Assertion element as a child element to the JDBC Request element:

Increase imageconfiguring mysql databases with jmeter and testing data

Now, when getting an empty response, all further acts or requests in the Thread Group will be stopped and the correct error message will appear:

Increase imageconnect your mysql db to jmeter and test entries and responses

Sometimes there are cases when the connection to the database is temporarily unavailable, the IP address has been changed, or some other reasons have arisen, as a result of which there was no response from the database. As a result, in the above code, errors occur during execution, although there is no error data when there is a connection to the database.

Increase imagehow to manage mysql dn connections with jmeter

To exclude such moments, you can add a connection test to the database and, in the absence of this connection, correctly display the error message.

Increase imagemysql database data testing with jmeter

Increase imageconnecting my sql db to jmeter

That’s it! You now know how to create and assert a basic DB configuration with JMeter. Stay tuned for part two, where we will learn how to perform advanced manipulations on variables and on responses, as well as how to perform advanced query types.

When testing your APIs, web service or other system parts, you might need to record or retrieve data from a database. The purpose of this interaction is to check the correct record of specific data in the DB or to prepare test data for the tests by adding specific records to the database. This article will show you how to check, update, and add entries to your database by using Apache JMeter™. Based on these examples, each tester can then perform the appropriate interaction with the database, during their further testing. ![5a1c140065d46.jpg](serve/attachment&path=5a1c140065d46.jpg) Make sure there is a user who has permission to connect and perform common actions CRUD in the database Make sure the database is available for remote or local access Install and choose the right properties for JMeter and the Java Development Kit Part 1 - Configuring the DB on JMeter Installing the Connector/J 5.1.42 Driver for DB Interaction To interact with the database it is necessary to: 1. Download the Connector/J 5.1.42 driver available from this link. 2. Enter the downloaded archive. Increase imagemysql databse connection jmeter 3. Copy the mysql-connector-java-5.1.42-bin.jar to ..\apache-jmeter-3.2\lib folder DB Connection Configuration 4. Boot JMeter and add the JDBC Connection Configuration element. Right click on Thread Group -> Add -> Config Element -> JDBC Connection Configuration Increase imagejmeter connection to mysql database The JDBC Connection Configuration is used to configure JMeter connections to the database. 5. Fill in the Variable Name field. The value of this field is used to associate a specific connection configuration (JDBC Connection Configuration) to the database and a specific request (JDBC Request) sent by JMeter. If the field isn’t filled, the request won’t be sent and the JMeter Console would show the following message: java.lang.IllegalArgumentException: Variable Name must not be empty for element:JDBC Connection Configuration. This can be seen in the screenshot below: Increase imagejmeter connection configuration mysql database 6. Configure the JDBC Connection Configuration. Anyone can use the following configuration by changing the value of localhost and the The rest of the fields can remain as set by default in JMeter. Increase imagehow to connect my sqldb to jmeter Part 2 - Sending a Request to the DB Sending a Data Request 7. Add a Thread Group element Increase imageconnecting mysqldb to jmeter After adding an element, leave all the parameters filled by default (as JMeter itself provides). 8. Add a JDBC Request as a child element to the Thread Group. The request will allow sending requests to the DB. Increase imageconnecitong mysql db to jmeter 9. Configure the JDBC Request Fill in the Variable Name field. I filled in “JDBC Request No. 1” because it is a meaningful name to the request installed by the JDBC Connection Configuration. Set the Query Type field to Select Statement. The value of Select Statement indicates that a request will be made to retrieve data from the database Set the variable name in the Result variable name field. This variable will store all fields and field values received from the database. The name of this variable must be unique within the Thread Group. If the name of this variable is used in several requests to the database (JDBC Request), then it will store the fields and field values received from the last query. 10. Add a View Results Tree Listener and run the test. The item View Results Tree Listener allows you to see the sent requests and received responses as shown in the picture below. Increase imagehow to connect your db to jmeter if you have a mysql db Note: Database requests can be built according to SQL language syntax, which supports a particular database. JMeter does not impose any restrictions on their design. Asserting the DB Response In most of the cases when working with a DB, data receiving is complete with respect to the given conditions. If the data corresponding to the request exists in the database, we will get the results. But what happens when the DB doesn’t have the data we were testing? In such a case, we will get an empty response, but JMeter won’t report it as an error. It will look like this: Increase imagejmeter to connect to mysql db and test the data To check if the response was correct, we can take the next steps: 10. Add the BeanShell Assertion element as a child element to the JDBC Request element: Increase imageconfiguring mysql databases with jmeter and testing data Now, when getting an empty response, all further acts or requests in the Thread Group will be stopped and the correct error message will appear: Increase imageconnect your mysql db to jmeter and test entries and responses Sometimes there are cases when the connection to the database is temporarily unavailable, the IP address has been changed, or some other reasons have arisen, as a result of which there was no response from the database. As a result, in the above code, errors occur during execution, although there is no error data when there is a connection to the database. Increase imagehow to manage mysql dn connections with jmeter To exclude such moments, you can add a connection test to the database and, in the absence of this connection, correctly display the error message. Increase imagemysql database data testing with jmeter Increase imageconnecting my sql db to jmeter That’s it! You now know how to create and assert a basic DB configuration with JMeter. Stay tuned for part two, where we will learn how to perform advanced manipulations on variables and on responses, as well as how to perform advanced query types.
edited Nov 27 '17 at 7:03 pm
 
0
reply
71
views
0
replies
1
followers
live preview
enter atleast 10 characters
WARNING: You mentioned %MENTIONS%, but they cannot see this message and will not be notified
Saving...
Saved
With selected deselect posts show selected posts
All posts under this topic will be deleted ?
Pending draft ... Click to resume editing
Discard draft