The difference between Preparedstatement and statement

The difference between Preparedstatement and statement

1. Preparedstatement is precompiled, which can greatly improve the efficiency for batch processing Also called 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. Preparedstatement objects are more expensive than statements and do not bring additional benefits for one-time operations.

3. Statement each time an SQL statement is executed, the relevant database must compile the SQL statement. The Preparedstatement is precompiled, and the Preparedstatement supports batch processing

4、Code Fragment 1: www.2cto. com String updateString = "UPDATE COFFEES SET SALES = 75 " + "WHERE COF_NAME LIKE ′Colombian′"; stmt. executeUpdate(updateString); Code Fragment

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

The difference between fragment 2 and fragment 1 is that the latter uses the Preparedstatement object, while the former is an ordinary statement object. The Preparedstatement object not only contains the SQL statement, but also has been precompiled in most cases. Therefore, when it is executed, only the DBMS needs to run the SQL statement without compiling first. When you need to execute the statement object many times, the Preparedstatement object will greatly reduce the running time and, of course, speed up the access to the database.

This conversion also brings you great convenience. You don't have to repeat the syntax of the SQL statement, but you can re execute the SQL statement by changing the value of the variable. Whether the Preparedstatement object is selected depends on whether the SQL statement with the same syntax has been executed multiple times, and the difference between the two times is only the difference of variables.

If it is executed only once, it should be no different from ordinary objects, which does not reflect its precompiled advantages.

5. JDBC programs that execute many SQL statements produce a large number of statement and Preparedstatement objects. It is generally considered that Preparedstatement object is more effective than statement object, especially if the same SQL statement with different parameters is executed multiple times. The Preparedstatement object allows the database to precompile SQL statements, which can save time and increase the readability of the code in subsequent runs. However, in an Oracle environment, developers actually have more flexibility. When a statement or Preparedstatement object is used, the Oracle database caches SQL statements for later use. In some cases, the Preparedstatement object actually takes longer to execute due to the additional processing required by the drive itself and the increased network activity between the Java application and the Oracle server. However, in addition to the buffering problem, there is at least one better reason why we prefer to use Preparedstatement objects in enterprise applications, that is, security. The parameters passed to the Preparedstatement object can be forced to be typed, enabling developers to ensure that they match the underlying database format when inserting or querying data. When dealing with data from users on public web sites, the problem of security becomes extremely important. String parameters 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 tragic failure. There is little need to create your own string ignore code. In the web environment, malicious users will take advantage of applications that are not well designed and can not handle strings correctly. Especially on public web sites, all user input should not be passed to SQL statements without first processing through the Preparedstatement object. In addition, where the user has the opportunity to modify the SQL statement, such as the hidden area of HTML or a query string, the SQL statement should not be displayed.

6. Generally speaking, I will get used to using Preparedstatement to operate on the database. Preparedstatement is a precompiled method, which is more efficient when executing SQL statements. In addition, Preparedstatement can better avoid SQL injection problems; When splicing SQL statements, using Preparedstatement can effectively reduce the probability of errors; Preparedstatement is a subclass of statement. Naturally, Preparedstatement extends statement to a certain extent and improves its performance.