Statement and preparedstatement Applicability

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.

For the following reasons:

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 pass in ['or '1' = '1] As varpasswd, the user name is random and you can see what it will become?

Select * From tb_name where name = 'random 'and passwd = ''or '1' = '1 ';

Because '1' = '1' must be true, any verification can be performed. What's more:

Input ['; drop table tb_name;] As varpasswd. Then:

Select * From tb_name = 'random 'and passwd = ''; drop table tb_name; some databases won't let you succeed, but many databases can execute these statements.

If you use precompiled statements. nothing you input will match the original statement. (the premise is that the database itself supports pre-compilation, but there may not be any server-side databases that do not support Compilation. There are only a few desktop databases that are accessed directly by files) as long as pre-compiled statements are fully used, you do not need to worry about the incoming data. however, if you use a common statement, you may need to make painstaking judgment and worry over the drop,; and so on.

Why are you still using preparedstatement at any time?