SQLJ: The 'open sesame' of Java database applications

The SQLJ standard now provides SQL-based database access from Java apps

By Shirley Ann Stern, JavaWorld.com, 05/01/99

Page 2 of 6

SQLJ productivity

So who can use SQLJ? The answer is, anyone involved in application development -- independent software vendors, corporate programmers, IT management, and all application developers. When should SQLJ be used? SQLJ is an excellent choice for static SQL programming tasks, and many SQL applications are static in nature. SQLJ does not handle dynamic SQL actions determined at runtime of the application; JDBC must be used to handle dynamic SQL.

SQLJ primarily is a productivity environment that gives Java developers a quick and easy way to use SQL directly in their Java applications without the tedium of having to do database programming. This means that applications involving a very large quantity of data manipulation, such as financial, personnel, or inventory control, can now be written in Java rather than in C.

Veteran SQL programmers can benefit from using SQLJ, as they can concentrate on Java application logic while continuing to use familiar SQL to access the database. And any developer familiar with JDBC will appreciate SQLJ because it eliminates the overhead of writing the actual JDBC calls.

How does SQLJ deliver all this?

SQLJ provides application developers with a higher-level programming interface than JDBC for static SQL
You can see the difference in the number of lines of code by comparing the following examples showing an update from a query result.

The first is the SQLJ example:

#sql iterator SeatCursor(Integer row, Integer col, String type, int status);
Integer status = ?;
SeatCursor sc; 
#sql sc = { 
select rownum, colnum from seats where status <= :status 
   };  
while(sc.next()) 
     {    
#sql { insert into categ values(:(sc.row()), :(sc.col())) };  
     }   
sc.close();

And here's the JDBC example:

Integer status = ?;
PreparedStatement stmt = conn.prepareStatement("select row, col from seats
where status <= ?");  
if (status == null) stmt.setNull(1,Types.INTEGER);  
else   stmt.setInt(1,status.intValue()); 
ResultSet sc = stmt.executeQuery();   
while(sc.next()) 
{    
int row = sc.getInt(1);   
boolean rowNull = sc.wasNull();    
int col = sc.getInt(2);  
boolean colNull = sc.wasNull();    
PreparedStatement stmt2 = conn.prepareStatement("insert into categ
values(?, ?)");    
if (rowNull) stmt2.setNull(3,Types.INTEGER);   
else stmt2.setInt(3,rownum);      
if (colNull) stmt2.setNull(4,Types.INTEGER);  
else stmt2.setInt(4,colnum);          
stmt2.executeUpdate();  
stmt2.close();
} 
sc.close();
stmt.close();

The SQLJ translator performs type-checking and schema-checking of SQL statements at program development time, rather than at runtime
Because of this, programs written in SQLJ are more robust than JDBC programs and are much easier to maintain. Also, unlike JDBC, SQLJ permits compile-time checking of the SQL syntax, of the type compatibility of the host-variables with the SQL statements in which they are used, and of the correctness of the query itself with respect to the definition of tables, views, stored procedures, and so on, in the database schema. It should be pointed out again that SQLJ and JDBC are complementary because SQLJ supports static SQL only. To perform dynamic SQL operations from a SQLJ program, you still must use JDBC.