1.5 A "Hello World" Program for SQLJ
Many programming books introduce new languages using a variation of the "Hello World" program. The classic "Hello World" program has a simple objective: to display the words "Hello World" on the screen. My version of "Hello World" uses SQLJ statements to connect to the database and retrieve the current date, which is then displayed on the screen. First, I'll show you the code required for this "Hello World" program; then I'll show how to compile and run it. By convention, SQLJ programs use the file extension .sqlj, so the "Hello World" program will be named HelloWorld.sqlj.
 | As mentioned in the Preface, source code for all the example programs in this book can be downloaded from the book's web site at O'Reilly, http://www.oreilly.com/catalog/orasqlj. |
|
1.5.1 Example Program: HelloWorld.sqlj
The HelloWorld.sqlj program, shown in Example 1-1, performs the following tasks:
-
Connects to the database.
-
Retrieves the current date from the database.
-
Displays a message containing the current date obtained in the previous step.
-
Disconnects from the database.
Example 1-1. HelloWorld.sqlj
/* The program HelloWorld.sqlj illustrates how to connect to a database, and display the words "Hello World" along with the current date. */ // import required packages import java.sql.Date; import java.sql.SQLException; import oracle.sqlj.runtime.Oracle; public class HelloWorld { public static void main(String [] args) { java.sql.Date current_date; try { // connect to the database Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger" ); // get the current date from the database #sql { SELECT sysdate INTO :current_date FROM dual }; // display message System.out.println("Hello World! The current date is " + current_date); } catch ( SQLException e ) { System.err.println("SQLException " + e); } finally { try { // disconnect from the database Oracle.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); } } } // end of main( ) } Most of the statements in this program are no different from those in a regular Java program. Let's look at the lines specific to SQLJ:
import java.sql.date; import java.sql.SQLException; import oracle.sqlj.runtime.Oracle;
The java.sql packages contain the classes, libraries, and interfaces for SQLJ programs. The program imports the java.sql.Date and java.sql.SQLException classes, which I'll describe later. The oracle.sqlj.runtime.Oracle package contains the runtime methods for establishing connections to the database, including the Oracle.connect( ) and Oracle.close( ) methods.
The following program line declares an object named current_date:
java.sql.Date current_date;
The current_date object is declared using the java.sql.Date class. This is the SQL version of the java.util.Date class; I used the SQL version because the program will later retrieve the current date from the database and store it in the current_date object. If you want to use both java.util.Date and java.sql.Date to declare objects in a SQLJ program, then you must explicitly use the full class name when declaring each object. For example:
java.sql.Date sql_date; java.util.Date java_date;
The main body of the program is contained within a try/catch block. All SQLJ statements must be contained within a try/catch block in order to trap any database errors that may occur. Such blocks may appear as follows:
try { ... } catch (SQLException e) { System.err.println("SQLException " + e); } If a database error occurs, an exception of type java.sql.SQLException is raised, and the catch block is entered. In the example shown here, the catch block displays the details of the exception. Let's look at the statements inside the try block. The first one connects to the database:
Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger" );
The Oracle.connect( ) method is used to connect to the database. The first parameter in the method call is known as the database URL, and specifies the name and location of the database along with the name of the driver to use when accessing the database. In the example shown here, the database URL is set to jdbc:oracle:thin:@localhost:1521:orcl, which specifies that a connect request is being made to a database instance named orcl running on the machine localhost using the Oracle JDBC Thin driver. I will discuss the details of database URLs and drivers in Chapter 3. The second parameter is the name of the schema you wish to access, in this case the scott schema. This is an example schema that should exist in your database; if it doesn't, any schema can be used for this example. The third parameter is the schema password; the default password for the scott schema is "tiger".
Next, a SQL statement is embedded into the program as follows:
#sql { SELECT sysdate INTO :current_date FROM dual }; This SQL statement retrieves the current date from the database and stores it in the current_date object; the colon indicates that current_date is a Java object defined in the program. The dual table used in this SQL statement is always present in an Oracle database; it is a dummy table that generally contains one row and allows you to use the Oracle built-in functions such as sysdate, which returns the current date. This date is then displayed in the "Hello World" greeting by means of the following statement:
System.out.println("Hello World! The current date is " + current_date); Disconnection from the database, performed using a call to the Oracle.close( ) method, is handled within a finally block. This is executed before the program exits:
} finally { try { // disconnect from the database Oracle.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); } } It is considered good programming practice to disconnect from the database within a finally block, but for conciseness, only the first two programs in this book do this.
1.5.1.1 Compiling and running HelloWorld.sqlj
The HelloWorld.sqlj file contains embedded SQL statements contained within SQLJ program statements. Having written the program, your next step is to translate the SQLJ statements contained in the HelloWorld.sqlj file into calls to the SQLJ runtime libraries. The SQLJ runtime libraries then use JDBC to actually perform the database operations. This translation is performed using the sqlj command-line utility, which translates the .sqlj file into a .java file and compiles the .java file into a .class file using the Java compiler.
The following command invokes sqlj to translate and compile HelloWorld.sqlj:
sqlj HelloWorld.sqlj
Here, the sqlj command-line utility translates HelloWorld.sqlj into HelloWorld.java, and then calls the Java compiler to compile HelloWorld.java into HelloWorld.class. The HelloWorld.class file may then be executed using the java command-line utility:
java HelloWorld
The program should display the following line (of course, your date will be different):
Hello World! The current date is 2000-11-10
That's it! You have successfully compiled and run your first SQLJ program. If your program is unable to connect to the database, check with your database administrator to determine whether the database URL is correct for your environment.
