1.1 Comparing SQLJ and JDBC
You may already be familiar with JDBC, another technology for adding SQL statements to a Java program. SQLJ operates at a higher level of abstraction and has a simpler, more concise syntax than JDBC. This results in SQLJ programs containing fewer lines of source code than comparable JDBC programs. Also unlike JDBC, SQLJ is strongly typed. With SQLJ, the compiler does more of the work for you because it checks your embedded SQL statements during compilation. JDBC checks your SQL statements only when you actually run your program. In short, SQLJ enables you to develop your programs more rapidly, more concisely, and with fewer mistakes than if you were to use JDBC.
To show you the power of SQLJ as compared to JDBC, the following two code snippets illustrate the use of each technology to perform the same task: retrieving the name and price of a product stored in a database. First, the JDBC code snippet:
int v_id = 1; String v_name = null; float v_price = 0.0; PreparedStatement prepared_statement = connection.prepareStatement( "SELECT name, price FROM products WHERE id = ?" ); prepared_statement.setInt(1, v_id); ResultSet result_set = prepared_statement.executeQuery( ); while (result_set.next( )) { v_name = result_set.getString(1); v_price = result_set.getFloat(2); System.out.println("Name = " + v_name + ", price = " + v_price); } result_set.close( ); prepared_statement.close( ) Next, the SQLJ code snippet:
int v_id = 1; String v_name = null; float v_price = 0.0; #sql { SELECT name, price INTO :v_name, :v_price FROM products WHERE id = :v_id }; System.out.println("Name = " + v_name + ", price = " + v_price); See how the SQLJ code is much shorter and easier to understand? Also, SQLJ can check the SQL statement and variables for correctness, helping you trap errors in your programs before you run them.
1.1.1 Static and Dynamic SQL in SQLJ
A static SQL statement is one in which the database tables and columns referenced in the statement are known when the program is written. It is likely that all, or most, of the SQL statements you use in a program will be static. A dynamic SQL statement allows the table and column names referenced by the SQL statement to be set when the program runs. Version 9i of SQLJ allows you to embed both static and dynamic SQL statements in your SQLJ programs. With SQLJ Version 8.1.7 and below, you can embed static SQL directly and use JDBC statements to handle dynamic SQL. Chapter 11 will show you how to use JDBC, and how to embed dynamic SQL directly in your SQLJ programs.
1.1.2 Where Can You Use SQLJ?
You can use SQLJ to embed SQL statements in many types of Java programs, including the following:
Most of the examples in this book are applications that may be run from the command line. You will see examples of applets, servlets, and JavaServer Pages containing SQLJ statements in Appendix C. You will see examples of Oracle Java Stored Procedures and Enterprise JavaBeans containing SQLJ statements in Chapter 6.
