Java中使用绑定变量的方法其好处

没有使用绑定变量是使用Oracle数据库的应用系统性能问题主要原因和可伸缩性的主要障碍，Oracle的共享池的操作方法就决定开发人员应该使用绑定变量，如果想要Oracle运行速度减慢，甚至完全中止，就可以拒绝使用绑定变量。

在SQL语句中，绑定变量是一个占位符。例如，为了查询员工号为123的员工的信息，可以查询：1）select * from emp where empno=123;另外，也可以查询：2）select * from emp where empno=:empno。

在一个典型的OLTP系统中，查询员工123一次，可能再也不会查询，以后将查询员工456，员工789等。如果像语句1）中那样使用硬编码量（常量），那么每次查询都是一个新查询，即在数据库共享池中以前没有过的查询。每次查询必须经过分析、限定（名称解析）、安全检查、优化等等，简单地说，执行的每条语句在每次执行时都将必须经过编译。

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

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

通过下面的两个小程序我们可以看出其中的差别，其中程序NoBind.java没有使用绑定变量，程序UseBind.java使用了绑定变量。

程序NoBind.java：

/*

* This sample can be used to demostrate if you don't use bind variable.

* You will find more than one sql statement was parsed.

*/

 

// You need to import the java.sql package to use JDBC

import java.sql.*;

import oracle.jdbc.*;

 

// We import java.io to be able to use the i/o Class

import java.io.*;

 

class NoBind

{

public static void main(String args[])

throws SQLException, IOException

{

// Load the Oracle JDBC driver

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521: demo","scott","tiger");

// Create a statement

Statement stmt = conn.createStatement();

for(int i=1;i<=5;i++)

{

ResultSet rset = stmt.executeQuery("select hisal from salgrade where grade="+i);

while (rset.next())

{

System.out.println(rset.getString(1));

}

// close the result set

rset.close();

}

// close the statement and connect

stmt.close();

conn.close();

}

}

 

程序UseBind.java：

/*

* This sample can be used to demostrate how to use bind variable.

* Just run it and select the sql statement from the v$sql,you will

* find only one sql statement was parsed.

*/

 

// You need to import the java.sql package to use JDBC

import java.sql.*;

import oracle.jdbc.*;

 

// We import java.io to be able to use the i/o Class

import java.io.*;

 

class UseBind

{

public static void main(String args[])

throws SQLException, IOException

{

// Load the Oracle JDBC driver

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521: demo","scott","tiger");

// Create a PreparedStatement

PreparedStatement pstmt = conn.prepareStatement("select hisal from salgrade where grade=?");

for (int i=1;i<=5;i++)

{

// use the setXX() method

pstmt.setInt (1,i);

ResultSet rset = pstmt.executeQuery();

while (rset.next())

{

System.out.println(rset.getString(1));// print the first column

}

// close the result set

rset.close();

}

// close the statement and connect

pstmt.close();

conn.close();

}

}

 

上面两个程序都是通过scott/tiger登录，从salgrade表中检索5条数据，然后在终端上打印出来，不同之处在于第一个程序使用硬编码量，通过拼字符串的方式来构造sql语句，第二个程序使用了绑定变量，先使用一个占位符代替实际数值，然后再通过setInt()方法给占位符赋值。

执行程序NoBind.java后，通过查询v$sql视图可以发现，Oracle解析了5条不同的sql语句，如下：

SQL> select sql_text from v$sql where sql_text like 'select hisal from%';

 

SQL_TEXT

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

 

select hisal from salgrade where grade=1

select hisal from salgrade where grade=4

select hisal from salgrade where grade=2

select hisal from salgrade where grade=5

select hisal from salgrade where grade=3

 

刷新共享池，然后再执行程序UseBind.java后，通过查询v$sql视图可以发现，Oracle只解析了1条sql语句，如下：

SQL> select sql_text from v$sql where sql_text like 'select hisal from%';

 

SQL_TEXT

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

 

select hisal from salgrade where grade=:1

 

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

参考资料：《Expert one-on-one Oracle》，Thomas kyte.