How much do you know about preparedstatement?

Preface

I believe everyone is familiar with the preparedstatement. Why should I use preparedstatement? Maybe you will answer preparedstatement as a pre-processing statement, which can improve the database execution efficiency. You may also want to use preparedstatement to prevent SQL injection. Then, do you think you have enough knowledge about preparedstatement? Is it correct for preparedstatement in the project?

Principle Analysis

First, let's take a look at the statement and preparedstatement execution processes. during the execution of an SQL statement, we will go through these steps:

1. Transmit SQL to the database

2. Verify the database and parse the SQL

3. Calculate the access plan. The database checks the index and statistics to provide the optimal access plan.

4. search based on the access plan and return data.

In the above steps, step 1 is very time-consuming. Therefore, to improve performance, the database caches execution statements and its access plan. This is called statement cache. In statement cache, the SQL statement itself is the key, and the access plan is the value. When the same SQL statement is sent, the database uses the access plan in the cache to save CPU time.

Next, let's take a look at the statement Execution Code:

Statement statement = connection.createStatement();String sql1="Select * from test where id=1";String sql2="Select * from test where id=";statement.execute(sql1);statement.execute(sql1);statement.execute(sql1);statement.execute(sql2+"2");statement.execute(sql2+"3");

When sql1 is executed for the first time, the execution plan needs to be calculated. However, the cached execution plan will be used for 2nd and 3 executions. Therefore, sql1 will not re-examine the syntax and computing execution plan, which is more efficient than the first execution plan.

Sql2 changes every time. In the cache, the key is the entire SQL statement, so sql2 cannot hit the cache every time, even if it only has different parameters, it is also necessary to re-test the syntax and computing execution plan, and the efficiency is naturally low.

Powerful databases optimize cache hits, but complex statements cannot be avoided.

Preparedstatement exists to avoid the disadvantage of sql2. See the following code.

String sql2="Select * from test where id=?";PreparedStatement pstmt = connection.prepareStatement(sql2);pstmt.setInt(1,2);pstmt.executQuery();pstmt.setInt(1,3);pstmt.executQuery();

When preparedstatement is created, it sends parameterized statements to the database for syntax detection and execution plan calculation. The key in the cache will be a parameterized statement. When the subsequent preparedstatement is executed, it hits the cache each time and uses an existing access plan for retrieval.

How to use it correctly

The life cycle of preparedstatement is the same as that of statement and is valid within the connection range of a database connection. Therefore, if one connection processes the same preparedstatement multiple times (with different parameters ), using preparedstatement can improve efficiency, but most scenarios use multiple connections to process the same preparedstatement. Therefore, even if preparedstatement is used, the efficiency cannot be improved, the lifecycle of preparedstatement is only in connection. So how can we use preparedstatement correctly?

In fact, you don't need to worry about it. The good news is that the connection pool manager of the J2EE server has implemented cache usage. The J2EE server maintains the prepared statement list prepared for each connection in the connection pool. When preparedstatement is called on a connection, the application server checks whether the statement has been prepared. If yes, the preparedstatement will be returned to the application. If no, the call will be transferred to the JDBC driver and the newly generated statement object will be stored in the connection cache.

What if the project does not use the database connection pool? Here, we can only tell you how it works. You can implement it by yourself.

How much do you know about preparedstatement?