动态SQL和绑定变量

转自;http://zhuyuehua.iteye.com/blog/1123334

 

 

说动态SQL之前先来说下静态SQL

 

静态SQL语句

 

语句中主变量的个数与数据类型在预编译时都是确定的，我们称这类嵌入式SQL语句为静态SQL语句。

 

与之相对应的就是动态SQL

 

动态SQL方法允许在程序运行过程中临时“组装”SQL语句。

 

那么他们之间的区别是什么呢？

 

静态sql的执行计划（DB2称存取路径）是在运行前就确定好的

 

动态sql的执行计划（DB2称存取路径）是在运行时动态生成的。由于是在运行时动态生成执行计划，因此生成的执行计划（DB2称存取路径）相对更优，但考虑到生成执行计划（DB2称存取路径）的开销，有可能应用程序的运行时间相对会比静态sql长些 。

 

绑定变量 

 

 

在 SQL 语句中，绑定变量是一个占位符。例如，为了查询员工号为 123 的员工的信息，可以查询：

1 ） select * from emp where empno=123;

 

另外，也可以查询：

2 ） select * from emp where empno=:empno 。

 

 

     那么每次查询都是一个新查询，即在数据库共享池中以前没有过的查询。每次查询必须经过分析、限定（名称解析）、安全检查、优化等等，简单地说，执行的每条语句在每次执行时都将必须经过编译。

 

在第二个查询 2 ）中使用了绑定变量 :empno ，它的值在查询执行时提供。查询经过一次编译后，查询方案将存储在共享池中，可以用来检索和重用。在性能和可伸缩性方面，这两者的差异是巨大的，甚至是惊人的

 

 

       从上所述，很明显看出，分析一个带有硬编码量的语句将比重用一条已分析过的查询方案花费更长的时间和消耗更多的资源，不明显的是前者将减少系统所能支持的用户数量。很明显，部分原因是由于增加资源消耗量，但更主要的因素是在解析 sql 语句的过程中对共享池中锁存器（ latch ）的争抢

 

       通过使用绑定变量，应用程序提交的相似的 sql 语句只需要解析一次，就可以重复使用，这非常有效，这也是Oracle 数据库要求使用的工作方式。不仅使用较少的资源，而且可以减少锁存（ latch ）时间，降低锁存（ latch ）次数，这将提高应用系统性能，并且大大提高可伸缩性。

 

     在IBATS中，采用的是动态SQL加绑定变量的方式。

    

     IBATS中是根据传递过来的变量是否空来拼接SQL的，这就是动态SQL。

     

     而传递来的参数是根据绑定变量来执行的。

 

     比如下面的例子，我要查询用户信息。

 

     在前台页面输入用户名，zhangsan 

         

     传到IBATS后，执行SQL，在数据库查看刚刚执行的SQL

 

     查看方法如下：

 

    

Sql代码

1. select  sql_text  from  v$sql  where  sql_text  like   'select  t.username ,t.password, t.sex ,t.mobile from users t%'   

 

     注意：在ORACLE中，要查看v$sql 这个视图，得较高的权限，我是用DBA权限进去查看的。

 

     看到这样的语句：

 

     

Sql代码

1. select   t.username ,t. password , t.sex ,t.mobile  from  users t              where               t.username = :1    

 

     如果再查lisi的用户信息，在数据库看执行的SQL。发现还是上面那条SQL。

 

    所以，绑定变量的好处就是每次都执行的同一条SQL，只是输入的变量值不同。

 

    下面直接在数据量中执行下面的两个SQL:

 

 

Sql代码

1. select   t.username ,t. password , t.sex ,t.mobile  from  users t   where   t.username =  'zhangsan' ;  
2. select   t.username ,t. password , t.sex ,t.mobile  from  users t   where   t.username =  'lisi' ;  

 

    再看数据库中执行的SQL，发现是下面的结果：

 

     

   可以看到，不使用绑定变量时，是执行的不同的SQL。

 

总结：ibats采用的是动态SQL+绑定变量的方式。 动态SQL在运行时编译，所以执行计划更优，但相对于静态编译的静态SQL或者存储过程更耗性能。

 绑定变量可以重复利用相似的SQL，使效率更高。

--------------------------------------------------------------------------------

 

附一段jdbc访问数据库时绑定和不绑定变量的测试例子：

       import java.sql.*;

       import oracle.jdbc.driver.*;

　

　　class ConOra {
　　public static void main(String args[] ) throws SQLException{
　　DriverManager.registerDriver(neworacle.jdbc.driver.OracleDriver());
　　Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.152:1521:whx","system","lukewhx");
　　PreparedStatement stmt ;
　　ResultSet rset ;
　　String v_sql;

　　/*不绑定
　　for (int i =1;i<=1000;i++){
　　v_sql="select object_name from objects where object_id="+i;
　　stmt =conn.prepareStatement(v_sql);
　　rset=stmt.executeQuery();
　　stmt.close();
　　} 
　　*/

      //绑定

　　for (int i =1 ;i<=1000;i++ ) {
　　v_sql = "select object_name from objects where object_id= :x ";
　　stmt=conn.prepareStatement(v_sql);
　　stmt.setString(1,Integer.toString(i));
　　rset = stmt.executeQuery();
　　stmt.close();
　　} 

　　System.out.println("Execute OK");
　　}
　　}