准备查询或参数化查询的另一个特征:引用自本文。

该语句是数据库系统重复执行同一条SQL语句的高效特性之一。准备好的语句是模板的一种，由应用程序使用不同的参数。

语句模板准备好后发送给数据库系统，数据库系统对该模板进行解析、编译和优化，不执行即可存储。

有些参数，比如在以后的应用程序创建模板时没有传递子句，将这些参数发送给数据库系统，数据库系统使用SQL语句模板并按请求执行。

预处理语句对于SQL注入非常有用，因为应用程序可以使用不同的技术和协议来准备参数。

当数据数量不断增加，索引频繁变化时，Prepared statement可能会失败，因为在这种情况下需要一个新的查询计划。

准备查询或参数化查询的另一个特征:引用自本文。

该语句是数据库系统重复执行同一条SQL语句的高效特性之一。准备好的语句是模板的一种，由应用程序使用不同的参数。

语句模板准备好后发送给数据库系统，数据库系统对该模板进行解析、编译和优化，不执行即可存储。

有些参数，比如在以后的应用程序创建模板时没有传递子句，将这些参数发送给数据库系统，数据库系统使用SQL语句模板并按请求执行。

预处理语句对于SQL注入非常有用，因为应用程序可以使用不同的技术和协议来准备参数。

当数据数量不断增加，索引频繁变化时，Prepared statement可能会失败，因为在这种情况下需要一个新的查询计划。

They are pre-compiled (once), so faster for repeated execution of dynamic SQL (where parameters change) Database statement caching boosts DB execution performance Databases store caches of execution plans for previously executed statements. This allows the database engine to reuse the plans for statements that have been executed previously. Because PreparedStatement uses parameters, each time it is executed it appears as the same SQL, the database can reuse the previous access plan, reducing processing. Statements "inline" the parameters into the SQL string and so do not appear as the same SQL to the DB, preventing cache usage. Binary communications protocol means less bandwidth and faster comms calls to DB server Prepared statements are normally executed through a non-SQL binary protocol. This means that there is less data in the packets, so communications to the server is faster. As a rule of thumb network operations are an order of magnitude slower than disk operations which are an order of magnitude slower than in-memory CPU operations. Hence, any reduction in amount of data sent over the network will have a good effect on overall performance. They protect against SQL injection, by escaping text for all the parameter values provided. They provide stronger separation between the query code and the parameter values (compared to concatenated SQL strings), boosting readability and helping code maintainers quickly understand inputs and outputs of the query. In java, can call getMetadata() and getParameterMetadata() to reflect on the result set fields and the parameter fields, respectively In java, intelligently accepts java objects as parameter types via setObject, setBoolean, setByte, setDate, setDouble, setDouble, setFloat, setInt, setLong, setShort, setTime, setTimestamp - it converts into JDBC type format that is comprehendible to DB (not just toString() format). In java, accepts SQL ARRAYs, as parameter type via setArray method In java, accepts CLOBs, BLOBs, OutputStreams and Readers as parameter "feeds" via setClob/setNClob, setBlob, setBinaryStream, setCharacterStream/setAsciiStream/setNCharacterStream methods, respectively In java, allows DB-specific values to be set for SQL DATALINK, SQL ROWID, SQL XML, and NULL via setURL, setRowId, setSQLXML ans setNull methods In java, inherits all methods from Statement. It inherits the addBatch method, and additionally allows a set of parameter values to be added to match the set of batched SQL commands via addBatch method. In java, a special type of PreparedStatement (the subclass CallableStatement) allows stored procedures to be executed - supporting high performance, encapsulation, procedural programming and SQL, DB administration/maintenance/tweaking of logic, and use of proprietary DB logic & features

语句接口执行不带参数的静态SQL语句

PreparedStatement接口(扩展Statement)执行预编译的带/不带参数的SQL语句

对于重复执行有效 它是预编译的，所以更快

PreparedStatement相对于Statement的一些好处是:

PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters. PreparedStatement allows us to execute dynamic queries with parameter inputs. PreparedStatement provides different types of setter methods to set the input parameters for the query. PreparedStatement is faster than Statement. It becomes more visible when we reuse the PreparedStatement or use it’s batch processing methods for executing multiple queries. PreparedStatement helps us in writing object Oriented code with setter methods whereas with Statement we have to use String Concatenation to create the query. If there are multiple parameters to set, writing Query using String concatenation looks very ugly and error prone.

阅读更多关于SQL注入问题，请访问http://www.journaldev.com/2489/jdbc-statement-vs-preparedstatement-sql-injection-example

PreparedStatement的优点:

Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches. Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the PreparedStatement setXxx() methods to set the values preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)"); preparedStatement.setString(1, person.getName()); preparedStatement.setString(2, person.getEmail()); preparedStatement.setTimestamp(3, new Timestamp(person.getBirthdate().getTime())); preparedStatement.setBinaryStream(4, person.getPhoto()); preparedStatement.executeUpdate(); and thus don't inline the values in the SQL string by string-concatenating. preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email) VALUES ('" + person.getName() + "', '" + person.getEmail() + "'"); preparedStatement.executeUpdate(); Eases setting of non-standard Java objects in a SQL string, e.g. Date, Time, Timestamp, BigDecimal, InputStream (Blob) and Reader (Clob). On most of those types you can't "just" do a toString() as you would do in a simple Statement. You could even refactor it all to using PreparedStatement#setObject() inside a loop as demonstrated in the utility method below: public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException { for (int i = 0; i < values.length; i++) { preparedStatement.setObject(i + 1, values[i]); } } Which can be used as below: preparedStatement = connection.prepareStatement("INSERT INTO Person (name, email, birthdate, photo) VALUES (?, ?, ?, ?)"); setValues(preparedStatement, person.getName(), person.getEmail(), new Timestamp(person.getBirthdate().getTime()), person.getPhoto()); preparedStatement.executeUpdate();