Database Connections and SQLJ Statements

This second column in my Learning SQLJ series explores how to connect to a database and embed SQL statements in your Java programs using SQLJ. These columns reference numerous SQL scripts, source code, and other files that are available for download at O'Reilly's Web site. For more specific information about the files used, or to get an introduction to SQLJ, read my first column, Setting Up Your Environment to Develop SQLJ Programs.

SQLJ Programs

A SQLJ program, like any other Java program, is divided up into blocks, and a SQLJ statement may appear anywhere that a normal Java statement may appear. All SQLJ statements begin with the language token #sql in order to differentiate those statements from other Java program statements.

There are two types of SQLJ statements:

• Declarations.
• Executable statements.

Declarations are used to declare iterators and connection contexts. Iterators are used to store result sets from SQL queries that may return more than one row. Connection contexts are used to establish database connections. Executable statements are used to execute embedded SQL statements and PL/SQL blocks. Any SQL statement that is supported by the JDBC driver may be embedded within a SQLJ executable statement. Executable statements may also contain host expressions, which are used to exchange information between the Java program and the database via Java variables.

In order to perform any type of database operation, your SQLJ program must first connect to the database, so without further ado, let's plunge in!

Database Connections

The easiest way to connect to a database is to use the connect() method defined in the class oracle.sqlj.runtime.Oracle. Recall from my first column that this is one of the classes that you must import into your SQLJ program.

The connect() Method

The connect() method accepts three parameters: a database username, a password, and a database URL. The syntax of a call to the connect() method is as follows:

Oracle.connect(URL, username, password);

The syntax elements are as follows:

URL

Identifies the database to which you want to connect, along with the driver you wish to use to make the connection. See the following section, titled "Database URL Structure," for details on this parameter.

username

Specifies your database username.

password

Specifies the password for your username.

The following example shows the connect() method being used to connect to a database:

Oracle.connect(

  "jdbc:oracle:thin:@localhost:1521:orcl",

  "fundamental_user",

  "fundamental_password"

);

In all cases, the connection to a database is made through Oracle Net8 (or above).