JDBC/Oracle Tutorial 3

Dynamic SQL

As opposed to specifying SQL statements as string parameters to functions, a program can also have stand alone SQL statements.  The former method, which is employed by JDBC, is known as a call level interface.  Stand alone SQL statements are called embedded SQL statements.  Here is an example of an embedded SQL statement:
 
#sql { INSERT INTO branch VALUES (99, 'Central', '321 W. 5 Ave.', 'Vancouver', 7458222) };


Embedded SQL statements are known as static SQL statements because their structure is determined at precompile time, i.e., before running javac.  Java's version of embedded SQL is called SQLJ.  A program with embedded SQL statements needs a precompiler (translator), which translates the SQL statements into calls to a DBMS-specific runtime library.  In SQLJ, a JDBC driver is used to access a database.  For information on how an embedded SQL program is compiled, click here.  For information on how a DBMS processes an SQL statement, click here.

The counterpart to embedded SQL is dynamic SQL.  Unlike embedded SQL statements, dynamic SQL statements can be built "on the fly" at runtime rather than being defined at precompile time.  This means that dynamic SQL does not require a precompiler.  You can do almost everything with dynamic SQL as you can with embedded SQL.  In addition, dynamic SQL allows you to make queries where the number of select-list items, number of input host variables, and the datatypes of the input host variables are unknown until runtime.  Although dynamic SQL is more flexible than embedded SQL, there are benefits to using embedded SQL.  For example, in embedded SQL, errors are checked earlier: at precompile time rather than at runtime.  A program can have both embedded and dynamic SQL statements.  For information on how a dynamic SQL statement is processed, click here.

As you will soon see, JDBC and thus call level interfaces have dynamic SQL capabilities.  Like dynamic SQL, call level interfaces pass SQL statements to the DBMS for processing at runtime.  However, with JDBC, there is no easy means to specify database object names at runtime because you cannot use ? in a prepared statement to represent a table or column name.  You will need to use string manipulation routines to dynamically build the SQL string.  The next section provides an example.

 

Specifying Database Object Names at Runtime

The example below is a function that is called when the OK button is clicked in a window that allows a user to select which columns in the branch table to view.  This function constructs the query string based on the columns that were selected.  It then calls a function named executeQuery() to execute the query.  The variables branchIDCBox, branchNameCBox, branchAddrCBox, branchCityCBox, and branchPhoneCBox are check boxes that allow the user to select the branch id, branch name, branch address, branch city, and branch phone columns respectively (these variables are declared somewhere else in the program).  For example, if the user wants to see the branch name and branch city columns, he/she would select the branchNameCBox and branchCityCBox.  It is not necessary for you to know Swing in order to understand this example.  The point of this example is to help you understand the capabilities of dynamic SQL.  The code is pretty much self-explanatory.
 
  /*
  * Constructs the query based on which columns were selected.
  * Returns true is the query is valid and successfully executed by
  * the executeQuery() method; otherwise returns false.
  */
 private boolean constructQuery()
 {
     StringBuffer statement = new StringBuffer("SELECT");

     int numBoxesSelected = 0;

     if (branchIDCBox.isSelected())
     {
       statement.append(" branch_id");
       numBoxesSelected++;
     }

     if (branchNameCBox.isSelected())
     {
       if (numBoxesSelected > 0)
       {
         statement.append(", branch_name");
       }
       else
       {
         statement.append(" branch_name");
         numBoxesSelected++;
       }
     }

     if (branchAddrCBox.isSelected())
     {
       if (numBoxesSelected > 0)
       {
         statement.append(", branch_addr");
       }
       else
       {
         statement.append(" branch_addr");
         numBoxesSelected++;
       }
     }

     if (branchCityCBox.isSelected())
     {
       if (numBoxesSelected > 0)
       {
         statement.append(", branch_city");
       }
       else
       {
         statement.append(" branch_city");
         numBoxesSelected++;
       }
     }

     if (branchPhoneCBox.isSelected())
     {
       if (numBoxesSelected > 0)
       {
         statement.append(", branch_phone");
       }
       else
       {
         statement.append(" branch_phone");
         numBoxesSelected++;
       }
     }

     statement.append(" FROM branch");

     if (numBoxesSelected > 0)
     {
       return executeQuery(statement.toString());
     }

     return false;
 }


 
 

JDBC/Oracle Tutorial 3
Last updated May 8 2011