Oracle pre-compiled SQL statement Processing

PreparedStatement is an interface in java. SQL. It is a subinterface of Statement. When you use the Statement object to execute an SQL Statement

PreparedStatement is an interface in java. SQL. It is a subinterface of Statement. When you use the Statement object to execute an SQL Statement

I. Pre-compiled SQL statement Processing

PreparedStatement is an interface in java. SQL. It is a subinterface of Statement. When you use the Statement object to execute an SQL Statement, you must send the SQL Statement to the DBMS. The DBMS compiles the Statement before executing it. Different from Statement, a pre-compiled Statement specifies an SQL Statement when creating a PreparedStatement object. This Statement is immediately sent to the DBMS for compilation. When the compiled statement is executed, the DBMS directly runs the compiled SQL statement without first compiling it like other SQL statements.The processing performance of pre-compiled SQL statements is slightly higher than that of common variables.

Ii. Functions of precompiled statements

· Improved efficiency: When data needs to be inserted, updated, or deleted, the program will send the entire SQL statement to the database for processing and execution. When a database processes an SQL statement, it needs to parse the SQL statement, check the syntax and semantics, and generate the code. Generally, the processing time is longer than the execution time. When a pre-compiled statement is created, the specified SQL statement is sent to the DBMS to complete parsing, checking, and compiling. Therefore, when an SQL statement needs to be executed multiple times, pre-compiled statements can reduce processing time and improve execution efficiency.

· Enhanced security: If malicious SQL statements exist: string SQL = "select * from t_student where name = '" + varname + "' and passwd = '" + varpasswd + "'"; if we pass "or '1' = '1'" as varpasswd. the username is random and you can see what it will become?
Select * from t_student = 'free' and passwd = ''or '1' = '1'; Because '1' = '1' must be true, any verification can be performed. what's more: Pass in "'; drop table t_student;" 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 a precompiled statement is used. NO content passed in will match the original statement. 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.

Iii. Use of precompiled statements

1. Create a PreparedStatement object
The following code snippet (where conn is the Connection object) creates a PreparedStatement object that contains four IN parameter placeholders for an SQL statement:

String SQL = "insert into t_customer values (?,?,?,?) "; // Organize an SQL statement containing parameters
PreparedStatement ps = conn. prepareStatement (SQL );
The ps object contains the statement insert into t_customer values (?,?,?,?), It has been sent to the DBMS and is ready for execution.
2. Pass IN Parameters
Before executing the PreparedStatement object, you must set each? Parameter value. This can be done by calling the setXXX method, where XXX is the type corresponding to this parameter. For example, if the parameter has the Java type long, the method used is setLong. The first parameter of the setXXX method is the ordinal position of the parameter to be set, and the second parameter is the value set to this parameter. For example, the following code sets the first parameter as the input account, and the second parameter as password:
Ps. setString (1, account );
Ps. setString (2, password );
Once the parameter value of a given statement is set, its value is retained until it is set as a new value or cleared by calling the clearParameters () method. The Code is as follows: