Transcript Introduction to JDBC Programming

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
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
Database
commands
JDBC calls
Driver
Database
 Implements interfaces in java.sql
 Can also provide a vendor’s extensions to the JDBC
standard
Oracle JDBC Driver
Oracle 8i
JDBC “Thin”
driver
Java Socket
JDBC “OCI”
driver
SQL*Net
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 “ServerSide 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 (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 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
O7 or O8
Client
Server
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
Oracle JDBC Drivers: 3. Server-Side
Driver
 Runs inside the database
 Java stored procedures must use this driver
Stored procedure
Oracle8i
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:oci8:@<TNSNAMES entry>
 Server-side driver: Use the default
connection
How to Make the Connection
1. Register the driver.
DriverManager.registerDriver (new
oracle.jdbc.driver.OracleDriver());
2. Connect to the database.
Connection conn = DriverManager.getConnection
(URL, userid, password);
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@myhost:1521:orcl",
"scott", "tiger");
Using Connection
java.sql.Connection
Creating Statement
createStatment()
prepareStatment(String)
prepareCall(String)
Transaction Management
commit()
rollback()
Get database metadata
getMetaData()
close()
isClosed()
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:
–
–
–
executeQuery() for QUERY statements
executeUpdate() 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.createStatement();
2. Execute the statement.
ResultSet rset = stmt.executeQuery(statement);
int count = stmt.executeUpdate(statement);
boolean isquery = stmt.execute(statement);
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");
Stage 3: Process the Results
Connect
Query
Step through the results
Process
results
Close
Assign results to Java
variables
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.
How to Process the Results
 1. Step through the result set.
while (rset.next()) { … }
 2. Use getXXX() to get each column value.
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
}
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
}
…}
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
Type
RENTAL_ID
NUMBER
RENTAL_DATE
DATE
STATUS
VARCHAR2
Stage 4: Close
Connect
Query
Close the result set
Process
results
Close the statement
Close
Close the connection
How to Close the Connection
1. Close the ResultSet 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 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.
How to Obtain Database Metadata
1. Get the DatabaseMetaData object.
DatabaseMetaData dbmd = conn.getMetaData();
2. Use the object’s methods to get the
metadata.
DatabaseMetaData dbmd = conn.getMetaData();
String s1 = dbmd getURL();
String s2 = dbmd.getSQLKeywords();
boolean b1 = dbmd.supportsTransactions();
boolean b2 = dbmd.supportsSelectForUpdate();
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
How to Obtain Result Set Metadata
1. Get the ResultSetMetaData object.
ResultSetMetaData rsmd = rset.getMetaData();
2. Use the object’s methods to get the
metadata.
ResultSetMetaData rsmd = rset.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); i++)
{
String colname = rsmd.getColumnName(i);
int coltype = rsmd.getColumnType(i);
…
}
Demonstration
Dynamic Query using MetaData
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.
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 = ?");
How to Execute a Prepared
Statement
1. Supply values for the variables.
pstmt.setXXX(index, value);
2. Execute the statement.
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.executeUpdate();
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.
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("{call " +
ADDITEM + "(?,?,?)}");
cstmt.registerOutParameter(2,Types.INTEGER);
cStmt.registerOutParameter(3,Types.DOUBLE);
How to Execute a Callable
Statement
1. Set the input parameters.
cstmt.setXXX(index, value);
2. Execute the statement.
cstmt.execute(statement);
3. Get the output parameters.
var = cstmt.getXXX(index);
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
Oracle JDBC Extensions
Oracle provides many extensions to standard
JDBC;
for example:
Connection
OracleConnection
Statement
OracleStatement
PreparedStatement
OraclePreparedStatement
CallableStatement
OracleCallableStatement
ResultSet
OracleResultSet
Advanced
Programming
Advanced Programming
 LOB Data type
 Advanced data type
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
BLOB
 Reading
–
getBinaryStream() 메서드 사용.
–
Return된 InputStream을 이용하여 처리(파일,
데이터베이스)
 Writing
