DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Load-Balancing Minecraft Servers with Kong Gateway
  • Rails 6: Multiple DB Support
  • Troubleshooting Connection Issues When Connecting to MySQL Server
  • A General Overview of TCPCopy Architecture

Trending

  • Why High-Performance AI/ML Is Essential in Modern Cybersecurity
  • Unlocking the Benefits of a Private API in AWS API Gateway
  • AI Meets Vector Databases: Redefining Data Retrieval in the Age of Intelligence
  • Unlocking AI Coding Assistants Part 4: Generate Spring Boot Application
  1. DZone
  2. Data Engineering
  3. Databases
  4. MySQL Database and JMeter: How to Test Your Connection

MySQL Database and JMeter: How to Test Your Connection

Learn how to check, update, and add entries to your DB with Apache JMeter so that you can effectively look at the correct record of specific data or prepare test data.

By 
Aleksey Merzlyakov user avatar
Aleksey Merzlyakov
·
Sep. 02, 17 · Tutorial
Likes (9)
Comment
Save
Tweet
Share
25.7K Views

Join the DZone community and get the full member experience.

Join For Free

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 database 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.

In this blog post, we will use JMeter 3.2, the database MySQL 5.7.18 Community Edition (which is free and can be installed on your PC) and Java 8.

Before you start working with a database using JMeter, you need to do the following:

  • 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.

Installing the Connector/J 5.1.42 Driver for DB Interaction

To interact with the database, it is necessary to do the following.

Download the Connector/J 5.1.42 driver available from this link. Enter the downloaded archive.

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

Note: The driver Connector/J 5.1.42 is used only for connection to the MySQL database.

DB Connection Configuration

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

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

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:

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

  • Datebase URL: Used for the database address. Fill in:
    • jdbc:mysql://: The unchanged sequence used in JMeter when specifying the MySQL database address
    • localhost:3306/: The IP address and port that makes the database available. Since in the given example we use a database that is installed on a local PC, we use the IP address and port, configured while installing MySQL. In most cases, it would be necessary to install different connections for databases, which are on the remote server and have, for instance, an IP address and the port 10.56.56.2:4101.
    • sakila is a scheme of database (or in a nutshell, the name of a definite quantity of tables). Without specifying the scheme, JMeter returns the error: "Response message: java.sql.SQLException: No database selected".
  • JDBC driver class: The class in which the code of work with a particular database is implemented (This class was created by the developers of JDBC driver).
  • Username: The name of a user who has access to the DB.
  • Password: The password for accessing the DB.

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

Sending a Data Request

Add a Thread Group element:

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

Add a JDBC request as a child element to the thread group. The request will allow sending requests to the DB.

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.

For example, let's say we have two JDBC requests under the same element of the thread group. For JDBC Request 1 and JDBC Request 2, the value of the Result variable name = A field is set. When JDBC Request 1 returns a response from the database, all fields and field values will be assigned to the A variable. After receiving a response from JDBC Request 2, all the fields and field values will be assigned again to the A variable, which already contains the values from the JDBC Request 1. Thus, the data received by JDBC Request 1 will not be stored in the А variable. The А variable will store the response only from the JDBC Request 2. In order for the response from JDBC Request 1 and JDBC Request 2 to be stored in variables and not be overwritten, the Result variable name field must be specified as unique (for example, for JDBC Request 1, the value of the Result variable name = A field, and for the JDBC Request 1 Result variable name = B).

  • Create an SQL query against the database in the Query field, in this example:  select * from payment where customer_id = 1 limit 10. 
  • The remaining parameters can be left out, since further processing of the data will be performed by using Java. (For example, instead of the Response Assertion element, a BeanShell Assertion with the code written will be used, as shown below)

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.

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 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:

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

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

Add the code as shown in the picture:

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:

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.

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.

Example:

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

Database Connection (dance) MySQL Requests Test data

Published at DZone with permission of Aleksey Merzlyakov, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Load-Balancing Minecraft Servers with Kong Gateway
  • Rails 6: Multiple DB Support
  • Troubleshooting Connection Issues When Connecting to MySQL Server
  • A General Overview of TCPCopy Architecture

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!