Introduction to JDBC Programming

1
(No Transcript)
2
Introduction to JDBC Programming
Oracle Korea
3
? ? ? ?

• Basic JDBC Programming
• Advanced Programming
• JDBC 2.0 Features
• SQLJ
• QA

4
Basic JDBC Programming
5
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

6
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

7
Overview of Querying a Database With JDBC
Connect
Query
Processresults
Close
8
Stage 1 Connect
Connect
Register the driver
Connect to the database
Query
Processresults
Close
9
A JDBC Driver

• Is an interpreter that translates JDBC method
  calls to vendor-specific database commands
• Implements interfaces in java.sql
• Can also provide a vendors extensions to the
  JDBC standard

Database commands
JDBC calls
Driver
Database
10
Oracle JDBC Driver
Oracle 8i
JDBC Thin driver
Java Socket
SQLNet
Java Store Procedure
Java Engine
JDBC Server- Side Internal driver
JDBC OCI driver
SQL PL/SQL Engines
SQLNet
OCI C Lib
database Lib

• JDBC Thin driver (also available in server)
• JDBC OCI driver
• JDBC Server-Side Internal driver (Kernal
  PRogram Bundled Calls driver)

11
Oracle JDBC Drivers

• Thin driver
• a 100 Java driver for client-side use without an
  Oracle installation, particularly with applets
• OCI drivers (OCI8 and OCI7)
• 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 middle-tier scenarios
• server-side internal driver
• for code that runs inside the target server

12
Oracle JDBC Drivers Thin Client Driver

• Written entirely in Java
• Applets must use this driver

Applet
JDBC
Thin driver
O7 or O8
Client
Server
13
Oracle JDBC Drivers OCI Client Drivers

• Written in C and Java
• Must be installed on the client

Application
JDBC
OCI driver
O7 or O8
ocixxx.dll
Client
Server
14
Oracle JDBC Drivers 3. Server-Side Driver

• Runs inside the database
• Java stored procedures must use this driver

Stored procedure
Oracle8i
SQLEngine
C library
15
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

16
About JDBC URLs

• JDBC uses a URL to identify the database
  connection.

jdbcltsubprotocolgtltsubnamegt
Databaseidentifier
Protocol
Subprotocol
jdbcoracleltdrivergt_at_ltdatabasegt
17
JDBC URLs with Oracle Drivers

• Thin driver
• OCI driver
• Server-side driver Use the default connection

jdbcoraclethin_at_lthostgtltportgtltSIDgt
jdbcoracleoci8_at_ltTNSNAMES entrygt
18
How to Make the Connection

• 1. Register the driver.

DriverManager.registerDriver (new oracle.jdbc.dri
ver.OracleDriver())
2. Connect to the database.
Connection conn DriverManager.getConnection (UR
L, userid, password)
Connection conn DriverManager.getConnection ("j
dbcoraclethin_at_myhost1521orcl", "scott",
"tiger")
19
Using Connection
java.sql.Connection
Creating Statement
createStatment() prepareStatment(String) prepareCa
ll(String)
Transaction Management
commit() rollback()
Get database metadata
getMetaData()
Conneciton related
close() isClosed()
20
Demonstration
Connection
21
Stage 2 Query
Connect
Query
Create a statement
Query the database
Processresults
Close
22
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
• executeQuery() for QUERY statements
• executeUpdate() for INSERT, UPDATE, DELETE, or
  DDL statements
• execute() for either type of statement

23
How to Query the Database

• 1. Create an empty statement object.
• 2. Execute the statement.

Statement stmt conn.createStatement()
ResultSet rset stmt.executeQuery(statement) int
count stmt.executeUpdate(statement) boolean
isquery stmt.execute(statement)
24
Querying the Database Examples

• Execute a select statement.

