The difference between statement and PreparedStatement in JDBC

1. Statement each execution of SQL statements, the relevant database to execute the compilation of SQL statements, PreparedStatement is precompiled , using the cache mechanism (precompiled statements, placed in the cache, The next time you execute the same SQL statement, you can remove it directly from the cache, which facilitates SQL to generate the query plan . ), for batch processing can greatly improve efficiency. Also called a JDBC stored procedure.

For example, if you want to execute two SQL statements

SELECT Colume from TABLE WHERE colume=1;
SELECT Colume from TABLE WHERE colume=2;

Generates two execution plans

1000 queries generate 1000 execution plans!

PreparedStatement for reusing execution plans using bound variables

SELECT Colume from TABLE WHERE colume=:x;

The set of different data requires only one execution plan to be generated and can be reused

Whether binding variables are used to affect the system is very large, and generating execution plans is extremely resource-intensive

Two implementation speed gaps may be hundreds of times

The latter uses the PreparedStatement object, whereas the former is a normal statement object. The PreparedStatement object not only contains the SQL statement, but in most cases the statement has been precompiled, so when it executes, only the DBMS runs the SQL statement without having to compile it first. When you need to execute statement objects multiple times, the PreparedStatement object will greatly reduce the run time and, of course, speed up the access to the database .

This conversion also provides you with great convenience, eliminating the need to repeat the syntax of the SQL statement, and simply changing the value of the variable in it to re-execute the SQL statement. The choice of the PreparedStatement object is whether the SQL statement with the same syntax executes multiple times, and the difference between the two times is only the difference between variables. if executed only once, when the database is only a one-time access, with the Statement object for processing, the PreparedStatement object is more expensive than Statement, for a one-time operation does not bring additional benefits.

2. The SQL statements executed in the preparestatement can be parameterized , that is, variables can be replaced and used as much as possible. the code to pass parameters, increase the readability of the codes and can be pre-compiled to accelerate, and statement not.

3. prevent SQL injection . When a keyword that contains special characters or SQL (such as ' or 1 or ') is included in SQL, statement will have unpredictable results (an exception occurred or the result of the query is incorrect) and can be resolved by PreparedStatement.

SQL injection or SQL injection attack is to exploit the vulnerability of statement, such as with a user login, then the form form has a user name and password then when I submit, in the User name input box, enter "AAA" or ' a ' = ' a "Password box casually input, then this means The query language of SQL is "select * from table where user name = ' aaa ' or ' a ' = ' a ' and password = ' 123 '", so that all data is queried or is chaotic. Then the unauthorized user can still log in, it is not black?! In fact, Java programmers do not have this way to write queries, generally use PreparedStatement to query, or simply use Hibernate and other persistent layer framework, so that through SQL injection can not talk about.

The difference between statement and PreparedStatement in JDBC