Java Programming with Oracle SQLJ

11.1. Static and Dynamic SQL

All the SQL statements you have seen so far in this book have been static, that is, the tables and columns referenced have remained fixed during the execution of a program. The following is an example of a static SQL statement:

SELECT
  id, first_name, last_name
FROM
  customers;

The next statement is still considered static, even though the asterisk (*) is used to select all the columns:

SELECT
  *
FROM
  products;

A dynamic SQL statement is one that may be set when the program is run, and in which the tables and columns referenced in the statement may be changed from time to time. The following example, which uses JDBC calls, shows a dynamic SQL statement being generated and executed at runtime:

String sql_statement = "SELECT id, first_name, last_name " +
  "FROM customers " +
  "WHERE " + where_clause;

PreparedStatement prepared_statement
  jdbc_conn.prepareStatement(sql_statement);

ResultSet result_set = prepared_statement.executeQuery(  );

The prepareStatement( ) method sends the sql_statement string containing the SQL statement to the database for parsing. The executeQuery( ) method runs the SQL statement and returns a ResultSet object, which is the JDBC equivalent of a SQLJ iterator. I talk more about JDBC result sets later in this chapter.

The WHERE clause in this example can be passed in from the command line when the program is run, and can contain any valid SQL WHERE clause syntax. This is the power of dynamic SQL: the ability to specify the SQL statement ...