in JDBC applications, if you are already a slightly horizontal developer, you should try to replace statement with PreparedStatement. That is to say, do not use statement for most of the time.
It is based on the following reasons:
I. Code readability and maintainability.
While using PreparedStatement instead of statement can make the code a few more lines, such code is both readable and maintainable. It's a lot higher than the code that uses statement directly:
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 ();
I don't need to say much about the first method. Don't say someone else is going to read your code, and you'll feel sad when you read it yourself for a while.
Two. PreparedStatement to improve performance as best as possible.
Each database will do its best to provide maximum performance optimization for precompiled statements. Because precompiled statements can be repeatedly called. So the statement is cached after the compiled code of the DB compiler, so that the next call, as long as the same precompiled statement does not need to compile, It is executed as long as the parameters are passed directly into the compiled statement execution code (equivalent to a number of numbers). This is not to say that only a precompiled statement that executes multiple times in a connection is cached, but for the entire DB, Whenever the precompiled statement syntax matches the cache. Then you can execute it without recompiling at any time. In statement statements, even the same operation, and because of the different data for each operation, the chances of matching the entire statement are very small and almost impossible to match. For example:
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 22 ');
Insert into Tb_name (col1,col2) VALUES (' 11 ', ' 23 ');
Even if the same operation but because the data content is different, so the whole statement itself can not match, do not have the meaning of cached statements. The fact is that no database is cached for the execution code compiled by the normal statement.
Of course not. The precompiled statements are bound to be cached, and the database itself uses a strategy, such as frequency, to determine when the existing precompiled results will no longer be cached. To save more space to store the 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 your two statements produced by the implementation plan is different, it is possible
A precompiled statement produces an execution plan that is slower than a normal statement, so we say that high performance refers to the high performance of the invocation method, such as invoking the API method with direct local calls and remote Call,local call is definitely faster than remote call. But if the execution logic in the function body is not at all, then it is not possible to say that local call is fast. So if the precompiled statements are sometimes slow, analyze their execution plans when they execute on the data side.
In addition, PreparedStatement can perform batch operations continuously, which is the main means of performance.
Three. The most important point is a significant increase in security.
Even so far, there are people who don't even know the basic semantics of the SQL syntax.
String sql = "SELECT * from Tb_name where name= '" +varname+ "' and passwd= '" +varpasswd+ "";
If we enter [' or ' 1 ' = ' 1] as a varpasswd. User name casually, see what will become?
SELECT * from tb_name = ' random ' and passwd = ' or ' 1 ' = ' 1 ';
Because ' 1 ' = ' 1 ' is definitely set up, so can any pass validation. What's worse:
Put [';d rop table tb_name;] Passed in as a varpasswd, then:
SELECT * from tb_name = ' random ' and passwd = ';d rop table tb_name; some databases won't make you successful, but there are a lot of databases that can get these statements executed.
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, you don't have to do anything about the incoming data. And if you use ordinary statement, you might want to make a few decisions and worry about the drop.
Are there several reasons why it is not enough for you to use PreparedStatement most of the time?