How much do you know about PreparedStatement?

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.

Use of PreparedStatement

// PreparedStatement stm = con. prepareStatement ("insert into TBL_USER values (?,?) ");
// Stm. setString (1, user. getUname ());
// Stm. setString (2, user. getUpass ());
//
// Int count00000000.exe cuteUpdate ();

PreparedStatement stm = con. prepareStatement ("select * from TBL_USER where uname =? And upass =? ");
// Stm. setString (1, user. getUname ());
// Stm. setString (2, user. getUpass ());
// ResultSet rs=cmd.exe cuteQuery ();
// If (rs. next ()){
// U = new User ();
// U. setUid (rs. getInt (1 ));
// U. setUname (rs. getString (2 ));
// U. setUpass (rs. getString (3 ));
//}

PreparedStatement stm = con. prepareStatement ("insert into tbl_fwxx values (?,?,?,?,?,?,?,?,?,?,?) ");
// Stm. setInt (1, fx. getUid ());
// Stm. setInt (2, fx. getJdid ());
// Stm. setInt (3, fx. getLxid ());
// Stm. setInt (4, fx. getShi ());
// Stm. setInt (5, fx. getTing ());
// Stm. setString (6, fx. getFwxx ());
// Stm. setDouble (7, fx. getZj ());
// Stm. setString (8, fx. getTitle ());
// Java. SQL. Date date = new java. SQL. Date (fx. getDate (). getTime ());
// Stm. setDate (9, date );
// Stm. setString (10, fx. getTelephone ());
// Stm. setString (11, fx. getLxr ());
// Int count distinct cmd.exe cuteUpdate ();

PreparedStatement stm = con. prepareStatement ("delete from tbl_fwxx where fwid =" + fwid );
// Count00000000.exe cute... the remaining full text>

What is the difference between preparedStatement and Statement?

In JDBC applications, if you are already a relatively level developer, you should always replace Statement with PreparedStatement. That is to say, do not use Statement at any time.

I. code readability and maintainability.
Although the use of PreparedStatement instead of Statement will lead to several more lines of code, such code is much higher than the use of Statement code in terms of readability and maintainability:

Stmt.exe cuteUpdate ("insert into tb_name (col1, col2, col2, col4) values ('" + var1 + "', '" + var2 + "'," + var3 + ", '"+ var4 + "')");

Perstmt = con. prepareStatement ("insert into tb_name (col1, col2, col2, col4) values (?,?,?,?) ");
Perstmt. setString (1, var1 );
Perstmt. setString (2, var2 );
Perstmt. setString (3, var3 );
Perstmt. setString (4, var4 );
Perstmt.exe cuteUpdate ();

I don't need to talk about it. For the first method, don't let others read your code. It means you will feel sad if you read your code later.

2. PreparedStatement to maximize performance.
Each database tries its best to optimize the performance of precompiled statements. because pre-compiled statements may be called repeatedly. therefore, the Execution Code of statements compiled by the DB compiler is cached. Therefore, if the statements are the same pre-compiled statements in the next call, compilation is not required, you only need to pass the parameters directly into the compiled statement Execution Code (equivalent to a number of Han) and the code will be executed. this does not mean that only the pre-compiled statements executed multiple times in a Connection are cached. Instead, if the pre-compiled statement syntax matches the cache. at any time, you can directly execute it without re-compiling. in statement statements, even if they are the same operation, the chance of matching the entire statement is very small because the data of each operation is different, and it is almost impossible to match the statement. for example:
Insert into tb_name (col1, col2) values ('11', '22 ');
Insert into tb_name (col1, col2) values ('11', '23 ');
Even if the operation is the same but the data content is different, the entire statement itself cannot match and has no significance for the cache statement. the fact is that no database will cache the Execution Code after compilation of common statements. in this way, the input statement must be compiled once every execution.

Of course, not all pre-compiled statements will be cached. The database itself will use a policy, such as the usage frequency, to determine when to stop caching the existing pre-compiled results. to store more space for new pre-compiled statements.

3. The most important thing is to greatly improve security.

Even so far, some people still do not know the basic SQL syntax.
String SQL = "select * from tb_name where name = '" + varname + "' and passwd = '" + varpasswd + "'";
If we take the ['or '1'... the remaining full text>