SQLJ: The 'open
sesame' of Java database applications
Tutorial Details:
SQLJ: The 'open sesame' of Java database applications
SQLJ: The 'open sesame' of Java database applications
By: By Shirley Ann Stern
The SQLJ standard now provides SQL-based database access from Java apps
NSI and ISO standards for embedded SQL in general-purpose programming languages such as C, FORTRAN, COBOL, and ADA have been available for several years. SQL (Structured Query Language) itself dates back to the 1970s and today is accepted as the standard RDBMS language. SQL is powerful because it lets you work with data at the logical level. Implementation details can wait until you are ready to manipulate the data. Thousands of businesses and organizations worldwide use SQL to manipulate and manage large amounts of data effectively. Because of this widespread use of SQL, many businesses saw Java's lack of SQL support as a serious challenge for enterprise applications. Several leading database and application vendors met this challenge head-on and resolved to do something about it. The result was the formation of a consortium to define the SQL Java (SQLJ) standard.
This article will take a look at that standard, which is a set of programming extensions that define the interaction between the SQL database language and the Java programming language. SQLJ consists of a set of clauses that extend Java to include static SQL constructs. The SQLJ clauses are transformed by a translator into standard Java statements that access the database via a call interface.
The article will also explore the differences between SQLJ and JDBC. It's important to note that SQLJ supports static SQL only, and is complementary to JDBC. JDBC provides a fine-grained, dynamic interface from Java to the database.
The birth of a standard
About two years ago (April 1997 to be exact) Oracle, IBM, and Compaq's Tandem Division formed a consortium to define and advance the standardization of Java development for enterprise-level and server-side database applications. Other key database and application server vendors -- Sybase, Informix, and what was then called JavaSoft (now the Java Software Division at Sun) soon joined them. As a cofounder of the original SQLJ consortium, Oracle distributed a reference implementation of SQLJ to the other members in December 1997.
For the next year, the consortium members cooperated to formulate a comprehensive specification for submission to the ANSI/ISO standards body. They also went to great lengths to ensure the compatibility and interoperability of all SQLJ implementations from the various database vendors. In December 1998, this specification, now called SQLJ, was accepted as ANSI Standard X3.135.10-1998. This was a significant milestone for the Java language for two reasons:
It provides integration of SQL and Java, thus reinforcing the adoption and use of Java for enterprise data-intensive applications
It represents a landmark in multivendor cooperation and support for standards-based application development
What does the SQLJ Standard provide? The standard consists of three parts: The SQLJ Language Specification provides standard language syntax and semantics for embedding static SQL in Java programs. The Stored Procedure Specification defines standards for implementing database stored procedures and functions in Java. This will allow users who have written stored procedures in Java to easily migrate them between databases. The Stored Java Class Specification addresses standard ways to store Java datatypes and classes as objects in a database.
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.
SQLJ is comprehensive
It provides embedded SQL syntax to simplify database access for a variety of different facilities. These include transaction management, queries, DDL statements, DML statements, and stored procedure and function calls. These procedures and functions could be written in Java, C, C++, or any other language supported by the database. Languages specific to a database, such as Oracle's PL/SQL, can also be used. Let's look at a stored procedure example using PL/SQL:
#sql { CALL PROC( ) };
In the example above, PROC is the name of the existing stored procedure defined in Oracle PL/SQL:
CREATE OR REPLACE PROCEDURE MAX_DEADLINE (deadline OUT DATE) IS
BEGIN
SELECT MAX(start_date + duration) INTO deadline FROM projects;
END;
This procedure reads the table called "projects" in the database, looks at the start_date and duration columns, calculates start_date plus duration in each row, then takes the maximum start_date + duration total and selects it into deadline , which is an output parameter of type DATE . In SQLJ, you can call this MAX_DEADLINE procedure as follows:
java.sql.Date maxDeadline;
...
#sql { CALL MAX_DEADLINE(:out maxDeadline) };
In short, SQLJ offers developers substantial benefits for static SQL applications. Use of SQLJ reduces the number of written lines of code and performs type and schema checking earlier in the development cycle for improved quality and productivity. At the same time, it opens up the richness of SQL for data manipulation to the Java language. Developers now truly have a choice of using Java, C, or any other programming language to develop the logic for their database applications.
SQLJ's database independence
Besides productivity, SQLJ offers a second major benefit -- increased portability. SQLJ applications were designed to be vendor-independent in three important ways: First, the SQLJ syntax is designed to be database-neutral, and the SQLJ translator makes minimal assumptions about the SQL dialect. Second, the consortium members share a common SQLJ translator reference implementation. Third, the SQLJ-generated code and runtime are standard. A SQLJ program can access any data server for which a SQLJ runtime implementation exists. Since the default implementation of the SQLJ runtime performs database access using JDBC, a SQLJ program can access any data server for which JDBC drivers are implemented.
By ensuring interoperability between SQLJ implementations, the standard offers users the ability to develop applications in Java that can be transparently moved from one database platform to another. The code generated by the SQLJ translator is 100 percent standard Java code that can be executed in any standards-compliant Java virtual machine (JVM). This means that compiled Java classes (Java bytecodes) from translated SQLJ programs can be moved to any compatible SQLJ platform and executed regardless of which platform initiated the original translation. This allows SQLJ programs to be partitioned easily across different tiers in a distributed architecture and deployed in many different environments without any code changes.
The SQLJ runtime environment is vendor-neutral also. SQLJ's runtime environment consists of a thin layer of pure Java code that communicat
Read
Tutorial at: Click here to view the tutorial
Rate Tutorial: SQLJ: The 'open
sesame' of Java database applications
View Tutorial: SQLJ: The 'open
sesame' of Java database applications
Related
Tutorials:
|
Displaying 1 - 50 of about 3517 Related Tutorials.
|
 Open Source Java Database
