Why try to use PreparedStatement instead of statement?

Source: Internet
Author: User



In a JDBC application, if you are already a slightly level developer, you should try to replace statement with PreparedStatement. That is, do not use statement in most cases.
This is based on the following reasons:
I. Readability and maintainability of the code.
Although using PreparedStatement instead of statement will make the code a few more lines, this code can be used in terms of readability and maintainability. It's much higher than the code that goes directly to statement:

Stmt.executeupdate ("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.executeupdate ();

Needless to say, for the first method. Don't tell anyone else to read your code, it will be sad if you read it yourself for some time.

Two. PreparedStatement to maximize performance.


Each database will do its best to provide maximum performance optimizations for precompiled statements. Because the precompiled statement is likely to be called repeatedly. So the execution code that is compiled by the compiler of the DB is cached, so the next call will not need to be compiled as long as it is the same precompiled statement. As long as the parameters are passed directly into the compiled statement execution code (equivalent to a culvert number) it will be executed. This is not to say that only one connection of the precompiled statements executed more than once is cached, but for the entire DB, As long as the precompiled statement syntax matches the cache. At any time, it can be executed without having to compile again. In statement statements, even though the same operation, the chances of matching the entire statement are minimal because of the different data for each operation, which is almost unlikely to match. For example:
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 22 ');
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 23 ');
Even though the same operation is not the same as the data content, the entire statement itself does not match, there is no meaning of the cached statement. The fact is that there is no database that executes code caches after the normal statement is compiled.

Of course not. So the precompiled statements are bound to be cached, and the database itself uses a strategy, such as frequency, to determine when the pre-compiled results are no longer cached. To save more space to store new precompiled statements.

Of course, in theory, the precompiled statement performance should be higher than the normal statement performance, but, at some point, such as the resulting execution plan of your two statements is different, it is possible

A precompiled statement produces a slower execution plan than a normal statement, so we say that high performance refers to the high performance of calls, such as calling the API in a way that has direct local call and remote Call,local calls, which is definitely faster than remote But if the execution logic in the function body is not at all the same, then it is not possible to say that local call is fast. So if there are times when precompiled statements are slow, analyze the execution plan when they are executed on the data side.

In addition, PreparedStatement can perform batch operations continuously, which is also the main means to raise high performance.



Three. The most important point is a significant increase in security.

Even so far, some people don't even know the basic semantics of SQL syntax.
String sql = "SELECT * from Tb_name where name= '" +varname+ "' and passwd= '" +varpasswd+ "'";
If we pass [' or ' 1 ' = ' 1] in as varpasswd. User name feel free to see what will become?

SELECT * from tb_name = ' random ' and passwd = ' or ' 1 ' = ' 1 ';
Because ' 1 ' = ' 1 ' is sure to be true, so you can pass any validation. What's more:
Put [';d rop table tb_name;] Incoming in as VARPASSWD:
SELECT * from tb_name = ' random ' and passwd = ';d rop table tb_name; some databases are not going to make you successful, but there are many databases that can make these statements executable.

And if you use precompiled statements. Anything you pass in will not have any matching relationship with the original statement. As long as you use precompiled statements all the time, you don't have to worry about the incoming data. If you use ordinary statement, you may want to make a decision on the drop,;

The above reasons are not enough for you to use PreparedStatement most of the time?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.