As Published In
Oracle Magazine
March/April 2006

DEVELOPER: Frameworks


Using Bind Variables

By Steve Muench Oracle ACE

Ensure maximum query performance—now and in the future.

In his latest book, Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress, 2005), Tom Kyte discusses how bind variables can improve performance by eliminating the costly step of reparsing an application's SQL statements. Numerous database security experts also cite the benefits of using bind variables as a best practice to prevent vulnerabilities such as SQL injection attacks. If you want to build higher-performance, secure applications, you should know how bind variables work. In this column, I'll discuss how to use them in J2EE applications built with Oracle Application Development Framework (ADF).

Creating View Objects with Bind Variables

A bind variable is a placeholder in a SQL command for a value that will be supplied at runtime by the application. Because you can change the value of the bind variable as many times as needed without changing the actual text of the SQL command, the database can reuse the same statement over and over without incurring the overhead of reparsing the command each time it's executed.

As I've discussed in earlier columns, an Oracle ADF view object is a Java component that encapsulates a SQL query. You can easily add a bind variable when building the query, by including it in the optional WHERE clause that you supply on the Query tab of the View Object editor. If you're familiar with bind variables in SQL, you might expect to use a predicate such as DNAME = UPPER(:DepartmentName) to build a view object that queries the familiar DEPT table. However, the Oracle database driver that accompanies Oracle JDeveloper Release 2 (10.1.2) or earlier forces bind variables to have numerical names such as :0 or :1. As a result, you must specify a WHERE clause such as DNAME = UPPER(:0).

The ViewObject interface in the oracle.jbo package provides a method called setWhereClauseParam(), which lets you assign a value to the bind variable at runtime. Listing 1 shows basic code you can use to set a view object bind variable value and test its results.

Code Listing 1: Setting a view object bind variable 

String AM = "oramag.adf.HRModule";
String CF = "HRModuleLocal";
ApplicationModule am = Configuration.createRootApplicationModule(AM, CF);
ViewObject vo = am.findViewObject("DepartmentList");
vo.setWhereClauseParam(0,"Accounting");
vo.executeQuery();
Row result = vo.first();
System.out.println(result.getAttribute("Deptno"));
Configuration.releaseRootApplicationModule(am, true);


The code in Listing 1 performs a number of tasks. First, it creates an instance of an oramag.adf.HRModule application module (using the HRModuleLocal configuration). Then it finds a view object instance named DepartmentList and sets a value of Accounting for its first (and only) bind variable. It then executes a test query, retrieves the first row of the result, and prints the value of its DEPTNO column to the console. If you enable the logging of Oracle ADF runtime diagnostic messages by setting the jbo.debugoutput system property to console, you'll see helpful output when you run the code in Listing 1. To set this system property, in Oracle JDeveloper 10g Release 2 (10.1.2), open the Project Properties window associated with your project, go to the Profiles->Development->Runner panel, and type -Djbo.debugoutput=console in the Java Options field.

With diagnostics enabled, when you run the code in Listing 1, the diagnostic messages will look something like this: 

[182] SELECT DEPTNO, DNAME, LOC 
FROM DEPT Dept WHERE DNAME = UPPER(:0)
[183] Bind params for ViewObject: DeptView
[184] Binding param 1: Accounting
10


Using Multiple Bind Variables

If your SQL statement requires multiple bind variables, you can add them by using additional positionally named variables such as :1, :2, and :3. For example, consider a WHERE clause for an EmpsInSalaryRangeView view object based on the familiar EMP table. In this case, you might enter a predicate such as SAL BETWEEN :0 AND :1. You can use the same setWhereClauseParam() API shown in Listing 1 to individually set the values of these two variables, or you can use setWhereClauseParams(), which accepts an array of objects to assign as values to all of the bind variables at once.

For example, given an instance named Emps of the EmpsInSalaryRangeView view object, you can find all of the employees with a salary between 0 and 2,000, by using the code shown in Listing 2. Note that the Number class I'm using here is the one that Oracle ADF supplies in the oracle.jbo.domain package.

Code Listing 2: Setting multiple bind variable values at one time 

ViewObject emps = am.findViewObject("Emps");
emps.setWhereClauseParams(
  new Object[]{new Number(0),          /* lowsal  */ 
                    new Number(2000)});   /* highsal */
emps.executeQuery();


If, however, you want to build a WHERE clause in which the same bind variable repeats multiple times, you can't simply use the same positionally named variable multiple times. For example, if you want to check that both SAL and COMM fall within the same range of values, you must use a predicate such as SAL BETWEEN :0 AND :1 AND COMM BETWEEN :2 AND :3 and then assign the same value to the positionally named bind variables as required. Listing 3 demonstrates this approach.