Open Source Java Database
Open Source Java Database
An Open Source Java Database
One$DB is an Open Source version of Daffodil... is an Open Source version of Daffodil DB, our commercial Java Database. One$DB
|
Open Source Database
;
An Open Source Java Database
One$DB is an Open Source version...
Open Source Database
Open Source Database
Open Source Database Benchmark
|
Eclipse Plugin-Rich Client Applications
applications with open source components.
...;
JFire - Free Java ERP framework
JFire is a flexible Open Source...
Eclipse Plugin-Rich Client Applications
Eclipse
|
Open Source Database Connection Pools written in Java
|
JDBC - Java Database Connectivity Tutorial
JDBC - Java Database Connectivity Tutorials
JDBC - Java Database Connectivity Tutorials
...;
Java Database
|
Database books Page11
) that allows Java applications to access multiple database management systems using...
Java Database Connectivity (JDBC) from the Java Software Division of Sun...
Database books Page11
Database books Page11
|
Applications and Applets
;
Now a days, Java is widely used for applications and
applets...
Applications and Applets
Applications and Applets... and is executed by a run-time interpreter.
Applications are stand alone
|
Open Source JMS
Open Source JMS
Open Source JMS
Open Source JMS
OpenJMS is an open source implementation of Sun Microsystems's Java Message... * Administration GUI
* XML-based configuration files * In-memory and database garbage
|
Building Search Engine Applications Using Servlets !
Building Search Engine Applications Using Servlets...
Search Engine Applications Using Servlets... of
building search engine using Java Servlets. You can Download
|
Database Engines
to use Hibernate (see below).
References
Open Source Database Engines in Java (java-source.net/open-source/database-engines)
has an excellent list...
Java: Database Engines
|
Open Source Exchange
, a community bent on developing open-source desktop applications that compete... Server, is written in Java, and sits upon familiar open source components like...
Open Source Exchange
Open Source Exchange
|
JDBC (Java Database Connectivity) -Tutorials
JDBC Tutorials, Java Database Connectivity Tutoria
JDBC (Java Database Connectivity) -Tutorials
... to come with necessary features for
making Java to shine in database
|
Open Source Groupware
Open Source Groupware
Open Source Groupware
Open
Groupware
Open Group 1.1.4 is the fourth official trunk snapshot (alpha) of the upcoming
Open Group 1.2. This release has
|
Open Source Jobs
the standard Java Timer API, and then we will focus on Quartz, an open source library... Scheduling Framework
If your Java applications depend on tasks that must... with leading open source Java frameworks, including Hibernate and
Struts. Using
|
Open Source Java
Open Source Java
Open Source Java
Open Source Software in Java
AspectJ is a seamless aspect-oriented...;
Sun to open-source Java
|
Open Source Jobs
the standard Java Timer API, and then we will focus on Quartz, an open source library... Scheduling Framework
If your Java applications depend on tasks that must... with leading open source Java frameworks, including Hibernate and
Struts. Using
|
Open Source Testing
;
Open Source Automated Test Tools Written in Java
MaxQ is a free web... for popular open-source programs, one that will submit applications such as Apache...
Open Source Testing
Open Source Testing
Open Source
|
Open Source Community
Open Source Community
Open Source Community
Open
Source Research Community
In the spirit of free and open source... will be freely exchanged, so that we may further the understanding of open source
|
Designing Database
database for this application. MySQL one of the open source,
free and widely used relational database for applications.
Creating the database:
I am assuming...
Designing Database
Designing Database
|
Eclipse Plunging-Database
in their Java program. * Visual Database Browser
Allows users...
PriDE is a thin open-source and high-performance Java O/R mapper...-
database
CROSSFIRE O/R generates JAVA source codes to execute O/R mapping
|
Open Source SQL
is an open-source JDBC 2.x compliant database front end written in Java. It implements... is an Open Source version of Daffodil DB, our commercial Java Database. One$DB...
Open Source SQL,Open Source SQL Clients in Java,Free SQL Open Source
|
Database books Page9
interface for your application.
ODBC (Open Database Connectivity... for Java in the database server provides powerful new ways of managing and storing...
Database books Page9
Database books Page9
|
Database books Page10
database applications. It consists of a high-performance relational database... of applications and libraries, which run on database clients. This arrangement...
Database books Page10
Database books Page10
|
Open Source Jobs
the standard Java Timer API, and then we will focus on Quartz, an open source library... Scheduling Framework
If your Java applications depend on tasks that must... with leading open source Java frameworks, including Hibernate and
Struts. Using
|
IBM Open Source
database containing an index of open-source computer code. This database should... including the Java-based Apache Geronimo web server and Apache Derby database...;
IBM urges Sun to make Java open source
IBM's vice president
|
Open Source web mail
;
Open Source Web Mail Clients in Java...
Open source web mail
Open Source web mail
Open
Web Mail Project
Open WebMail is a webmail system based on the Neomail
|
Open Source e-commerce
versions of applications that are built in adherence to open-source development...
Open Source e-commerce
Open Source e-commerce
Open Source Online Shop E-Commerce
|
Connecting to the Database Using JDBC and Pure Java driver
Connecting to the Database Using JDBC and Pure Java driver...
engine we are using MySQL database server and MM.MySQL Driver
for connecting our application to the database. MM.MySQL Driver
|
Open Source Images
several database-driven Web applications in my new role as coordinator...
Open Source Images
Open Source Images
Open
Source Image analysis Environment
TINA (TINA Is No Acronym) is an open source
|
Open Source Blog
reference process model working. We maintain a database with the open source...
Open Source Blog
Open Source Blog
About Roller
Roller is the open source blog server that drives Sun Microsystem's
|
Open Source CMS
Content Management
Apache Lenya is an Open Source Java/XML Content...
Open Source CMS
Open Source CMS
Open Source Content Management
|
Open Source PHP
Open Source PHP,Open Source PHP Shopping Cart,Open Source PHP & MySQL Resources
Open Source PHP
Open-source PHP framework
AjaxAC is an open-source framework written in PHP, used to develop/create/generate
|
Open Source Web Frameworks in Java
Open Source Web Frameworks in Java
Open Source Web Frameworks
in Java
Struts
Struts Frame work... Java developers to quickly build web applications. Turbine allows you to use
|
Open Source Servers
to an open source environment so they can create applications without having to change... to open-source server software
Sun is considering making its Java Enterprise...
Open Source Server
Open Source Servers
Open Source
|
Open Source Intelligence
Open Source Intelligence
Open Source Intelligence
Open
source Intelligence
The Open Source movement has established over... is the collaborative gathering and analysis of information, a practice we term "Open Source
|
Applications - text example
Java: Applications - text example
Java NotesApplications - text example
The programs that you normally run are called applications.
A typical MS Windows application has an extension
|
Open Source projects
;
Java technology : Open source projects... Server open source project is derived from the Sun Java System Portal Server 7...
Open Source Projects
Open Source projects
Mono
|
Database books Page4
/server database applications.
This manual provides a starting point... Adaptive Servers, Open Server(TM) applications (such as Backup Server...
Database books Page4
Database books Page4
|
Database books
for developing client/server database applications.
... for developing and deploying relational database applications. It consists... database servers, and a collection of applications and libraries, which run
|
Database books Page8
;
Database server Connect Programmer's Reference for PL/1
Open ServerConnect... applications can execute. Open ServerConnect transactions can retrieve and update data... clients such as Open Client applications, third-party tools, and server
|
Open Source Workflow Engines in Java
Open Source Workflow Engines in Java
Open Source Workflow Engines in Java
The Open For Business Project: Workflow Engine Guide...) is the first open source graphical Java workflow process editor fully according
|
Accessing database from JSP
Accessing database from JSP
Accessing database... going to discuss the connectivity from MYSQL
database with JSP.we take a example of Books database. This database
contains a table named books_details. This table
|
Retrieve image from database using Servlet
to develop program for
your java based applications that retrieves the image from...
Retrieve image from database using Servlet
Retrieve image from database using Servlet
|
Setting up MySQL Database and Tables
applications. The Java Server Faces specification is defined by JSR 127 of the Java... database. In the next section we will
write java stuffs to integrate struts...
MySQL Database Setup,MySQL Hibernate,Hibernate MySQL,Hibernate MySQL Tomcat
|
What is MySQL
is a open source Relational Database Management System. MySQL is
very fast... on the web. The MySQL
Database has become the world's most popular open..., Java , Delphi etc. are available to connect to MySQL database.
MySQL Advantages
|
Developing responsive Ajax based Applications with ajax technologies
on open standards supported by many
browsers and platforms. AJAX is a new...
applications. These days Ajax has been used for the development of
responsive web... of chat application developed in Ajax. These chat applications are very
|
Accessing Database from servlets through JDBC!
to clean up any resources(such as open files or
database connections...
Installing Servlets,How to Download and Install Java Servlet,Help to Install Java Servlet
Java Servlets - Downloading
|
Open Source Forum
Open Source Forum
Open Source Forum
Open Source Forum on Software Patents
The Open Source Forums will be short and focused seminars on topical developments in the Open Source field. It is intended that each
|
Database books Page14
that supports DB-Library, CT-Library, and Open Database Connectivity (ODBC) application... building block for highly-scalable database middleware applications. DirectConnect...
Database books Page14
Database books Page14
|
Open Source Templatess
;
Java
open Source J2EE Templates
EJOSA (Enterprise Java Open Source Architecture) is a project...
The Enterprise Java Open Source Architecture Template
|
|
|
|