–
getBinaryOutputStream() 메서드 사용
–
java.io.OutputStream 객체를 이용하여 Writing
BLOB : Reading
1. create statement
2. create resultset
3. get Blob locator
4. get InputStream
5. InputStream 처리
6. InputStream close
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();
}
BLOB : Writing
1. create statement
2. create resultset
3. get Blob locator
4. get OutputStream
5. Source read
6. write blob
7. close stream
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery
(“select blob_column from blob_table for update”);
while (rs.next())
{
BLOB blob = ((OracleResultSet)rs).getBLOB(1);
OutputStream os = blob.getBinaryOutputStream();
InputStream src = new InputStream(…);
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
–
–
–
getAsciiStream() : 아스키 스트림
getCharacterStream() : 유니코드 스트림.
Return된 InputStream을 이용하여 처리(파일,
데이터베이스에 Writing)
• Writing
–
–
–
getAsciiOutputStream() 메서드 사용해서
locator 획득
getCharacterOutputStream() 메서드 사용.
java.io.OutputStream 객체를 이용하여 Writing
CLOB : Reading
1. create statement
2. create resultset
3. get Clob locator
4. get InputStream
5. InputStream 처리
6. InputStream close
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 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 OutputStream
5. Source read
6. write clob
7. close stream
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.getCharacterOutputStream();
FileInputStream src = new FileInputStream(“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 :
–
getOracleObject(), getBFILE() 메서드 이용
 oracle.sql.BFILE methods
–
fileExists(), isFileOpen() 메서드
–
openFile(), closeFile()
 Creating a BFILE column
 Reading
 Writing
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);
BFILE : Processing
cmd = "SELECT * FROM my_bfile_table WHERE x =
’one’";
1. select the locator
rset = stmt.executeQuery (cmd);
if (rset.next ()) {
2. Open the bfile
BFILE bfile = ((OracleResultSet)rset).getBFILE (2);
bfile.openFile();
InputStream in = bfile.getBinaryStream();
3. get a binary stream
int length ;
byte[] buf = new byte[512];
while ((length = in.read(buf)) != -1)
4. InputStream 처리
{
// to do something using byte buffer
}
5. Close resoruce
in.close();
bfile.closeFile();
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
 CustomDatum
 JPublisher can help generate CustomDatum 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
 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
Access Data in oracle.sql.STRUCT
ResultSet
SSN
oracle.sql.STRUCT
Person(empid, name)
(1001, ’Scott’)
(1002, ‘Alice’)
...
s
rset.getObject(2) or
rset.getSTRUCT(2)
Object [ ]
(0) 1001
(1) ‘Scott’
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) attrs[0]).intValue());
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 -- 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
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);
}
}
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("JCustomer"));
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();
}
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
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(attributes[0], attributes[1]);
}
}
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();
}
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
Access Data in Array
oracle.sql.ARRAY
ResultSet
Name
‘Scott’
‘Alice’
...
Phones
varray(10) of varchar2(25)
(‘111-2222’, ‘222-3333’)
(‘333-4444’)
...
s
rset.getObject(2) or
rset.getArray(2) or
rset.getARRAY(2)
s.getArray()
Object [ ]
(0)
‘111-2222’
(1)
‘‘222-3333’
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 i<0; i<elems.length; i++)
System.out.println((String) elems[i]);
}
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
Access Data using REF
ResultSet
ADDR
oracle.sql.REF
(street_name, house_no)
name varchar2,
PEOPLE col1 REF ADDR
(‘sam’,
addr1)
(‘sam’
0000280209420D2400 ….)
ref
r.getValue()
rset.getObject(2) or
rset.getREF(2) or rset.getRef(2)
(
‘Samsung-dong’,
144-17
)
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 i<0; i<elems.length; i++)
System.out.println((String) elems[i]);
}
Demonstration
Advanced Data type
JDBC 2.0 Features
JDBC 2.0 Features
 ResultSet enhancements
 Batch updates
 Statement Caching
 JNDI
 Connection Pooling & Caching
 Distributed Transactions
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
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
Scrollable Resultset
Java Program
Statement stmt =
conn.createStatemen(
ResultSet.TYPE_SCROLL_IN
SENSITIVE,
ResultSet.CONCURR_READ_
ONLY);
ResultSet rset =
stmt.executeQuery();
rset.absolute(2);
...
Scrollable
ResultSet
Oracle 8i
Cache
Cursor
next()
previous()
relative()
absolute()
first()
last()
Table
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.CONCUR_READ_ONLY
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.CONCUR_UPDATABLE
ResultSet.TYPE_SCROLL_SENSITIVE
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
Example : Backward
Statement stmt = conn.createStatement
(ResultSet.TYPE_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"));
}
...
Example : delete row
...
rs.absolute(5);
rs.deleteRow();
...
Example : update row
Statement stmt = conn.createStatement
(ResultSet.TYPE_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.
...
Example : insert row
...
Statement stmt = conn.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp");
rs.moveToInsertRow();
rs.updateString(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...
...
Visibility of Internal & External
Changes
Can See Can See Can See
Internal
Internal
Internal
DELETE? UPDATE? INSERT?
Can See Can See
Can See
External External
External
DELETE? UPDATE? 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()
Demonstration
ResultSet
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 OraclePreparedStatement is suppored.
Casting is required.
 batch value : 5 ~ 30 (default 1)
 sendBatch() method will be executed, when
–
–
–
commit() method call
statement’close() method call
connection’ close() method call
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();
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.
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();
...
Performance Issues
–
–
–
–
Disable Auto-Commit Mode
 매번 커밋하지 않고 필요할 때에만, 커밋하도록
한다.기본적으로 'auto-commit'으로 되어 있다.
Prefetch Rows
 질의 시, 클라이언트가 미리 패치하는 행의 수 명시.
기본적으로는 10개의 행을 패치한다.
Batch Updates
 오라클 JDBC 드라이버에서는 삽입/갱신 작업을 배치
작업으로 처리할 수 있다.
Define Column types
 데이터베이스에게 타입을 알려주는 팁 설정. 데이터
베이스 컬럼 알기 위한 network round-trip을 줄일 수
있다.
Performance Issues
 Disable Auto-Commit Mode
conn.setAutoCommit (false);
 Prefetch Rows
conn.setDefaultRowPrefetch (20);
 Batch Updates
conn.setDefaultExecuteBatch (10);
stmt.setExecuteBatch (15);
 Define Column types
stmt.defineColumntype (1, <type> );
stmt.defineColumnType (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
((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(“mykey”);
pstmt=((OracleConnection)conn).prepareStatementWithKey(“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
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);
Example : JNDI
// lookup and create connection
OracleDataSource odsconn =
(OracleDataSource)ctx.lookup("jdbc/sampledb");
Connection conn = odsconn.getConnection();
...
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
getConnection
getPooledConnection
PooledConnection
ConnectionPoolDataSource
( close or error event)
ConnectionEvent
ConnectionEventListener
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 addConnectionEventListener(ConnectionEventListener listener) ... ;
void removeConnectionEventListener(ConnectionEventListener listener);
void setStmtCacheSize(int size);
void setStmtCacheSize(int size, boolean clearMetaData);
int getStmtCacheSize();
}
Example : Connection Pooling
// Create a OracleConnectionPoolDataSource instance
OracleConnectionPoolDataSource ocpds =
1. Creating ConnectionPoolDataSource
new OracleConnectionPoolDataSource();
// Set connection parameters
2. Creating PooledConnection
ocpds.setURL("jdbc:oracle:oci8:@");
ocpds.setUser("scott");
3. Creating Connection Object
ocpds.setPassword("tiger");
// Create a pooled connection
PooledConnection pc = ocpds.getPooledConnection();
// Get a Logical connection
Connection conn = pc.getConnection();
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
OracleConnectionEventListener
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
OracleConnectionCacheImpl
 Oracle’s implementation of a basic Cache
 Extends OracleDataSource and implements
OracleConnectionCache
 JNDI Referenceable
 Implements java.io.Serializable
 Simple, Easy and Efficient
 3 Schemes are provided
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.
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
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
–
–
OracleXid
OracleXAException
 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 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;
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
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();
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 Example1 {
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 EmpCursor (String ENAME, Double SAL);
 A class EmpCursor 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
EmpCursor 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