Accessing a Database Using SQLJ

Accessing a Database Using SQLJ

Objectives • After completing this lesson, you should be able to do the following: • Describe the design goals of SQLJ • Connect to a database using SQLJ • Perform DML and DDL operations • Use AppBuilder for Java to precompile SQLJ code

Overview Java with JDBC SQLJ SQLJ file pre-compiled SQL Development time SQL code first parse Java file compiled Java file compiled JDBC SQL code first parse Run time

Using SQLJ • SQLJ is passed through a precompiler: • Checks SQL against the database • Generates Java code with JDBC calls SQLJ preprocessor Java compiler Java code with JDBC calls SQLJ code RegularJava class Oracle

SQL Design Goals of SQLJ • Allows easy embedding of SQL statements in Java source code • More concise than JDBC • Early checking of SQL statements eliminates many run time errors: • SQL syntax errors • Incorrect assumption of table structures • Java/SQL type mismatch

What Does SQLJ Look Like? • SQL code is embedded in Java code • File typically has a .sqlj extension • Equivalent code using JDBC: myfile.sqlj void myJavaMethod() { #sql { create table EMP(EMPNO number(5) }; } another.java void myJavaMethod() { Statement st = conn.createStatement(); st.execute("create table EMP(EMPNO number(5))"); }

SQLJ Syntax: A Closer Look import java.sql.*; import sqlj.runtime.*; import sqlj.runtime.ref.*; class X { void myJavaMethod() { try { #sql{update EMP set SAL = SAL + 100 where SAL < 1500}; } catch (SQLException e) {…} } • Imports needed • SQLJ statementbegins with#sql • SQL statement placed in braces - can throw SQLException

Loading the JDBC Driver • SQLJ requires that the JDBC driver class is loaded • This can be performed in the same way as for JDBC: try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { System.out.println("Could not load the driver"); }

Specifying a Connection Context • All SQLJ statements execute in a “connection context” • Defines the database schema, session, and transaction try { Class.forName(…); DefaultContext.setDefaultContext( new DefaultContext( "jdbc:oracle:thin:@HOSTID:1521:ORCL", "theUser", "thePassword") ); } catch (Exception e) {…}

Executing the SQL Statement • Once the JDBC driver has been loaded, and a connection context has been set, SQLJ statements can be executed try { Class.forName(…); DefaultContext.setDefaultContext(…); #sql { update EMP set SAL = SAL + 100 where SAL < 1500 }; } catch (Exception e) {…}

Passing Host Variables into a SQLJ Statement • A host variable is a variable in your Java program • Host variables can be used in a SQLJ statement as follows: void deleteHighEarners(BigDecimal amt) { try { #sql {delete from EMP where SAL >= :amt}; } catch (SQLException e) {…} }

Guided Practice: SQLJ and JDBC • This example uses JDBC to give a pay raise to selected employees. • How would it look in SQLJ? BigDecimal amt, low; … Class.forName(…); Connection conn = DriverManager.getConnection(…); PreparedStatement stmt = conn.prepareStatement ("update EMP set SAL = SAL + ? where SAL < ?"); stmt.setBigDecimal(1, amt); stmt.setBigDecimal(2, low); stmt.execute();

Assigning Results to Host Variables • Host variables can be assignment targets, for values retrieved by SQL operations void printJobTitle(BigDecimal empno) { String job; try { #sql { select JOB into :job from EMP where EMPNO = :empno }; System.out.println("Job title is " + job); } catch (SQLException e) {} }

Dealing with Query Result Sets • SQLJ can be used to execute queries that return a result set • To process the result set, define an “iterator” type • Specifies the data type of each column • Use the iterator to retrieve columns … select ENAME, SAL from EMP …

Defining a Named Iterator • An iterator type can be defined as shown here, to retrieve columns byname: • SQLJ translates this into a Java class called MyIter, with these methods: #sql iterator MyIter(String ENAME, String JOB); String ENAME()… // Get ENAME column String JOB()… // Get JOB column boolean next()… // Go to next row

Using a Named Iterator • The iterator can be used to extract columns by name, in a type-safe manner #sql iterator MyIter(String ENAME, String JOB); class MyClass { void aMethod() { MyIter iter; #sql iter = { select ENAME, JOB from EMP }; while(iter.next()) { String ename = iter.ENAME(); String job = iter.JOB(); } …

Defining By-Position Iterators • By-position iterators define the type of each column, but not a name • Columns must be fetched by position: #sql iterator MyIter(String, String); … MyIter iter; String name, job; #sql iter = { select ENAME, JOB from EMP }; while(true) { #sql {fetch :iter into :name, :job}; if (iter.endFetch()) break; System.out.println(name + " is a " + job); …

Calling Stored Procedures and Stored Functions • A SQLJ statement can call a stored procedure as follows: • Stored functions are called as follows: String ename = …; #sql { call PayBonus(:ename, 100) }; String dname = …; BigDecimal topSal; #sql topSal = { values( GetTopSal(:dname) ) };

Specifying a Different Database Connection • A different connection context can be specified for SQLJ statements: #sql context MyDBContext; class MyClass { void aMethod() { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); MyDBContext ctxt = new MyDBContext( "jdbc:odbc:MyOtherDb", "user", "password"); #sql (ctxt) { select * from TABLEX }; … ctxt.close();

Using SQLJ in AppBuilder • .sqlj files can be added directly to an AppBuilder project

Enabling Compile-Time SQL Checking • Select Project—>Project Properties from Menu bar • Check this check box

Specifying Connection Properties

Summary • SQLJ enables you to embed SQL statements directly in Java code • SQLJ statements are compiled statically, whereas JDBC is dynamic • SQLJ statements begin with #sql • Iterators and contexts can be defined • #sqliterator • #sqlcontext

Practice 7-1 Overview • Create and build SQLJ files using AppBuilder tools • Connect to a database using SQLJ • Create and execute a query using SQJL • Iterate a result set • Perform an update operation • Perform an update operation with parameters

Accessing a Database Using SQLJ