Transcript Programming in Java - The College of Saint Rose

Connecting to
Oracle using Java
March 10, 2009
David Goldschmidt, Ph.D.
[email protected]
JDBC


JDBC is a platform-independent Java API for
executing SQL statements
Use JDBC to:
JDBC
Connect to a database
 Send SQL statements
 Receive results


i.e. rows of data
Add or update existing rows of data
 Call PL/SQL procedures, functions, etc.

database
JDBC Drivers

Before connecting to a database,
a driver class must first be loaded into
the Java Virtual Machine (or JVM)

A driver is simply a class in Java
MySQL:
 Oracle:


"com.mysql.jdbc.Driver"
"oracle.jdbc.driver.OracleDriver"
Oracle driver is located within the ojdbc14.jar
JAR file of the Oracle distribution
JDBC Drivers

Sun defines four categories of JDBC drivers:
1.
2.
3.
4.
JDBC bridge driver – uses native code to connect a
Java client to a third-party API (e.g. JDBC-ODBC)
Native API (part Java driver) – wraps native code with
Java classes (e.g. Oracle Call Interface (OCI ) driver)
Network protocol (pure Java driver) – Java classes
communicate via a network protocol to a middletier server, which communicates with the database
Native protocol (pure Java driver) – Java classes
communicate directly with the database (e.g. Thin)
JDBC Drivers
JDBC-ODBC
driver
ODBC
driver
Oracle Call
Interface
(OCI)
Oracle Listener
Oracle RDBMS
JDBC OCI
driver
JDBC Thin
driver
JDBC Connection Strings

Connect to a database using its connect string
(i.e. its Connection URL):
Access:
 MySQL:
 Oracle:


"jdbc:odbc:dataSource"
"jdbc:mysql://hostname/dbname"
"jdbc:oracle:thin:@hostname:port#:SID"
Use a Connection object:
Connection connection =
DriverManager.getConnection( dbConnectURL );
JDBC Interfaces
Driver
Connection
Connection
Statement
Statement
Statement
Statement
ResultSet
ResultSet
ResultSet
ResultSet
Using JDBC
Building Statements

Statement createStatement()
 Prepare
a query without parameters
 Result set is read-only and forward-only
 For repeated queries, gain performance
speedup by using prepareStatement()
instead
Building Statements

PreparedStatement
prepareStatement( String sql )
 Prepare
a parameterized query
 Result set is read-only and forward-only
Building Statements

CallableStatement
prepareCall( String sql )
 Prepare
a call to a stored procedure
 Register
any OUT (or IN OUT) parameters
 Set any IN (or IN OUT) parameters
 Results
are read-only and forward-only
Processing Statements

boolean execute( String sql )
 Use
this method to execute DDL statements
and stored procedures
 Return value indicates whether
a ResultSet object is available
Processing Statements

ResultSet executeQuery( String sql )
 Use
this method to execute DDL statements
you expect to receive results from
 i.e. Use for your SELECT statements
Processing Statements

int executeUpdate( String sql )
 Use
this method to execute INSERT, UPDATE,
and DELETE statements
 Return value is the number of rows affected
Processing Results

Default ResultSet behavior
is read-only and forward-only
ResultSet
 Change
default using resultSetType and
resultSetConcurrency parameters
ResultSet
 For resultSetType:
 TYPE_FORWARD_ONLY
 TYPE_SCROLL_INSENSITIVE
 TYPE_SCROLL_SENSITIVE
scrolling uses a client-side memory cache
sensitivity refers to whether
database changes made while
the ResultSet object is open
are visible
Processing Results
 For resultSetConcurrency:
ResultSet
 CONCUR_READ_ONLY
 CONCUR_UPDATABLE
 To
implement updateability, all queries request
the ROWID for each row
ROWID is
a proprietary SQL data type
that uniquely identifies each row of
the database
Sensitivity and Updateability

A scroll-sensitive ResultSet must:
Perform SELECT against only one table
 Explicitly specify columns (i.e. not SELECT *)
 Not use an ORDER BY clause


An updateable ResultSet must also:

Include all nonnullable columns (for INSERT)
Batching

Improve performance by combining
multiple SQL statements into a batch
Disable auto-commit
 Oracle supports PreparedStatement batching only
 Call addBatch() instead of executeUpdate()


Repeat!
Then call executeBatch() and commit()
 Tune frequency of commits
 Use clearBatch() to cancel your batch
