JDBC预编译语句

什么是预编译语句

预编译语句PreparedStatement 是java.sql中的一个接口，它是Statement的子接口。通过Statement对象执行SQL语句时，需要将SQL语句发送给DBMS，由DBMS首先进行编译后再执行。预编译语句和Statement不同，在创建PreparedStatement 对象时就指定了SQL语句，该语句立即发送给DBMS进行编译。当该编译语句被执行时，DBMS直接运行编译后的SQL语句，而不需要像其他SQL语句那样首先将其编译。

 

什么时候使用预编译语句

一般是在需要反复使用一个SQL语句时才使用预编译语句，预编译语句常常放在一个for或者while循环里面使用，通过反复设置参数从而多次使用该SQL语句；为了防止SQL注入漏洞，在某些数据操作中也使用预编译语句。

JDBC使用预编译SQL的好处

1. 执行效率

PreparedStatement可以尽可能的提高访问数据库的性能，数据库在处理SQL语句时都有一个预编译的过程，而预编译对象就是把一些格式固定的SQL编译后，存放在内存池中即数据库缓冲池，当我们再次执行相同的SQL语句时就不需要预编译的过程了，只需DBMS运行SQL语句。所以当你需要执行Statement对象多次的时候，PreparedStatement对象将会大大降低运行时间，特别是的大型的数据库中，它可以有效的也加快了访问数据库的速度。

2. 代码可读性、可维护性

比如向记录用户信息的表中插入记录: user(id, password, name, email, address)。使用Statement的SQ语句如下：

[sql] view plain copy

1. String sqlString ="insert into user values('"+  
2. user.id + "', '"+  
3. user.password + "', '" +  
4. user.name + "', '" +  
5. user.email + "', '" +  
6. user.address + "')";  

使用PrearedStatement的SQL语句如下：

[sql] view plain copy

1. String sqlString ="insert into user(id, password, name, email, address) values(?, ?, ?, ?, ?)";  
2. PreparedStatement pstmt = connection.PreparedStatement(sqlString);  
3. pstmt.setString(1, user.id);  
4. pstmt.setString(2, user.password);  
5. pstmt.setString(3, user.name);  
6. pstmt.setString(4, user.email);  
7. pstmt.setString(5, user.address);  

使用占位符？代替参数，将参数与SQL语句分离出来，这样就可以方便对程序的更改和延续，同样，也可以减少不必要的错误。

3. SQL执行的安全性

SQL注入攻击：是从客户端输入一些非法的特殊字符，从而使服务器端在构造SQL语句时仍能正确构造，从而收集程序和服务器的信息或数据。比如在Web信息系统的登录入口处，要求用户输入用户名和密码，客户端输入后，服务器端根据用户输入的信息来构造SQL语句，在数据库中查询是否存在此用户名以及密码是否正确。假设使用上述例子中的表“user”构造SQL语句的Java程序可能是:

1

sqlString = "select * from user where user.id = '" + userID + "' and user.password = "' + userPassword + "'";

其中userID, userPassword是从用户输入的用户名及密码。如果用户和密码输的都是 '1' or '1'='1',则服务器端生成的SQL语句如下：

1

sqlString = "select * from user where user.id ='1'or'1'='1'and user.password ='1'or'1'='1'

这个SQL语句中的where字句没有起到数据筛选的作用，因为只要数据库中有记录，就会返回一个结果不为空的记录集，查询就会通过。上面的例子说明：在Web环境中，有恶意的用户会利用那些设计不完善的、不能正确处理字符串的应用程序。特别是在公共Web站点上,在没有首先通过PreparedStatement对象处理的情况下，所有的用户输入都不应该传递给SQL语句。此外，在用户有机会修改SQL语句的地方，如HTML的隐藏区域或一个查询字符串上，SQL语句都不应该被显示出来。

3. 减少硬解析，节约大量的CPU资源

使用预编译可以利用数据库的软解析降低资源消耗，所谓软解析，就是因为相同文本的SQL语句存在于library cache中，所以本次SQL语句的解析就可以去掉硬解析中的一个或多个步骤（主要为选择执行计划步骤），从而节省大量的资源的耗费。

