The difference between statement and PreparedStatement in JDBC

Take Oracle for 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;
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

1.PreparedStatement is precompiled and can greatly improve efficiency for batch processing. Also known as JDBC stored procedures

2. Use the Statement object. When only one-time access to the database is performed, the Statement object is used for processing. The overhead of PreparedStatement objects is larger than statement, and there is no additional benefit for one-time operations.

3.statement each execution of SQL statements, the relevant database to execute SQL statement compilation, PreparedStatement is precompiled, PreparedStatement support batch processing

4.
Code Fragment 1:

[Java]View Plaincopy

1. String updatestring = "UPDATE coffees SET SALES =" + "Wherecof_name like′colombian′";
2. Stmt.executeupdate (updatestring);

Code Fragment 2:

[Java]View Plaincopy

1. PreparedStatement updatesales = con.preparestatement ("UPDATE coffees setsales =?") WHERE cof_name like?  ");
2. Updatesales.setint (1, 75);
3. Updatesales.setstring (2, "Colombian");
4. Updatesales.executeupdate ();

The difference between fragment 2 and fragment 1 is that the latter uses the PreparedStatement object, whereas the former is a normal statement object. The PreparedStatement object contains not only the SQL statements,And most of the time this 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 it is executed only once, it should be no different from the ordinary object, and it cannot demonstrate the superiority of its precompilation.
5. The JDBC program that executes many SQL statements produces a large number of statement and PreparedStatement objects. PreparedStatement objects are generally considered more efficient than statement objects, especially if the same SQL statement with different parameters is executed multiple times. The PreparedStatement object allows the database to precompile SQL statements, which saves time and increases the readability of the code in subsequent runs.

However, in an Oracle environment, developers actually have greater flexibility. When using statement or PreparedStatement objects, the Oracle database caches SQL statements for later use. In some cases, executing a PreparedStatement object can actually take longer because the drive itself requires additional processing and increased network activity between the Java application and the Oracle server.

However, in addition to buffering, there is at least one better reason why we prefer to use PreparedStatement objects in enterprise applications, which is security. The arguments passed to the PreparedStatement object can be coerced into type conversion, allowing the developer to ensure that the data is inserted or queried to match the underlying database format.

When dealing with data coming from users on a public Web site, the issue of security becomes extremely important. String arguments passed to PreparedStatement are automatically ignored by the drive. In the simplest case, this means that when your program tries to insert the string "D ' Angelo" into VARCHAR2, the statement will not recognize the first "," resulting in a tragic failure. It is rarely necessary to create your own string-ignoring code.

In a Web environment, malicious users exploit applications that are not well-designed to handle strings correctly. In particular, on public Web sites, all user input should not be passed to the SQL statement without first processing through the PreparedStatement object. In addition, the SQL statement should not be displayed where the user has the opportunity to modify the SQL statement, such as the hidden area of the HTML or a query string.
When executing SQL commands, we have two choices: you can use the PreparedStatement object, or you can use the statement object. No matter how many times the same SQL command is used, PreparedStatement only parses and compiles it once. When a statement object is used, it is parsed and compiled each time an SQL command is executed.

First:
Preparestatement Initializes SQL first, submits the SQL to the database for preprocessing, and uses multiple times to improve efficiency.
Createstatement does not initialize, no preprocessing, no time is starting from 0 to execute SQL

Second:
Preparestatement can replace variables
Can be included in the SQL statement, can be used Ps=conn.preparestatement ("select* from Cust where id=?");
int sid=1001;
Ps.setint (1, SID);
rs = Ps.executequery ();
You can replace them with variables.
And statement can only use
int sid=1001;
Statement stmt = Conn.createstatement ();
ResultSet rs = stmt.executequery ("select * from Cust where id=" +sid);
To achieve.

Third:
Preparestatement Initializes SQL first, submits the SQL to the database for preprocessing, and uses multiple times to improve efficiency.
Createstatement does not initialize, no preprocessing, no time is starting from 0 to execute SQL

The difference between statement and PreparedStatement in JDBC