Code Listing 3: Handling repeating bind values 

Number lowRangeVal  = new Number(0);
Number highRangeVal = new Number(2000);
emps.setWhereClauseParams(
  new Object[]{lowRangeVal,highRangeVal,       /* for SAL */
                     lowRangeVal,highRangeVal});     /* for COMM */
emps.executeQuery();


Encapsulating Bind Variables

The examples in Listings 2 and 3 show that working with positional bind variables can be tedious when the number of bind variables is high or when they have repeating values. To deal with these situations, one best practice is to write a custom method on your view object that encapsulates positional bind variable information, so that when developers use the view object, they don't have to remember its implementation details. In this approach, the logical meaning of each bind variable and the repeating nature of a value (including the exact positions that have repeating values) get encapsulated in the method. You could apply this technique to the view object described in the previous section by opening its EmpsInSalaryRangeViewImpl.java class and adding the method in Listing 4.

Code Listing 4: Encapsulating positional bind variables in a method 

public void findEmployeesInRange(Number lowRangeVal, Number highRangeVal) {
  setWhereClauseParams(
    new Object[]{lowRangeVal,highRangeVal,       /* for SAL */
                       lowRangeVal,highRangeVal});     /* for COMM */
  executeQuery();
}


This technique hides the details of the positional bind variable slots and exposes meaningful parameter names such as lowRangeVal and highRangeVal for use by other code that accesses this view object. Once you write this method and ensure that it compiles, you then go to the Client Interface panel of the View Object editor and add the findEmployeesInRange() method to the Selected list. This action prompts Oracle JDeveloper to generate a custom interface named EmpsInSalaryRangeView for the view object. With this interface, developers can easily use the custom method as shown in Listing 5.

Code Listing 5: Using the custom method 

EmpsInSalaryRangeView emps = (EmpsInSalaryRangeView)am.findViewObject("Emps");
emps.findEmployeesInRange(new Number(0), new Number(2000));


Bind Variable Enhancements in Oracle JDeveloper 10.1.3

Oracle JDeveloper 10g Release 3 (10.1.3) includes a new underlying database driver that supports bind variables with proper names. As a result, this release incorporates several new features in Oracle ADF that take advantage of this new capability. For example, there's an "Oracle Named" style of bind variable on the Query tab in the View Object editor. Also, a new Bind Variables panel lets you define one or more named bind variables as well as assign an appropriate Java type and a sensible default value for each one. These new bind variables can have meaningful names, and you can repeat them in any order in your WHERE clause as needed.

Using the new release, you could implement the example above by defining two bind variables named LowValue and HighValue for the EmpsInSalaryRangeView view object. You could then use a WHERE clause such as SAL BETWEEN :LowValue AND :HighValue AND COMM BETWEEN :LowValue AND :HighValue.

Next Steps


READ more about Oracle JDeveloper 10g and Oracle ADF
oracle.com/technetwork/products/jdev
oracle.com/technetwork/products/jdev/tips/muench/designpatterns

 DOWNLOAD Oracle JDeveloper 10g

Furthermore, Oracle JDeveloper 10g Release 3 introduces a new setNamedWhereClauseParam() API that lets you set bind variables by name. Using this API, Oracle JDeveloper 10g Release 3 automatically generates methods on a view object that encapsulate the behavior of setting named bind variables in a type-safe way. The new release also improves the Business Components Tester tool by displaying the named bind variables for a view object and by letting you easily change their values for testing purposes. Finally, the new release enhances its declarative data binding features to let you bind parameter form fields to named bind variables when building search pages.

Conclusion

In this column, I've presented a number of best-practice tips to show you how to make the most out of bind variables in Oracle ADF view objects. I've also described some improvements in Oracle JDeveloper 10g Release 3 (10.1.3) that will further simplify your life as an application developer. Now that you have this information under your belt, go put these techniques into practice on your next Oracle ADF application. 


Steve Muench is a consulting product manager for Oracle JDeveloper and an Oracle ACE. In his more than 15 years at Oracle, he has developed and supported Oracle tools and XML technologies and continues to evangelize them. Muench wrote Building Oracle XML Applications (O'Reilly Media, 2000) and shares tips and tricks on OTN and in his Dive into BC4J and ADF Weblog .

Send us your comments

Oracle Open World 2014 Banner

In-Memory Replay Banner