accessing a database using sqlj
DESCRIPTION
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. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/1.jpg)
Copyright Oracle Corporation, 1998. All rights reserved.
77
Accessing a Database Using SQLJ
Accessing a Database Using SQLJ
![Page 2: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/2.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-22
ObjectivesObjectives
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
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
![Page 3: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/3.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-33
OverviewOverview
Java file compiled
SQL code first parse
Development Development timetime
Run timeRun time
Java with Java with JDBCJDBC SQLJSQLJ
Java file compiled
SQLJ file pre-
compiledSQL code first parse
SQL
JDBC
![Page 4: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/4.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-44
SQLJ is passed through a precompiler:
• Checks SQL against the database
• Generates Java code with JDBC calls
SQLJ is passed through a precompiler:
• Checks SQL against the database
• Generates Java code with JDBC calls
Using SQLJUsing SQLJ
SQLJ codeJava code
withJDBC calls
Java compiler
SQLJpreprocessor
OracleRegular
Java class
![Page 5: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/5.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-55
Design Goals of SQLJDesign 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
• 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 mismatchSQL
![Page 6: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/6.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-66
What Does SQLJ Look Like?What Does SQLJ Look Like?
SQL code is embedded in Java code
• File typically has a .sqlj extension
• Equivalent code using JDBC:
SQL code is embedded in Java code
• File typically has a .sqlj extension
• Equivalent code using JDBC:
void myJavaMethod() {
#sql { create table EMP(EMPNO number(5) };
}
void myJavaMethod() {
Statement st = conn.createStatement();
st.execute("create table EMP(EMPNO number(5))");
}
myfile.sqlj
another.java
![Page 7: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/7.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-77
• Imports needed
• SQLJ statementbegins with#sql
• SQL statement placed in braces - can throw SQLException
• Imports needed
• SQLJ statementbegins with#sql
• SQL statement placed in braces - can throw SQLException
SQLJ Syntax: A Closer LookSQLJ 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) {…}
}
![Page 8: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/8.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-88
Loading the JDBC DriverLoading the JDBC Driver
• SQLJ requires that the JDBC driver class is loaded
• This can be performed in the same way as for JDBC:
• 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");}
![Page 9: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/9.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-99
Specifying a Connection Context
Specifying a Connection Context
All SQLJ statements execute in a “connection context”
• Defines the database schema, session, and transaction
All SQLJ statements execute in a “connection context”
• Defines the database schema, session, and transactiontry { Class.forName(…);
DefaultContext.setDefaultContext( new DefaultContext( "jdbc:oracle:thin:@HOSTID:1521:ORCL", "theUser", "thePassword") ); }catch (Exception e) {…}
![Page 10: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/10.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1010
Once the JDBC driver has been loaded, and a connection context has been set, SQLJ statements can be executed
Once the JDBC driver has been loaded, and a connection context has been set, SQLJ statements can be executed
Executing the SQL StatementExecuting the SQL Statement
try { Class.forName(…); DefaultContext.setDefaultContext(…);
#sql { update EMP
set SAL = SAL + 100
where SAL < 1500 };
}catch (Exception e) {…}
![Page 11: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/11.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1111
Passing Host Variables into a SQLJ Statement
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:
• 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) {…}}
![Page 12: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/12.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1212
Guided Practice: SQLJ and JDBC
Guided Practice: SQLJ and JDBC
This example uses JDBC to give a pay raise to selected employees.
• How would it look in SQLJ?
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();
![Page 13: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/13.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1313
Assigning Results to Host Variables
Assigning Results to Host Variables
Host variables can be assignment targets, for values retrieved by SQL operationsHost 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) {}}
![Page 14: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/14.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1414
Dealing with Query Result SetsDealing 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
• 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 …
![Page 15: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/15.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1515
Defining a Named IteratorDefining 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:
• 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
![Page 16: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/16.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1616
Using a Named IteratorUsing a Named Iterator
The iterator can be used to extract columns by name, in a type-safe mannerThe 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(); } …
![Page 17: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/17.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1717
Defining By-Position IteratorsDefining By-Position Iterators
• By-position iterators define the type of each column, but not a name
• Columns must be fetched by position:
• 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); …
![Page 18: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/18.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1818
Calling Stored Procedures and Stored Functions
Calling Stored Procedures and Stored Functions
• A SQLJ statement can call a stored procedure as follows:
• Stored functions are called as follows:
• 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) ) };
![Page 19: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/19.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-1919
Specifying a Different Database ConnectionSpecifying a Different Database Connection
A different connection context can be specified for SQLJ statements: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();
![Page 20: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/20.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-2020
Using SQLJ in AppBuilderUsing SQLJ in AppBuilder
.sqlj files can be added directly to an AppBuilder project.sqlj files can be added directly to an AppBuilder project
![Page 21: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/21.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-2121
Enabling Compile-Time SQL Checking
Enabling Compile-Time SQL Checking
Select Project—>Project Properties from Menu bar
Check this check box
Select Project—>Project Properties from Menu bar
Check this check box
![Page 22: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/22.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-2222
Specifying Connection Properties
Specifying Connection Properties
![Page 23: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/23.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-2323
SummarySummary
• 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
– #sql iterator
– #sql context
• 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
– #sql iterator
– #sql context
![Page 24: Accessing a Database Using SQLJ](https://reader035.vdocuments.us/reader035/viewer/2022062308/568133cc550346895d9ac50a/html5/thumbnails/24.jpg)
Copyright Oracle Corporation, 1998. All rights reserved. 7-7-2424
Practice 7-1 OverviewPractice 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
• 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