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 ...