1、创建时的区别:
Statement stm = con.createStatement();
PrepareStatement pstm = con.prepareStatement(sql);
执行的时候:
stm.execute(sql);
pstm.execute();
2、pstm一旦绑定了SQL,此pstm就不能执行其他的SQL,即只能执行一条SQL命令。stm可以执行其他的SQL命令。
3、对于执行同构的SQL(只有值不同,其他结构都相同),用pstm的执行效率比较高,对于异构的SQL语句,Statement的执行效率要高。
4、当需要外部变量的时候,pstm的执行效率更高。
下面是一个statement的例子
package JDBC;
import com.mysql.jdbc.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class StatementTest {
public static void main(String[] args){
Connection connection = null;
Statement statement = null;
ResultSet rSet = null;
try{
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/scutcs", "root", "yuping");
String sqlString = "SELECT SNO,SNAME FROM STUDENT WHERE HEIGHT = 0.00";
statement = (Statement) connection.createStatement();
rSet = statement.executeQuery(sqlString);
while(rSet.next()){
System.out.println(rSet.getString(1) + " " +rSet.getString(2));
}
}catch(SQLException e){
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
rSet.close();
statement.close();
connection.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
}
下面是一个PrepareStatement的例子
package JDBC;
import java.security.interfaces.RSAKey;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
public class PrepareStatementTest {
public static void main(String[] args){
Connection connection = null;
PreparedStatement psmt = null;
ResultSet resultSet = null;
try{
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/scutcs", "root", "yuping");
String sqlString = "SELECT SNO, SNAME,SEX FROM STUDENT WHERE SEX = ?";
psmt = (PreparedStatement) connection.prepareStatement(sqlString);
psmt.setString(1, "1");
resultSet = psmt.executeQuery();
while(resultSet.next()){
System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + " " + resultSet.getString(3));
}
}catch(SQLException e){
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
resultSet.close();
psmt.close();
connection.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
}
其中 psmt.setString(1,"1");第一个参数代表第一个"?"应该设置的值。