JMeter matching of DB data to interface data

The interface parameter data is combined with the DB data to verify the calibration force more accurately ~

JMeter self-signed JDBC Request Sampler

This sampler can send a JDBC request (SQL statement) to the database and get the returned database data to operate. It often needs to be used with the JDBC Connection configuration master (configuration database connection related properties, such as connection name, password, etc.).

SETP1: Preparing for work

1. This article uses SQL Server database for testing, the database user name is SA (your own user name), username password is ********* (your own password)

2. DB instance name: such as ——— TMSS_DB2, library name: seg6654, table name: Configuration, table data is as follows:

3. Add the required drive jar package

Using different databases, we need to introduce different jar packages.

Method 1: Copy the jar package directly to the JMeter Lib\ext directory

• MySQL Database: no need to introduce additional database driver jar packages.
• SQL Server database : Download Sqljdbc42.jar into the JMeter root directory under the Lib directory
• Oracle Database: Place the \product\10.2.0\db_1\jdbc\lib\ojdbc14.jar under the installation directory of the Oracle data under the JMeter root directory in the Lib directory

Mode 2: Pass Test Plan

If we do not use the way to copy the jar to the JMeter Lib directory, we can also use the JMeter test plan to introduce the corresponding jar package, such as the following jar package that introduces SQL Server data

Step2: Configuring the JDBC Connection configuration

Important Parameter Description:

• VariableName: Database connection pool names, we can have multiple JDBC connection configuration, each can have a different name, in the JDBC The request can be used by selecting the appropriate connection pool for this name.
• Database URL: DB url,jdbc:mysql://host IP or machine name: MySQL listener port number/database name, such as: Jdbc:mysql://localhost:3306/test
• jdbc Driver class:JDBC Driver
• username: User name of database login
• passwrod: password for database login

Different database specific filling methods, you can refer to the following table:

Datebase	Driver class	Database URL
Mysql	Com.mysql.jdbc.Driver	Jdbc:mysql://host:port/{dbname}
PostgreSQL	Org.postgresql.Driver	Jdbc:postgresql:{dbname}
Oracle	Oracle.jdbc.driver.OracleDriver	Jdbc:oracle:thin:user/[email Protected]//host:port/service
ingres (2006)	ingres.jdbc.ingresdriver	jdbc:ingres://host:port/db[;attr=value]
MSSQL	Com.microsoft.sqlserver.jdbc.SQLServerDriver Or Net.sourceforge.jtds.jdbc.Driver	Jdbc:sqlserver://ip:1433;databasename=dbname Or jdbc:jtds:sqlserver://localhost:1433/"+" library "

After filling in the following general:

Step3: Configuring the JDBC Request

Important Parameter Description:

• VariableName: The name of the database connection pool needs to be consistent with the Variable name Bound pool name of the JDBC Connection configuration
• Query: Fill in the SQL statement do not add ";"
• Parameter valus: Parameter values
• Parameter types: Parameter type, refer to: Javadoc for Java.sql.Types
• Variable names: variable name to save SQL statement return result
• result variable name: Creates an object variable that holds all returned results
• Query timeout: Queries Time out
• Handle Result set: Defines how to handle the results returned by the callable statements statement

Execution Result:

Execution here, we have the data from the database is the original, but how to remove the data we need, obviously, if we query the SQL returned is only a data, the above way can meet our needs, but if we just public_ Adresskey data, how do we go to fetch it?

At this point we need to use the above parameters in the JDBC request.

Lifting using variable names and result variable name can be, but the value of the key is different, such as we are now as above, plus a debug sampler

Variable names the key that returns the result is Count_1,result Variable name corresponds to the key that returns the result.

So that the value of our value is assigned to a key, then how do we get the public_adress in result?

Because the result is a JSON data format, the JSON data is first fetched and then read by Jsonpath

We add a BeanShell postprocessor, through the vars.getobject (variable). Get (Index). get (field) function gets

Important Parameter Description:

• Variable : variable name set for Variable names or result Variable name
• Index: Represents the location of the query results (because I have a previous SQL statement that only restricts the content of the query, therefore 0)
• Field: The name of the query fields

Get to this value followed by the interface parameters for consistency check (because the front has been vars.put into the ~ now directly used)

To this end ~

In addition, the use of other JDBC methods

Parameterization of Step4:jdbc Request

Method (i), define variables, use variables in SQL query:

1. Define a variable in user Defined variables or Testplan or CSV Data Set Config:

2. Reference in SQL query using ${variable name}:

Method (ii), use in SQL query "? "As a placeholder and pass the parameter value and the parameter type, as shown in:

1, passed the parameter value is a constant, passing 2 variables, multiple variables using "," separated. If you have data that is of type int, it is also marked as a varchar type in parameter types, otherwise it cannot be run.

2, passed the parameter value is a variable, using the ${variable name} way

Step5:variables How to use names parameters:

JMeter's official website explains that if you set a value for this parameter , it will save the data returned by the SQL statement and the total number of rows that return the data . If the SQL statement returns 2 rows, 3 columns, and variables names is set to A,,c, the following variables are set to:

a_#=2 (total number of rows)
a_1= 1th column, line 1th
a_2= 1th column, line 2nd
c_#=2 (total number of rows)
c_1= 3rd column, line 1th
c_2= 3rd column, line 2nd

• If the return result is 0, then a_# and c_# will be set to 0, and the other variables will not set the value.
• If you return 6 rows of data for the first time and return only 3 rows for the second time, then the first number of 3 rows of data variables will be cleared.
• You can use ${a_#}, ${a_1} ... To get the corresponding value

Example:

We still use the above database, to find out all the data, test table has 3 fields, 5 records (forgotten can go back to the first step to see)

1. Add a JDBC request named "Parameter 4", add a "Debug Sampler" to view the output results, set variables name to COLUMN1,COLUMN2,COLUMN3:

2. Execution Result:

Analytical:

Column1 represents all the data in the first column, column1_# can get the number of rows to the first column

Column1_n: Gets the data for row N of the first column.

Column2 and Column3 function Similarly, if we only need the first column and the third column of data, can be written in column1,,column3, the middle of "," can not be omitted.

Step6:result variable The name parameter uses the method:

If you set a value for this parameter, it creates an object variable, saves all returned results, and gets the exact value of the method: Columnvalue = Vars.getobject ("Resultobject"). Get (0). Get ("Column Name")

Execution Result:

JMeter matching of DB data to interface data