Database Programming
in SQLJ
Julie Basu, Ekkehard Rohwedder, and Brian Becker
Java Products Group, Oracle Corporation
1105023628 JavaOne '99 Confidential 1
Outline of Talk
• SQLJ Language
• SQLJ Translator
• SQLJ Runtime
1105023628 JavaOne '99 Confidential 2
Part One
The SQLJ Language
Julie Basu
1105023628 JavaOne '99 Confidential 3
What Is SQLJ?
The ANSI standard for
embedded SQL in the Java™
programming language
• Java programming language code with
snippets of SQL—start with #sql, end with ;
int ssn; String name;
#sql { … SQL statement … };
// more Java code
1105023628 JavaOne '99 Confidential 4
SQLJ Design Goals
• Standard compact syntax for static SQL
• Simpler model than JDBC™ technology
• Robust:
– Strongly typed resultsets and connections
– Compile-time SQL checking
• Binary portability and extensibility
1105023628 JavaOne '99 Confidential 5
Compact SQLJ Syntax
• SQL statement can span multiple lines
• Java programming language host
expressions in SQL statement
String bug = “spider”;
#sql {
INSERT INTO bugs (name, numlegs)
VALUES (:bug, :(getNumLegs(bug)))
};
1105023628 JavaOne '99 Confidential 6
Simpler than the JDBC™ API
No explicit setXXX binds
– Correct setXXX calls made automatically
No explicit statement handles
– Managed by SQLJ runtime
Default connection context
– Static variable set in SQLJ runtime
– #sql statement can implicitly use it
1105023628 JavaOne '99 Confidential 7
15-minute SQLJ Tutorial
• Connect to database(s)
• Query
• Update
• FAQ
1105023628 JavaOne '99 Confidential 8
Connecting to a Database
• Step 1: Load your JDBC driver
[Link]
( “[Link]” );
• Step2: Set the SQLJ DefaultContext
import [Link];
…
[Link](
new DefaultContext(url, user, pwd,
autoCommit));
1105023628 JavaOne '99 Confidential 9
Using Multiple Databases
• Use an explicit connection context
– For multiple databases or connections
– For applets
#sql context Db1;
// context class declaration
…
Db1 myDb1 = new Db1(url, user, pwd,false);
#sql [myDb1] { UPDATE bugs ... };
1105023628 JavaOne '99 Confidential 10
BUGS Table
NAME NUMLEGS PICTURE
ant 6
spider 8
centipede 44
beetle 6
1105023628 11
Querying One Row of Data
Use SELECT..INTO for single-row queries
int count; double avgLegs;
#sql { SELECT count(*), avg(numLegs)
INTO :count, :avgLegs
FROM bugs
};
• No equivalent construct in the JDBC API
– Need to use next() on a ResultSet
1105023628 JavaOne '99 Confidential 12
Multi-row Queries
• Use SQLJ iterators:
Typed resultsets with query “shape”
– Java programming language types of columns +
names -or- positions
• Named iterator class declaration
#sql public iterator BugIter(
String name, int numLegs);
• Positional iterator class declaration
#sql public iterator BugIter2(String,int);
1105023628 JavaOne '99 Confidential 13
Using SQLJ Iterators
5 program steps:
1. Declare an iterator class ( in #sql )
2. Define an instance of the class
3. Populate the instance with query (in #sql)
4. Access the column data
– Use method calls for named iterators
– Use #sql syntax for positional iterators
5. Close the iterator
1105023628 JavaOne '99 Confidential 14
Declaring an Iterator
#sql public iterator BugIter(
String name, int numLegs);
Declares a class
• Class name: BugIter, modifier: public
• Scroll down rows: boolean next()
• Column accessor methods
– String name(), int numLegs()
– Method names match columns of SQL query
• Done: close()
1105023628 JavaOne '99 Confidential 15
Populating an Iterator
public void listBugs() throws SQLException {
BugIter b = null;
try {
#sql b = { SELECT name, numLegs
FROM bugs };
while ([Link]()) printBugs(b);
} finally {
if (b != null) [Link]();
}
}
1105023628 JavaOne '99 Confidential 16
Accessing Iterator Columns
• Use accessor methods for named iterators
private void printBugs (BugIter b)
throws SQLException {
[Link](
"Bug name: " + [Link]() +
", # legs: " + [Link]());
}
• For positional iterators use
#sql {FETCH :b INTO :name, :numLegs};
1105023628 JavaOne '99 Confidential 17
Updates
[Link] ec =
new [Link]();
#sql [ec] { UPDATE bugs SET picture = :pic
WHERE name = :bug };
[Link]([Link]() +
" rows updated");
• Use execution context for:
– Results of update, SQL warnings
– Fine control: query timeout, max rows, field size
– Multi-threaded SQLJ programs
1105023628 JavaOne '99 Confidential 18
Calling Stored Programs
• Stored procedures
#sql { call STORED_PROC(
:IN x, :OUT y, :INOUT z) };
• Stored functions
int a; String b;
#sql a = { VALUES ( STORED_FUNC(:IN b))};
• Procedural blocks (optional feature)
#sql { BEGIN … procedural block …
END; };
1105023628 JavaOne '99 Confidential 19
Common Questions
Q: My SQL column name is not a valid Java
identifier; can I still use named iterators?
A: Yes, in your SQL query use AS < Java id >
to give an alias to the SQL column name
Q: Why do I run out of database cursors?
A: Make sure you are closing all iterators
1105023628 JavaOne '99 Confidential 20
Common Questions
Q: Can I use dynamic SQL in a
SQLJ application?
A: Yes, use the JDBC API for dynamic SQL;
SQLJ code interoperates with JDBC
• SQLJ iterators are convertible to
JDBC API resultsets and vice-versa
• SQLJ connection contexts are convertible to
JDBC API connections and vice-versa
1105023628 JavaOne '99 Confidential 21
Benefits of SQLJ Language
& Vendor-neutral ANSI standard
& Concise syntax for static SQL
& High productivity
& Robust: typed iterators and connections
& Interoperates with the JDBC™ API
1105023628 JavaOne '99 Confidential 22
Part II
The SQLJ Translator
Ekkehard Rohwedder
1105023628 JavaOne '99 Confidential 23
SQLJ Translation
• Generate Java™ programming language
code that calls the SQLJ runtime
– Optional: connect to database to check SQL
• Compile
• Run
1105023628 24
One Step
SQLJ Translator
[Link] [Link] [Link]
Java Compiler
% sqlj [Link]
% java Bug
1105023628 25
SQL Checking
String bug = “Centipede”;
byte[] pic = new byte[] { … };
#sql { UPDATE bugs SET picture = :bug
WHERE name = :pic };
• Type mismatch!
– Must translate online to detect
1105023628 JavaOne '99 Confidential 26
Checking SQL in Database
SQLJ Translator
[Link] [Link] [Link]
Java Compiler
Checker
% sqlj [Link] -user=scott/tiger
DBMS % java Bug
1105023628 27
Offline Checking
• Type resolution for host expressions
int i;
#sql i =
{ SELECT name,
numLegs + :(i-”x”)
FROM bugs };
• May need to pass .java files to SQLJ
• Static SQL syntax
#sql { comitt work };
1105023628 28
SQL Checkers
• Offline and online
• Vendor-specific
• Analyze SQL statement
and bind expressions
• Implement the interface
[Link]
1105023628 29
SQL Checkers (Cont.)
• boolean
supportsDatabase(errlog,conn)
– For online checkers
• describeSQLOperation
(errlog,conn,sqlop)
– Determine role of SQL statement
– Check modes and types of host expressions
– Issue errors and warnings
• Resulting program cannot be different
between offline/online checking
1105023628 30
Standard Checkers
• JDBC™ API-based SQL Checker
– Uses JDBC API ResultSetMetaData to
determine actual shape of queries
– Uses JDBC API meta data to verify
• Query column types
• Stored procedure and function signatures
• Vendor-specific checkers
– e.g. Oracle-checker uses system tables for
metadata, parses SQL in database
1105023628 31
SQLJ Customizations
• Usually vendor-specific, for example to
support SQLJ standard
• Example
#sql { SET :x = 5 };
// in Oracle
#sql { BEGIN :OUT x := 5; END };
• Customizer reads static SQL information,
adds information to profile (.ser file)
1105023628 32
SQL Stored [Link] Files
SQLJ Translator
[Link] [Link] [Link]
Java Compiler
#sql
#sql{{ Profile:
Profile:
INSERT…
INSERT… Entry0
Entry0
};};
[Link]
Entry0:
Entry0:
INSERT…
INSERT…
1105023628 33
Transparent Translation
• sqlj works like javac
– Similar options (and then some)
– Mix .sqlj and .java files
– Get translation and compilation errors on .sqlj
source files
– Instrument class files to support debugging of
and runtime errors on .sqlj source
• SQLJ can adapt to your command-line
JDE (JDK™ software is default)
1105023628 34
SQLJ Implementation
• All Java technology (JDK 1.1+ software)
• Uses Java™ Compiler Compiler™
(JavaCC) parser generator
• Embeddable in IDE tools via call API
– Oracle’s JDeveloper IDE
– JServer Java technology/SQLJ compiler
– Others…
• Plug-in SQL checker, customizer
1105023628 35
Standard Components
• JDBC technology-based components
– SQL Checker
– Runtime implementation
• Customization utilities
– Profile pretty printer
– Runtime call auditor
1105023628 36
Free Stuff!!
• SQLJ Reference Implementation provided
by Oracle. Source code in public domain.
Available at [Link]
• Goals
– Get large installed base ASAP
– Share “standard” code: Java programming
language parsing and analysis, code generation,
runtime libraries
– Define components for parts that are vendor-
specific, extensible
1105023628 37
Part Three
The SQLJ Runtime
Brian Becker
1105023628 JavaOne '99 Confidential 38
Binary Portability Standard
• More than traditional source portability
• Does for DBMS what Java™ technology
does for OS
• Parts of the binary standard
– Standard runtime library
– Standard code generation
– Standard description of SQL operations
(SQLJ Profile)
1105023628 39
Generated Objects
SQLJ Translator
[Link] [Link] [Link]
Java Compiler
#sql
#sql{{ Profile:
Profile:
INSERT…
INSERT… Entry0
Entry0
};};
[Link]
Entry0:
Entry0:
INSERT…
INSERT…
1105023628 40
Generated .java Files
• #sql statements replaced by sequence of
standard calls to the SQLJ runtime
– Create a statement object
– Bind inputs (if any)
– Execute
– Fetch outputs (if any)
– Close
1105023628 41
Example: [Link]
String bug = “spider”;
#sql {
INSERT INTO bugs (name, numLegs)
VALUES (:bug, :(getNumLegs(bug)))
};
1105023628 42
Example: [Link]
String bug = “spider”;
RTStatement stmt =
[Link](“BugProfile”, 0);
[Link](1, bug);
[Link](2, getNumLegs(bug));
[Link]();
[Link]();
1105023628 43
Generated Profile Files
Profile
Profile ProfileData
ProfileData EntryInfo
EntryInfo n TypeInfo
TypeInfo
data
data profile
profile n
params
params n
entries
entries results
results TypeInfo
TypeInfo
• Resource file that describes the
SQL operations in a SQLJ program
• Serialized object or Class format
• Standard API
1105023628 44
Example: BugProfile
• Entries (1):
– SQLString:
“INSERT INTO bugs (name, numLegs) VALUES (?, ?)”
– Role: STATEMENT
– ExecutionType: EXECUTE_UPDATE
– Parameters (2):
• JavaType: String, SQLType: VARCHAR,
Mode: IN, Name: “bug”
• JavaType: int, SQLType: INTEGER,
Mode: IN, Name: null
1105023628 45
Runtime Call Stack
Generated Code Profile
Standard Runtime
Runtime Implementation
DBMS
1105023628 46
Runtime Implementations
SQLJ Program
Standard Runtime
JDBC Extended JDBC Compiled SQL
JDBC
DBMS IBM DB2
Oracle
1105023628 47
Runtime Performance
• Why not standardize on
JDBC™ technology?
• Static vs. dynamic SQL execution
• Execution performance tuned according
to static SQL characteristics in profile
• Tuning can be done at “deployment-time”
– Work moved from runtime to deploy time
– May use online connection
– May require additional persistent data
1105023628 48
Profile Customization
• Vendor specific (AKA “customization”)
data installed into SQLJ profile
• Customization data available at runtime
Profile
Profile ProfileData
ProfileData EntryInfo
EntryInfo n TypeInfo
TypeInfo
data
data profile
profile n
params
params n
custs entries
entries results
results TypeInfo
TypeInfo
n
Oracle IBM SAP
1105023628 49
Customization Steps
Oracle Customizer
IBM Customizer
[Link] [Link] [Link]
8i Cust 8i Cust
DB2 Cust
DBMS
1105023628 50
Customization Selection
• At runtime, connection is used to select a
matching customization, which installs a
matching runtime implementation
Profile
Profile Connection
Connected
Connected
Customization
Customization Profile
Profile
1105023628 51
Extensibility
• Profile customization also allows standard
way to extend SQLJ
– at runtime
– at deployment time
• Customization object is able to specify
exactly what gets executed and how
1105023628 52
Runtime Extensibility
SQLJ Program
• Examples:
– Bind expression
type extensions
Standard Runtime
– Debug call tracing
Call Auditor
– Y2K bug fixes
JDBC
DBMS
1105023628 53
Deploy-time Extensibility
• Redefine characteristics of the SQL
statements to execute
• Use same runtime engine, but execute
using modified statements
• Examples:
– Map standard SQL into dialect of DBMS
– Define new SQL statements, clauses
and shorthands
1105023628 54
Example: Oracle 8i
• Extended Types
– ROWID, Ref CURSOR, LOBs, Native Types
– Object, Ref, Array, Custom Types
• Extended SQL
– PSM-style assignment statement
– PL/SQL
1105023628 55
Other Success Stories
• All DBMS Vendors:
Shared reference implementation
• Oracle:
JServer, Oracle 8i Lite
• IBM, Compaq:
Compiled SQL runtime implementation
• 3rd Party:
SQL debugging and optimization
1105023628 56
Benefits
• Does for DBMS what Java technology
does for OS
• DBMS industry 1st for shared technology
• Opens the SQL engine to value added by
vendors, 3rd parties, and end users
• Allows for unanticipated improvements
without the need for source recompilation
1105023628 57
Resources
• [Link]
– Free download of source/binary for Ref Impl
• [Link]
– Free 8.0.5 and 7.3.4 versions of SQLJ
– 8i documentation and business collateral
• Books
– SQLJ chapter in “Client/Server Programming with
Java™ and CORBA,” by Orfali,Harkey et al
– Several others in the works
1105023628 58
1105023628 59