Statement stmt conn.createStatement() ResultSet
rset stmt.executeQuery ("select RENTAL_ID,
STATUS from ACME_RENTALS")

• Execute a delete statement.

Statement stmt conn.createStatement() int
rowcount stmt.executeUpdate ("delete from
ACME_RENTAL_ITEMS where rental_id 1011")
25
Stage 3 Process the Results
Connect
Query
Step through the results
Assign results to Java variables
Processresults
Close
26
The ResultSet Object

• JDBC returns the results of a query in a
  ResultSet object.
• A ResultSet maintains a cursor pointing to its
  current row of data.
• Use next() to step through the result set row by
  row.
• getString(), getInt(), and so on assign each
  value to a Java variable.

27
How to Process the Results

• 1. Step through the result set.
• 2. Use getXXX() to get each column value.

while (rset.next())
String val rset.getString(colname)
String val rset.getString(colIndex)
while (rset.next()) String title
rset.getString("TITLE") String year
rset.getString("YEAR") // Process or display
the data
28
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 ResultSet.wasNull() to determine whether a
  column has a null value.

while (rset.next()) String year
rset.getString("YEAR") if (rset.wasNull()
// Handle null value
29
Mapping Database Types to Java Types

• ResultSet maps database types to Java types.

ResultSet rset stmt.executeQuery ("select
RENTAL_ID, RENTAL_DATE, STATUS from
ACME_RENTALS") int id rset.getInt(1) Date
rentaldate rset.getDate(2) String status
rset.getString(3)
Col Name RENTAL_ID RENTAL_DATE STATUS
Type NUMBER DATE VARCHAR2
30
Stage 4 Close
Connect
Query
Close the result set
Processresults
Close the statement
Close
Close the connection
31
How to Close the Connection

• 1. Close the ResultSet object.
• 2. Close the Statement object.
• 3. Close the connection (not necessary for
  server-side driver).

rset.close()
stmt.close()
conn.close()
32
Demonstration
A Simple JDBC Program
33
The DatabaseMetaData Object

• The Connection object can be used to get a
  DatabaseMetaData object.
• This object provides more than 100 methods to
  obtain information about the database.

34
How to Obtain Database Metadata

• 1. Get the DatabaseMetaData object.
• 2. Use the objects methods to get the metadata.

DatabaseMetaData dbmd conn.getMetaData()
DatabaseMetaData dbmd conn.getMetaData() String
s1 dbmd getURL() String s2
dbmd.getSQLKeywords() boolean b1
dbmd.supportsTransactions() boolean b2
dbmd.supportsSelectForUpdate()
35
The ResultSetMetaData Object

• The ResultSet object can be used to get a
  ResultSetMetaData object.
• ResultSetMetaData object provides metadata,
  including
• Number of columns in the result set
• Column type
• Column name

36
How to Obtain Result Set Metadata

• 1. Get the ResultSetMetaData object.
• 2. Use the objects methods to get the metadata.

ResultSetMetaData rsmd rset.getMetaData()
ResultSetMetaData rsmd rset.getMetaData() for
(int i 0 i lt rsmd.getColumnCount() i)
String colname rsmd.getColumnName(i) int
coltype rsmd.getColumnType(i)
37
Demonstration
Dynamic Query using MetaData
38
The PreparedStatement Object

• A PreparedStatement 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.

39
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 (?).

PreparedStatement pstmt conn.prepareStatement(
"update ACME_RENTALS set STATUS ? where
RENTAL_ID ?")
PreparedStatement pstmt conn.prepareStatement(
"select STATUS from ACME_RENTALS where
RENTAL_ID ?")
40
How to Execute a Prepared Statement

• 1. Supply values for the variables.
• 2. Execute the statement.

pstmt.setXXX(index, value)
pstmt.executeQuery() pstmt.executeUpdate()
PreparedStatement pstmt conn.prepareStatement(
"update ACME_RENTALS set STATUS ? where
RENTAL_ID ?") pstmt.setString(1,
"OUT") pstmt.setInt(2, rentalid) pstmt.executeUp
date()
41
The CallableStatement Object

• A CallableStatement 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
  CallabableStatement object.

42
How to Create a Callable Statement

• Register the driver and create the database
  connection.
• Create the callable statement, identifying
  variables with a question mark (?).

CallableStatement cstmt conn.prepareCall("c
all " ADDITEM "(?,?,?)")
cstmt.registerOutParameter(2,Types.INTEGER)
cStmt.registerOutParameter(3,Types.DOUBLE)
43
How to Execute a Callable Statement

• 1. Set the input parameters.
• 2. Execute the statement.
• 3. Get the output parameters.

cstmt.setXXX(index, value)
cstmt.execute(statement)
var cstmt.getXXX(index)
44
Using Transactions

• The server-side driver does not support
  autocommit mode.
• With other drivers
• New connections are in autocommit mode.
• Use conn.setAutoCommit(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

45
Oracle JDBC Extensions

• Oracle provides many extensions to standard JDBC
• for example

Connection
OracleConnection
Statement
OracleStatement
PreparedStatement
OraclePreparedStatement
CallableStatement
OracleCallableStatement
ResultSet
OracleResultSet
46
Advanced Programming
47
Advanced Programming

• LOB Data type
• Advanced data type

48
LOB Data type

• oracle.sql.BLOB
• java.sql.Blob
• Processing locator getBLOB(), setBLOB() methods
  using jdk1.2.x
• oracle.sql.CLOB
• java.sql.Clob
• Processing locator getCLOB(), setCLOB()
  methods using jdk1.2.x
• oracle.sql.BFILE
• Oracle Specific Datatype

49
BLOB

• Reading
• getBinaryStream() ??? ??.
• Return? InputStream? ???? ??(??, ??????)
• Writing
• getBinaryOutputStream() ??? ??
• java.io.OutputStream ??? ???? Writing

50
BLOB Reading
Statement stmt conn.createStatement() ResultSet
rs stmt.executeQuery (select blob_column
from blob_table) while (rs.next()) BLOB blob
((OracleResultSet)rs).getBLOB(1) InputStream
is blob.getBinaryStream() int read 0 while
( (read is.read()) ! -1) // to do like
writing a file using the stream is.close()
1. create statement
2. create resultset
3. get Blob locator
4. get InputStream
5. InputStream ??
6. InputStream close
51
BLOB Writing
1. create statement
Statement stmt conn.createStatement() ResultSet
rs stmt.executeQuery (select blob_column
from blob_table for update) while
(rs.next()) BLOB blob ((OracleResultSet)rs).ge
tBLOB(1) OutputStream os blob.getBinaryOutputSt
ream() InputStream src new InputStream() byte
buffer new byte1024 int read 0 while
( (read src.read(buffer)) !
-1) os.write(buffer, 0, read) // write
blob. src.close() os.close()
2. create resultset
3. get Blob locator
4. get OutputStream
5. Source read
6. write blob
7. close stream
52
CLOB

• Reading
• getAsciiStream() ??? ???
• getCharacterStream() ???? ???.
• Return? InputStream? ???? ??(??, ??????? Writing)
  
• Writing
• getAsciiOutputStream() ??? ???? locator ??
• getCharacterOutputStream() ??? ??.
• java.io.OutputStream ??? ???? Writing

53
CLOB Reading
Statement stmt conn.createStatement() ResultSet
rs stmt.executeQuery (select clob_column
from clob_table) while (rs.next()) CLOB blob
((OracleResultSet)rs).getCLOB(1) Reader reader
clob.getCharacterStream() char buffer new
char10 int length 0 while ( (length
reader.read (buffer)) ! -1) System.out.println
(new String(buffer)) is.close()
1. create statement
2. create resultset
3. get Clob locator
4. get InputStream
5. InputStream ??
6. InputStream close
54
CLOB Writing
Statement stmt conn.createStatement() ResultSet
rs stmt.executeQuery (select clob_column
from clob_table for update) while
(rs.next()) CLOB clob ((OracleResultSet)rs).
getCLOB(1) Writer writer clob.getCharacterOu
tputStream() FileInputStream src new
FileInputStream(tmp) byte buffer new
byte512 int read 0 while ( (read
src.read(buffer)) ! -1)
writer.write(buffer, 0, read) // write clob.
src.close() writer.close()
1. create statement
2. create resultset
3. get clob locator
4. get OutputStream
5. Source read
6. write clob
7. close stream
55
BFILE

• Locator
• getOracleObject(), getBFILE() ??? ??
• oracle.sql.BFILE methods
• fileExists(), isFileOpen() ???
• openFile(), closeFile()
• Creating a BFILE column
• Reading
• Writing

56
BFILE Creating a BFILE column
cmd "INSERT INTO my_bfile_table VALUES (one,
bfilename(test_dir,file1.data))" stmt.execute
(cmd)
cmd "INSERT INTO my_bfile_table VALUES (three,
null)" stmt.execute(cmd)
57
BFILE Processing
cmd "SELECT FROM my_bfile_table WHERE x
one" rset stmt.executeQuery (cmd) if
(rset.next ()) BFILE bfile
((OracleResultSet)rset).getBFILE (2)
bfile.openFile() InputStream in
bfile.getBinaryStream() int length byte
buf new byte512 while ((length
in.read(buf)) ! -1) // to do something
using byte buffer in.close() bfile.closeFile()

1. select the locator
2. Open the bfile
3. get a binary stream
4. InputStream ??
5. Close resoruce
58
Demonstration
LOB Data Handling
59
Advanced data type

• STRUCT
• Customized mapping
• ARRAY
• REF

60
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
• CustomDatum
• JPublisher can help generate CustomDatum and
  SQLData mappings

61
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
• getAttribute() retrieves values array as
  java.lang.Object
• Casts Struct to oracle.sql.STRUCT to use
  oracle.sql extensions
• getOracleAttributes() returns value array as
  oracle.sql objects (native SQL format)
• getSQLTypeName() retrieves object type name
• References StructDescriptor for type information

62
Access Data in oracle.sql.STRUCT
Object
ResultSet
oracle.sql.STRUCT
Person(empid, name)
SSN
1001 Scott
(0) (1)
s
(1001, Scott) (1002, Alice) ...
rset.getObject(2) or rset.getSTRUCT(2)
s.getAttributes()
ResultSet rset st.executeQuery("select from
hr_table") while(rset.next())
System.out.println(rset.getString(1)) // SSN
STRUCT s (STRUCT) rset.getObject(2) // Person
Object attrs s.getAttributes()
System.out.println(((BigDecimal)
attrs0).intValue()) System.out.println((String
) atts0)
63
Customized Mapping

• You can specify how our drivers materialize
  object data
• The JDBC 2.0 way -- SQLData
• The Oracle way -- CustomDatum
• 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

64
Implementing SQLData

• public class JCustomer implements
  java.sql.SQLData
• private String sql_type
• public int custNo public String custName
• public String getSQLTypeName() throws
  SQLException return sql_type
• public void readSQL (SQLInput stream, String
  typeName) throws SQLException
• sql_type typeName
• custNo stream.readInt()
• custName stream.readString()
• public void writeSQL (SQLOutput stream) throws
  SQLException
• stream.writeInt(custNo)
• stream.writeString(custName)

65
Reading Writing SQLData

• //put an entry in the typemap
• Map map conn.getTypeMap() // Dictionary in
  JDK 1.1.x
• map.put("CUSTOMER_INFO_T", Class.forName("JCustome
  r"))
• ResultSet rs
• stmt.executeQuery("select VALUE p from
  CUSTOMER_TAB p")
• while (rs.next())
• //retrieve the object
• JCustomer jc (JCustomer) rs.getObject(1)
• PreparedStatement pstmt conn.prepareStatement
• ("INSERT INTO NEW_CUSTOMER_TAB VALUES (?)")
• pstmt.setObject(1, jc) // insert the object
• pstmt.executeUpdate()

66
CustomDatum

• A flexible, efficient and powerful mapping
• You need to provide two classes
• one that implements the CustomDatumFactory
  interface
• one that implements CustomDatum
• The factory is used to generate the instance of
  type CustomDatum
• Can be used for any customized conversion

67
Implementing CustomDatum CustomDatumFactory

• public class JCustomer implements
  oracle.sql.CustomDatum
• public Datum toDatum (OracleConnection c)
  throws SQLException
• StructDescriptor sd
• StructDescriptor.createDescriptor(CUSTOMER_
  INFO_T",c)
• Object attributes empName, empNo
• return new STRUCT(sd, c, attributes)
• public class JCustomerFactory implement
  oracle.sql.CustomDatumFactory
• public CustomDatum create(Datum d, int sqlType)
  throws SQLException
• if (d null) return null
• Object attributes ((STRUCT)
  d).getAttributes()
• return new JCustomer(attributes0,
  attributes1)

68
Reading Writing CustomDatum

• Statement s conn.createStatement()
• OracleResultSet rs (OracleResultSet)
• s.executeQuery("SELECT VALUE p FROM
  CUSTOMER_TAB p")
• while(rs.next())
• // retrieve the CustomDatum object
• Jcustomer jc (JCustomer)
• rs.getCustomDatum (1, new JCustomDatumFactory(
  ))
• PreparedStatement pstmt conn.prepareStatement
• ("INSERT INTO NEW_CUSTOMER_TAB VALUES (?)")
• // inserts a CustomDatum object
• pstmt.setObject(1, jc)
• pstmt.executeUpdate()

69
Array

• Accesses collection types in database
• Handles both VARRAY and Nested Table
• oracle.sql.ARRAY holds the pickle image or
  locator bytes
• References ArrayDescriptor for type information
• getArray() retrieves the array contents in
  default JDBC types
• getOracleArray() retrieves elements in oracle.sql
  format
• getResultSet() retrieves elements as a ResultSet

70
Access Data in Array
oracle.sql.ARRAY
Object
ResultSet
Phones varray(10) of varchar2(25)
Name
111-2222 222-3333
(0) (1)
s
(111-2222, 222-3333) (333-4444) ...
Scott Alice ...
s.getArray()
rset.getObject(2) or rset.getArray(2)
or rset.getARRAY(2)
ResultSet rset st.executeQuery("select from
hr_table") while(rset.next())
System.out.println(rset.getString(1)) // Name
ARRAY s (ARRAY) rset.getObject(2) // Phones
Object elems s.getArray() //
retrieve elements for (int ilt0 iltelems.length
i) System.out.println((String) elemsi)
71
Ref

• Accesses object reference in database
• oracle.sql.REF holds the ref
• getValue() to dereference
• setValue() to update the referenced object in the
  database immediately

72
Access Data using REF
ADDR (street_name, house_no)
oracle.sql.REF
ResultSet
name varchar2, col1 REF ADDR
PEOPLE
( Samsung-dong, 144-17 )
ref
(sam 0000280209420D2400 .)
(sam, addr1)
r.getValue()
rset.getObject(2) or rset.getREF(2) or
rset.getRef(2)
ResultSet rset st.executeQuery("select name,
ref(p) from people p") while(rset.next())
System.out.println(rset.getString(1)) // Name
REF r (REF)rset.getObject(2) // ADDR REF
STURCT addr r.getValue() // get
ADDR struct Object elems addr.getAttributes(
) // retrive attributes for (int ilt0
iltelems.length i) System.out.println((String)
elemsi)
73
Demonstration
Advanced Data type
74
JDBC 2.0 Features
75
JDBC 2.0 Features

• ResultSet enhancements
• Batch updates
• Statement Caching
• JNDI
• Connection Pooling Caching
• Distributed Transactions

76
ResultSet 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

77
6 Types of ResultSet

• forward-only/read-only
• forward-only/updatable
• scroll-sensitive/read-only
• scroll-sensitive/updatable
• scroll-insensitive/read-only
• scroll-insensitive/updatable

78
Scrollable Resultset
Scrollable ResultSet
Oracle 8i
Java Program
Statement stmt conn.createStatemen( ResultSet.
TYPE_SCROLL_INSENSITIVE, ResultSet.CONCURR_READ_ON
LY) ResultSet rset stmt.executeQuery() rset.
absolute(2) ...
Cache
Cursor
Table
next() previous() relative() absolute() first() la
st()
79
APIs
java.sql.Connection
Statement createStatement (int resultSetType,
int resultSetConcurrency) PreparedStatement
prepareStatement (String sql, int resultSetType,
int resultSetConcurrency) CallableStatement
prepareCall (String sql, int resultSetType, int
resultSetConcurrency)
resultSetType
resultSetConcurrency
ResultSet.TYPE_FORWARD_ONLY ResultSet.TYPE_SCROLL_
INSENSITIVE ResultSet.TYPE_SCROLL_SENSITIVE
ResultSet.CONCUR_READ_ONLY ResultSet.CONCUR_UPDATA
BLE
80
APIs
java.sql.ResultSet
void beforeFirst() throws SQLException void
afterLast() throws SQLException boolean first()
throws SQLException boolean last() throws
SQLException boolean absolute(int row) throws
SQLException boolean relative(int row) throws
SQLException
void deleteRow(int row) throws SQLException void
updateXXX(int idx, XXX x) throws
SQLException void updateRow() throws
SQLException void moveToInsertRow () throws
SQLException void moveToCurrentRow() throws
SQLException void insertRow() throws SQLException
81
Example Backward
Statement stmt conn.createStatement (ResultSet.T
YPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
ResultSet rs stmt.executeQuery("SELECT empno,
sal FROM emp") rs.afterLast() while (
rs.previous() ) System.out.println(rs.getString(
"empno") " " rs.getFloat("sal")) ...
82
Example delete row
... rs.absolute(5) rs.deleteRow() ...
83
Example update row
Statement stmt conn.createStatement (ResultSet.T
YPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
ResultSet rs stmt.executeQuery("SELECT empno,
sal FROM emp") if (rs.absolute(10)) // (returns
false if row does not exist) rs.updateString(1,
"28959") rs.updateFloat("sal",
100000.0f) rs.updateRow() // Changes will be
made permanent with the next COMMIT operation. ...
84
Example insert row
... Statement stmt conn.createStatement (ResultS
et.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATAB
LE) ResultSet rs stmt.executeQuery("SELECT
empno, sal FROM emp") rs.moveToInsertRow() rs.up
dateString(1, "28959") rs.updateFloat("sal",
100000.0f) rs.insertRow() // Changes will be
made permanent with the next COMMIT
operation. rs.moveToCurrentRow() // Go back to
where we came from... ...
85
Visibility of Internal External Changes
Can See Internal DELETE?
Can See Internal UPDATE?
Can See Internal INSERT?
Can See External DELETE?
Can See External UPDATE?
Can See External INSERT?
forward-only
no
yes
no
no
no
no
scroll-sensitive
yes
yes
no
no
yes
no
scroll-insensitive
yes
yes
no
no
no
no

• Detection of External Changes
• no use of rowDeleted(), rowUpdated(),
  rowInserted()
• Oracle Implementation of Scroll-Sensitive Result
  Sets
• the concept of a window based on the fetch size
• refreshRow()

86
Demonstration
ResultSet
87
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 Suns JDBC Spec. since 8.1.6
• Oracle specific model Oracles implementation.
  since 8.1.5
• Dont mix these types in a single connection

88
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 OraclePreparedStatement is suppored. Casting
  is required.
• batch value 5 30 (default 1)
• sendBatch() method will be executed, when
• commit() method call
• statementclose() method call
• connection close() method call

89
Examples Oracle Specific
PreparedStatement ps conn.prepareStatement(
"insert into dept values (?, ?, ?)") //Change
batch size for this statement to
3 ((OraclePreparedStatement)ps).setExecuteBatch
(3) ps.setInt(1, 23) ps.setString(2,
"Sales") ps.setString(3, "USA") ps.executeUpdate
() //JDBC queues this for later
execution //Third insert statement ps.setInt(1,
26) ps.setString(2, "HR") ps.setString(3,
"Mongolia") ps.executeUpdate() //JDBC send the
requests to the database ((OraclePreparedStatement
)ps).sendBatch() // JDBC sends the queued
request conn.commit() ps.close()
90
Batch update Standard model

• Explicitly add statement to the batch using
  addBatch() method
• explicitly executing the batch using an
  executeBatch() method after finish to add batch.
• Statement, PreparedStatement, CallableStatement
  are supported.

91
Example Standard
conn.setAutoCommit(false) PreparedStatement
pstmt conn.prepareStatement("INSERT INTO
employees VALUES(?, ?)") pstmt.setInt(1,
2000) pstmt.setString(2, "Milo
Mumford") pstmt.addBatch() pstmt.setInt(1,
3000) pstmt.setString(2, "Sulu
Simpson") pstmt.addBatch() int updateCounts
pstmt.executeBatch() conn.commit() pstmt.close()
...
92
Performance Issues

• Disable Auto-Commit Mode
• ?? ???? ?? ??? ???, ????? ??.?????
  'auto-commit'?? ?? ??.
• Prefetch Rows
• ?? ?, ?????? ?? ???? ?? ? ??. ?????? 10?? ??
  ????.
• Batch Updates
• ??? JDBC ??????? ??/?? ??? ?? ???? ??? ? ??.
• Define Column types
• ???????? ??? ???? ? ??. ??? ??? ?? ?? ?? network
  round-trip? ?? ? ??.

93
Performance Issues

• Disable Auto-Commit Mode
• Prefetch Rows
• Batch Updates
• Define Column types

conn.setAutoCommit (false)
conn.setDefaultRowPrefetch (20)
conn.setDefaultExecuteBatch (10) stmt.setExecuteB
atch (15)
stmt.defineColumntype (1, lttypegt
) stmt.defineColumnType (1, lttypegt, ltlengthgt )
94
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

95
Using Statement Caching

• Enabling and Disabling Statement Caching
• ((OracleConnection)conn).setStmtCacheSize(10)
• ((OracleConnection)conn).setStmtCacheSize(0)
• Checking for Statement Creation Status
• int state ((OracleStatement)stmt).creationState(
  )
• 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
• ((OraclePreparedStatement(pstmt).closeWithKey(myk
  ey)
• pstmt((OracleConnection)conn).prepareStatementWit
  hKey(mykey)

96
JNDI

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

97
Example JNDI

• // Creating
• OracleDataSource ods new OracleDataSource()
• ods.setDriverType("oci8")
• ods.setServerName("dlsun999")
• ods.setNetworkProtocol("tcp")
• ods.setDatabaseName("816")
• ods.setPortNumber(1521)
• ods.setUser("scott")
• ods.setPassword("tiger")
• // Register
• Context ctx new InitialContext()
• ctx.bind("jdbc/sampledb", ods)

98
Example JNDI
// lookup and create connection OracleDataSource
odsconn (OracleDataSource)ctx.lookup("jdbc/sampl
edb") Connection conn odsconn.getConnection()
...
99
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

100
Connection Pooling
Connection
getConnection
getPooledConnection
PooledConnection
( close or error event)
ConnectionEvent
ConnectionEventListener
101
Connection Pooling API
public interface ConnectionPoolDataSource
PooledConnection getPooledConnection() throws
SQLException PooledConnection getPooledConnection
(String user, String password) throws
SQLException
public interface PooledConnection Connection
getConnection() throws SQLException void close()
throws SQLException void addConnectionEventListen
er(ConnectionEventListener listener) ... void
removeConnectionEventListener(ConnectionEventListe
ner listener) void setStmtCacheSize(int
size) void setStmtCacheSize(int size, boolean
clearMetaData) int getStmtCacheSize()
102
Example Connection Pooling
// Create a OracleConnectionPoolDataSource
instance OracleConnectionPoolDataSource ocpds
new OracleConnectionPoolDataSource() // Set
connection parameters ocpds.setURL("jdbcoracleoc
i8_at_") ocpds.setUser("scott") ocpds.setPassword(
"tiger") // Create a pooled connection PooledConn
ection pc ocpds.getPooledConnection() // Get a
Logical connection Connection conn
pc.getConnection()
1. Creating ConnectionPoolDataSource
2. Creating PooledConnection
3. Creating Connection Object
103
Connection Cache

• JDBC 2.0 doesnt 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 OracleConnectionEventListener

104
OracleConnectionCache

• An interface one needs to implement if they like
  to have their own Cache but reuse our
  infrastructure
• Extends DataSource interface
• Additional methods
• reusePooledConnection(PooledConnection)
• closePooledConnection(PooledConnection)
• close

105
OracleConnectionCacheImpl

• Oracles implementation of a basic Cache
• Extends OracleDataSource and implements
  OracleConnectionCache
• JNDI Referenceable
• Implements java.io.Serializable
• Simple, Easy and Efficient
• 3 Schemes are provided

106
OracleConnectionCacheImpl

• 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.

107
OracleConnectionCacheImpl

• 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 DataSource
• Through a ConnectionPoolDataSource

108
Demonstration
Connection Pooling
109
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 middle-tier environment such as an application
  server.

110
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 11
• Physical DB session XA resource 11
• Transaction ID
• Identifier of each transaction branch.

111
Distributed Transactions XA APIs

• oracle.jdbc.xa package
• OracleXid
• OracleXAException
• oracle.jdbc.xa.client package
• XADatasource, XAConnection, XAResource
• outside Oracle database
• oracle.jdbc.xa.server package
• XADatasource, XAConnection, XAResource
• inside Oracle database

112
Distributed Transactions XA APIs
public interface XADatasource . XAConnection
getXAConnection() throws SQLException
public interface XAConnection extends
PooledConnection . XAResource getXAResource()
throws SQLException
public interface XAResource . void commit(Xid
xid, boolean onePhase) 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 isSameRM(XAResource
xares) throws XAException
113
Example Distributed TransactionXA 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.
114
SQLJ
115
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

116
Comparing SQLJ with JDBC
SQLJ
JDBC
java.sql.PreparedStatement stmt stmt
conn.prepareStatement (INSERT INTO emp
VALUES(?, ?, ?, ?, ?) stmt.setString(1,
name) stmt.setInt(2, dept) stmt.setString(3,
mgr) stmt.setDouble(4, sal) stmt.setDate(5,today
) stmt.execute() stmt.close()
sql INSERT INTO emp VALUES
(name,dept,mgr, sal,today)
117
What you need to learn

• SQLJ program template
• Issuing a query
• Updating a table using a statement with bind
  variables

118
SQLJ Program Template

• import java.sql. // Program uses JDBC
• import oracle.sqlj.runtime. // and SQLJ
• class Example1
• public static void main (String args )
• throws SQLException
• // Your SQLJ code goes here

119
SQLJ clauses

• A executable SQLJ clause has this form
• The SQL statement appears inside the curly
  braces

sql SQL-statement
sql DELETE FROM tab WHERE col lt 0
120
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

121
Define an Iterator Type

• The Iterator definition lists the SQL names and
  the Java types of the result columns
• A class EmpCursor will be produced with the
  following methods

sql iterator EmpCursor (String ENAME, Double
SAL)
boolean next() //Moves to next row, if any
String ENAME() //Gets column ENAME as String
Double SAL() //Gets column SAL as Double
122
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

EmpCursor c sql c select ENAME, SAL from
EMP while (c.next ()) String ename
c.ENAME () Double sal c.SAL ()
123
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
124
Q A
125
(No Transcript)