Why does JDBC use PreparedStatement instead of Statement,

[Switch] Why does JDBC use PreparedStatement instead of Statement,

PreparedStatement is one of the APIs used to execute SQL query statements. Java provides Statement, PreparedStatement, and CallableStatement to execute query statements. Statement is used for general queries and PreparedStatement is used to execute parameterized queries, callableStatement is used for stored procedures. At the same time, PreparedStatement is often mentioned during Java interviews. For example, the difference between Statement and PreparedStatement and how to avoid SQL injection attacks? In this tutorial, we will discuss why PreparedStatement is used? What are the advantages of PreparedStatement? How does PreparedStatement avoid SQL injection attacks?

What is PreparedStatement?

PreparedStatement is an interface in the java. SQL package to execute SQL statement queries. You can obtain the PreparedStatment object by calling the connection. preparedStatement (SQL) method. The database system will pre-compile the SQL statements (if the JDBC driver supports this), and the pre-compiled statements will be prepared in advance, this pre-compiled SQL query Statement can be reused in future queries. As a result, it is faster than the query generated by the Statement object. The following is an example:

12345678910111213141516171819

public class PreparedStmtExample {     public static void main(String args[]) throws SQLException {        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"));        }           }} Output:Loan Type: Personal LoanLoan Type: Auto LoanLoan Type: Home LoanLoan Type: Gold Loan

In this examplePreparedStatementMake the same query, even if the parameter values are different, such as "Standard Chated" or "HSBC" as the parameter values, the database system will still call the previously compiled execution statements of the compiler (the system database system will optimize the query statement for the first time ). By default, "TYPE_FORWARD_ONLY" result set (ResultSet), Of course, you can also use the preparedstatment () overload method to return different types of result sets.

Advantages of preprocessing statements

PreparedStatementIt provides many benefits. In enterprise application development, PreparedStatement is strongly recommended for SQL queries. The following lists the advantages of PreparedStatement.

Limitations of PreparedStatement

Although PreparedStatement is very practical, it still has certain restrictions.
1. To prevent SQL injection attacks, PreparedStatement does not allow a placeholder (?) When there are multiple values, this problem becomes tricky when the ** IN ** clause query is executed. If PreparedStatement is used in the following SQL query, no results will be returned.

12	SELECT * FROM loan WHERE loan_type IN (?)preparedSatement.setString(1, "'personal loan', 'home loan', 'gold loan'");

So how can we solve this problem? Please continue to follow this blog and tell you the answer in the next issue.

Summary

For the PreparedStatement interface, note the following:
1. PreparedStatement can be used to write parameterized queries, providing better performance than Statement.
2. For PreparedStatement, the database can use the compiled and defined execution plan, which is faster than normal queries.
3. PreparedStatement can prevent common SQL injection attacks.
4. PreparedStatement can be used to write dynamic query statements.
5. PreparedStatement is associated with the java. SQL. Connection object. Once you close the connection, PreparedStatement cannot be used.
6. "?" It is called a placeholder.
7. The PreparedStatement query returns the FORWARD_ONLY ResultSet by default. You can only move the result set cursor in one direction. Of course, you can also set it to another type value, for example, "CONCUR_READ_ONLY ".
8. the JDBC driver for pre-compiled SQL queries is not supported. during prepareStatement (SQL), it does not send SQL query statements to the database for preprocessing, but waits until the query action is executed (when the executeQuery () method is called) the query Statement is sent to a database, which is the same as Statement.
9. The index position of the placeholder starts from 1 rather than 0. If it is filled with 0, * java. SQL. SQLException invalid column index * is abnormal. Therefore, if PreparedStatement has two placeholders, the index of the first parameter is 1 and that of the second parameter is 2.

The above are all the reasons for using PreparedStatement, but you can still use the Statement object for testing. However, in the production environment, you must consider usingPreparedStatement.
For more information, see:
SQL injection attacks
Parameterized Query
Pre-processing statements and stored procedures

Original article: Javarevisited Translation: ImportNew.com-Liu Zhijun
Http://www.importnew.com/5006.html.