| Java Programming with Oracle SQLJ | | | Copyright | | | Table of Contents | | | Preface | | | 1. Introduction | | | 2. Relational Databases, SQL, and PL/SQL | | | 3. Fundamental SQLJ Programming | | | 4. Database Objects | | | 5. Collections | | | 6. Deploying SQLJ in the JServer | | | 7. Large Objects | | | 8. Contexts and Multithreading | | | 9. Advanced Transaction Control | | | 10. Performance Tuning | | | 11. Combining JDBC, SQLJ, and Dynamic SQL | | | A. Java and Oracle Type Mappings | | | B. Oracle Java Utilities Reference | | | C. SQLJ in Applets, Servlets, and JavaServer Pages | | | Colophon | | | Index | | | Database > Java Programming with Oracle SQLJ > 11. Combining JDBC, SQLJ, and Dynamic SQL | Chapter 11. Combining JDBC, SQLJ, and Dynamic SQL JDBC is an extension to the Java language that may be used to access a database. It is far more difficult to write and understand than SQLJ, and unlike SQLJ, JDBC is not strongly typed. By using SQLJ, you get the compiler to do more of the work for you because it checks your SQL statements during compilation, whereas JDBC checks your SQL statements only when you actually run your program. SQLJ enables you to develop your programs more rapidly, more concisely, and with fewer mistakes than if you were to use JDBC. The one advantage JDBC does have over SQLJ at least through Version 8.1.7, but not for 9i and above is the ability to run dynamic SQL statements. A dynamic SQL statement is one in which the database tables and columns referenced in the statement may be set when the program is executed, and are not necessarily known when the program is compiled. Static SQL statements remain fixed during the execution of a program, and the tables and columns involved are known when the program is compiled. This advantage of JDBC over SQLJ goes away in Version 9i, however. In SQLJ Version 9i, you can use SQLJ statements to execute dynamic SQL without having to use JDBC at all; I show you how to do this later in this chapter. If you are using SQLJ Version 8.1.7 or below, you can embed JDBC statements in your SQLJ programs to handle your dynamic SQL needs; I show you how to do that as well. In the first section of this chapter, I discuss in detail the differences between static and dynamic SQL statements, and show you how SQLJ 9i now supports dynamic SQL. In subsequent sections, I show the two ways in which SQLJ and JDBC may work together in the same program. The first way is through the sharing of database connections, which allows your SQLJ and JDBC statements to share the same database session and transaction. The second way is by sharing rows retrieved using SQLJ iterators with JDBC result sets, and vice versa. A JDBC result set is similar to a SQLJ iterator, and is used to store the results of a SQL SELECT statement. This allows you to write JDBC statements that contain dynamic SQL to populate a JDBC result set, which you can then access using a SQLJ iterator. As you will see, processing an iterator is a lot easier than processing a result set. For a thorough introduction to the subject of JDBC, I recommend the book Database Programming with JDBC and Java by George Reese (O'Reilly). |
Index terms contained in this section dynamic SQL static vs. JDBC (Java Database Connectivity) SQL (Structured Query Language) dynamic static vs. static SQL statements | |