How to Initialize Database Variables and Assign Them Values in JMeter
A variable is an information storage element (for example, storing numeric values, strings, etc.) whose value can be changed. In order to create a variable, it must be declared (specified a name and the type of data stored) and initialized (assigned a value). Creating a variable and assigning a value to it, is important for writing test cases that use database queries. This is because we need get data from the database and also use the values during the execution of test cases.
In our last blog post, we created and asserted a basic data configuration to our MySQL database with Apache JMeter™. Now, we are ready to move on to more advanced scenarios. In this blog post we will learn to initialize variables and assign values to them, in a database. We will do this for one Thread Group.
Let’s get started.
Suppose that we have an API that as an input parameter (for example, the "address" parameter) that takes the unique value of the client's address and returns data about the payments made by it. The API takes the payment information from the database.
Now let’s say we want to execute the following test scenario:
- Obtaining the client's ID and the unique value of the customer's address (customer_id, address) in the Customer Table and assigning the data to the variable.
- Getting a list of payments from the Payment Table according to the value of customer_id.
- Running an API with a variable that stores the unique value of the client's address
- Comparison of the data that the service returned to what is in the database
- The verification of the existence of a unique client address value is checked in the Customer Table, where address = address_id
- The unique value of the customer address, in the Customer Table, defines a unique customer number (customer_id)
- The value of the customer_id in the Payment Table shows the payments made by the customer
- If the payment exists, the API displays all the data from the Payment Table
- If there are no payments, the API returns an empty response
Depending on the architecture that the tester created in JMeter, all four parts of the test case can be placed in one Thread Group, or each part can be placed in a separate Thread Group. This will determine how the client ID is assigned to the variable.
In this example we will run the test case in one Thread Group, through multiple JMeter elements. Now let’s build our test scenario in JMeter:
1. Add a Thread Group
Right Click -> Threads -> Thread Group
2. Configure the connection to the database as indicated in the previous article "MySQL Database and JMeter - How to Test Your Connection".
3. In the Thread Group element, add 2 JDBC Request elements.
Right Click on Thread Group -> Add -> Sampler -> JDBC Request
- JDBC Request № 1 - will implement the first part of the test case
- JDBC Request № 2 - will implement the second part of the test case
4. Add a BeanShell Assertion element to JDBC Request №1
Right Click on JDBC Request -> Add -> Assertions -> BeanShell Assertion
5. Add the View Results Tree element, which allows you to display the responses received from the database.
Right Click on Test Plan -> Add -> Listener -> View Results Tree
6. Now, perform a customer search in the Customer Table through an SQL Query.
I used this code in the first JDBC Request:
- select customer_id, address_id - output fields from the Customer Table only
- customer_id, address_id - the remaining fields are not in the output.
- where address_id is not null and customer_id is not null - selects only the lines in which the value for the fields address_id and customer_id is filled
- limit 5 - displays only the first 5 rows
7. Here are the results in the View Results Tree listener:
8. Now we will assign the value of the customer_id variable and assign the address_id of the variable in the BeanShell Assertion, to get the results of the specific customer we want.
This is the code I used:
Let's take a closer look at this line of code:
- When the database sends a response, all the data received in the image below is written to the variable "customer".
- The method vars.getObject ("customer") gets the value of the variable "customer", which is the same structure as in the image
- The get (0) method gets the first string from the value of the variable (in our case this is a string where customer_id = 1 and address_id = 5). The method get (int a) as the value of the parameter a takes the sequence number of the line starting with "0" (That is, the serial number for the first line will be "0", for the second "1", etc.)
- The get ("customer_id") method from the string received by the get (0) method gets the value for the address_id field. In our case, this value is equal to "5". The get (String name) method, as the value of the parameter name, takes the name of the column from which you want to get the value
- The value obtained by the get ("customer_id") method is assigned to the String IDClient variable. Thus, the value of the IDClient variable will be 5. Similar operations are performed to assign the value of the variable address.
Note that the put() method from the JMeterVariables class is used to create a variable available in any JMeter element within the same Thread Group.
9. Now we will configure the script so we get payment information from the Table Payment through the IDClient variable.
This is the code I used in the second JDBC Request:
10. The IDClient variable is converted to a value at the time the request is submitted to the Payment Table.
11. View the results of receiving all IDClient = customer_id payment from the Payment Table.
Let's see what happens if you do not use the put() method after assigning the value of the variable IDClient.
This is the example code:
We will get the payment information from the Payment Table:
As seen in the image above, the IDClient variable was not converted to a value, because the put() method, which allows the variable to be used in other JMeter elements, was not used. Without the put () method, the variable can be used only within the element we are using (in this case this is BeanShell Assertion).
By passing the IDClient variable to the log.info() method, it is converted to a value.
If you plan to use a variable in one Thread Group, but in different JMeter elements, then you need to use the vars.put(String key, String value) method.
- String key - the name of the variable to be used
- String value - the value that will be used for the variable
To finish the creation of the test case in JMeter, all that remains is to send the address variable to the API request and compare the results.
That’s it! You now know how to initialize database variables and assign them values, when working with one thread group. Next time, we will show how to do this for multiple thread groups.
You can learn more about variables and other JMeter test configurations for free through our JMeter academy.