Application of JDBC in Performance Testing

Preface

Can we bypass the http protocol and directly test the performance of the database? Do you find it troublesome to export CSV files from a database to construct pressure test data? How to clean up data after pressure testing? Can I assert pressure testing requests through insertion (deletion) records in the database? Using the Alibaba Cloud performance testing tool PTS can easily solve the above problems.

What is JDBC?

JDBC (Java DataBase Connectivity) is a Java API used to execute SQL statements, providing unified access to multiple relational databases. It consists of a set of classes and interfaces written in the Java language. JDBC provides a benchmark from which you can build more advanced tools and interfaces that enable database developers to write database applications.

Simply put, JDBC can do three things: establish a connection to a database, send statements to manipulate the database, and process the results.

Design principles of JDBC

Overall architecture

JDBC has developed a set of standards for interacting with databases, and database vendors provide implementations of this set of standards, which allows you to connect various databases through a unified JDBC interface. It can be said that the role of JDBC is to shield the differences between the underlying databases, enabling users to execute code written according to JDBC on various databases. So how did this happen? As shown in the following figure:

JDBC defines the Driver interface, which is the driver of the database. All operations related to the database will ultimately come down to this interface. The database manufacturer must implement this interface to complete the interaction between the caller of the upper layer application and the underlying specific data base. The driver is registered through the DriverManager provided by JDBC. The registration code is written in the static block of the driver, as shown in the MySQL registration code below:

As the manager of the driver, DriverManager is not only responsible for the registration/logout of the driver, but also can directly obtain connections. How did it do it? Looking at the following code, it is found that the actual process is to traverse all registered drivers, find a driver that can successfully establish a connection, and return the Connection. The DriverManager, like a proxy, hands over the actual connection establishment process to the specific driver.

Connection Design

From the previous section, we know that database providers provide services to users by implementing the Driver interface. The core method of the Driver interface is to obtain connections. Connection is the core interface for dealing with databases. Let's take a look at its design scheme.

By observing the design diagram, we find that there are two main types of interfaces: DataSource and Connection. Let's introduce them one by one.

• DataSource

Looking directly at the source code, as shown below, we found that its core method is to obtain connections, just like Driver. So why do we need DataSource? Isn't the driver itself used to obtain connections? Let's take a look at how DataSource actually obtains connections.



However, we found that JDBC only defines the interface of the DataSource and does not provide a specific implementation. Next, let's take the SimpleDriverDataSource implemented by Spring as an example to see how it works. The code is shown below. We found that the getConnection (...) method of the DataSource was ultimately handed over to driver. connect (...) to actually establish the connection. So back to what we described at the beginning, Driver is the real interface for dealing with databases.

So the question arises, why do we still need an interface like DataSource? Isn't it unnecessary? Obviously not. DataSource is an enhanced driver. It delegates the core connection establishment process to the driver for execution, and handles seemingly unrelated matters such as cache establishment, processing of distributed transactions, and connection pooling on its own. As shown in the design diagram of the class, take the Druid connection pool used by PTS as an example:

• ConnectionPoolDataSource: The implementation of a connection pool. This data source implementation does not directly create a physical connection to the database, but is a logical implementation. Its role is to pool physical connections to the database.

• PooledConnection: Cooperate with the ConnectionPoolDataSource to obtain a pooled object PooledConnection, and then indirectly obtain a physical connection through the PooledConnection.

Obviously, through connection pooling, we can escape from the management of connections, improve the efficiency of connection utilization, and also improve the pressure application ability of the press.

Statement Design

After establishing a connection, the user may have to start writing SQL statements and hand them over to the database for execution. These are implemented through Statements. Mainly divided into:

• Statement: Defines a static SQL statement that requires recompilation every time the database is executed. It is typically used in situations where only one query is executed and results are returned.

• PreparedStatement: Defines a precompiled SQL statement with parameters. The next time it is executed, the subsequent statements will be fetched from the cache all over, without the need to recompile them. It is suitable for executing multiple SQL statements with the same logic. Of course, it also has features such as SQL injection prevention, which is high in security and efficiency, and is frequently used. For performance testing, selecting PreparedStatement is the most appropriate choice.

• CallableStatement: Used to call stored procedures.

ResultSet Design

JDBC uses the ResultSet interface to accept the execution results of a Statement. The ResultSet uses the pointer method (next()) to retrieve the search results item by item. When the pointer points to a certain piece of data, the user can freely choose to retrieve the data of a certain column. PTS assists users in constructing complex pressure test data with a single SQL statement by converting ResultSet into a CSV file.

Summary of JDBC architecture

Through the above introduction, we found that the design of JDBC is still hierarchical.

(1) Driver and DriverManager are database oriented, and have designed a set of Java access data specifications. Database manufacturers only need to implement this set of specifications;

(2) DataSource and Connection are targeted at application developers, and they do not care about how JDBC specifically interacts with the database. A Connection can be obtained through a unified DataSource interface, and users' data operations can be implemented through this Connection;

(3) Statements carry specific SQL commands, and users can define different statements to send instructions to the database;

(4) The ResultSet is used to carry the execution results of SQL commands.

So far, the entire process of interacting with the database has been completed, such as loading drivers ->establishing connections ->executing commands ->returning results. If this process is flexibly embedded in PTS performance testing, various issues mentioned in the preamble can be resolved.

Application of JDBC in Performance Testing

Database performance testing

• Background

Most database operations are performed through HTTP, FTP, or other protocols, but in some cases, bypassing intermediate protocols to directly test the database is also meaningful. For example, we want to not trigger all related queries, but only test the performance of specific high value queries; Verify the performance of the new database under high loads. 2. Verify some database connection pool parameters, such as the maximum number of connections 3. Save time and resources. When we want to optimize SQL, modifying SQL statements and other database operations in the code is very cumbersome. Through JDBC pressure testing, we can avoid code intrusion and focus on SQL tuning.

• Steps

1. Create a scene. We create a PTS pressure test scenario in [Pressure Test Center] ->[Create Scenario] on the PTS console;

2. Scenario configuration. PTS supports pressure testing on four databases, including MySQL and PostgreSQL. The user can initiate a pressure test by filling in the JDBC URL, username, password, and SQL. At the same time, PTS also supports extracting data from the ResultSet as output parameters for use by downstream APIs; Asserting the response.

3. Monitoring and pressure test report during pressure test. PTS supports binding to Alibaba Cloud RDS cloud resource monitoring, observing RDS real-time performance indicators during pressure testing. In addition, PTS also provides clear and complete pressure test reports and sampling logs for users to view at any time.

Pressure measurement data structure

• Background

In scenarios such as simulating different user logins and pressure testing service parameter transfer, parameter functions are needed to implement various dynamic operations in pressure testing requests. If you use traditional CSV file parameters, you will be limited by the file size and manual creation will consume effort. Using JDBC to construct pressure measurement data can avoid the above problems.

• Steps

1. Add a data source. In Scenario Editing - Data Source Management, select Add DB Data Source, and enter the URL, username, password, and SQL.

2. Add parameters. Fill in the custom parameter name and column index.

3. Commissioning verification. Click the debug scenario to verify whether the extracted result set meets expectations. Then, we can use any parameter that we want to use

Use the ${} reference instead.

Pressure measurement dirty data cleaning

• Background

Pressure testing for write requests can generate a large amount of dirty data in the database. How to automatically clean up after pressure testing?

• Steps

PTS provides users with solutions. PTS supports logical sequencing of serial links, namely, pre link, normal link, and post link. The execution order is from first to last. To set a serial link as a post link, fill in the number of cycles.