JMeter (11) JDBC request Query Type

At work, you encounter a problem like this:

10W test data needs to be prepared, using the JDBC request in JMeter to bulk insert the data into the database (as long as the primary key is not duplicated, using random in the function Assistant to randomly insert the five digits of the ID of the primary key),

Response Data error: Can not issue data manipulation statements with ExecuteQuery (). After reviewing a lot of data, it was found to be related to the type selection of query type in JDBC request;

Finally, the conclusion is: if the SQL statement is update, insert, and other updated statements, you should use the statement execute () method, if you use Statement executequery () will be error.

PS: Previous blog has a brief introduction to how JDBC request is used, Portal:http://www.cnblogs.com/imyalost/p/5947193.html

The following is mainly about the Query Typein the JDBC Request for JMeter:

The JDBC request interface is as follows:

where query type (SQL statement type) contains 10 types, each with a different effect, as described below.

1. Select statement

This is a type of query statement, and if the query content in the JDBC request is a query statement, select this type.

PS: Multiple query statements (without parameters) can be executed sequentially, the query type is set to: callable Statement; If query type is: Select Statement, only the first SELECT statement is executed.

2. Update statement

This is an UPDATE statement type (including insert and update), and if the query content in the JDBC request is an UPDATE statement, select this type.

PS: If multiple UPDATE statements are written under this type, only the first one is executed (for the same reason, as described below).

3, callable statement

This is a callable statement type, and CallableStatement provides a way for all DBMS to invoke stored procedures in a standard form. Stored procedures are stored in the database, and the call to the stored procedure is the content contained in the CallableStatement object.

This invocation is written in a code-changing syntax, in two forms: one form with the result parameter, the other without the result parameter, and the result parameter is an output (out) parameter, which is the return value of the stored procedure.

Both forms can have a variable number of input (in parameters), output (out parameter), or input and output (INOUT parameter) parameters, and the question mark will be used as a placeholder for the parameter.

The syntax for invoking stored procedures in JDBC is as follows. Note that the square brackets indicate that the contents are optional, and that the square brackets themselves are not part of the syntax.

{Call Procedure name [(?,?, ...)]}, the syntax for the procedure that returns the result parameter is: {? = call Procedure name [(?,?, ...)]} ；

A stored procedure with no parameters is syntactically similar: {Call Procedure name}.

Refer to this article for more detailed use:http://blog.csdn.net/imust_can/article/details/6989954

4. Prepared SELECT statement

statement is used to generate an execution plan for an SQL statement (an SQL statement generates an execution plan, multiple execution plans are generated, which is why SELECT statement only executes the first SELECT statement), If the SQL statement is executed only once, statement is the best type;

Prepared statement is used for binding variable reuse execution plans, Prepared statement is undoubtedly the best type for SQL statements that are executed more than once (the use of bound variables is very significant to the system, and the generation of execution plans consumes resources two implementations The speed gap may be hundreds of times);

The first execution consumption of ps:preparedstatement is very high. Its performance is reflected in the subsequent repeated executions.

for a more detailed explanation, please refer to this article: http://blog.csdn.net/jiangwei0910410003/article/details/26143977

5. Prepared UPDATE statement

The usage of Prepared UPDATE statement and Prepared SELECT statement is very similar and can be referenced in the fourth type.

6. Commit

The commit means to write the results of an unsaved SQL statement to a database table, and in JMeter's JDBC request, you can select this type of query depending on the usage.

7, Rollback

Rollback refers to the process of revoking the specified SQL statement, and in the JMeter JDBC request, the same type can be used as needed.

8, autocommit (FALSE)

The MySQL default mode of operation is autocommit auto-commit mode. This means that unless a transaction is explicitly started, each SQL statement is automatically executed as a separate transaction; we can change whether the autocommit mode is automatically submitted by setting the value of the autocommit;

While autocommit (false) means autocommit (false), the user operation will remain in a transaction until a commit commit or ROLLBACK statement is executed to end the current transaction and start a new transaction again.

9, autocommit (True)

The effect of this option is the opposite of the previous one, that is, in either case, Autocommit writes the result, ending the current transaction and starting the next transaction.

10. Edit (${})

The SQL statement in the JDBC request in JMeter cannot be used, for example: SELECT * from ${table_name} is invalid.

If you need to implement multiple different users at the same time using different sql sql statement parameterized to implement, that is: the sql statement placed in csv  file, and then in JDBC request  query  ${sql_statement} 

Note: the following seven items related to the database transaction control and other knowledge points, if there is no understanding of the place please self-query relevant knowledge, or wait a few days, I will database transaction management and other knowledge published, for reference ...

JMeter (11) JDBC request Query Type