Turn JMeter performance test use of JDBC Request (querying database for database data)

JDBC Request

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

First, the preparatory work

1, this article uses the MySQL database to test , the database user name is root (your own user name), the user name password is ********* (your own password)

2, the database has a table : test, the data structure of the table is as follows:

The data in the table is as follows:

SELECT * FROM Test

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 directory

• MySQL database: No need to introduce additional database driver jar packages.
• SQL Server database: Download Sqljdbc4.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 the jar is copied to the JMeter Lib directory, we can also use JMeter's test plan to introduce the corresponding jar package, such as the following Jar package that introduces Oracle data

Second, configure 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:

Third, configure 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, such as the number of records we query data,

Select COUNT (*) from Test

The result of the query is what we need, or we can get our content with regular expressions.

But what if, like the above, we get out of multi-line data, how do we traverse the data and just take out the data we need? Take a look at the analysis below.

Iv. parameterization of the JDBC Request

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

1. Define a variable in test Plan (you can of course use parameterization: JMeter parameterization):

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

SELECT * FROM test where name= ' ${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

Five, Variables names parameter use method:

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.

VI. RESULT variable Name parameter use 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 Simple to use: http://www.cnblogs.com/0201zcr/p/5046193.html

JMeter gets the return data as the next request parameter: http://www.cnblogs.com/0201zcr/p/5089620.html

Source file: Http://pan.baidu.com/s/1mh3Ag5M

Turn JMeter performance test use of JDBC Request (querying database for database data)