SQLJ Statements and the SQLJ Translator
| To use SQLJ, we embed SQLJ statements directly in our Java code. SQLJ statements are not valid Java source code, however, so we need to distinguish between SQLJ files and Java files. We do this by naming our SQLJ files with a .sqlj suffix. Then, instead of compiling them with the javac compiler, we need to process them with the SQLJ translator. Normally, the SQLJ compiler does two things for you: It interprets the SQLJ file and creates a valid Java source file a .java file from it, then compiles the Java source file into an executable class file. The process is illustrated in Figure 7-1. Figure 7-1. SQLJ compilation.
Let's start with this short example that logs us into the Oracle sample account, SCOTT, with the password TIGER: // SimpleExample.sqlj import java.sql.*; import sqlj.runtime.ref.DefaultContext; import oracle.sqlj.runtime.Oracle; public class SimpleExample { public static void main (String [] args) { try { Oracle.connect("jdbc:oracle:oci:scott/tiger@osiris"); String name; #sql {SELECT ENAME INTO :name FROM EMP WHERE EMPNO=7900}; System.out.println("Found: " + name); } catch (SQLException e) { System.out.println("Caught: " + e); } } } First, notice the import statements at the top: We import the SQL package and a couple of classes specific to SQLJ. Because our code deals with SQL, we need to catch SQLExceptions. In this simple example, we don't do anything but print out the result, should a problem occur. In a real program, we need to be more discerning than this. We can anticipate certain exceptions and provide an appropriate response. Next, notice the Oracle.connect() method. This contains a string, the database URL, that must be modified to include the name of your database. This actually will load one of two JDBC drivers and make it available as the default connection context for the SQLJ statements in the program. Once we've imported those SQL and SQLJ classes and gotten connected, we can start embedding SQL statements in our code. This example has just one SQLJ statement that queries the Oracle sample table, EMP: #sql {SELECT ENAME INTO :name FROM EMP WHERE EMPNO=7900}; SQL statements in SQLJ are always preceded with #sql. This can be followed by a number of options, including Java class modifiers such as public and static, and an assignment. Because this is a simple example, we haven't included any of these. Next is an opening brace: This is where we can actually start using SQL but not just standard SQL. SQLJ extends SQL to make it easier to integrate with Java. There are two notable things about the statement in this example. The query includes the syntax, SELECT…INTO. This is an SQLJ (and PL/SQL) extension of the standard SQL SELECT that makes it easier to obtain single-row results. We'll learn more about single row queries later in this chapter. For now, we'll just note the general features of this statement. Like a standard SQL select statement, SELECT is followed by a select list, in this case, a single column. SQLJ also introduces the INTO syntax, which allows us to specify a Java host variable where the value returned by the SQLJ statement is to be stored. Finally, we close the SQLJ statement with a brace and semicolon. Note that we do not terminate the SQL statement itself with a semicolon. The database connectivity layer, JDBC, which is working behind the scenes for us, is responsible for properly terminating SQL queries. After this SQLJ statement is executed, we will find the query's result in the Java variable name and, if everything was successful, we print it out. Having typed this program into a file called SimpleExample.sqlj, we are prepared to compile it. We use the sqlj command at a command prompt to do this. The easiest way is simply: C:\sqlj SimpleExample.sqlj But we can also provide a connection string so that the SQLJ translator can connect to the database to do additional checking for us. C:\sqlj -user=scott/tiger SimpleExample.sqlj The -user=scott/tiger option causes the SQLJ translator to connect to the database at compile time and validate the SQL statements against the database. If our path and CLASSPATH are set up correctly and there are no other errors in the code, SQLJ will do its job silently no news is good news. If you want feedback, you can add the -status flag to the command line. The SQLJ translator creates a number of different files. First, it converts the embedded SQLJ statements into calls to the SQLJ runtime, which is a thin wrapper for the JDBC driver, and saves this as a Java source file, SimpleExample.java. By default, it also compiles it into SimpleExample.class. This isn't all, however; it also creates Java objects containing detailed information about each SQL statement in our program. By default, these Java objects are serialized converted to a form that can be saved and retrieved as a file but there is an SQLJ translator option that allows them to be saved as .class files, instead.We can run the SimpleExample.class file like any other Java program. C:\java SimpleExample If all goes well, it will print out: Found: JAMES Simple Embedded SQL StatementsThe easiest SQLJ statements to code are DDL statements, such as CREATE TABLE, ALTER TABLE, and DROP TABLE. This is the general format: #sql { SQL statement }; The following code sample fragment creates a table: #sql { CREATE TABLE EXAMPLE( COL1NUMBER, COL2VARCHAR2(10))}; DML statements such as INSERT, UPDATE, and DELETE are easy, too. Typically, they are used to insert, change, or delete data, based on values determined at runtime by the Java code. To facilitate this, SQLJ allows Java variables and expressions within the SQL statement. Note that, although SQL statements, including column names are case-insensitive, Java host variables and expressions are sensitive. Java variables embedded in SQL are called host variables or bind variables and are distinguished by being preceded by a colon. :name There are three types of host variables which have the modes IN, OUT, and INOUT. IN variables provide input values from the SQL point of view and are used, for example, to add data to the database in INSERT statements and to set criteria in WHERE clauses. OUT variables must be l-values that is, they must be assignable, or valid to use on the left side of an equal sign; OUT variables, we shall see, are used principally for returning results from queries. INOUT variables are used when calling procedures that have parameters that are used for both input and output. We can specify the mode by adding it after the colon and separating it from the variable name by whitespace. :IN name In general, however, the host variables default to the appropriate mode, IN or OUT, based on the SQL statement. (We won't be using procedures in this chapter, so we don't need to be concerned about INOUT for now.) DDL statements expect IN variables. In addition to variables, we can also use other types of Java expressions in place of IN variables. If we are using a Java expression that consists of more than just a variable name, however, the expression needs to be enclosed within parentheses; otherwise, the SQLJ translator will try to interpret the first token alone as a variable. Assuming that the variables have been previously declared and initialized, here are some examples of valid host variables and expressions: :name :(names[i]) :(firstname + " " + lastname) :IN (mystring.substring(2,3)) :IN (i + 100) Suppose we want to populate the table created above, EXAMPLE, with three rows like these:
The following code sample will do this: int i; for(i=1; i<=3; i++) { #sql {INSERT INTO EXAMPLE VALUES (:i, :(Character.toString((char)('A' + i - 1))))}; } Notice that we use a static method, Character.toString(), as the host expression. It converts a character value, calculated from the int variable i, into a String, so that we can generate the sequence of letters. There is one final statement that we must include after inserting this data: COMMIT. Unless we commit the transaction, the changes we made to the database will be discarded when the program ends because, by default; SQLJ does not automatically commit our changes. This following statement should be added after the for loop above: #sql {COMMIT}; It's also possible to commit automatically so that changes to the database are immediately made permanent. This is a property of our connection, and the easiest way to change it is by using an alternate form of the Oracle.connect() method that takes an additional parameter, a flag that controls auto-commit. Oracle.connect("jdbc:oracle:oci:scott/tiger@osiris", true); By default, this flag is set to false by the SQLJ compiler. (As we will see, this is the opposite of the behavior for JDBC, where auto-commit is on by default.) There are advantages to both, but the difference is basically that auto-commit is slightly more convenient to use, whereas manual-commit allows better control over transactions because it allows us to back out of a transaction completely when a portion of it fails. Single-Row QueriesOur example above used the special single-row SELECT…INTO statement which, as you will see, is far easier to use than any other way of querying a database. The drawback is that you must somehow ensure that the query will return only a single row; otherwise, an SQLException will result at runtime. The surest way to ensure this is to restrict using SELECT…INTO statements to queries that have a WHERE clause that uses the table's primary key. The general format of a single-row query in SQLJ is SELECT expression1[, expression2[, ...]] INTO :hostexpression1[,:hostexpression2[,...]] FROM table [optional SQL clause]
In our simple example, we obtained the name of an employee, given by an employee number. Let's take a look at an example that uses several columns of several types. Taking a look at the EMP table, we see SQL> desc emp; Name Null? Type --------------------------------- -------- ------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) We'll pick just a few columns for our SELECT statement: ENAME, JOB, SAL, and HIREDATE. It's good practice to first try our SQL statement in SQL*Plus. SQL> SELECT ENAME, JOB, SAL, HIREDATE 2 FROM EMP 3 WHERE EMPNO=7900; ENAME JOB SAL HIREDATE ---------- --------- ---------- --------- JAMES CLERK 950 03-DEC-81 In our Java code, we'll create the host variables: String name, job; int salary; Date hiredate; Then we'll embed our SQL statement, adding the INTO clause to transfer the values from the SELECT into our Java host variables. #sql {SELECT ENAME, JOB, SAL, HIREDATE INTO :name, : job, :salary, :hiredate FROM EMP WHERE EMPNO=7900}; Finally, we print out the values. System.out.println("Name: " + name + ", Job: " + job, ", Salary: " + salary + ", Hire date: " + hiredate); This is the output, if all's gone well: Name: JAMES, Job: CLERK, Salary: 950, Hire date: 1981-12-03 In one final example, we'll use a join and combine values from two different tables. We'll include the employee's name and city, and calculate length of employment based on hire date. The name and date of hire are in the EMP table. We can calculate the length of employment by subtracting the date of hire from the current date. In Oracle, the current date is available as the pseudo-column SYSDATE. We can find out the employee's location based on the department table. This requires a join between the EMP and the DEPT table; joining the two on the DEPTNO column to obtain the LOC. We can first run the following SQL statement in SQL*Plus to verify that this works and that it gives us what we want. SQL> SELECT ENAME, LOC, TRUNC((SYSDATE-HIREDATE)/365.25,0) 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.EMPNO=7900; ENAME LOC TRUNC((SYSDATE-HIREDATE)/365.25,0) ---------- ------------- ---------------------------------- JAMES CHICAGO 20 (Notice that we need to identify the table for each column in the WHERE statement, but in the select list, this isn't necessary because the columns we are selecting are unambiguous.) To use this SQL statement in an SQLJ single-row query, we need to add the INTO clause to transfer the results to the Java host variables. String name, city; int years; #sql { SELECT ENAME, LOC,TRUNC((SYSDATE-HIREDATE)/365.25,0) INTO :name, :city, :years FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.EMPNO=7900 }; System.out.println("Name: " + name +", City: " + city + ", Years: " + years); Multiple-Row QueriesAlthough the single-row query is convenient, the more frequent case is that a query returns (or can return) multiple rows. There is no way around the fact that this is significantly more complicated to code. The only consolation is that using SQLJ to process multiple-row result sets is easier and less error-prone than using JDBC. There are two ways to process multiple-row queries: named iterators and positional iterators. In both cases, we declare an iterator class that we will use to iterate through the result set, row by row. The iterator class has a parameter list that defines the host variables it accepts. The host variables parameter list must match the select list of the SELECT statement by either name or type. As you might guess, in a named iterator, the host variable parameters are associated by name with the select list items. In a positional iterator, they are associated by the position of the select list items. An important feature of both types of iterators is that they are strongly typed the number and types of the host variables are specified at compile time in the class's parameter list. In general, the steps required for creating and using an iterator, named or positional, are the same:
The difference is in the details, as we shall see. Named IteratorsIn a named iterator, the iterator's parameters are associated with the items in the select list by name this requirement for explicit identification between a named iterator and its corresponding SELECT statement makes for a stronger and less error-prone association than that of a positional iterator. Named iterators provide an additional convenience. Because the parameters have names, the SQLJ translator is able to provide us with accessor methods, based on those names. Declaring a Named IteratorThe declaration for a named iterator looks more like a method declaration than a class declaration, mostly because it has a list of parameters with a type and a name for each but it is nonetheless a class declaration. This is the basic format: #sql <modifiers> iterator classname( hosttype1 hostname1[, hosttype2 hostname2,...]);
This class declaration can appear anywhere that a standard Java class declaration can appear, because the SQLJ translator will, in fact, turn it into a full-fledged class in place, complete with member variables and methods. Declaring the iterator outside the scope of any class is probably the easiest thing to do. The main limitation is that if it is declared public, it will need to be in a file of its own but it's not a good idea to make iterators public, anyway. If functionality of this type needs to be exposed to other classes, it should be done indirectly through a wrapper or an accessor class. This is an example of a declaration outside the class: // EmployeeExample.sql import java.sql.*; import sqlj.runtime.ref.DefaultContext; import oracle.sqlj.runtime.Oracle; #sql iterator EmployeeIter(String ename, Long sal, String job); public class EmployeeExample { // etc. There are advantages to declaring the iterator inside a class as an inner class but this can cause problems if we try to instantiate the iterator in a static method such as factory method.
Declaring the Named Iterator Instance VariableBefore we can instantiate and populate an iterator, we first need to declare an instance variable for the iterator class, as follows: EmployeeIter empiter; Instantiating and Populating a Named IteratorNow that we have an instance variable for our iterator class, we can use SQLJ to instantiate it and populate the iterator for us. We do it with this statement: #sql empiter = {SELECT ENAME, JOB, SAL FROM EMP}; SQLJ translates this into code that instantiates the iterator class, executes the SQL statement using JDBC, and populates the iterator with the result set. Each item in the SQL statement's select list is matched by name to a host variable, as declared in the iterator class's parameter list. It doesn't matter if the select list items appear in the same order as the host variables parameter list. There must be at least as many items in the select list as there are host variables. Extra items in the select list are ignored. Sometimes, columns have cryptic names, and it may be out of our power to change them. We can assign an alias in the SELECT statement using the AS keyword; in this example we change SAL to SALARY: #sql empiter = {SELECT ENAME, JOB, SAL AS SALARY FROM EMP}; Column aliases are especially useful when we perform calculations or include function calls in our select list. For example, in the single-query example, we calculated the number of years from an employee's hire date. To include this in a query using a named iterator, we need to use an alias, as follows: SELECT ENAME, TRUNC((SYSDATE-HIREDATE)/365.25,0) AS YEARS FROM EMP Accessing the Rows in a Named IteratorIn order for us to move to the first row and to move from row to row after that, the iterator class that SQLJ generates for us provides a next() method. The next() method returns true if there is a next row, false if there are no more rows. The logic to iterate through a result set is while(empiter.next()) { // access and process this row... } To access the columns in each row, the iterator class provides us with a set of accessor methods, one accessor method for each host variable we listed in the named iterator's declaration. The methods each have the same name and type as the variables. For this declaration: #sql iterator EmployeeIter(String ename, long sal, String job); SQLJ will produce the following accessor methods:
Closing the IteratorThe last step after we have finished using the iterator is to free the resources that it is using by calling its close() method. Failing to do this can cause a resource leak, which can eventually cause the program or more mysteriously, other people's programs on other systems to fail when the database resources necessary to perform a query or other operations are exhausted. Named Iterators Sample ProgramsThis small sample program goes through the steps necessary for using a named iterator to perform a multiple-row query: declaring the named iterator, declaring an instance variable for the iterator, instantiating and populating the iterator variable with an SQL call, accessing the rows, and closing the iterator when we're finished. // EmpQuery.sql import java.sql.*; import sqlj.runtime.ref.DefaultContext; import oracle.sqlj.runtime.Oracle; #sql iterator EmployeeIter(String ename, Long sal, String job); public class EmpQuery { public static void main(String []args) { try { Oracle.connect("jdbc:oracle:oci:scott/tiger@osiris"); EmployeeIter empiter; #sql empiter = {SELECT JOB, SAL, ENAME FROM EMP ORDER BY SAL DESC}; while(empiter.next()) { System.out.println("Name: " + empiter.ename() + ", Job: " + empiter.job() + ", Salary: " + empiter.sal()); } empiter.close(); } catch (SQLException e) { System.out.println("Caught: " + e); } } } Here is another example that uses columns aliases and performs a join on two tables. It is similar to the second example we saw for single queries, except that we don't restrict the query to a single employee. // EmpAliasQuery.sql import java.sql.*; import sqlj.runtime.ref.DefaultContext; import oracle.sqlj.runtime.Oracle; #sql iterator EmployeeIter(String name, String city, int years); public class EmpAliasQuery { public static void main(String []args) { try { Oracle.connect("jdbc:oracle:oci:scott/tiger@osiris"); EmployeeIter empiter; #sql empiter = {SELECT ENAME AS NAME, LOC AS CITY, TRUNC((SYSDATE-HIREDATE)/365.25,0)AS YEARS FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO}; while(empiter.next()) { System.out.println("Name: "+empiter.name() + ", City: "+empiter.city() + ", Years: " + empiter.years()); } empiter.close(); } catch (SQLException e) { System.out.println("Caught: " + e); } } } Positional IteratorsPositional iterators are an alternative to named iterators. Because they are similar to the way embedded SQL queries are used in other languages (in particular, Oracle's Pro*C precompiler for C/C++), many experienced developers find them more familiar and comfortable to use. For those unfamiliar with Pro*C, however, they are somewhat more complicated and less flexible to code than are named iterators. Because they associate the iterator's parameters with items in the select list anonymously, the only check performed at runtime is to ensure that the types of each are compatible; extra care needs to be taken to prevent errors. Declaring Positional IteratorsIn a positional iterator, the iterator's parameters are associated with items in the SQL select list by position, not name. Because the SQLJ positional iterator declaration includes an anonymous list of parameter types, it doesn't look quite like either a method or class declaration. This is the basic format: #sql <modifiers> iterator classname( hosttype1 [, hosttype2 ,...]);
This declaration can appear anywhere that a standard Java class declaration can appear. The order of the host types in the parameter list must correspond to the SQL SELECT statement. Suppose we want to process the results returned by the query: SELECT ENAME, JOB, SAL FROM EMP; The corresponding Java type for ENAME and JOB is String. We have several suitable variable types for SAL, but we'll go with long, just to keep it simple and safe. The positional iterator declaration then would be #sql iterator EmployeeIter(String, String, long); The easiest way to use this is as a top-level class, by declaring it in the same file but outside the scope of the class that uses it. We'll see an example shortly. Declaring the Positional Iterator Instance Variable and Host VariablesExactly as with a named iterator, we need to declare an instance variable for the iterator before we can instantiate and populate it. However, in addition to the iterator instance variable, we also need to declare and initialize host variables for each of the iterator's parameters. These names don't need to match the select list item names, of course, but it's helpful if the names are at least similar, so that the relationship is clear. We also need to initialize them, to either 0 in the case of primitives or null in the case of classes such as String or Date. // Declare Iterator instance variable EmployeeIter empiter; // Declare and initialize iterator host variables String empName = null; String empJob = null; long empSalary = 0L; Instantiating and Populating a Named IteratorThe positional iterator is instantiated and populated by an SQLJ statement, the same as a named iterator. #sql empiter = {SELECT ENAME, JOB, SAL FROM EMP}; This instantiates the instance variable, executes the SQL statement, and populates the iterator with the result set. Accessing the Rows in a Positional IteratorPositional iterators provide us with a way to move from row to row and to access the columns, like named iterators, but the technique is quite different. To access the rows in a positional iterator, we need to use a special SQLJ statement, FETCH…INTO, which transfers the results of each row from the iterator to our host variables. It looks like (but isn't) a standard SQL statement. #sql {FETCH :iterator INTO :hostvariable1, : hostvariable2, ... }; When we first call this statement it advances to the first row of the iterator's result set. Thereafter, it moves to the next row. If this move is successful, it then populates the host variables. We can then test to see whether the call to FETCH…INTO was successful by calling the method endFetch(). If endFetch() returns true, it means that the FETCH…INTO was unsuccessful and that we have exhausted our result set. If endFetch() returns false, it means that FETCH…INTO was successful and a new row has been read into our host variables. This might suggest, incorrectly, that we might be able to use a while loop as follows: // This loop doesn't work while(!empiter.endFetch()) { #sql {FETCH :empiter INTO :empName, :empJob, :empSalary); // process empName, empJob, emp Salary here? } There are two problems with this loop. First, endFetch() returns true if it is called before FETCH…INTO, so we would never enter this loop in the first place. We could fix that by using a do…while loop, which guarantees that the loop executes at least once, but we're still left with the problem that we don't know whether valid values were returned by FETCH…INTO until after we call endFetch(), so at the end, we process a row of bogus values. (Because we are guaranteed that the last call to FETCH…INTO is invalid, we could use a do…while loop as long as we discard the last set of results. This is possible if the host variables are members of an array or if we are inserting the returned values into a dynamic structure, such as a vector or linked list, for example, that we can fix up after the loop.) A better approach, however, is to test endFetch() immediately after the FETCH…INTO statement and use it to set a flag for the while loop and, at the same time, to decide whether to process the host variables. boolean done = false; while(!done) { #sql {FETCH :empiter INTO :empName, :empJob, :empSalary}; if(!(done = empiter.endFetch()) { System.out.println("Name: " + empName + ", Job: " + empJob + ", Salary: " + empSalary); } } Another way to accomplish the same thing is to use an infinite loop and break out of it when endFetch() returns true: while(true) { #sql {FETCH :empiter INTO :empName, :empJob, :empSalary}; if(empiter.endFetch()) { break; } System.out.println("Name: " + empName + ", Job: " + empJob + ", Salary: " + empSalary); } Note that because the assignment of the host variables is conditionally executed, the Java compiler will complain if they are not initialized outside the loop. This is why we initialize them to 0 or null when we declared them. |
lib\ru ntime12.zip;c:\oracle\ora90\sqlj\lib\translator.zip 
EAN: 2147483647
Pages: 71
- Using SQL Data Definition Language (DDL) to Create Data Tables and Other Database Objects
- Using SQL Data Manipulation Language (DML) to Insert and Manipulate Data Within SQL Tables
- Using Keys and Constraints to Maintain Database Integrity
- Working with SQL JOIN Statements and Other Multiple-table Queries
- Understanding SQL Subqueries