关于软解析和硬解析，可参见：http://blog.csdn.net/cyzero/article/details/8621171

附：

Statement，PreparedStatement 与 PreparedStatement + 批处理 的用法与比较

java方面

1.使用Statement对象

2.预编译PreparedStatement

3.使用PreparedStatement + 批处理

为了区分出这三者之间的效率，下面的事例执行过程都是在数据库表t1中插入1万条记录，并记录出所需的时间(此时间与电脑硬件有关)

1.使用Statement对象

使用范围：当执行相似SQL(结构相同，具体值不同)语句的次数比较少

优点：语法简单

缺点:采用硬编码效率低，安全性较差。

原理：硬编码，每次执行时相似SQL都会进行编译   

        

事例执行过程:

   public void exec(Connection conn){

           try {

                     Long beginTime = System.currentTimeMillis();

                           conn.setAutoCommit(false);//设置手动提交

                    Statement st = conn.createStatement();

                            for(int i=0;i<10000;i++){

                       String sql="insert into t1(id) values ("+i+")";

                       st.executeUpdate(sql); 

                    }

                           Long endTime = System.currentTimeMillis();

                   System.out.println("Statement用时："+(endTime-beginTime)/1000+"秒");//计算时间

                   st.close();

                  conn.close();

                } catch (SQLException e) {               

                  e.printStackTrace();

          } 

   }

 

执行时间：Statement用时：31秒

 

2.预编译PreparedStatement

使用范围：当执行相似sql语句的次数比较多（例如用户登陆，对表频繁操作..）语句一样，只是具体的值不一样，被称为动态SQL

优点：语句只编译一次，减少编译次数。提高了安全性（阻止了SQL注入）

缺点: 执行非相似SQL语句时，速度较慢。

原理：相似SQL只编译一次，减少编译次数

名词解释：

SQL注入:select * from user where username="张三" and password="123" or 1=1;

前面这条语句红色部分就是利用sql注入，使得这条词句使终都会返回一条记录，从而降低了安全性。

事例执行过程:

      public void exec2(Connection conn){

         try {

                   Long beginTime = System.currentTimeMillis();

                    conn.setAutoCommit(false);//手动提交

                    PreparedStatement pst = conn.prepareStatement("insert into t1(id) values (?)");

                    for(int i=0;i<10000;i++){

                       pst.setInt(1, i);

                       pst.execute();   

                    }

                      conn.commit();

                      Long endTime = System.currentTimeMillis();

                      System.out.println("Pst用时:"+(endTime-beginTime)+"秒");//计算时间

                      pst.close();

                      conn.close();

          } catch (SQLException e) {                 

                     e.printStackTrace();

          }

    }

执行时间：Pst用时:14秒

 

3.使用PreparedStatement + 批处理

使用范围：一次需要更新数据库表多条记录

优点：减少和SQL引擎交互的次数，再次提高效率,相似语句只编译一次，减少编译次数。提高了安全性（阻止了SQL注入）

缺点:

原理：

批处理: 减少和SQL引擎交互的次数，一次传递给SQL引擎多条SQL。

名词解释：

PL/SQL引擎：在oracle中执行pl/sql代码的引擎，在执行中发现标准的sql会交给sql引擎进行处理。

SQL引擎：执行标准sql的引擎。

事例执行过程:

public void exec3(Connection conn){

                     try {

                      conn.setAutoCommit(false);

                      Long beginTime = System.currentTimeMillis();

                      PreparedStatement pst = conn.prepareStatement("insert into t1(id) values (?)");

                     

                      for(int i=1;i<=10000;i++){   

                       pst.setInt(1, i);

                       pst.addBatch();//加入批处理，进行打包

                       if(i%1000==0){//可以设置不同的大小；如50，100，500，1000等等

                        pst.executeBatch();

                        conn.commit();

                        pst.clearBatch();

                       }

                      }

                     pst.executeBatch();

                      Long endTime = System.currentTimeMillis();

                      System.out.println("pst+batch用时："+(endTime-beginTime)+"毫秒");

                      pst.close();

                      conn.close();

                     } catch (SQLException e) {

                      // TODO Auto-generated catch block

                      e.printStackTrace();

                     }

 }

