Comparison of Jdbc-statement,preparestatement,callablestatement

Reference: https://www.cnblogs.com/Lxiaojiang/p/6708570.html

The JDBC Core API provides three classes for sending SQL statements to a database:

• Statement: Created with createstatement ();
• PreparedStatement: SQL statements that are precompiled and stored in the PreparedStatement object are created using the Preparestatement () method.
• CallableStatement: Used to execute SQL stored procedures, created using the Preparecall () method.

　　　　　1. Statement objects

The statement object is used to execute static SQL statements and obtain SQL-generated results. Defines three ways to execute SQL statements to handle SQL commands that return different results:

• Executeupdate (String SQL): Executes a SQL insert,update or DELETE statement that returns the number of rows affected or 0;
  The return value is of type int
• ExecuteQuery (String SQL): Executes an SQL statement that returns a single resultset;
  return type resultset
• Execute (String SQL): Executes an SQL statement that can return multiple results.
  The return type is Boolean and returns True if the number of updates is returned, False if ResultSet is returned.

  2. PreparedStatement statements

  PreparedStatement is an interface under the JAVA.SQL package that executes SQL statement queries that can be obtained preparedstatment objects by invoking the Connection.preparedstatement (SQL) method.

  The database system pre-compiles the SQL statement (if supported by the JDBC driver), and the preprocessed statement is pre-compiled, and this precompiled SQL query can be reused in future queries, which makes it faster than the query generated by the statement object.

  PreparedStatement is simply a precompiled statement. You can use placeholders.

•  Public classPreparedstmtexample { Public Static voidMain (String args[])throwsSQLException {Connection conn= Drivermanager.getconnection ("mysql:\\localhost:1520", "root", "root"); PreparedStatement prestatement= Conn.preparestatement ("SELECT distinct loan_type from loan where bank=?")); Prestatement.setstring (1, "Citibank"); ResultSet result=Prestatement.executequery ();  while(Result.next ()) {System.out.println ("Loan Type:" + result.getstring ("Loan_type")); }           }} 

  In this example, if the same query is used with PreparedStatement, even if the parameter value is different, for example: "Standard chated" or "HSBC" as the parameter value, The database system will still call the previous compiler to compile the execution of the statement (System library system for the first time the query statement to do the maximum performance optimization).

  The default is to return a result set of type type_forward_only (ResultSet), but you can also return different types of result sets using the overloaded methods of Preparedstatment ().

  III. Advantages of Preprocessing statements

  PreparedStatement provides a number of benefits, enterprise-level application development is strongly recommended to use PreparedStatement to do SQL queries, listed below PreparedStatement several advantages.

  1) PreparedStatement can write dynamically parameterized queries
• With PreparedStatement you can write SQL query statements with parameters, by using the same SQL statement and different parameter values to make a query than to create a different query statement better, here is a parameterized query:

  SELECT interest_rate from loan WHERE loan_type=?

  Now you can use any kind of loan type such as: "Personal loan", "home loan" or "gold loan" to query, this example is called parameterized query, because it can be called with different parameters, here's "?" is the placeholder for the parameter.

  2) PreparedStatement faster than Statement

  One of the most important benefits of using PreparedStatement is that it has a better performance advantage, and SQL statements are precompiled in the database system. The execution plan is also cached, which allows the database to make parameterized queries. Using a preprocessing statement is faster than a normal query because it does less work (database parsing of SQL statements, compilation, optimization already done before the first query). In order to reduce the load on the database, you should always use PreparedStatement for the German JDBC code in the production environment. It is important to note that in order to gain a performance advantage, you should use parameterized SQL queries instead of string append methods. With the following two select queries, the first select query has no performance benefits.

