Differences between Statement and PreparedStatement in JDBC

Take Oracle as an Example

Statement generates an execution plan for an SQL Statement,
If you want to execute two SQL statements
Select colume from table where colume = 1;
Select colume from table where colume = 2;
Two execution plans are generated.
One thousand execution plans are generated for one thousand queries!

PreparedStatement is used to reuse an execution plan using Bound variables.
Select colume from table where colume =: x;
You can use the set data to generate an execution plan only once and reuse it.

Whether to bind variables has a great impact on the system. resource consumption is extremely high when an execution plan is generated.
The speed difference between the two implementations may be several hundred times

1. PreparedStatement is pre-compiled, which greatly improves the efficiency of batch processing. It is also called the JDBC stored procedure.

2. Use the Statement object. When only one-time access is performed to the database, the Statement object is used for processing. The overhead of the PreparedStatement object is greater than that of the Statement object, which does not bring additional benefits to one-time operations.

3. Each time statement executes an SQL statement, the relevant database must compile the SQL statement. preparedstatement is pre-compiled, and preparedstatement supports batch processing.

4.
Code Fragment 1:

String updateString = "UPDATE COFFEES SET SALES = 75 " + "WHERECOF_NAME LIKE ′Colombian′";stmt.executeUpdate(updateString);

Code Fragment 2:

PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SETSALES = ? WHERE COF_NAME LIKE ? ");updateSales.setInt(1, 75);updateSales.setString(2, "Colombian");updateSales.executeUpdate();

The difference between Segment 2 and segment 1 is that the latter uses the PreparedStatement object, while the former is a common Statement object. The PreparedStatement object not only contains the SQL statement, but also has been pre-compiled in most cases. Therefore, when executing the statement, you only need to run the SQL statement in the DBMS instead of compiling it first. When you need to execute the Statement object multiple times, the PreparedStatement object will greatly reduce the running time and, of course, speed up database access.
This conversion also brings you great convenience. You do not have to repeat the syntax of SQL statements. Instead, you only need to change the value of the variable to execute the SQL statement again. Whether the PreparedStatement object is selected depends on whether the SQL statements with the same syntax are executed multiple times, and the difference between the two statements is only the difference between variables. If it is executed only once, it should be no different from ordinary objects, and it does not reflect the superiority of its pre-compilation.

5. JDBC programs that execute many SQL statements generate a large number of Statement and PreparedStatement objects. The PreparedStatement object is generally considered to be more effective than the Statement object, especially when the same SQL Statement with different parameters is executed multiple times. The PreparedStatement object allows the database to pre-compile SQL statements, saving time and increasing code readability in subsequent operations.

However, in the Oracle environment, developers are actually more flexible. When Statement or PreparedStatement object is used, the Oracle database caches SQL statements for later use. In some cases, execution of the PreparedStatement object takes a longer time because the drive itself requires additional processing and added network activity between the Java application and the Oracle server.

However, in addition to buffering, there is at least one better reason for us to prefer PreparedStatement objects in enterprise applications, that is, security. Parameters passed to the PreparedStatement object can be forcibly converted, so that developers can ensure that they match the underlying database format when inserting or querying data.

When processing data from users on a public Web site, security issues become extremely important. The string parameter passed to PreparedStatement is automatically ignored by the drive. In the simplest case, this means that when your program tries to insert the string "D 'Angelo" into VARCHAR2, this statement will not recognize the first ",", this leads to a miserable failure. There is almost no need to create your own strings to ignore the code.

In the Web environment, malicious users use applications that are poorly designed and cannot correctly process strings. Especially on public websites, all user input should not be passed to SQL statements without PreparedStatement object processing. In addition, SQL statements should not be displayed when users have the opportunity to modify SQL statements, such as HTML hidden areas or a query string.
When executing SQL commands, we have two options: You can use the PreparedStatement object or the Statement object. No matter how many times you use the same SQL command, PreparedStatement only parses and compiles it once. When a Statement object is used, it is parsed and compiled every time an SQL command is executed.

First:
PrepareStatement initializes the SQL statement and submits the SQL statement to the database for preprocessing. It can be used multiple times to improve efficiency.
CreateStatement will not be initialized, no preprocessing, and SQL will be executed from 0 once

Second:
PrepareStatement can replace variables
Which of the following SQL statements can contain ?, Ps = conn. prepareStatement ("select * from Cust where ID =? ");
Int sid = 1001;
Ps. setInt (1, sid );
Rs = ps.exe cuteQuery ();
Can you set? Replace it with a variable.
Statement can only be used
Int sid = 1001;
Statement stmt = conn. createStatement ();
ResultSet rs = stmt.exe cuteQuery ("select * from Cust where ID =" + sid );
.

Third:
PrepareStatement initializes the SQL statement and submits the SQL statement to the database for preprocessing. It can be used multiple times to improve efficiency.
CreateStatement will not be initialized, no preprocessing, and SQL will be executed from 0 once