JMeter is a widely-used tool for performing API testing. It is very easy to automate APIs to send requests and receive responses using JMeter. But many of us have faced a situation when we have to store some values in a database and/or fetch records from a database and then assert the database responses. To address this problem, I have written this blog where we will learn how to integrate a DB with JMeter, fetch the records from the DB, and then assert the records fetched from the DB.
To start the integration of the database with JMeter, we need to have following prerequisites:
To start with, we need to have a MySQL connector on our machine to set up a proper communication channel between JMeter and MySQL. Follow these steps to do this correctly.
2. Unzip the compressed file and copy the mysql-connector-java-5.1.xx-bin file.
3. Paste this file into the Apache JMeter lib folder, which will be at following path: ..\apache-jmeter-x.x\lib.
If you do not complete the above steps, you may see this error while running the JMeter tests: “No suitable driver found for jdbc:mysql://localhost”3306/Testing.”
To configure the database, you must first open the JMeter batch file. Then, add a JDBC Connection Configuration using the following flow shown in the following image: Test Plan > Thread Group > Add > Config Element > JDBC Connection Configuration.
Then, we need to fill in the values for following fields:
2. JDBC driver class – This is the class in which the code with a particular database is implemented:
If the correct driver class name is not provided in the configuration, the following error may appear as we are running the JMeter tests: “No suitable driver found for jdbc:mysql://localhost:3306/Testing.”
3. Add a JDBC Request as a child element to the Thread Group. This will be used to send requests to the DB.
In the JDBC request, we will send the Query that we want to use to fetch records from the DB. For example, we could use the following code:
1. select * from ApplicantProfile where ApplicantId = < ApplicantId > ;
Do not use the extractors, because the DB values are not extracted properly. Instead, use Beanshell Postprocessor as a child under the JDBC Request. BeanShell Postprocessor stores the DB value in Array, which can be used for assertion.
Use following code with the BeanShell PostProcessor:
1. ArrayList result = vars.getObject("result"); 2. 3. for (HashMap row: result) { 4. Iterator it = row.entrySet().iterator(); 5. while (it.hasNext()) { 6. Map.Entry pair = (Map.Entry) it.next(); 7. log.info(pair.getKey() + " = " + pair.getValue()); 8. } 9. }
JMeter Logs
Debug Sampler is another way to get the response to show as JMeter variables. Add the Debug sampler to the thread group:
Here is how the response of the debug sampler is shown:
We will add the BeanShell Assertion as a child element to the JDBC Request element in order to assert the response of the BeanShell Postprocessor:
Enter the following code in the BeanShell assertion:
1. String expected = ("Roger"); 2. String actual = ("${FirstName_1}"); 3. if (expected.equals(actual)) { 4. Failure = false; 5. } 6. else { 7. Failure = true; 8. FailureMessage = "Difference detected, expected: " + expected + " and actual: " + actual; 9. }
If both the string ‘expected’ and ‘actual’ match. then the result is passed; otherwise, the result will fail. We can also add a CSV file to read the expected value from there.
There are some issues that may need some troubleshooting while setting up this integration. I will address few of those here.
Issue 1
You receive the following message: “Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The drive has not received any packets from the server.”
There are a few probable solutions for this issue:
Issue 2
You receive the following message: “Access denied for user ‘ca4app’@%’ to database ‘sakila'”
There are following probable solutions for this issue:
This concludes my process for integrating a database with JMeter, and then using this integration to fetch records from the database and assert that these records were accurately fetched. Please feel free to leave comments or questions.
Need to st Up DB and need to f=get response from that.