Introduction to JDBC Programming Oracle Korea Basic JDBC

Introduction to JDBC Programming Oracle Korea

발표순서 Ÿ Basic JDBC Programming Ÿ Advanced Programming Ÿ JDBC 2. 0 Features Ÿ SQLJ Ÿ Q&A

Basic JDBC Programming

Basic JDBC Programming Ÿ After completing this lesson, you should be able to do the following: – Connect to a database using Java Database Connectivity (JDBC) – Create and execute a query using JDBC – Invoke prepared statements – Commit and roll back transactions – Use the Oracle JDBC extensions to improve performance

JDBC Ÿ JDBC is a standard interface for connecting to relational databases from Java. Ÿ The JDBC classes and interfaces are in the java. sql package. Ÿ JDBC 1. 22 is part of JDK 1. 1; JDBC 2. 0 is part of Java 2

Overview of Querying a Database With JDBC Connect Query Process results Close

Stage 1: Connect Register the driver Query Connect to the database Process results Close

A JDBC Driver Ÿ Is an interpreter that translates JDBC method calls to vendor-specific database commands JDBC calls Driver Database commands Database Ÿ Implements interfaces in java. sql Ÿ Can also provide a vendor’s extensions to the JDBC standard

Oracle JDBC Driver Oracle 8 i JDBC “Thin” driver Java Socket JDBC “OCI” driver SQL*Net OCI C Lib Java Store Procedure SQL & PL/SQL Engines Ÿ JDBC “Thin” driver (also available in server) Ÿ JDBC “OCI” driver Ÿ Java Engine JDBC “Server. Side Internal” driver database Lib JDBC “Server-Side Internal” driver (Kernal PRogram Bundled Calls driver)

Oracle JDBC Drivers Ÿ Thin driver – a 100% Java driver for client-side use without an Oracle installation, particularly with applets Ÿ OCI drivers (OCI 8 and OCI 7) – for client-side use with an Oracle client installation Ÿ server-side Thin driver – which is functionally the same as the client-side Thin driver, but is for code that runs inside an Oracle server and needs to access a remote server, including middletier scenarios Ÿ server-side internal driver – for code that runs inside the target server

Oracle JDBC Drivers: Thin Client Driver Ÿ Written entirely in Java Ÿ Applets must use this driver Applet JDBC Thin driver O 7 or O 8 Client Server

Oracle JDBC Drivers: OCI Client Drivers Ÿ Written in C and Java Ÿ Must be installed on the client Application JDBC OCI driver O 7 or O 8 ocixxx. dll Client Server

Oracle JDBC Drivers: 3. Server-Side Driver Ÿ Runs inside the database Ÿ Java stored procedures must use this driver Stored procedure Oracle 8 i JDBC Server side driver C library SQL Engine

Other JDBC Drivers Ÿ JDBC-ODBC Bridge – Translates JDBC into open database connectivity (ODBC) calls – Allows communication with existing ODBC drivers when no JDBC driver is available Ÿ Oracle Lite Driver For communication with an Oracle Lite database

About JDBC URLs Ÿ JDBC uses a URL to identify the database connection. jdbc: <subprotocol>: <subname> Protocol Subprotocol Database identifier jdbc: oracle: <driver>: @<database>

JDBC URLs with Oracle Drivers Ÿ Thin driver jdbc: oracle: thin: @<host>: <port>: <SID> Ÿ OCI driver jdbc: oracle: oci 8: @<TNSNAMES entry> Ÿ Server-side driver: Use the default connection

How to Make the Connection 1. Register the driver. Driver. Manager. register. Driver (new oracle. jdbc. driver. Oracle. Driver()); 2. Connect to the database. Connection conn = Driver. Manager. get. Connection (URL, userid, password); Connection conn = Driver. Manager. get. Connection ("jdbc: oracle: thin: @myhost: 1521: orcl", "scott", "tiger");

Using Connection java. sql. Connection Creating Statement create. Statment() prepare. Statment(String) prepare. Call(String) Transaction Management commit() rollback() Get database metadata get. Meta. Data() close() is. Closed() Conneciton related

Demonstration Connection

Stage 2: Query Connect Query Create a statement Process results Query the database Close

The Statement Object Ÿ A Statement object sends your SQL statement to the database. Ÿ You need an active connection to create a JDBC statement. Ÿ Statement has three methods to execute a SQL statement: – – – execute. Query() for QUERY statements execute. Update() for INSERT, UPDATE, DELETE, or DDL statements execute() for either type of statement

How to Query the Database 1. Create an empty statement object. Statement stmt = conn. create. Statement(); 2. Execute the statement. Result. Set rset = stmt. execute. Query(statement); int count = stmt. execute. Update(statement); boolean isquery = stmt. execute(statement);

