In real life, there are many situations when you have to connect to a database using Jmeter for example: load testing of your database, manipulating (insert, update, delete) data in database during your load test or just fetching some data from database that will be passed to subsequent samplers. To achieve this you would require following elements to be added to your test plan:
- JDBC Connection configuration
- JDBC Request sampler
JDBC Connection configuration
Creates a database connection as per supplied connection settings, this connection will be used by JDBC Request Sampler
Variable name: The name of the variable the connection is tied to. Multiple connections can be used in a test plan but variable name must be different for each connection, this helps JDBC Samplers to select the appropriate connection.
Max Number of Connections: Defines maximum number of connections allowed in the pool. 0 means that each thread will get its own pool with a single connection in it, i.e. the connections are not shared between threads. If you want to use shared pooling, then set the max count to the same as the number of threads to ensure threads don’t wait on each other
Different databases and JDBC drivers require different JDBC settings. The Database URL and JDBC Driver class are defined by the provider of the JDBC implementation. In this example we are connecting to MSSQL database. For other database connection settings, please refer to: http://jmeter.apache.org/usermanual/component_reference.html#JDBC_Connection_Configuration
Database URL: JDBC Connection string for the database. jdbc:sqlserver://<host>;<port>;DatabaseName=<dbname>;
JDBC Driver class: Fully qualified name of driver class. com.microsoft.sqlserver.jdbc.SQLServerDriver
Username: Username to connect to database
Password: Password to connect to database
JDBC Request
JDBC sampler lets you send a JDBC Request (an SQL query) to a database as per your configured JDBC Connection.
Variable name: Name of the Jmeter variable that the connection pool is bound to. This must agree with the ‘Variable Name’ field of a JDBC Connection Configuration.
Query Type: Set this according to the statement type:
- Select Statement
- Update Statement – use this for Inserts and Deletes as well
- Callable Statement
- Prepared Select Statement
- Prepared Update Statement – use this for Inserts and Deletes as well
- Commit
- Rollback
- Autocommit (false)
- Autocommit (true)
- Edit – this should be a variable reference that evaluates to one of the above
SQL Query: Allow you to write the actual SQL queries for example:
- select * from <tablename> where id=<id>
- insert into <tablename> (column1,column2,…) VALUES (value1,value2,…)
- update <tablename> set column1=value where <id>=<id>
Parameter values: Comma-separated list of parameter values
Parameter types: Comma-separated list of SQL parameter types (e.g. INTEGER, DATE, VARCHAR, DOUBLE)
Variable Names: Comma-separated list of variable names to hold values returned by Select statements, Prepared Select Statements or CallableStatement.
Result Variable Name: If specified, this will create an Object variable containing a list of row maps. Each map contains the column name as the key and the column data as the value
For more details please refer to: http://jmeter.apache.org/usermanual/component_reference.html#JDBC_Request
Create your JDBC test plan
- Download required sql jdbc driver and put jar file in jmeter/lib directory
- Add a JDBC config element “JDBC Connection Configuration” to your test plan and define your database connection settings (database url, driver class, username and password)
- Add a JDBC request sampler in a thread group and define
- Variable name which must match to variable name given in JDBC connection config.
- Write your sql query and choose Query Type accordingly.
