The JAVA Way: JDBC and SQLJ
David Toman
School of Computer Science University of Waterloo
The JAVA way to Access RDBMS
JDBC:
Java version of ODBC
http://java.sun.com/products/jdbc/
Hamilton, et al: JDBC Database Access with JAVA.
A Tutorial and annotated Reference. Addison Wesley 1997.
SQLJ:
Embedded SQL in Java
http://www.service-architecture.com/
database/articles/sqlj.html
Examples, etc., again courtesy of IBM:
JDBC Overview
•
JAVA API patterned after ODBC
) uses OO features to handle sets by iterators
•
Standardized
) should work with any RDBMS
•
What does it do for us?
1
establishes connection with the database
2
manages SQL statements
JDBC Drivers and Connections
Connection to the DBMS is made using two pieces of software:
1JDBC
driver (supplied by the DBMS vendor)
COM.ibm.db2.jdbc.app.DB2Driver: IBM DB2
jdbc:oracle:oci8:@database
: ORACLE 8
sun.jdbc.odbc.JdbcOdbcDriver
: JDBC to ODBC
) use Class.forName to load it.
2JDBC
Driver Manager (in JDK1.1)
) makes the actual connection:
DriverManager.getConnection("jdbc:db2:db")
) uses an URL to identify the database
) picks the right driver based on the URL
JDBC Example
// DB2 UDB JDBC Samples
//
// (c) Copyright International Business Machines
// Corporation, 1996, 1997. All Rights Reserved.
import java.sql.*; class Appl {
static { try {
// register the driver with DriverManager Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); } catch (Exception e) {
e.printStackTrace(); }
JDBC (connection)
public static void main(String argv[]) { Connection con = null;
// URL is jdbc:db2:dbname String url = "jdbc:db2:cs448"; try {
if (argv.length == 0) {
// connect with default id/password con = DriverManager.getConnection(url); }
else if (argv.length == 2) { String userid = argv[0]; String passwd = argv[1];
// connect with user-provided username and password con = DriverManager.getConnection(url, userid, passwd); }
else {
System.out.println("\nUse: java Appl [name passwd]\n"); System.exit(0);
Statements et al.
•
get a “statement”:
stmt = con.createStatement()
or
stmt = con.prepareStatement(String)
where String may contain parameters (?)
•
supply “input parameters”:
stmt.setXXX(i,val)
(or stmt.setNull())
•
and execute it:
ResultSet stmt.ExecuteQuery()
int = stmt.ExecuteUpdate()
Errors
Much nicer than in ODBC/Embedded SQL in C: use JAVA Exception
handling mechanisms.
The methods used in JDBC may throw SQLException
) you HAVE to catch it.
Provides:
1
SQL state: exception.getSQLState()
2Text Message: exception.getMessage()
3Vendor code: exception.getErrorCode()
4. . . to get “next exception”:
Retrieving Answers
JDBC returns answers to queries using
ResultSet: essentially an
implicit cursor.
•
returned by rs = stmt.executeQuery()
•
get tuple/no more tuples: boolean rs.next()
•
get value of attributes:
XXX rs.getXXX(int)
(by column number) or
XXX rs.getXXX(String)
(by attribute name)
•
close: rs.close()
Retrieving Answers
JDBC returns answers to queries using
ResultSet: essentially an
implicit cursor.
•
returned by rs = stmt.executeQuery()
•
get tuple/no more tuples: boolean rs.next()
•
get value of attributes:
XXX rs.getXXX(int)
(by column number) or
XXX rs.getXXX(String)
(by attribute name)
•
close: rs.close()
JDBC query
// get a statement handle
Statement stmt = con.createStatement(); // retrieve data from the database
ResultSet rs = stmt.executeQuery("SELECT * from publication"); System.out.println("Received results:");
// display the result set
// rs.next() returns false when there are no more rows while (rs.next()) {
String a = rs.getString(1); String str = rs.getString(2); System.out.print(" pubid= " + a); System.out.print(" title= " + str); System.out.print("\n");
}
rs.close(); stmt.close();
JDBC update
// get a statement handle
stmt = con.prepareStatement("UPDATE author set name = ’SHILI’ where aid = ?"); //set up the paremeter(s)
stmt.setInt(1,1); // update the database
int rowsUpdated = stmt.executeUpdate();
System.out.println("Changed "+rowsUpdated+" row(s)."); stmt.close(); con.close(); } catch( Exception e ) { e.printStackTrace(); } }
Transactions and Connections
Transactions managed by
database connection:
•
con.commit()
) commits the transaction
•
con.rollback()
) aborts the transaction
•
con.close()
) closes the database connection (and releases DBMS
resources)
SQLJ Overview
Embedded SQL for Java
•
Only Static SQL (use JDBC for dynamic SQL)
•
Type checking
) allows disconnected operation
•
Magic keyword in SQLJ:
#sql <sqlj command>
•
Error handling:
) exception-based (same as in JDBC)
•
SQLJ compiles foo.sqlj to foo.java.
) connects to the database
) uses exemplar schema
Connections and Contexts
•
embedded SQL implicitly “‘talks” to a context
) essentially a database connection
) Exemplar Schema for every context
•
context declarations
#sql context MyContext;
•
contexts may be used explicitly:
#sql [MyContext]
... ;
context
omitted
! default context
SQLJ Example
// DB2 UDB SQLJ Samples
//
// (c) Copyright International Business Machines
// Corporation, 1998. All Rights Reserved.
import java.sql.*; import sqlj.runtime.*;
#sql iterator App_Cursor1 (String empno, String firstnme) ; #sql iterator App_Cursor2 (String) ;
class App { static { try {
// register the driver with DriverManager Class.forName("COM.ibm.db2.jdbc.app.DB2Driver"); } catch (Exception e) { e.printStackTrace(); } }
SQLJ (cont.)
public static void main(String argv[]) { try {
Connection con = null; // URL is jdbc:db2:dbname String url = "jdbc:db2:cs448";
DefaultContext ctx = DefaultContext.getDefaultContext(); if (ctx == null) {
try {
// connect with default id/password con = DriverManager.getConnection(url); ctx = new DefaultContext(con);
}
catch (SQLException e) {
System.out.println("Error: no default context"); System.err.println(e) ; System.exit(1);
}
DefaultContext.setDefaultContext(ctx); }
SQLJ Statements
•
Embedded SQL statements:
#sql [context] { embedded sql };
) embedded sql must be a static SQL statement
) host variables connect SQL and JAVA
SQLJ update
try {
// update the database
#sql { UPDATE author set name = :string where aid = :aid }; // rollback the update
#sql { ROLLBACK work }; }
catch( Exception e ) { e.printStackTrace(); }
Cursors, Iterators, etc.
•
iterator object declarations
#sql iterator Iter (a1, ..., ak);
where ai is a type and identifier (name)
•
used for
queries:
#sql Iter = { SELECT b1,...,bk
FROM
...
};
•
binding of columns:
1
positional:
#sql { FETCH :Iter INTO :v1,...,:vk };
more rows: Iter.endFetch() not true
2
named:
value of column
a
i: vi = Iter.ai()
SQLJ query
try {
#sql iterator cursor1(String pubid, String title); #sql cursor1 = { SELECT pubid, title from publicaion }; System.out.println("Received results:"); while (cursor1.next()) { str1 = cursor1.pubid(); str2 = cursor1.title(); System.out.print (" pubid= " + str1); System.out.print (" title= " + str2); System.out.print ("\n"); } cursor1.close();
#sql { SELECT count(*) into :count1 from author }; System.out.println("There are "+count1+" authors."); }
catch( Exception e ) { e.printStackTrace(); }