Querying the Database: Examples Ÿ Execute a select statement. Statement stmt = conn. create. Statement(); Result. Set rset = stmt. execute. Query ("select RENTAL_ID, STATUS from ACME_RENTALS"); • Execute a delete statement. Statement stmt = conn. create. Statement(); int rowcount = stmt. execute. Update ("delete from ACME_RENTAL_ITEMS where rental_id = 1011");

Stage 3: Process the Results Connect Query Step through the results Process results Close Assign results to Java variables

The Result. Set Object Ÿ JDBC returns the results of a query in a Result. Set object. Ÿ A Result. Set maintains a cursor pointing to its current row of data. Ÿ Use next() to step through the result set row by row. Ÿ get. String(), get. Int(), and so on assign each value to a Java variable.

How to Process the Results Ÿ 1. Step through the result set. while (rset. next()) { … } Ÿ 2. Use get. XXX() to get each column value. String val = rset. get. String(colname); rset. get. String(col. Index); while (rset. next()) { String title = rset. get. String("TITLE"); String year = rset. get. String("YEAR"); … // Process or display the data }

How to Handle SQL Null Values Ÿ Java primitive types cannot have null values. Ÿ Do not use a primitive type when your query might return a SQL null. Ÿ Use Result. Set. was. Null() to determine whether a column has a null value. while (rset. next()) { String year = rset. get. String("YEAR"); if (rset. was. Null() { … // Handle null value } …}

Mapping Database Types to Java Types Ÿ Result. Set maps database types to Java types. Result. Set rset = stmt. execute. Query ("select RENTAL_ID, RENTAL_DATE, STATUS from ACME_RENTALS"); int id = rset. get. Int(1); Date rentaldate = rset. get. Date(2); String status = rset. get. String(3); Col Name Type RENTAL_ID NUMBER RENTAL_DATE STATUS VARCHAR 2

Stage 4: Close Connect Query Close the result set Process results Close the statement Close the connection

How to Close the Connection 1. Close the Result. Set object. rset. close(); 2. Close the Statement object. stmt. close(); 3. Close the connection (not necessary for server-side driver). conn. close();

Demonstration A Simple JDBC Program

The Database. Meta. Data Object Ÿ The Connection object can be used to get a Database. Meta. Data object. Ÿ This object provides more than 100 methods to obtain information about the database.

How to Obtain Database Metadata 1. Get the Database. Meta. Data object. Database. Meta. Data dbmd = conn. get. Meta. Data(); 2. Use the object’s methods to get the metadata. Database. Meta. Data dbmd = conn. get. Meta. Data(); String s 1 = dbmd get. URL(); String s 2 = dbmd. get. SQLKeywords(); boolean b 1 = dbmd. supports. Transactions(); boolean b 2 = dbmd. supports. Select. For. Update();

The Result. Set. Meta. Data Object Ÿ The Result. Set object can be used to get a Result. Set. Meta. Data object. Ÿ Result. Set. Meta. Data object provides metadata, including: – Number of columns in the result set – Column type – Column name

How to Obtain Result Set Metadata 1. Get the Result. Set. Meta. Data object. Result. Set. Meta. Data rsmd = rset. get. Meta. Data(); 2. Use the object’s methods to get the metadata. Result. Set. Meta. Data rsmd = rset. get. Meta. Data(); for (int i = 0; i < rsmd. get. Column. Count(); i++) { String colname = rsmd. get. Column. Name(i); int coltype = rsmd. get. Column. Type(i); … }

Demonstration Dynamic Query using Meta. Data

The Prepared. Statement Object Ÿ A Prepared. Statement object holds precompiled SQL statements. Ÿ Use this object for statements you want to execute more than once. Ÿ A prepared statement can contain variables that you supply each time you execute the statement.

How to Create a Prepared Statement 1. Register the driver and create the database connection. 2. Create the prepared statement, identifying variables with a question mark (? ). Prepared. Statement pstmt = conn. prepare. Statement("update ACME_RENTALS set STATUS = ? where RENTAL_ID = ? "); Prepared. Statement pstmt = conn. prepare. Statement("select STATUS from ACME_RENTALS where RENTAL_ID = ? ");

How to Execute a Prepared Statement 1. Supply values for the variables. pstmt. set. XXX(index, value); 2. Execute the statement. pstmt. execute. Query(); pstmt. execute. Update(); Prepared. Statement pstmt = conn. prepare. Statement("update ACME_RENTALS set STATUS = ? where RENTAL_ID = ? "); pstmt. set. String(1, "OUT"); pstmt. set. Int(2, rentalid); pstmt. execute. Update();

The Callable. Statement Object Ÿ A Callable. Statement object holds parameters for calling stored procedures. Ÿ A callable statement can contain variables that you supply each time you execute the call. Ÿ When the stored procedure returns, computed values (if any) are retrieved through the Callabable. Statement object.

How to Create a Callable Statement Ÿ Register the driver and create the database connection. Ÿ Create the callable statement, identifying variables with a question mark (? ). Callable. Statement cstmt = conn. prepare. Call("{call " + ADDITEM + "(? , ? )}"); cstmt. register. Out. Parameter(2, Types. INTEGER); c. Stmt. register. Out. Parameter(3, Types. DOUBLE);

How to Execute a Callable Statement 1. Set the input parameters. cstmt. set. XXX(index, value); 2. Execute the statement. cstmt. execute(statement); 3. Get the output parameters. var = cstmt. get. XXX(index);

Using Transactions Ÿ The server-side driver does not support autocommit mode. Ÿ With other drivers: – – New connections are in autocommit mode. Use conn. set. Auto. Commit(false) to turn autocommit off. Ÿ To control transactions when you are not in autocommit mode: – – conn. commit(): Commit a transaction conn. rollback(): Roll back a transaction

Oracle JDBC Extensions Oracle provides many extensions to standard JDBC; for example: Connection Oracle. Connection Statement Oracle. Statement Prepared. Statement Oracle. Prepared. Statement Callable. Statement Oracle. Callable. Statement Result. Set Oracle. Result. Set

Advanced Programming

Advanced Programming Ÿ LOB Data type Ÿ Advanced data type

LOB Data type Ÿ oracle. sql. BLOB – java. sql. Blob – Processing locator : get. BLOB(), set. BLOB() methods using jdk 1. 2. x Ÿ oracle. sql. CLOB – java. sql. Clob – Processing locator : get. CLOB(), set. CLOB() methods using jdk 1. 2. x Ÿ oracle. sql. BFILE – Oracle Specific Datatype

BLOB Ÿ Reading – get. Binary. Stream() 메서드 사용. – Return된 Input. Stream을 이용하여 처리(파일, 데 이터베이스) Ÿ Writing – get. Binary. Output. Stream() 메서드 사용 – java. io. Output. Stream 객체를 이용하여 Writing

BLOB : Reading 1. create statement 2. create resultset 3. get Blob locator 4. get Input. Stream 5. Input. Stream 처리 6. Input. Stream close Statement stmt = conn. create. Statement(); Result. Set rs = stmt. execute. Query (“select blob_column from blob_table”); while (rs. next()) { BLOB blob = ((Oracle. Result. Set)rs). get. BLOB(1); Input. Stream is = blob. get. Binary. Stream(); int read = 0; while ( (read = is. read()) != -1) { // to do like writing a file using the stream } is. close(); }

BLOB : Writing 1. create statement 2. create resultset 3. get Blob locator 4. get Output. Stream 5. Source read 6. write blob 7. close stream Statement stmt = conn. create. Statement(); Result. Set rs = stmt. execute. Query (“select blob_column from blob_table for update”); while (rs. next()) { BLOB blob = ((Oracle. Result. Set)rs). get. BLOB(1); Output. Stream os = blob. get. Binary. Output. Stream(); Input. Stream src = new Input. Stream(…); byte [] buffer = new byte[1024]; int read = 0; while ( (read = src. read(buffer)) != -1) { os. write(buffer, 0, read); // write blob. } src. close(); os. close(); }

CLOB Ÿ Reading – – – get. Ascii. Stream() : 아스키 스트림 get. Character. Stream() : 유니코드 스트림. Return된 Input. Stream을 이용하여 처리(파일, 데 이터베이스에 Writing) • Writing – – – get. Ascii. Output. Stream() 메서드 사용해서 locator 획득 get. Character. Output. Stream() 메서드 사용. java. io. Output. Stream 객체를 이용하여 Writing

CLOB : Reading 1. create statement 2. create resultset 3. get Clob locator 4. get Input. Stream 5. Input. Stream 처리 6. Input. Stream close Statement stmt = conn. create. Statement(); Result. Set rs = stmt. execute. Query (“select clob_column from clob_table”); while (rs. next()) { CLOB blob = ((Oracle. Result. Set)rs). get. CLOB(1); Reader reader = clob. get. Character. Stream(); char [] buffer = new char[10]; int length = 0; while ( (length = reader. read (buffer)) != -1) { System. out. println (new String(buffer)); } is. close(); }

CLOB : Writing 1. create statement 2. create resultset 3. get clob locator 4. get Output. Stream 5. Source read 6. write clob 7. close stream Statement stmt = conn. create. Statement(); Result. Set rs = stmt. execute. Query (“select clob_column from clob_table for update”); while (rs. next()) { CLOB clob = ((Oracle. Result. Set)rs). get. CLOB(1); Writer writer = clob. get. Character. Output. Stream(); File. Input. Stream src = new File. Input. Stream(“tmp”); byte [] buffer = new byte[512]; int read = 0; while ( (read = src. read(buffer)) != -1) { writer. write(buffer, 0, read); // write clob. } src. close(); writer. close(); }

BFILE Ÿ Locator : – get. Oracle. Object(), get. BFILE() 메서드 이용 Ÿ oracle. sql. BFILE methods – file. Exists(), is. File. Open() 메서드 – open. File(), close. File() Ÿ Creating a BFILE column Ÿ Reading Ÿ Writing

BFILE : Creating a BFILE column cmd ="INSERT INTO my_bfile_table VALUES (’one’, bfilename(test_dir, ’file 1. data’))"; stmt. execute (cmd); cmd ="INSERT INTO my_bfile_table VALUES (’three’, null)"; stmt. execute(cmd);

BFILE : Processing 1. select the locator cmd = "SELECT * FROM my_bfile_table WHERE x = ’one’"; rset = stmt. execute. Query (cmd); if (rset. next ()) { 2. Open the bfile BFILE bfile = ((Oracle. Result. Set)rset). get. BFILE (2); bfile. open. File(); Input. Stream in = bfile. get. Binary. Stream(); 3. get a binary stream int length ; byte[] buf = new byte[512]; while ((length = in. read(buf)) != -1) 4. Input. Stream 처리 { // to do something using byte buffer 5. Close resoruce } in. close(); bfile. close. File();

Demonstration LOB Data Handling

Advanced data type Ÿ STRUCT Ÿ Customized mapping Ÿ ARRAY Ÿ REF

Struct Ÿ The Oracle JDBC drivers materialize database objects as instances of Java objects Ÿ Multiple ways to map between Java and SQL: – Default Mapping: JDBC materializes the object as an oracle. sql. STRUCT – Customized mappings: You explicitly specify mappings between database objects and Java classes ŸSQLData ŸCustom. Datum Ÿ JPublisher can help generate Custom. Datum and SQLData mappings

Default Mapping -oracle. sql. STRUCT Ÿ Holds the pickle image (in the low level storage format) Ÿ Contains a “values” array of oracle. sql. Datum objects Ÿ Each attribute is already in its true data type Ÿ get. Attribute() retrieves values array as java. lang. Object[] Ÿ Casts Struct to oracle. sql. STRUCT to use oracle. sql extensions – get. Oracle. Attributes() returns value array as oracle. sql objects (native SQL format) Ÿ get. SQLType. Name() retrieves object type name Ÿ References Struct. Descriptor for type information

Access Data in oracle. sql. STRUCT Result. Set SSN Person(empid, name) (1001, ’Scott’) (1002, ‘Alice’). . . oracle. sql. STRUCT s rset. get. Object(2) or rset. get. STRUCT(2) Object [ ] (0) 1001 (1) ‘Scott’ s. get. Attributes() Result. Set rset = st. execute. Query("select * from hr_table"); while(rset. next()){ System. out. println(rset. get. String(1)); // SSN STRUCT s = (STRUCT) rset. get. Object(2); // Person Object[] attrs = s. get. Attributes(); System. out. println(((Big. Decimal) attrs[0]). int. Value()); System. out. println((String) atts[0]); }

Customized Mapping Ÿ You can specify how our drivers materialize object data – – The JDBC 2. 0 way -- SQLData The Oracle way -- Custom. Datum Ÿ The custom Java class you create must implement one of these interfaces Ÿ Two steps: – – step 1) create the customized class step 2) register the customized mapping

Implementing SQLData public class JCustomer implements java. sql. SQLData { private String sql_type; public int cust. No; public String cust. Name; public String get. SQLType. Name() throws SQLException { return sql_type; } public void read. SQL (SQLInput stream, String type. Name) throws SQLException { sql_type = type. Name; cust. No = stream. read. Int(); cust. Name = stream. read. String(); } public void write. SQL (SQLOutput stream) throws SQLException { stream. write. Int(cust. No); stream. write. String(cust. Name); } }

Reading & Writing SQLData //put an entry in the typemap Map map = conn. get. Type. Map(); // Dictionary in JDK 1. 1. x map. put("CUSTOMER_INFO_T", Class. for. Name("JCustomer")); Result. Set rs = stmt. execute. Query("select VALUE p from CUSTOMER_TAB p"); while (rs. next()) { //retrieve the object JCustomer jc = (JCustomer) rs. get. Object(1); Prepared. Statement pstmt = conn. prepare. Statement ("INSERT INTO NEW_CUSTOMER_TAB VALUES (? )"); pstmt. set. Object(1, jc); // insert the object pstmt. execute. Update(); }

Custom. Datum Ÿ A flexible, efficient and powerful mapping Ÿ You need to provide two classes: – one that implements the Custom. Datum. Factory interface – one that implements Custom. Datum – The factory is used to generate the instance of type Custom. Datum Ÿ Can be used for any customized conversion

Implementing Custom. Datum & Custom. Datum. Factory public class JCustomer implements oracle. sql. Custom. Datum { public Datum to. Datum (Oracle. Connection c) throws SQLException { Struct. Descriptor sd = Struct. Descriptor. create. Descriptor(“CUSTOMER_INFO_T", c); Object [] attributes = { emp. Name, emp. No }; return new STRUCT(sd, c, attributes); } } public class JCustomer. Factory implement oracle. sql. Custom. Datum. Factory { public Custom. Datum create(Datum d, int sql. Type) throws SQLException { if (d == null) return null; Object [] attributes = ((STRUCT) d). get. Attributes(); return new JCustomer(attributes[0], attributes[1]); } }

Reading & Writing Custom. Datum Statement s = conn. create. Statement(); Oracle. Result. Set rs = (Oracle. Result. Set) s. execute. Query("SELECT VALUE p FROM CUSTOMER_TAB p"); while(rs. next()) { // retrieve the Custom. Datum object Jcustomer jc = (JCustomer) rs. get. Custom. Datum (1, new JCustom. Datum. Factory()); Prepared. Statement pstmt = conn. prepare. Statement ("INSERT INTO NEW_CUSTOMER_TAB VALUES (? )"); // inserts a Custom. Datum object pstmt. set. Object(1, jc); pstmt. execute. Update(); }

Array Ÿ Accesses collection types in database Ÿ Handles both VARRAY and Nested Table Ÿ oracle. sql. ARRAY holds the pickle image or locator bytes Ÿ References Array. Descriptor for type information Ÿ get. Array() retrieves the array contents in “default” JDBC types Ÿ get. Oracle. Array() retrieves elements in oracle. sql format Ÿ get. Result. Set() retrieves elements as a Result. Set

Access Data in Array oracle. sql. ARRAY Result. Set Name ‘Scott’ ‘Alice’. . . Phones varray(10) of varchar 2(25) (‘ 111 -2222’, ‘ 222 -3333’) (‘ 333 -4444’). . . s rset. get. Object(2) or rset. get. Array(2) or rset. get. ARRAY(2) s. get. Array() Object [ ] (0) ‘ 111 -2222’ (1) ‘‘ 222 -3333’ Result. Set rset = st. execute. Query("select * from hr_table"); while(rset. next()){ System. out. println(rset. get. String(1)); // Name ARRAY s = (ARRAY) rset. get. Object(2); // Phones Object[] elems = s. get. Array(); // retrieve elements for (int i<0; i<elems. length; i++) System. out. println((String) elems[i]); }

Ref Ÿ Accesses object reference in database Ÿ oracle. sql. REF holds the ref Ÿ get. Value() to dereference Ÿ set. Value() to update the referenced object in the database immediately

Access Data using REF Result. Set ADDR oracle. sql. REF (street_name, house_no) name varchar 2, PEOPLE col 1 REF ADDR (‘sam’, addr 1) (‘sam’ 0000280209420 D 2400 …. ) ref r. get. Value() rset. get. Object(2) or rset. get. REF(2) or rset. get. Ref(2) Result. Set rset = st. execute. Query("select name, ref(p) while(rset. next()){ System. out. println(rset. get. String(1)); // REF r = (REF)rset. get. Object(2); // STURCT addr = r. get. Value(); // Object [] elems = addr. get. Attributes(); // for (int i<0; i<elems. length; i++) System. out. println((String) elems[i]); } ( ‘Samsung-dong’, 144 -17 ) from people p"); Name ADDR REF get ADDR struct retrive attributes

Demonstration Advanced Data type

JDBC 2. 0 Features

JDBC 2. 0 Features Ÿ Result. Set enhancements Ÿ Batch updates Ÿ Statement Caching Ÿ JNDI Ÿ Connection Pooling & Caching Ÿ Distributed Transactions

Result. Set enhancements Ÿ Scrollability – The ability to move backward as well as forward through a result set. – The ability to move to any particular position in the result set – Sensitivity must be specified. Sensitivity can detect whether data is changed or not. – Sensitive or Insensitive Mode Ÿ Updatability – Can insert, modify, delete using while navigating a resultset

6 Types of Result. Set Ÿ forward-only/read-only Ÿ forward-only/updatable Ÿ scroll-sensitive/read-only Ÿ scroll-sensitive/updatable Ÿ scroll-insensitive/read-only Ÿ scroll-insensitive/updatable

Scrollable Resultset Java Program Statement stmt = conn. create. Statemen( Result. Set. TYPE_SCROLL_IN SENSITIVE, Result. Set. CONCURR_READ_ ONLY); Result. Set rset = stmt. execute. Query(); rset. absolute(2); . . . Scrollable Result. Set Oracle 8 i Cache Cursor next() previous() relative() absolute() first() last() Table

APIs java. sql. Connection Statement create. Statement (int result. Set. Type, int result. Set. Concurrency) Prepared. Statement prepare. Statement (String sql, int result. Set. Type, int result. Set. Concurrency) Callable. Statement prepare. Call (String sql, int result. Set. Type, int result. Set. Concurrency) result. Set. Type result. Set. Concurrency Result. Set. TYPE_FORWARD_ONLY Result. Set. CONCUR_READ_ONLY Result. Set. TYPE_SCROLL_INSENSITIVE Result. Set. CONCUR_UPDATABLE Result. Set. TYPE_SCROLL_SENSITIVE

APIs java. sql. Result. Set void before. First() throws SQLException void after. Last() throws SQLException boolean first() throws SQLException boolean last() throws SQLException boolean absolute(int row) throws SQLException boolean relative(int row) throws SQLException void delete. Row(int row) throws SQLException void update. XXX(int idx, XXX x) throws SQLException void update. Row() throws SQLException void move. To. Insert. Row () throws SQLException void move. To. Current. Row() throws SQLException void insert. Row() throws SQLException

Example : Backward Statement stmt = conn. create. Statement (Result. Set. TYPE_SCROLL_SENSITIVE, Result. Set. CONCUR_UPDATABLE); Result. Set rs = stmt. execute. Query("SELECT empno, sal FROM emp"); rs. after. Last(); while ( rs. previous() ) { System. out. println(rs. get. String("empno") + " " + rs. get. Float("sal")); }. . .

Example : delete row. . . rs. absolute(5); rs. delete. Row(); . . .

Example : update row Statement stmt = conn. create. Statement (Result. Set. TYPE_SCROLL_SENSITIVE, Result. Set. CONCUR_UPDATABLE); Result. Set rs = stmt. execute. Query("SELECT empno, sal FROM emp"); if (rs. absolute(10)) // (returns false if row does not exist) { rs. update. String(1, "28959"); rs. update. Float("sal", 100000. 0 f); rs. update. Row(); } // Changes will be made permanent with the next COMMIT operation. .

Example : insert row. . . Statement stmt = conn. create. Statement (Result. Set. TYPE_SCROLL_SENSITIVE, Result. Set. CONCUR_UPDATABLE); Result. Set rs = stmt. execute. Query("SELECT empno, sal FROM emp"); rs. move. To. Insert. Row(); rs. update. String(1, "28959"); rs. update. Float("sal", 100000. 0 f); rs. insert. Row(); // Changes will be made permanent with the next COMMIT operation. rs. move. To. Current. Row(); // Go back to where we came from. . .

Visibility of Internal & External Changes Can See Internal DELETE? UPDATE? INSERT? Can See External DELETE? UPDATE? INSERT? forward-only no yes no no scroll-sensitive yes no no yes no scroll-insensitive yes no no Ÿ Detection of External Changes – no use of row. Deleted(), row. Updated(), row. Inserted() Ÿ Oracle Implementation of Scroll-Sensitive Result Sets – the concept of a window based on the fetch size Ÿ refresh. Row()

Demonstration Result. Set

Batch updates Ÿ Grouping multiple UPDATE, DELETE, or INSERT statements into a single "batch“ Ÿ Performance improvement because of reducing round trip !! Ÿ Two type of batch update – Standard model : Sun’s JDBC Spec. since 8. 1. 6 – Oracle specific model : Oracle’s implementation. since 8. 1. 5 Ÿ Don’t mix these types in a single connection

Batch update : Oracle specific model Ÿ Oracle model use batch value and results in implicit processing. Ÿ The driver knows ahead of time how many operations will be batched. Ÿ Only Oracle. Prepared. Statement is suppored. Casting is required. Ÿ batch value : 5 ~ 30 (default 1) Ÿ send. Batch() method will be executed, when – – – commit() method call statement’close() method call connection’ close() method call

Examples : Oracle Specific Prepared. Statement ps = conn. prepare. Statement("insert into dept values (? , ? )"); //Change batch size for this statement to 3 ((Oracle. Prepared. Statement)ps). set. Execute. Batch (3); ps. set. Int(1, 23); ps. set. String(2, "Sales"); ps. set. String(3, "USA"); ps. execute. Update(); //JDBC queues this for later execution … //Third insert statement ps. set. Int(1, 26); ps. set. String(2, "HR"); ps. set. String(3, "Mongolia"); ps. execute. Update(); //JDBC send the requests to the database ((Oracle. Prepared. Statement)ps). send. Batch(); // JDBC sends the queued request conn. commit(); ps. close();

Batch update : Standard model Ÿ Explicitly add statement to the batch using add. Batch() method Ÿ explicitly executing the batch using an execute. Batch() method after finish to add batch. Ÿ Statement, Prepared. Statement, Callable. Statement are supported.

Example : Standard conn. set. Auto. Commit(false); Prepared. Statement pstmt = conn. prepare. Statement("INSERT INTO employees VALUES(? , ? )"); pstmt. set. Int(1, 2000); pstmt. set. String(2, "Milo Mumford"); pstmt. add. Batch(); pstmt. set. Int(1, 3000); pstmt. set. String(2, "Sulu Simpson"); pstmt. add. Batch(); int[] update. Counts = pstmt. execute. Batch(); conn. commit(); pstmt. close(); . . .

Performance Issues Ÿ Disable Auto-Commit Mode conn. set. Auto. Commit (false); Ÿ Prefetch Rows conn. set. Default. Row. Prefetch (20); Ÿ Batch Updates conn. set. Default. Execute. Batch (10); stmt. set. Execute. Batch (15); Ÿ Define Column types stmt. define. Columntype (1, <type> ); stmt. define. Column. Type (1, <type>, <length> );

Statement Caching Ÿ Performance Improved by Caching Executable Statements – Repeated use (e. g. loop) Ÿ prevent the overhead of repeated cursor creation Ÿ prevent repeated statement parsing and creation – – Cache statements associated with a particular physical connection Two Types Ÿ implicit Ÿ explicit

Using Statement Caching Ÿ Enabling and Disabling Statement Caching ((Oracle. Connection)conn). set. Stmt. Cache. Size(10) ((Oracle. Connection)conn). set. Stmt. Cache. Size(0); Ÿ Checking for Statement Creation Status int state = ((Oracle. Statement)stmt). creation. State() Ÿ Physically Closing a Cached Statement – The close() method of a statement object caches the statement instead of closing it. Ÿ Using Implicit Statement Caching Ÿ Using Explicit Statement Caching ((Oracle. Prepared. Statement(pstmt). close. With. Key(“mykey”); pstmt=((Oracle. Connection)conn). prepare. Statement. With. Key(“mykey”);

JNDI Ÿ JNDI(Java Naming and Directory Interface) can be used in addition to the JDBC driver manager to manage data sources and connections. Ÿ Don’t care about JDBC connection string. It isn’t hard coded anymore. Ÿ Can access databases using their names not connection string. Ÿ Steps – – creating datasource : set datasource’s properties Register : register connection properties to JNDI Server Lookup : find datasource object using given name within JNDI Server. Create Connection : using datasource

Example : JNDI // Creating Oracle. Data. Source ods = new Oracle. Data. Source(); ods. set. Driver. Type("oci 8"); ods. set. Server. Name("dlsun 999"); ods. set. Network. Protocol("tcp"); ods. set. Database. Name("816"); ods. set. Port. Number(1521); ods. set. User("scott"); ods. set. Password("tiger"); // Register Context ctx = new Initial. Context(); ctx. bind("jdbc/sampledb", ods);

Example : JNDI // lookup and create connection Oracle. Data. Source odsconn = (Oracle. Data. Source)ctx. lookup("jdbc/sampledb"); Connection conn = odsconn. get. Connection(); . . .

Connection Pooling Ÿ Connection pooling is a framework for caching database connection Ÿ reuse of physical connections. reduce overhead of creating new connections. Ÿ Connection pool data source – Factory to creating pooled connections Ÿ Pooled connection – represents a physical connection to a data source. – Factory for the Connection Objects. Ÿ Connection Cache

Connection Pooling Connection get. Pooled. Connection. Pool. Data. Source ( close or error event) Connection. Event. Listener

Connection Pooling : API public interface Connection. Pool. Data. Source { Pooled. Connection get. Pooled. Connection() throws SQLException; Pooled. Connection get. Pooled. Connection(String user, String password) throws SQLException; } public interface Pooled. Connection { Connection get. Connection() throws SQLException; void close() throws SQLException; void add. Connection. Event. Listener(Connection. Event. Listener listener). . . ; void remove. Connection. Event. Listener(Connection. Event. Listener listener); void set. Stmt. Cache. Size(int size, boolean clear. Meta. Data); int get. Stmt. Cache. Size(); }

Example : Connection Pooling // Create a Oracle. Connection. Pool. Data. Source instance Oracle. Connection. Pool. Data. Source ocpds = 1. Creating Connection. Pool. Data. Source new Oracle. Connection. Pool. Data. Source(); // Set connection parameters ocpds. set. URL("jdbc: oracle: oci 8: @"); ocpds. set. User("scott"); ocpds. set. Password("tiger"); 2. Creating Pooled. Connection 3. Creating Connection Object // Create a pooled connection Pooled. Connection pc = ocpds. get. Pooled. Connection(); // Get a Logical connection Connection conn = pc. get. Connection();

Connection Cache Ÿ JDBC 2. 0 doesn’t mandate that JDBC vendors provide one, but they highly recommend it Ÿ 2 -fold strategy – We implemented a cache with 3 commonly used schemes – Provide an interface for the end user to develop their own cache but they still would like to reuse Oracle. Connection. Event. Listener

Oracle. Connection. Cache Ÿ An interface one needs to implement if they like to have their own Cache but reuse our infrastructure Ÿ Extends Data. Source interface Ÿ Additional methods – reuse. Pooled. Connection(Pooled. Connection) – close

Oracle. Connection. Cache. Impl Ÿ Oracle’s implementation of a basic Cache Ÿ Extends Oracle. Data. Source and implements Oracle. Connection. Cache Ÿ JNDI Referenceable Ÿ Implements java. io. Serializable Ÿ Simple, Easy and Efficient Ÿ 3 Schemes are provided

Oracle. Connection. Cache. Impl Ÿ Dynamic : A typical grow and shrink scheme. Could create new connections beyond the maximum limit when all the existing ones are active. This is the default Scheme. Ÿ Fixed with No Wait : Request for new connections beyond the maximum limit will return null. Ÿ Fixed With Wait : Request for new connections are blocked when all the connections in the cache up to the limit are active and consumed.

Oracle. Connection. Cache. Impl Ÿ All Connections in a cache are to the same Database and have the schema Ÿ Connections obtained are logical connections Ÿ Connection Properties can be set in 2 ways – Set Properties directly like on Data. Source – Through a Connection. Pool. Data. Source

Demonstration Connection Pooling

Distributed Transactions Ÿ A set of two or more related transactions that must be managed in a coordinated way. Ÿ Global transaction vs Branch transaction Ÿ X/Open Standard : XA. not specific java Ÿ Each transaction is managed by Transaction Manager that implements Java Transaction API (JTA). Ÿ XA functionality is usually isolated from a client application, being implemented instead in a middletier environment such as an application server.

Distributed Transactions : XA Components Ÿ XA data source – extensions of connection pool data sources and other data sources, and similar in concept and functionality. Ÿ XA connection – extensions of pooled connection Ÿ XA resource – – – Database resource XA connection : XA resource = 1: 1 Physical DB session : XA resource = 1: 1 Ÿ Transaction ID – Identifier of each transaction branch.

Distributed Transactions : XA APIs Ÿ oracle. jdbc. xa package – – Oracle. Xid Oracle. XAException Ÿ oracle. jdbc. xa. client package – – XADatasource, XAConnection, XAResource outside Oracle database Ÿ oracle. jdbc. xa. server package – – XADatasource, XAConnection, XAResource inside Oracle database

Distributed Transactions : XA APIs public interface XADatasource. XAConnection get. XAConnection() throws SQLException public interface XAConnection extends Pooled. Connection. XAResource get. XAResource() throws SQLException; public interface XAResource. void commit(Xid xid, boolean one. Phase) throws XAException; . void end(Xid xid, int flags) throws XAException; . void forget(Xid xid) throws XAException; . int prepare(Xid xid) throws XAException; . Xid[] recover(int flag) throws XAException; . void rollback(Xid xid) throws XAException; . void start(Xid xid, int flags) throws XAException; . boolean is. Same. RM(XAResource xares) throws XAException;

Example : Distributed Transaction XA with Two-Phase Commit Operation 1. Start transaction branch #1. 2. Start transaction branch #2. 3. Execute DML operations on branch #1. 4. Execute DML operations on branch #2. 5. End transaction branch #1. 6. End transaction branch #2. 7. Prepare branch #1. 8. Prepare branch #2. 9. Commit branch #1. 10. Commit branch #2.

SQLJ

SQLJ Ÿ Standard way to embed SQL statements in Java programs. Ÿ 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

Comparing SQLJ with JDBC java. sql. Prepared. Statement stmt; stmt = conn. prepare. Statement (“INSERT INTO emp ” + “VALUES(? , ? , ? ”); stmt. set. String(1, name); stmt. set. Int(2, dept); stmt. set. String(3, mgr); stmt. set. Double(4, sal); stmt. set. Date(5, today); stmt. execute(); stmt. close(); SQLJ #sql { INSERT INTO emp VALUES (: name, : dept, : mgr, : sal, : today)};

What you need to learn Ÿ SQLJ program template Ÿ Issuing a query Ÿ Updating a table using a statement with bind variables

SQLJ Program Template import java. sql. *; // Program uses JDBC import oracle. sqlj. runtime. *; // and SQLJ class Example 1 { public static void main (String args []) throws SQLException { // Your SQLJ code goes here } }

SQLJ clauses • A executable SQLJ clause has this form: #sql { SQL-statement } ; • The SQL statement appears inside the curly braces: #sql { DELETE FROM tab WHERE col < 0 };

Doing a Query Ÿ Define an Iterator Type Ÿ Create instances of the Iterator Type Ÿ Populate Iterator with results from query Ÿ Use Iterator methods to access the data

Define an Iterator Type • The Iterator definition lists the SQL names and the Java types of the result columns #sql iterator Emp. Cursor (String ENAME, Double SAL); Ÿ A class Emp. Cursor will be produced with the following methods: boolean next(); //Moves to next row, if any String ENAME(); //Gets column ENAME as String Double SAL(); //Gets column SAL as Double

Use the Iterator Type to do a Query • Declare variable of the Iterator Type • Populate iterator with results from query • Use the Iterator methods to access data Emp. Cursor c; #sql c = {select ENAME, SAL from EMP}; while (c. next ()) { String ename = c. ENAME (); Double sal = c. SAL (); }

How to do an Update • The SQL statement may have Java bind variables prefixed with a colon ': ' String ename = “Seik Waljay”; Double sal = 15000; #sql { update EMP set SAL = : sal where ENAME = : ename };

Q&A