• SQL Query 1: preparedstatemen of string Append form

  String Loantype == conn.preparestatement ("Select banks from loan where loan_type=" + loantype);

  SQL Query 2: PreparedStatement with parameterized queries

  PreparedStatement prestmt = conn.preparestatement ("Select banks from loan where loan_type=?") );p restmt.setstring (1,loantype);

  The second query is the correct use of the PreparedStatement query, which can achieve better performance than SQL1.

  3) PreparedStatement can prevent SQL injection attacks if you are developing Java Web applications, you must be familiar with the infamous SQL injection attack. Sony suffered a SQL injection attack last year, stealing data from a number of Sony play station (PS) users. In SQL injection attacks, malicious users enter through SQL metadata binding, such as: A Web site's login verification SQL query code:

  strSQL = "SELECT * from users WHERE name = '" + userName + "' and pw = '" + PassWord + "';"

  Malicious filling in:

  UserName = "1 ' or ' 1 ' = ' 1"= "1 ' or ' 1 ' = ' 1";

  Then the final SQL statement becomes:

  strSQL = "SELECT * from users WHERE name = ' 1 ' or ' 1 ' = ' 1 ' and pw = ' 1 ' or ' 1 ' = ' 1 ';"

  Because where conditions are constant, this is equivalent to executing:

  strSQL = "SELECT * from Users;"

  So you can access the website without an account password. If a malicious user is worse, the user fills in:

  PassWord = "1 ' OR ' 1 ' = ' 1";D rop TABLE USERS;

  The SQL statement becomes:

  strSQL = "SELECT * from users WHERE name = ' Any_value ' and pw = '; DROP TABLE Users "

  This way, the data tables are deleted, although they are not logged in.
  
  However, a parameterized query using PreparedStatement can block most SQL injections. In the case of parameterized queries, the database system (EG:MYSQL) does not treat the contents of the parameters as part of the SQL instruction, but only runs the parameters after the database has completed compiling the SQL instructions, so that even if the parameters contain destructive instructions, they will not be run by the database.

• add: The second way to avoid SQL injection:
  When combining the SQL string, first replace the passed-in parameter with the single quote character (2 consecutive single quote characters, because 2 single quote characters in the SQL database are treated as a single quote character in the character, for example:

  strSQL = "SELECT * from users WHERE name = '" + userName + "';"

  Incoming string:

  UserName  = "1 ' OR 1=1"

  Replace the username with the following characters:

  UserName = "1" OR 1=1 "

  The last SQL query statement generated is:

  strSQL = "SELECT * from users WHERE name = ' 1" OR 1=1 '

  The PreparedStatement query is more readable and more secure than a messy string appended to the query.

  Iv. Limitations of PreparedStatement

  
  Although PreparedStatement is very practical, it still has some limitations.
  1. To prevent SQL injection attacks, PreparedStatement does not allow a placeholder (? There are multiple values, and this problem becomes tricky when you execute a query with a **in** clause.

  Summary of the summary of the five, do not calculate

  About the PreparedStatement interface, it is important to remember that:
      1. PreparedStatement can write parameterized queries, which can achieve better performance than statement.
      2. For PreparedStatement, the database can use a compiled and well-defined execution plan, which is faster than a normal query.
      3. PreparedStatement can block common SQL injection attacks.
      4. PreparedStatement can write a dynamic query statement
      5. PreparedStatement is associated with the Java.sql.Connection object, and once you close the connection,preparedstatement you can't use it.
      6. "?" is called a placeholder.
      7. PreparedStatement query returns forward_only resultset by default, you can only move a cursor in one direction to the result set. Of course you can also set other types of values such as: "Concur_read_only".
      8. JDBC driver that does not support precompiled SQL queries, and when calling Connection.preparestatement (SQL), it does not send SQL query statements to the database for preprocessing. Instead, it waits for the query to be executed (when the ExecuteQuery () method is called) to send the query to a database, which is the same as using statement. The index position of the
      9. Placeholder is starting at 1 instead of 0, and filling in 0 results in a *java.sql.sqlexception invalid column index* exception. So if PreparedStatement has two placeholders, then the index of the first parameter is 1, and the second parameter is indexed at 2.

  These are all the reasons why you should use PreparedStatement, but you can still use the statement object to do the testing. But in the production environment you must consider using preparedstatement .

  3. CallableStatement

  Allows a database stored procedure to be called from a Java application. The CallableStatement object contains a call to a stored procedure, but does not contain the stored procedure itself, because the stored procedure is stored in the database.

  How to use: callablestatement cstmt = Conn.preparecall ("{Call stored procedure name (Parameter table column)}");

• Core Code :

Comparison of Jdbc-statement,preparestatement,callablestatement