重温jdbc中的PreparedStatement与Statement

PreparedStatement与Statement的主要区别：
1:创建时的区别：
   Statement stm=con.createStatement();
   PreparedStatement 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 每次执行sql语句，数据库都要执行sql语句的编译 ，最好用于仅执行一次查询并返回结果的情形，效率高于PreparedStatement.

PreparedStatement是预编译的，使用PreparedStatement有几个好处

a. 在执行可变参数的一条SQL时，PreparedStatement比Statement的效率高，因为DBMS预编译一条SQL当然会比多次编译一条SQL的效率要高。

b. 安全性好，有效防止Sql注入等问题。

c.  对于多次重复执行的语句，使用PreparedStament效率会更高一点，并且在这种情况下也比较适合使用batch；

d.  代码的可读性和可维护性。

 

JDBC实例 Statement,PreparedStatement,CallableStatement三个方法的实例

  //1. 初始化并加载JDBC-ODBC驱动程序
  Class.forName("jdbc.odbc.JdbcOdbcDriver");

  //2. 创建连接对象
  Connection Ex1Con = DriverManager.getConnection("jdbc:odbc:StudentDB";uid="admin";pw="sa");

  //3. 创建一个简单的Statement对象
  Statement Ex1Stmt = Ex1Con.createStatement();

  //4. 创建SQL串，传送到DBMS并执行SQL语句
  ResultSet Ex1rs = Ex1Stmt.executeQuery("SELECT StudentID, FirstName, LastName FROM Students");

  //5. 处理每一个数据行，直到不再有数据行
  while(Ex1rs.next())

  {
       //将列值保存到java变量中
       StNo = Ex1rs.getString(1);
       StFName = Ex1rs.getString(2);
       StLName = Ex1rs.getString(3);
   }


  //初始化并加载JDBC-ODBC驱动程序
  Class.forName("jdbc.odbc.JdbcOdbcDriver");

  //创建连接对象
  Connection Ex1Con = DriverManager.getConnection("jdbc:odbc:StudentDB";uid="admin";pw="sa");

  //创建一个简单的Statement对象
  Statement Ex1Stmt = Ex1Con.createStatement();

  //创建SQL串，传送到DBMS并执行该SQL语句
  String SQLBuffer = "UPDATE Students SET FirstName = " +
   StFName + ",LastName = " + StLName +
   "WHERE StudentNumber = " + StNo;

  retValue = Ex1Stmt.executeUpdate(SQLBuffer);
 
-------------------------PreparedStatement---------------------------------------------------------------
//使用PreparedStatement改进实例
//Declare class variables
Connection Con;
PreparedStatement PrepStmt;
boolean Initialized = false;
public void InitConnection() throws SQLException, ClassNotFoundException{
  //Initialize and load the JDBC-ODBC driver.
  Class.forName("jdbc.odbc.JdbcOdbcDriver");

  //Make the connection object.
  Con = DriverManager.getConnection("jdbc:odbc:StudentDB";uid="admin";pw="sa");

  //Create a prepared Statement object.
  PrepStmt = Con.prepareStatement("SELECT ClassName, Location, DaysAndTimes FROM Classes WHERE ClassName = ?");
  
  //Set the SQL parameter to the one passed into this method
  PrepStmt.setString(1, ListClassName);
  ResultSet Ex1rs = PrepStmt.executeQuery();

  //Process each row until there are no more rows and display the results on the console.
  while(Ex1rs.next()){
   ClassName = Ex1rs.getString(1);
   ClassLocation = Ex1rs.getString(2);
   ClassSchedule = Ex1rs.getString(3);
  
  }

-----------------------------------CallableStatement------------------------------------------------------------------------------------------------------

//使用CallableStatement显示成绩
//预先定义好的存储过程的调用形式为：studentGrade = getStudentGrade(StudentID, ClassID)
public void DisplayGrade(String StudentID, String ClassID) throws SQLException
{
  int Grade;
  //Initialize and load the JDBC-ODBC dirver.
  Class.forName("jdbc.odbc.JdbcOdbcDriver");
  //Make the connection object;
  Connection Con = DriverManager.getConnection("jdbc:odbc:studentDB";uid="admin";pw="sa");
  //Create a Callable Statement object;
  CallableStatement CStmt = Con.prepareCall({? = call getStudentGrade[?,?]});
 
  //Now tie the placeholders with actual parameters.
  //Register the return value from the stored procedure
  //as an integer type so that the driver knows how to handle it.
  //Note the type is defined in the java.sql.Types.
  CStmt.registerOutParameter(1,java.sql.Types.INTEGER);
 
  //Set the In parameters (which are inherited from the PreparedStatement class)
  CStmt.setString(1,StudentID);
  CStmt.setString(2,ClassID);
 
  //Now we are ready to call the stored procedure
 
  int RetVal = CStmt.excuteUpdate();
 
  //Get the OUT Parameter from the registered parameter
  //Note that we get the result from the CallableStatement object
  Grade = CStmt.getInt(1);
 
  //And display the results on the console;
  System.out.println("The Grade is:" + Grade);
}