jdbc preparedstatement 使用范例

jdbc preparedstatement 对象扩展了 statement 对象，给 statement 对象增加几个高级功能。

jdbc preparedstatement 对 sql 语句进行预编译，效率更高。另外，可以接收动态参数，避免 statement 中的 sql 注入问题。

1. preparedstatement 使用范例

//step 1. import required packages
import java.sql.*;

public class jdbcexample {
   // jdbc driver name and database url
   static final string jdbc_driver = "com.mysql.jdbc.driver";  
   static final string db_url = "jdbc:mysql://localhost/emp";

   //  database credentials
   static final string user = "username";
   static final string pass = "password";
   
   public static void main(string[] args) {
   connection conn = null;
   preparedstatement stmt = null;
   try{
      //step 2: register jdbc driver
      class.forname("com.mysql.jdbc.driver");

      //step 3: open a connection
      system.out.println("connecting to database...");
      conn = drivermanager.getconnection(db_url,user,pass);

      //step 4: execute a query
      system.out.println("creating statement...");
      string sql = "update employees set age=? where id=?";
      stmt = conn.preparestatement(sql);
      
      //bind values into the parameters.
      stmt.setint(1, 35);  // this would set age
      stmt.setint(2, 102); // this would set id
      
      // let us update age of the record with id = 102;
      int rows = stmt.executeupdate();
      system.out.println("rows impacted : " + rows );

      // let us select all the records and display them.
      sql = "select id, first, last, age from employees";
      resultset rs = stmt.executequery(sql);

      //step 5: extract data from result set
      while(rs.next()){
         //retrieve by column name
         int id  = rs.getint("id");
         int age = rs.getint("age");
         string first = rs.getstring("first");
         string last = rs.getstring("last");

         //display values
         system.out.print("id: " + id);
         system.out.print(", age: " + age);
         system.out.print(", first: " + first);
         system.out.println(", last: " + last);
      }
      //step 6: clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(sqlexception se){
      //handle errors for jdbc
      se.printstacktrace();
   }catch(exception e){
      //handle errors for class.forname
      e.printstacktrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(sqlexception se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(sqlexception se){
         se.printstacktrace();
      }//end finally try
   }//end try
   system.out.println("goodbye!");
}//end main
}//end jdbcexample

2. 编译运行

现在来编译上面的例子：

c:>javac jdbcexample.java
c:>

当运行 jdbcexample，它会产生以下结果：

c:>java jdbcexample
connecting to database...
creating statement...
rows impacted : 1
id: 100, age: 18, first: zara, last: ali
id: 101, age: 25, first: mahnaz, last: fatma
id: 102, age: 35, first: zaid, last: khan
id: 103, age: 30, first: sumit, last: mittal
goodbye!
c:>