SQLJ: Java and Relational Databases

1. 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.

2. 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

3. 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

4. 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

5. 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

6. 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

7. SQLJ - Implementation Status • SQLJ Embedded SQL • Public-domain reference implementation available from Oraclehttp://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

8. 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);

9. 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);

10. 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

11. 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

12. 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)

13. 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) };

14. 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();

15. 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();

16. 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();

17. 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)};

18. 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

19. SQLJ translator framework Java Class Files SQLChecker SQLChecker SQLJ program Java Frontend SQLJ Customizations Profile Customizer Utility SQLJ Profiles SQLJ Translator SQLJ JAR FILE

20. SQLJ portability layers SQLJ Program Profile Entries JDBC SQL DB SQL DB SQL DB

21. Custom SQL execution SQLJ Program Profile Entries JDBC Customizations SQL Module Stored procedure TP service SQL DB SQL DB SQL DB

22. Profile customization selection Customizations Profile Data source URLs

23. SQLJ profile objects Profile ProfileData EntryInfo TypeInfo Customization ConnectedProfile RTStatement

24. Java Compiler SQLJ Translator SQLJ compilation phases Foo.sqlj Foo.java Foo.class

25. SQLJ Translator SQLJ translation phase Foo.sqlj

26. SQLJ Translator describe(SQL0) SQLChecker0 SQLJ semantic analysis Foo.sqlj [Ctx0]{SQL0}

27. SQLJ Translator SQLJ semantic analysis Foo.sqlj [Ctx0]{SQL0} [Ctx0]{SQL1} describe(SQL1) SQLChecker0

28. SQLJ Translator describe(SQL2) SQLChecker0 SQLChecker1 SQLJ semantic analysis Foo.sqlj [Ctx0]{SQL0} [Ctx0]{SQL1} (Ctx1){SQL2}

29. SQLJ Translator SQLJ code generation Foo.sqlj Foo.java [Ctx0]{SQL0} [Ctx0]{SQL1} [Ctx1]{SQL2}

30. SQLJ Translator SQLJ code generation Foo.jsql Foo.java [Ctx0]{SQL0} Profile0:Entry0 [Ctx0]{SQL1} [Ctx1)]SQL2} Profile0.ser Entry0

31. SQLJ Translator SQLJ code generation Foo.jsql Foo.java [Ctx0]{SQL0} Profile0:Entry0 [Ctx0]{SQL1} Profile0:Entry1 [Ctx1]{SQL2} Profile0.ser Entry0 Entry1

32. SQLJ Translator SQLJ code generation Foo.jsql Foo.java [Ctx0]{SQL0} Profile0:Entry0 [Ctx0]{SQL1} Profile0:Entry1 [Ctx1]{SQL2} Profile1:Entry0 Profile0.ser Entry0 Entry1 Profile1.ser Entry0

33. SQLJ Translator Java compilation Foo.sqlj Foo.java Foo.class [Ctx0]{SQL0} Profile0:Entry0 Profile0:Entry0 Java Compiler [Ctx0]{SQL1} Profile0:Entry1 Profile0:Entry1 [Ctx1]{SQL2} Profile1:Entry0 Profile1:Entry0 Profile0.ser Entry0 Entry1 Profile1.ser Entry0

34. SQLJ Translator SQLJ packaging Foo.jar Foo.sqlj Foo.java Foo.class [Ctx0]{SQL0} Profile0:Entry0 Profile0:Entry0 Java Compiler [Ctx0]{SQL1} Profile0:Entry1 Profile0:Entry1 [Ctx1]{SQL2} Profile1:Entry0 Profile1:Entry0 Profile0.ser Entry0 Entry1 Profile1.ser Entry0

35. Foo.jar Foo.class Profile0.ser Profile1.ser SQLJ installation phase

36. Foo.jar Foo.class Customizer1 Profile0.ser Profile1.ser SQLJ installation phase Foo.jar Foo.class Profile0.ser Customization Profile1.ser

37. Foo.jar Foo.class Customizer1 Profile0.ser Profile1.ser SQLJ installation phase Foo.jar Foo.jar Foo.class Foo.class Customizer2 Profile0.ser Profile0.ser Customization1 Customization1 Customization2 Profile1.ser Profile1.ser Customization2

