October 25, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Introduction to SQLJ

  • October 13, 2005
  • By Serge Bornow
  • Send Email »
  • More Articles »

Want to write less code within your Java applications that deal with SQL calls to a database? Would you like your SQL statements to be checked during compilation and not runtime? Then SQLJ just may be what you need! SQLJ gives exactly what you need for quick development, with less code, ease of debugging, and automatic performance improvement.

This article is part of the SQLJ series. This article assumes you have basic knowledge of JDBC and some of its methods, as well as some SQL. If you want to refresh your JDBC knowledge, have a look this article—JDBC Basics.

What is SQLJ?

This article will give you all the necessary information on how to start coding SQLJ and get you started coding the Data Tier much quicker then ever. SQLJ is essentially embedded SQL (Structured Query Language, sometimes referred to as Sequel) placed into Java applications. SQLJ has been developed to facilitate the development of database-oriented projects, cutting down the development/debugging time significantly. When developing Java applications using JDBC, you are still left to test your SQL against the database.

Code trouble may come from syntax and other semantics that may not be correct, or simply column names may be misspelled, causing the cycle test/debug/code to repeat. Debugging is particular easy with SQLJ because you see the actually generated source and can quickly locate any bug.

By using SQLJ, your code becomes more maintainable and flexible. Sometimes, specifications for your application may change and that should not be unexpected. You often create complex SQL statements combining unions, joins, and multiple where clauses with dynamic values. However, if you use SQLJ, you will be pleased to see that your code is still readable even if it contains an SQL jungle. For example, with SQLJ you don't need to concatenate your SQL statements; you can write as many SQL statements as you like as long as they are properly marked. If you use Oracle, you can use any dbms package, procedures, custom packages, and all those neat built-in functions right inside your Java application. If you are a PL/SQL developer, you might find this very helpful in developing an advanced software reusing your PL/SQL code and style.

Dynamic versus Static SQL

Unlike embedded static SQL, dynamic SQL may not be known until runtime. At this point, all the validation and parsing of the SQL statement sent to the database server from your application may generate an error. Optimization and analysis of the static SQL can be performed during compilation time, therefore significantly improving the performance of your application.

The Tasks that SQLJ Translator Performs

  • Syntactic checking of the embedded SQL constructs
  • Java and SQL data type checking
  • Schema checking

Figure 1: SQLJ workflow

Setup

At this point, you may be asking, "What do I need for this to work?" You can use pretty much any Oracle Database starting from the 8th version. You must always remember that the database that you will use must have JVM (Java Virtual Machine) built in it, IBM AS/400 (OS 360), or what they now call I-Series IBM operating system has JVM.

For this article, I've used Oracle 8i and in the code examples you note that the connection and part of the code refer to Oracle libraries. Create/Drop scripts are available in the source file. Below is the sequence and a table that will be used in this article as example:

CREATE SEQUENCE customer_seq
   MINVALUE 1
   MAXVALUE 999999999999999
   START WITH  1
   INCREMENT BY 1
   CACHE 20; 
create table customer (
   ID number(12) PRIMARY KEY ,
   fullname varchar2(100)  NULL,
   street varchar2(100) null,
   city varchar2(100) null,
   Province varchar2(100) null,
   country varchar2(100) null
);
COMMIT;

Besides your database, you would need the following additional libraries to transform SQLJ:

translator.jar, runtime12.jar (oracle library)

Writing a SQLJ Application

Now that you have an understanding of setups, you can write a simple application that uses SQLJ to retrieve data from the fullname and street name data columns:

Code Listing 1.1

import java.sql.*;    // you need this import for SQLException
                      // and other classes from JDBC
import oracle.sqlj.runtime.Oracle;
public class SingleRowQuery extends Base {
   public static void main(String[] args) {
      try {
         connect();
         singleRowQuery(1);
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
   public static void singleRowQuery(int id) throws SQLException {
      String fullname = null;
      String street = null;
      //OUT is actually the default for INTO host variables
      #sql {
         SELECT fullname,
            street INTO : OUT fullname,
            : OUT street FROM customer WHERE ID = :id};
      System.out.println("Customer with ID = " + id);
      System.out.println();
      System.out.println(fullname + " " + street);
   }
}

Code Explanation:

Next, you are going to look at the important parts of the code in Listing 1.1. The class Base deals with connection context and is available with this article.

import oracle.sqlj.runtime.Oracle;    // part of the translator.jar
                                      // that deals with all SQLJ
                                      // statements

Any method that has a SQLJ statement must always have throws SQLException; otherwise, the translator will not work. You will see why from the generated code (see Listing 1.2).

Now, move on to the data handling part. You maybe a little confused if you know PL/SQL. Why is "INTO" being used in this context? In this example, you are using a Select statement to save the values into two Host Variables by specifying INTO : OUT.

: OUT means the values that are stored in host variables
: IN means the variables read from; if you are using a method to get the value, you must enclose it with brackets like this: :( this.getName() ).

: [mode] host_variable

Mode can only be OUT, IN, or both INOUT. Remember that OUT is write and IN is read; INOUT will do both.





Page 1 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel