Statement和PreparedStatement之间的区别

1.PreparedStatement是预编译的,对于批量处理可以大大提高效率. 也叫JDBC存储过程

2.使用 Statement 对象。在对数据库只执行一次性存取的时侯，用 Statement 对象进行处理。PreparedStatement 对象的开销比Statement大，对于一次性操作并不会带来额外的好处。

3.statement每次执行sql语句，相关数据库都要执行sql语句的编译，preparedstatement是预编译得,   preparedstatement支持批处理

4.

Code Fragment 1:

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();

片断2和片断1的区别在于，后者使用了PreparedStatement对象，而前者是普通的Statement对象。PreparedStatement对象不仅包含了SQL语句，而且大多数情况下这个语句已经被预编译过，因而当其执行时，只需DBMS运行SQL语句，而不必先编译。当你需要执行Statement对象多次的时候，PreparedStatement对象将会大大降低运行时间，当然也加快了访问数据库的速度。
这种转换也给你带来很大的便利，不必重复SQL语句的句法，而只需更改其中变量的值，便可重新执行SQL语句。选择PreparedStatement对象与否，在于相同句法的SQL语句是否执行了多次，而且两次之间的差别仅仅是变量的不同。如果仅仅执行了一次的话，它应该和普通的对象毫无差异，体现不出它预编译的优越性。

5.执行许多SQL语句的JDBC程序产生大量的Statement和PreparedStatement对象。通常认为PreparedStatement对象比Statement对象更有效,特别是如果带有不同参数的同一SQL语句被多次执行的时候。PreparedStatement对象允许数据库预编译SQL语句，这样在随后的运行中可以节省时间并增加代码的可读性。

注:

The best reasons for using PreparedStatements are these:

(1) Executing the same query multiple times in loop, binding different parameter values each time, and
(2) Using the setDate()/setString() methods to escape dates and strings properly, in a database-independent way.

The second one compels me to use PreparedStatement often, even if I'm not executing it in a loop. I don't have to worry about String or Date formatting that way.

I don't worry about the performance hit until it becomes a problem. Network latency is usually the bigger problem, and that has to do with the way queries are done rather the Statement vs PreparedStatement.

SQL injection attacks on a system are virtually impossible when using Prepared Statements.

what is SQL injection ?

Suppose your web application asks the user for their ID number. They type it into a box and click submit. This ends up calling the following method:

public List processUserID(String idNumber)
   throws SQLException
{
   String query = "SELECT role FROM roles WHERE id = '" + idNumber + "'";
   ResultSet rs = this.connection.executeQuery(query);
   // ... process results ...
}

If out of a sense of informed malice, your user enters the following text into the ID number field:

12345'; TRUNCATE role; SELECT '

They may be able to drop the contents of your role table, because the string that ends up in "query" will be:

SELECT role FROM roles WHERE id = '12345'; TRUNCATE role; SELECT ''

They have successfully injected SQL into your application that wasn't there before, hence the name. The specifics of this depend to some extent on your database, but there's some pretty portable SQL you can use to achieve this.

On the other hand, if you use a prepared statement:
public List processUserID(String idNumber)
   throws SQLException
{
   String query = "SELECT role FROM roles WHERE id = ?";
   PreparedStatement statement = this.connection.prepare(query);
   statement.setString(id,idNumber);
 
   ResultSet rs = this.connection.executeQuery(query);
   // ... process results ...
}
The database is told to compile the SQL in query first. The parameter is then submitted - so whatever you put into it will never get executed as SQL (well, ok, it's possible if you're passing it as a parameter to a stored proc, but it's very unlikely) - it will just return no matching records (because there won't be any users with id "12345'; TRUNCATE role; SELECT '"