SQLJ: Java and Relational Databases Phil Shaw, Sybase Inc. Brian Becker, Oracle Corp. Johannes Klein, Tandem/Compaq Mark Hapner, JavaSoft Gray Clossman, Oracle Corp. Richard Pledereder, Sybase Inc. Agenda ➨ Introduction ✔ SQLJ Part 0: Embedded SQL and Portability Profile ✔ SQLJ Part 1: Java Methods as SQL Procedures ✔ SQLJ Part 2: Java Classes as SQL Types 1
Java and Databases – JDBC • Java Database Connectivity API • Widely Implemented – SQLJ™ • Java-Relational Database Technology • Portability; Productivity; Java in the Database • Leverages JDBC technology – JavaBlend • Object/Relational Mapping for Java – The focus of this tutorial is on SQLJ SQLJ - The Consortium – Structure is informal • Participants include Oracle, IBM, Sybase, Tandem, JavaSoft, Microsoft, Informix, XDB • Open to other participants – Meetings • Approximately every 3-4 weeks • Hosted by one of the Bay Area resident vendors (Oracle, Sybase, Tandem, JavaSoft, Informix, etc.) • Participants: Product Architects + SQL Standards people 2
SQLJ - The Technology – Part 0: SQLJ Embeded SQL • Mostly reviewed and implemented • Integrated with JDBC API • Oracle has placed Translator source into public domain – Part 1: SQLJ Stored Procedures and UDFs • Using Java static methods as SQL stored procedures & functions • Leverages JDBC API – Part 2: SQLJ Data Types • Pure Java Classes as SQL ADTs • Alternative to SQL3 Abstract Data Types SQLJ - The Standard – Goal of the SQLJ Consortium is to create workable standards specifications in web time – The Consortium is working with ANSI X3H2 on a fast- track process for adopting SQLJ as a standard – The Consortium also works with The Open Group on a set of conformance tests 3
SQLJ - Implementation Status – SQLJ Embedded SQL • Public-domain reference implementation available from Oracle http://www.oracle.com/st/products/jdbc/sqlj • Profile customizations available from Oracle, IBM, Sybase, Tandem, … – SQLJ Procedures • Specifications mostly reviewed • Implementations, e.g., Sybase Adaptive Server Anywhere 6.0, Oracle 8.1, IBM – SQLJ Data Types • Specifications through first pass • Implementations, e.g., Sybase Adaptive Server Anywhere 6.0 JDBC 2.0 - SQLJ Features ✔ Support for user-defined, object data types – Java Classes • Persistent Java objects stored in the DBMS – SQL3 types • BLOB, CLOB, array, reference • Structured and distinct types ✔ New type codes – JAVA_OBJECT, STRUCT, BLOB, etc. – Metadata for user-defined types int[] types = {Types.JAVA_OBJECT}; ResultSet rs = dmd.getUDTs("catalog-name", "schema-name", "%", types); 4
JDBC 2.0 - SQLJ Features ✔ Objects-by-Value – Java Classes as database types • this just works – SQL3 ADTs as database types • Java mapping maintained per Connection – Seamless extension of get/setObject() Statement stmt; … ResultSet rs = stmt.executeQuery( "SELECT CUSTOMER FROM ACCOUNTS"); rs.next(); Customer cust = (Customer)rs.getObject(1); Agenda ✔ Overview and JDBC 2.0: New Features ➨ SQLJ Part 0: Embedded SQL and Portability Profile ✔ SQLJ Part 1: Java Methods as SQL Procedures ✔ SQLJ Part 2: Java Classes as SQL Types 5
SQLJ Part 0: SQL Embedded in Java ✔ Objectives – Simple, concise language for embedding SQL statements in Java programs – Standard to allow for assembly of binary components produced by different tools – Standard to allow for binary portability across different database systems Advantages – Ahead-of-time syntax and type checking – Strongly typed cursors (iterators) – Offline pre-compilation (for performance) – Deployment-time customization (for binary portability and native pre-compilation) 6
SQLJ clauses – SQLJ statements start with “#sql” – SQLJ statements terminate with “;” – SQLJ host variables start with “:” – SQL text is enclosed in curly braces “{..}” int n; #sql { INSERT INTO emp VALUES (:n) }; SQLJ more concise than JDBC // SQLJ int n; #sql { INSERT INTO emp VALUES (:n)}; // JDBC int n; Statement stmt = conn.prepareStatement (“INSERT INTO emp VALUES (?)”); stmt.setInt(1,n); stmt.execute (); stmt.close(); 7
Strongly typed cursors – Positional binding to columns #sql public iterator ByPos (String, int); ByPos positer; String name = null; int year = 0; #sql positer = { SELECT name, year FROM people}; while (true) { #sql { FETCH :positer INTO :name, :year}; if (positer.endFetch()) break; // process name, year } positer.close(); Strongly typed cursors (cont.) – Named binding to columns #sql public iterator ByName (int year, String name); ByName namiter; String name = null; int year = 0; #sql namiter = { SELECT name, year FROM people}; while (namiter.next()) { name = namiter.name(); year = namiter.year(); // process name, year } namiter.close(); 8
Connection context – SQLJ statements are associated with a connection context – Context type identifies exemplar schema, e.g. views, tables, privileges #sql context Department; Department dept = newDepartment(“jdbc:odbc:acme.cs”); int n; #sql [dept] { insert into EMP values (:n)}; Extensible SQLJ framework – Database vendors plug-in SQL syntax checkers and semantic analyzers using SQLChecker framework – Database vendors provide customizers to install SQLJ “binaries” (profiles) in target database – Default SQLJ binaries run on any JDBC driver 9
SQLJ translator framework Java Class Files SQLChecker SQLJ program Java Frontend SQLJ Profile SQLJ Customizations Customizer Profiles Utility SQLJ Translator SQLJ JAR FILE SQLJ portability layers SQLJ Program Profile Entries JDBC SQL DB SQL DB SQL DB 10
Custom SQL execution SQLJ Program Profile Entries JDBC Customizations SQL Module Stored procedure TP service SQL DB SQL DB SQL DB Profile customization selection Customizations Profile Data source URLs 11
SQLJ profile objects Profile ProfileData EntryInfo TypeInfo Customization ConnectedProfile RTStatement SQLJ compilation phases Foo.sqlj Foo.java Foo.class SQLJ Translator Java Compiler 12
SQLJ translation phase Foo.sqlj SQLJ Translator SQLJ semantic analysis Foo.sqlj SQLJ Translator [Ctx0] {SQL0} describe(SQL0) SQLChecker0 13
SQLJ semantic analysis Foo.sqlj SQLJ Translator [Ctx0] {SQL0} [Ctx0] {SQL1} describe(SQL1) SQLChecker0 SQLJ semantic analysis Foo.sqlj SQLJ Translator [Ctx0] {SQL0} [Ctx0] {SQL1} (Ctx1) {SQL2} describe(SQL2) SQLChecker0 SQLChecker1 14
SQLJ code generation Foo.sqlj Foo.java SQLJ Translator [Ctx0] {SQL0} [Ctx0] {SQL1} [Ctx1] {SQL2} SQLJ code generation Foo.jsql Foo.java SQLJ Translator [Ctx0] Profile0: {SQL0} Entry0 [Ctx0] {SQL1} [Ctx1) ]SQL2} Profile0.ser Entry0 15
SQLJ code generation Foo.jsql Foo.java SQLJ Translator [Ctx0] Profile0: Entry0 {SQL0} [Ctx0] Profile0: {SQL1} Entry1 [Ctx1] {SQL2} Profile0.ser Entry0 Entry1 SQLJ code generation Foo.jsql Foo.java SQLJ Translator [Ctx0] Profile0: Entry0 {SQL0} [Ctx0] Profile0: Entry1 {SQL1} [Ctx1] Profile1: {SQL2} Entry0 Profile0.ser Entry0 Entry1 Profile1.ser Entry0 16
Java compilation Foo.sqlj Foo.java Foo.class SQLJ Translator Java Compiler [Ctx0] Profile0: Profile0: Entry0 Entry0 {SQL0} [Ctx0] Profile0: Profile0: Entry1 Entry1 {SQL1} [Ctx1] Profile1: Profile1: Entry0 Entry0 {SQL2} Profile0.ser Entry0 Entry1 Profile1.ser Entry0 SQLJ packaging Foo.jar Foo.sqlj Foo.java Foo.class SQLJ Translator Java Compiler [Ctx0] Profile0: Profile0: Entry0 Entry0 {SQL0} [Ctx0] Profile0: Profile0: Entry1 Entry1 {SQL1} [Ctx1] Profile1: Profile1: Entry0 Entry0 {SQL2} Profile0.ser Entry0 Entry1 Profile1.ser Entry0 17
SQLJ installation phase Foo.jar Foo.class Profile0.ser Profile1.ser SQLJ installation phase Foo.jar Foo.jar Foo.class Foo.class Customizer1 Profile0.ser Profile0.ser Customization Profile1.ser Profile1.ser 18
SQLJ installation phase Foo.jar Foo.jar Foo.jar Foo.class Foo.class Foo.class Customizer1 Customizer2 Profile0.ser Profile0.ser Profile0.ser Customization1 Customization1 Customization2 Profile1.ser Profile1.ser Profile1.ser Customization2 Agenda ✔ JDBC 2.0: New Features ✔ SQLJ Part 0: Embedded SQL and Portability Profile ➨ SQLJ Part 1: Java Methods as SQL Procedures ✔ SQLJ Part 2: Java Classes as SQL Types 19
SQLJ Part 1: Java methods as SQL procedures ✔ Use Java static methods as SQL stored procedures and functions. – Advantage to SQL: Direct use of pre-written Java libraries. ✔ A procedural and scripting language for SQL. – Portable across DBMSs. – Deployable across tiers. Technical objectives – Convenient for Java programmers. • Not just aimed at SQL programmers. – Portable across DBMSs. – Same capability as regular SQL stored procedures. • Arbitrary SQL stored procedures re-codable as SQLJ stored procedures. – Convenience and performance comparable with SQL routines. – Callable from CLI/ODBC, from other SQL stored procedures, from JDBC/JSQL, and directly from Java. • Caller needn't know the SQLJ stored procedure is in Java. 20
Recommend
More recommend
