The JAVA Way-Introduction to Database Management-Lecture 09 Slides-Computer Science, Slides of Introduction to Database Management Systems

Download The JAVA Way-Introduction to Database Management-Lecture 09 Slides-Computer Science and more Slides Introduction to Database Management Systems in PDF only on Docsity!

The JAVA Way: JDBC and SQLJ

David Toman

School of Computer Science University of Waterloo

Introduction to Databases CS

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:

http://www.software.ibm.com/data/db2/java/

JDBC Drivers and Connections

Connection to the DBMS is made using two pieces of software:

1 JDBC driver (supplied by the DBMS vendor)

COM.ibm.db2.jdbc.app.DB2Driver: IBM DB

jdbc:oracle:oci8:@database : ORACLE 8

sun.jdbc.odbc.JdbcOdbcDriver : JDBC to ODBC

) use Class.forName to load it.

2 JDBC 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(); } }

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()

2 Text Message: exception.getMessage()

3 Vendor code: exception.getErrorCode()

4... to get “next exception”:

exception.getNextException()

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()

No indicator variables: uses JAVA’s NULL.

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

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

• Error handling:

) exception-based (same as in JDBC)

• SQLJ compiles foo.sqlj to foo.java.

) connects to the database

) uses exemplar schema

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 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 ai : vi = Iter.ai()

next row: Iter.next() is true