38. 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

39. 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.

40. 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.

41. Technical objectives (cont.) • Any Java static method callable as a stored procedure: • Initially support only parameter and result types mappable to SQL. • Extensible to support arbitrary Java types, for Java caller and callee. • Body of SQLJ stored procedure routines can use JDBC and/or SQLJ to access SQL, or Java computation • Initially support persistence only for duration of a call. • Consider session and database persistence as follow-on.

42. Topics • Example Java classes • Defining Java classes to SQL • Installing jar files • Specifying SQL names • SQL Permissions • OUT parameters • Result sets • Error handling • Paths • Deployment descriptors

43. Examples • Example table: createtable emps ( name varchar(50), id char(5), state char(20), sales decimal (6,2)); • Example classes and methods: • Routines1.region – Maps a state code to a region number. Plain Java (no SQL). • Routines1.correctStates –Performs an SQL update to correct the state codes. • Routines2.bestEmps—Returns the top two employees as output parameters. • Routines3.rankedEmps—Returns the employees as a result set.

44. Examples (cont.) • The region and correctStates methods public class Routines1 { //The region method //An Integer method that will be called as a function public static Integer region(String s) throws SQLException { if (s == "MN" || s == "VT" || s == "NH" ) return 1; else if (s == "FL" || s == "GA" || s == "AL" ) return 2; else if (s == "CA" || s == "AZ" || s == "NV") return 3; else return 4; } //The correctStates method //A void method that will be called as a stored procedure public static void correctStates (String oldSpelling, String newSpelling) throws SQLException { Connection conn = DriverManager.getConnection ("JDBC:DEFAULT:CONNECTION"); PreparedStatement stmt = conn.prepareStatement ("UPDATE emps SET state = ? WHERE state = ?"); stmt.setString(1, newSpelling); stmt.setString(2, oldSpelling); stmt.executeUpdate(); return; } }

45. Installing Java Classes in SQL • New install_jar procedure: sqlj.install_jar ('file:~/classes/Routines1.jar', 'routines1_jar' ) • Two parameters: • The URL of a jar file containing a set of Java classes • A character string that will be used to identify the Jar in SQL • Installs all classes in the jar file: • Uses Java reflection to determine their names, methods, and signatures • Retains the Jar file, the character string identifies it: • The jar name is specified in a later remove_jar procedure: • Follow-on facilities will address replacing and downloading jar files, etc.

46. Defining SQL names for Java methods • A form of the SQL create procedure/function statement:. createprocedure correct_states(old char(20), new char(20)) modifies sql data externalname'routines1_jar:Routines1.correctStates' language java parameter style java; createfunction region_of(state char(20)) returns integer nosql externalname 'routines1_jar:Routines1.region' language java parameter style java; • The create procedure statement and the external language X are standard. • The language alternative java is an SQLJ extension.

47. Defining SQL names for Java methods • The procedure/function names "correct_states" and "region_of" are normal SQL 3-part names, with normal defaults. • You can do multiple create procedure statements pointing to the same Java method. • The key role of create procedure is to define an SQL synonym for the Java method. • Why use an SQL name? • Java names have different syntax: case-sensitive, package names, Unicode, etc. • SQL metadata and permissions are keyed to SQL names.

48. Privileges • The usage privilege on the installed jar file is grantable: grant usage on routines1_jar to Smith • The execute permission on the SQL names is grantable. grant execute on correct_states to Smith • Methods run with "definer's rights".

49. Invoking Java methods • Use the SQL names, with normal defaults for the first two parts: select name, region_of(state) as region from emps where region_of(state) = 3 call correct_states ('CAL', 'CA');

50. OUT parameters • SQL procedures have OUT and INOUT parameters; Java doesn't. • If a Java method will be used as an SQL proc with OUT parameters, those parameters are declared as Java arrays, to act as "containers". • Example (next page): • bestTwoEmps returns the two top employees in a given region. • The specific region is an in parameter. • The column values of the two top employees are out parameters. • The bestTwoEmps method is coded with JSQL. • A version of bestTwoEmps coded with JDBC is shown in the draft specs, for comparison.