为什么要始终使用PreparedStatement代替Statement

在JDBC应用中,如果你已经是稍有水平开发者,你就应该始终以PreparedStatement代替Statement.也就是说,在任何时候都不要使用Statement.

基于以下的原因:

一.代码的可读性和可维护性.

虽然用PreparedStatement来代替Statement会使代码多出几行,但这样的代码无论从可读性还是可维护性上来说.都比直接用Statement的代码高很多档次

二.PreparedStatement尽最大可能提高性能.

每一种数据库都会尽最大努力对预编译语句提供最大的性能优化.因为预编译语句有可能被重复调用.所以语句在被DB的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中(相当于一个涵数)就会得到执行.这并不是说只有一个Connection中多次执行的预编译语句被缓存,而是对于整个DB中,只要预编译的语句语法和缓存中匹配.那么在任何时候就可以不需要再次编译而可以直接执行.而statement的语句中,即使是相同一操作,而由于每次操作的数据不同所以使整个语句相匹配的机会极小,几乎不太可能匹配.比如:

insert into tb_name (col1,col2) values ('11','22');
insert into tb_name (col1,col2) values ('11','23');

即使是相同操作但因为数据内容不一样,所以整个个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存.

当然并不是所以预编译语句都一定会被缓存,数据库本身会用一种策略,比如使用频度等因素来决定什么时候不再缓存已有的预编译结果.以保存有更多的空间存储新的预编译语句.

三.最重要的一点是极大地提高了安全性.

即使到目前为止,仍有一些人连基本的恶义SQL语法都不知道.

String sql = "select * from tb_name where name= '"+varname+"' and passwd='"+varpasswd+"'";

如果我们把[' or '1' = '1]作为varpasswd传入进来.用户名随意,看看会成为什么?

select * from tb_name = '随意' and passwd = '' or '1' = '1';

因为'1'='1'肯定成立,所以可以任何通过验证.更有甚者:

把[';drop table tb_name;]作为varpasswd传入进来,则:

select * from tb_name = '随意' and passwd = '';drop table tb_name;

有些数据库是不会让你成功的,但也有很多数据库就可以使这些语句得到执行.

 

而如果你使用预编译语句.你传入的任何内容就不会和原来的语句发生任何匹配的关系.只要全使用预编译语句,你就用不着对传入的数据做任何过虑.而如果使用普通的statement,有可能要对drop,;等做费尽心机的判断和过虑.

 // Possible solution to prevent SQL injection: JDBC PreparedStatement
// code to connect to the database and exception handling omitted to maximize space
final String id = httpRequest.getParameter ("id") // parameter from request
final String sql = "SELECT * FROM Customers WHERE CustomerID =?";
final PreparedStatement ps = con.prepareStatement (sql);
ps.setString (1, id);
ResultSet rs = ps.executeQuery ();

// Possible solution to prevent SQL injection: Named Parameters in JPA or Hibernate
// code to connect to the database and exception handling omitted to maximize space
final String id = httpRequest.getParameter ("id") / / parameter from request
q = entityManager.createQuery ("SELECT * FROM Customers WHERE CustomerID = ': id'");
q.setParameter ("id", id);

//Examples of codes subject to SQL Injection using JDBC PreparedStatement.
// MISUSE OF PreparedStatement - # FAIL
final String id = httpRequest.getParameter ("id") / / parameter from request
final String sql = "SELECT * FROM Customers WHERE CustomerID = '" + id + "'"
final PreparedStatement ps = con.prepareStatement (sql);
ResultSet rs = ps.executeQuery ();

Advantages of a PreparedStatement:
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 faster than disk operations which are an order of magnitude faster than in-memory CPU oeprations. 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