执行时间：pst+batch用时：485毫秒

 

数据库方面

1 静态SQL

使用范围：

优点：每次执行相似sql都会进行编译。

缺点:效率低,占用破费cpu资源，耗费SGA 资源。

原理：(检查SQL 语句在SGA 中的共享池中是否存在, 如果不存在，则编译、解析后执行：硬解析,

     如果已存在，则取出编译结果执行：软解析)

事例执行过程:

DECLARE

 time1 NUMBER;

 time2 NUMBER;

BEGIN

  time1 := dbms_utility.get_time;

  EXECUTE IMMEDIATE 'truncate table t_demo';

  FOR i IN 1..10000

  LOOP

     EXECUTE IMMEDIATE

       'insert into t_demo(u_id) values('||i||')'; 

  END LOOP;

  time2 := dbms_utility.get_time;

  dbms_output.put_line((time2-time1)/100||'秒');

END

执行时间：pst+batch用时：20.93秒

2 动态SQL

使用范围：

优点：语句只编译一次，减少编译次数，提高效率。

缺点:

原理：减少编译次数(检查SQL 语句在SGA 中的共享池中是否存在, 如果不存在，则编译、解析后执行：硬解析,

     如果已存在，则取出编译结果执行：软解析)

事例执行过程:

DECLARE

 time1 NUMBER;

 time2 NUMBER;

BEGIN

  time1 := dbms_utility.get_time;

  EXECUTE IMMEDIATE 'truncate table t_demo';

  FOR i IN 1..10000

  LOOP

     EXECUTE IMMEDIATE

       'insert into t1(u_id) values(:x)' USING i; 

  END LOOP;

  time2 := dbms_utility.get_time;

  dbms_output.put_line((time2-time1)/100||'秒');

END;        

执行时间：pst+batch用时：10.55秒

 

3 利用forall进行批处理<相似java中的批处理>

使用范围：当执行相似sql语句的次数比较多（例如用户登陆，对表频繁操作..）FORALL只能运行一条sql语句。

优点：减少与SQL引擎转替次数，提交效率。

缺点:循环时从开始到结束，一次性读完，不能取其中的某些循环进行操作。

原理：使用ForAll语句可以让PL/SQL引擎一次将多条SQL转发给SQL引擎，从而减少转发次数，提高系统性能。

事例执行过程:

CREATE  OR  REPLACE  PROCEDURE  p_test

AS

   --定义新的类型(不是定义变量！)

   TYPE id_table_type IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER; 

   --用新的类型来定义变量

   id_table id_table_type;

   time1 NUMBER;

 time2 NUMBER;

BEGIN

    FOR i IN 1..10000

    LOOP

       --往两个数组中存放数据

       id_table(i) := i;  

    END LOOP;

   --★：一次性向SQL引擎传递了多条数据而不是一条

    time1 := dbms_utility.get_time;

    FORALL i IN 1..id_table.COUNT

        INSERT INTO t_demo(u_id) VALUES(id_table(i));

  time2 := dbms_utility.get_time;

  dbms_output.put_line((time2-time1)*10||'毫秒');

end p_test;

 

call p_test();

执行时间：pst+batch用时：170毫秒

 

4 利用BULK COLLECT

使用范围：处理数据量比较少时。

优点：一次读取多行数据，提高效率。

缺点: 需要较大的内存开销

原理：将多个行引入一个或多个集合中。

事例执行过程:

DECLARE

   TYPE books_aat

 

      IS TABLE OF book%ROWTYPE

      INDEX BY PLS_INTEGER;

   book books_aat;

BEGIN

   SELECT *

     BULK COLLECT INTO book

     FROM books

    WHERE title LIKE '%PL/SQL%';

   ...

END;