Oracle Applications Using Java & JDBC

ORACLE
APPLICATIONS USING
JAVA & JDBC
CIS 612 Topics on Advanced Database Systems
Victor Matos
What is JDBC ?
2
JDBC (Java database connectivity) is an API for the

Java programming language that defines how a client
may access a database.
It provides methods for querying and updating data in

a database.
JDBC is oriented towards relational databases.
It was originally included in the Java 2 Platform,

Standard Edition, version 1.4 (J2SE) (1997).
JDBC Architecture
Components
3
JDBC
JDBC 4.0
4.0 Specification.
Specification. JSR
JSR 221
221
Lance
Lance Andersen,
Andersen, Specification
Specification
Lead
Lead
November
November 7,
7, 2006
2006
What is JDBC ? Components
JDBC connections support creating and executing statements.
Statements may be action commands such as: SQL CREATE,

INSERT, UPDATE and DELETE or they may be query statements using
the SELECT statement.
Stored procedures may also be invoked through a statement.
Statements are one of the following types:
Statement the statement is sent to the database server each and

every time.
PreparedStatement the statement is cached and then the execution

path is pre determined on the database server allowing it to be executed
multiple times in an efficient manner.
CallableStatement used for executing stored procedures on the

database.
Update statements such as INSERT, UPDATE and

DELETE return an update count that indicates how
many rows were affected in the database. These
statements do not return any other information.
Query statements return a JDBC row ResultSet.
The row result set is used to traverse the result set.
Individual columns in a row are retrieved either by name or by

column number.
There may be any number of rows in the result set.
The row result set has metadata that describes the names of
the columns and their types.
A ResultSet is a Java object that contains the rows

resulting from executing a SQL query.
The data stored in a ResultSet object is retrieved with various
getXXX methods that allows access to the columns of the
current row.
The .next method is used to move to the next row of the
ResultSet.
The Statement methods executeQuery and getResultSet
both return a ResultSet object.
Example
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery (select * from employee);
A Brief Example How do JDBC apps

look like?
7
A JDBC based application makes possible

to do three things:
1.
2.
3.
Establish a connection with a data source

Send queries and update statements to the
data source
Process the results
A Brief Example How JDBC apps

look like
8
Typical Code Fragment

1
Class.forName("oracle.jdbc.OracleDriver");
String URL = "jdbc:oracle:thin:@localhost:1521:XE";
Connection con = DriverManager.getConnection(
URL, "myUserName", "myPassword");
Statement stmt = con.createStatement();

String mySQL = "select fName, salary from employee";
ResultSet rs = stmt.executeQuery(mySQL);
while (rs.next()) {
String fName = rs.getString("fName");
float salary = rs.getFloat("salary");
//... Do some work with the data
}
Download JDBC Driver

9
(Oracle)
http://java.sun.com/products/jdbc/overview.html
(MS SQL Server) http://msdn2.microsoft.com/en-us/data/aa937724.aspx
(MySQL)
http://www.mysql.com/products/connector/j/
10
Setting Up The SQL2005 JDBC

Drivers
Setting the Classpath
The JDBC driver is not part of the Java SDK. Therefore, you must
set the classpath to include the sqljdbc.jar file if you want to use
it. If the classpath is missing an entry for sqljdbc.jar, your
application will throw the common "Class not found" exception.
The sqljdbc.jar file is installed in the following location:
<installation
directory>\sqljdbc_<version>\<language>\sqljdbc.jar
The following is an example of the CLASSPATH statement that is
used for a Windows application:
CLASSPATH =.;C:\Program Files\Microsoft SQL Server

2005 JDBC Driver\sqljdbc_1.2\enu\sqljdbc.jar
11
Setting Up The SQL2005 JDBC

Drivers
Making a Simple Connection to a Microsoft SQL-Server-2005
Database
To connect to a MSSQL2005 database by using the DriverManager
class, you must first register the driver as follows:
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
When the driver is loaded, you can establish a connection by using a

connection URL:
String connectionUrl = "jdbc:sqlserver://localhost:1433;"
"databaseName=Northwind;
+
"user=MyUserName;password=*****;" ;
Connection con = DriverManager.getConnection(connectionUrl);
JDBC Drivers
12
There are four types of JDBC drivers. Each type

provides an increasingly higher levels of platform
independence, performance, and deployment
administration.
Type 1: JDBC-ODBC Bridge

Type 2: Native-API/partly Java driver
Type 3: Net-protocol/all-Java driver
Type 4: Native-protocol/all-Java driver
13
JDBC Drivers. Type1: JDBCODBC Bridge

The JDBC-ODBC Bridge, translates all JDBC calls into ODBC
calls and sends them to the local ODBC driver. The ODBC
driver must be present on the client machine.
Advantages
The JDBC-ODBC Bridge allows access to almost any database.

Type 1 drivers may be useful for those companies that have
an ODBC driver already installed on client machines.
Disadvantages
Performance is compromised since JDBC calls go through the

bridge to the local ODBC driver, then to the native database
connectivity interface.
Type 1 drivers may not be suitable for large-scale
applications.
Have the same deployment problems of traditional
applications.
14
JDBC Drivers - JDBC-ODBC

Bridge
Client Machine
Server Machine
Application
JDBC-ODBC
Bridge
ODBC Drive
Vendor DB
Library
Database
Server
JDBC Drivers - Type 4: Nativeprotocol/all-Java driver

15
JDBC driver type 4 converts JDBC calls into vendor-specific

DBMS protocol behavior so that client applications can
communicate directly with the database server.
Advantages
Type 4 JDBC drivers dont have to translate database

requests to ODBC or a native connectivity interface therefore
performance is typically good.
Theres no need to install special software on the client or
server.
Drivers can be downloaded dynamically.
Disadvantages
With type 4 drivers, the user needs a different driver for each
database.
JDBC Drivers Type 4: Native Protocol

Pure Java Driver
16
Client Machine
Server Machine
Application
JDBC
Native
Protocol Pure
Java Driver
Database
Server
JDBC DriverManager Object

17
The DriverManager typically registers

a particular Driver class by means of the
following statement
Class.forName("oracle.jdbc.OracleDriver")
This method explicitly loads the driver

class.
It does not depend on any external setup.
JDBC Connection Object

18
Connection Overview
A Connection object represents a link/pipe leading to/from a
database.
A connection session includes the SQL statements that are executed
and the results that are returned over that connection.
A single application can have one or more connections with a single
database, or it can have connections with many different databases.
Opening a Connection
The typical way to establish a connection with a database is to call
the method DriverManager.getConnection (URL,).
The DriverManager class attempts to locate a driver that can
connect to the database represented by that URL.
The DriverManager class maintains a list of registered Driver
classes, and when the method getConnection is called, it checks
with each driver in the list until it finds one that can connect to the
database specified in the URL.
JDBC Connection Object

19
Example: This fragment illustrates the loading of the

Oracle JDBC driver and the setting of a connection
using the jdbc.thin driver to the local database
instance identified as XE for user= csuperson using
password= Euclid.
//register the Oracle JDBC driver
//you may replace "localhost" by "127.0.0.1"
//or real TCP
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE",
"csuperson", "euclid");
JDBC Statement Object

20
1.
2.
The JDBC API provides three interfaces for sending

SQL statements to the database.
A Statement object is used for sending SQL statements
with no parameters.
PreparedStatement object is used for precompiled SQL
statements. These can take one or more parameters as
input arguments.
A PreparedStatement object has the potential to be more
efficient than a Statement object because it has been
precompiled and stored for future use.
3.
CallableStatement objects are used to execute SQL stored

procedures. A CallableStatement object has mechanisms for
dealing with IN, OUT and INOUT parameters.

21
Methods supporting Statement objects
createStatement methods-for a simple

SQL statement (no parameters)
prepareStatement methods-for an SQL
statement that is executed frequently
prepareCall methods-for a call to a stored
procedure

22
Creating Statement Objects

Once a connection to a particular database is established, that
connection can be used to send SQL statements. A Statement
object is created with the method createStatement, as in the
following code fragment:
conn = DriverManager.getConnection (
"jdbc:oracle:thin:@localhost:1521:XE", "csuperson",
"euclid" );
ResultSet rset = stmt.executeQuery ("select Salary, Fname
from employee");
The variable rset references a forward-only, read-only result set.

23
Executing Statements
The Statement interface provides three different methods for
executing SQL statements: executeQuery, executeUpdate, and
execute.
executeQuery is designed for statements that produce a single

result set, such as SELECT statements.
executeUpdate is used to execute INSERT, UPDATE, or DELETE

statements and also SQL DDL (Data Definition Language)
statements like CREATE TABLE, DROP TABLE, and ALTER TABLE.
The return value of executeUpdate is an integer (referred to as the

update count) that indicates the number of rows that were affected.
For statements such as CREATE TABLE or DROP TABLE, which do not
operate on rows, the return value of executeUpdate is always zero.
execute is used to execute statements that return more than one

result set, more than one update count, or a combination of the
two.

24
Statement Completion
When a connection is in auto-commit mode, the
statements being executed within it are committed
or rolled back when they are completed.
A statement is considered complete when it has

been executed and all its results have been
returned.
For the method executeQuery, which returns one result

set, the statement is completed when all the rows of the
ResultSet object have been retrieved.
For the method executeUpdate, a statement is
completed when it is executed.

25
Closing Statements
Statement objects will be closed automatically by
the Java garbage collector.
However it is recommended as good

programming practice that they be closed
explicitly when they are no longer needed.

26
Returning an Update Count

Statements invoking an action SQL query return the number of
rows affected by the maintenance operation. Some DML
operations that do not produce rows return the value zero.
String mySQL = "Update Employee set dno=1 where ssn=123456789";
int myRows = stmt.executeUpdate(mySQL);
if (myRows > 0) {
//record found update applied!
...
}
The method executeUpdate throws an SQLException if the SQL

string being
executed returns a ResultSet.

27
Using UpdateXXX Statements

Assume the DEPARTMENT table schema is as follows
< DNAME, DNUMBER, MGRSSN, MGRSTARTDATE >
The following statements locally update the rs result set that
is produced after evaluating a SQL query. The .updateRow
(or .cancelRowUpdates) method is used to commit (or rollback) the changes.
rs.absolute(4);
rs.updateString (1, "International Sales");
rs.updateLong (3, 123456789);
rs.updateRow();
The 4th row of the result set is fetched. The 1st column
(DNAME) is changed to International Sales, its 3 rd column
(MGRSSN) is changed to 123456789. All the changes to the
row are committed with the .updateRow() method.

28
Using UpdateXXX Statements
(cont.)
Assume the DEPARTMENT table schema is as follows
< DNAME, DNUMBER, MGRSSN, MGRSTARTDATE >
The following statements locally update the rs result set that
is produced after evaluating a SQL query. The .updateRow
(or .cancelRowUpdates) method is used to commit (or rollback) the changes.
rs.absolute(4);
rs.updateString ("DNAME", "International Sales");
rs.updateLong ("MGRSSN", 123456789);
rs.updateRow();
The 4th row of the result set is fetched. The 1st column
(DNAME) is changed to International Sales, its 3 rd column
(MGRSSN) is changed to 123456789. All the changes to the
row are committed with the .updateRow() method.
JDBC ResultSet Object

29
Deleting a Row
The deleteRow method deletes the current row.

Before calling deleteRow, an application must position the
cursor on the row it wants to delete.
This method affects both the current row in the result set
and the underlying row in the database.
Example. The following two lines of code remove the first
row of the ResultSet object rs and also delete the underlying
row from the database (which may or may not be the first
row of the database table).
rs.first();
rs.deleteRow();
JDBC ResultSet Object

30
Inserting Rows
New rows may be inserted into an updatble result set and into the underlying
database table using the insertRow method.
The moveToInsertRow call positions the cursor on the insert row.
updateXXX methods add column values to the insert row.
When all of the columns of the row to be inserted have been set, the
application calls the method insertRow. This method adds the insert row to
both the result set and the underlying database simultaneously.
Finally, the application needs to position the cursor on a row back in the result
set.
Example. The following code fragment demonstrates these steps for inserting
a row from an application written in the Java programming language.
rs.moveToInsertRow();
rs.updateObject (1, myListPeople);
rs.updateInt (2, 101);
rs.updateString (3, "Automation Project");
rs.insertRow();
rs.first();

31
Batch Updates
Example. An application uses a try/catch block, and if a

BatchUpdateException is thrown, it retrieves the exception's array of update
counts to discover which commands in a batch update executed successfully
before the BatchUpdateException object was thrown.

conn.setAutoCommit(false);
try {
stmt.addBatch ("INSERT INTO works_on VALUES (777888999, 10, 35)" );
int [] updateCounts = stmt.executeBatch();
}
catch(BatchUpdateException b) {
System.err.println("Update counts of successful commands: ");
int [] updateCounts = b.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i ++) {
System.err.print(updateCounts[i] +
" ");
}
System.err.println("");
}
32
JDBC PreparedStatement
Object
Using a Prepared Statement to Create Result Sets

The following code fragment creates a result set using a
PreparedStatement object.
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE","csuperson", "euclid");
String mySQL = "select hours from works_on where sex= ? And pno= ?
";
PreparedStatement pstmt = conn.prepareStatement( mySQL,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt.setFetchSize(25);
pstmt.setString(1, "F");
pstmt.setInt(2, 10);
ResultSet rset = pstmt.executeQuery();
The variable rset contains the HOURS value for the row where the SSN
33
JDBC PreparedStatement
Object
Using a Prepared Statement to Create Result Sets

select hours from works_on where sex= ? And pno= ?
1
pstmt.setInt(1, F);
pstmt.setInt(2, 10);
JDBC CallableStatements
34
CallableStatement Overview
A CallableStatement object provides a way to call

stored procedures in a standard way for all RDBMSs.
A stored procedure is stored in a database
CallableStatements accept IN, OUT and INOUT

parameters.
{ O? = call FunctionProcName ( ? ) }
U
T
I
N
35
Stored Procedures and Functions

If a database supports stored procedures, they can be
invoked using JDBC escape syntax as follows:
{ call <procedure_name> [ ( <argument-list> ) ] }
or, where a procedure returns a result parameter:
{ ? = call <procedure_name> [ ( <argument-list> )
]}
The square brackets indicate that the (argument-list)
portion is optional. Input arguments may be either literals
or parameter markers.
36
Assume the following Stored PL/SQL function is available in our Oracle

server
FUNCTION getName ( theSSN IN NUMBER ) RETURN VARCHAR2
IS
theTotal NUMBER;
theName VARCHAR2(40);
BEGIN
--Accept SSN and return full name of requested employee
select count(*) into theTotal from employee where SSN = theSSN;
if (theTotal = 0) then
RETURN('');
else
select (FName || ' ' || Lname) into theName from employee
where SSN = theSSN;
RETURN (theName);
end if;
EXCEPTION
when others then
return ('***ERROR***');
END;
DBMS
Data
+
Stored
Procedures
Mmm
Mmm
m
Mmm
m
mmm
m
Mmm
m
mmm
Mmm
m
Mmm
m
Mm m
37
Invoking stored procedure

CallableStatement cstmt = conn.prepareCall( " { ? = call
getName ( ? ) } " );
O
U
T
1
//register parameter TYPEs

cstmt.registerOutParameter (1, Types.VARCHAR);
cstmt.registerInParameter (2, java.sql.Types.INTEGER);
//set SSN value to identify employee
cstmt.setLong (2, 123456789);
cstmt.execute ();
// Retrieve OUT parameters
String fullName = cstmt.getString (1);
System.out.println("result is ==> " + fullName);
I
N
2
ResultSet Object
38
Processing a Data Row from a ResultSet

In the example below expression: rs.getInt(0) is equivalent to
rs.getInt(SSN)
ResultSet rs = stmt.executeQuery("SELECT ssn, fName, salary FROM
employee");
while (rs.next()) {
// retrieve and print the values for the current row
int mySSN = rs.getInt(SSN");
String myFName = rs.getString(fName");
float mySalary = rs.getFloat(salary");
System.out.println("ROW => " + mySSN + " " + myFName + " " +
mySalary);
}
39
ResultSet
Object
ResultSet Object
40
Determining the Number of Rows in a Result

Set
String mySQL = "SELECT * FROM employee";
ResultSet rs = stmt.executeQuery(mySQL);
rs.last();
int numberOfRows = rs.getRow();
System.out.println(" Employee table size: " + numberOfRows );
rs.beforeFirst();
while (next()) {
// do some work here with the EMPLOYEE records
. . .
}
ResultSet Objects
41
Example: ResultSet Concurrency

Connection conn = ds.getConnection(user, passwd);
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY,
ResultSet.CLOSE_CURSORS_AT_COMMIT);
This example creates a Statement object that will return

scrollable, read-only ResultSet objects that are insensitive to
updates made to the data source and that will be closed when
the transaction in which they were created is committed.
ResultSet Objects
42
Types of ResultSets
TYPE_FORWARD_ONLY
TYPE_SCROLL_INSENSITIVE
The result set is non-scrollable; its cursor moves forward only, from top to
bottom.
The result set is scrollable: Its cursor can move forward or backward and can be
moved to a particular row or to a row whose position is relative to its current
position.
Changes made to the underlying database are not reflected in the ResulSet while
it is open.
TYPE_SCROLL_SENSITIVE
The result set is scrollable; its cursor can move forward or backward and can be
moved to a particular row or to a row whose position is relative to its current
position.
The result set is sensitive to changes made to the underlying database while it is
open.
ResultSet Objects
43
Concurrency Types
A result set is by default READ_ONLY may however this
could be changed.
CONCUR_READ_ONLY
This allow users to read (unlocked) data but not to
change it.
CONCUR_UPDATABLE
Indicates a result set that can be updated
programmatically. Updatable results sets may use
write-only locks so that only one user at a time has
access to a data item.
JDBC ResultSet Class

44
Cursor Movement
next() moves the cursor forward one row. Returns true if the cursor is now
positioned on a row and false if the cursor is positioned after the last row.
previous() moves the cursor backwards one row. Returns true if the cursor
is now positioned on a row and false if the cursor is positioned before the first
row.
first() moves the cursor to the first row in the ResultSet object. Returns true
if the cursor is now positioned on the first row and false if the ResultSet object
does not contain any rows.
last() moves the cursor to the last row in the ResultSet object. Returns true
if the cursor is now positioned on the last row and false if the ResultSet object
does not contain any rows.
beforeFirst() positions the cursor at the start of the ResultSet object, before
the first row. If the ResultSet object does not contain any rows, this method has
no effect.
afterLast() positions the cursor at the end of the ResultSet object, after
the last row. If the ResultSet object does not contain any rows, this method has no
effect.
relative(int rows) moves the cursor relative to its current position.
absolute(int row) positions the cursor on the row-th row of the ResultSet
object.
ResultSet Objects
45
Positioned Updates and Deletes

JDBC drivers or DBMSs that do not support performing updates via the
ResultSet interface may support positioned updates and deletes via
SQL commands.
EXAMPLE
Statement stmt1 = conn.createStatement();
stmt1.setCursorName(CURSOR1);
ResultSet rs = stmt1.executeQuery(select * from employee for update of salary);
// move to the row we want to update
while ( ... ) {
rs.next()
}
String cursorName = rs.getCursorName();
Statement stmt2 = conn.createStatement();
// now update the row
String mySQL = "update employee set salary = salary + 0.10 where current of +
cursorName;
int updateCount = stmt2.executeUpdate(mySQL);
ResultSet: Updating a Row

46
Updating a row in a ResultSet object is a two-phase process.

1. The new value for each column being updated is set, and
2. The change is applied to the row.
The row in the underlying data source is not updated until the
second phase is completed.
Example
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet rs = stmt.executeQuery(select * from employee where ssn =
123456789) ;
rs.next();
//replace (old) Last name with new value: Macarena
rs.updateString(Lname, Macarena);
rs.updateRow();
ResultSet: Deleting a Row

47
A row in a ResultSet object can be deleted using

the method deleteRow.
EXAMPLE
//remove the fourth row of the ResultSet rs
rs.absolute(4);
rs.deleteRow();
ResultSet: Inserting a Row

48
The steps to insert a new row are:

1. Move the cursor to the insert row
2. Set the values for the columns of the row using the ResultSet interface
update methods
3. Insert the new row into the ResultSet object
EXAMPLE
// assign employee 123456789 to project No. 10 a total of 2.5 hours/week
// select all the columns from the WORKS_ON(ESSN, PNO, HOURS) table
ResultSet rs = stmt.executeQuery(select * from Works_On );
rs.moveToInsertRow();
// set values for each column
rs.updateString(1, 123456789);
rs.updateInt(2, 10);
rs.updateFloat(3, 2.5);
// insert the row
rs.insertRow();
// move the cursor back to its position in the result set all done!
rs.moveToCurrentRow();
NULL values
49
NULL Result Values

A JDBC NULL retrieved by one of the ResultSet.getXXX methods is
converted to either null, 0, or false, depending on the type of the value.
Returned Values
null-for those getXXX methods that return objects in the Java programming
language (getString, getBigDecimal, getBytes, getDate, getTime, getTime-stamp,
getAsciiStream, getCharacterStream, getUnicodeStream, getBinary-Stream,
getObject, getArray, getBlob, getClob, and getRef)
0 (zero)-for getByte, getShort, getInt, getLong, getFloat, and getDouble
false-for getBoolean
Example. If the method getDouble returns 0 from a column that allows

null values an application could call the method wasNull to determine if
the original value was NULL.
double c = rs.getDouble("Commission");
boolean b = rs.wasNull();
The method wasNull checks only the last value retrieved. If b is true, the
value stored in the third column of the current row of rs is JDBC NULL.
Transaction Processing
50
Committing Changes
By default, data manipulation language (DML) operations are
committed
automatically as soon as they are run. This is known as the
(Oracle) auto-commit mode. However, you can disable autocommit mode with the following method call on the Connection
object:
conn.setAutoCommit(false);
If you disable the auto-commit mode, then you must manually
commit or roll back changes with the appropriate method call on
the Connection object:
conn.commit();
or:
conn.rollback();
Note: A COMMIT or ROLLBACK operation affects all DML
statements run since the last COMMIT or ROLLBACK.
JDBC - Support Classes

51
If you are using Jdeveloper 11g make sure

you include the Oracle JDBC Library to your
application.
If you are using Eclipse (or other Java IDE)

the following JAR files must be needed to the
app.
C:\JDeveloper11G\jlib\dms.jar
C:\JDeveloper11G\jlib\ojdl.jar
C:\JDeveloper11G\jlib\orai18n.jar
C:\JDeveloper11G\jdbc\lib\ojdbc5dms.jar
JDBC - Support Classes

52
JDeveloper11g from Oracle
http://www.oracle.com
SUN - JDBC and Database Page
http://java.sun.com/javase/technologies/database/
index.jsp
53
Eclipse IDE JDBC Support

Classes
Jdeveloper 11g JDBC Support

Classes
54
Example1. Using a Local ODBC

DataSource
55
package JavaJDBCDemo1;
Exampe1.
public class JavaJDBCDemo1 {
Use an ODBC-JDBC Bridge to reach
public static void main(String[] args)
Oracle and create, populate, query,
{
and drop a table.
try {
//create a jdbc to odbc bridge
Assuming ODBC data source already
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
set.
import java.sql.*;
//there is a local ODBC data source already defined
//the Oracle instance
String database =
under the name: myOracleODBC to access
using credentials: user=csuperson, password=euclid
"jdbc:odbc:myOracleODBC";
Connection con = DriverManager.getConnection( database ,"csuPerson","euclid");

Statement s = con.createStatement();
//Using the Statement class: create a table, populate, query and drop it
s.execute("create table myTable ( myCol1 integer, myCol2 varchar2(20) )");
s.execute("insert into myTable values(11, 'AAA')");
// insert data into the table
s.execute("insert into myTable values(22, 'BBB')");
// insert data into the table
s.execute("select * from myTable");

ResultSet rs = s.getResultSet();
// select data from the table

// get ResultSet from query

DataSource cont.
56
//if rs == null, then there is no ResultSet to view
if (rs != null)
while ( rs.next() ) // this loop will step through our data row-by-row
{
//alternatively use: rs.getInt(1) or rs.getString(2)
System.out.println(" myCol1: " +
" myCol2: " +
}//end while
s.execute("drop table myTable");
s.close();
con.close();
} //end if
catch (Exception e) {
System.out.println("Error: " + e);
}
}//end main
}//end class
rs.getInt("myCol1") +
rs.getString("myCol2"));
Exampe1.
Use an ODBC-JDBC
Bridge to reach
Oracle and create,
populate, query,
and drop a table

DataSource cont.
57
Exampe1.
Use an ODBC-JDBC Bridge to reach
Oracle and create, populate, query,
and drop a table
58
Example1B. Using Thin JDBC

Layer
Exampe1.
Use an ODBC-JDBC Bridge to reach Oracle
and create, populate, query, and drop a
table
We will modify the previous Example1 to by-pass the local ODBC data
source and directly go to the database server using the Thin JDBC driver.
This approach should be more efficient. You need to make the following
code changes:
Class.forName ("oracle.jdbc.OracleDriver");
String database = "jdbc:oracle:thin:@localhost:1521:XE";
Connection con =
DriverManager.getConnection( database ,"csuPerson","euclid");
You must add the Oracle JDBC driver to the applications path. For
example, in this case we added all the .jar files from the folder
c:\Jdeveloper\jdbc\lib
59
Example2. Using JDBC Thin

Driver (1/3)
Example2.
package JavaAccessDB2Thin;
import java.sql.*;
Use the JDBC Thin Driver to create

a ResultSet holding the SSN and
name of each employee in the
COMPANY
EMPLOYEE
table.database.
It uses the JDBC THIN driver
// Example shows how to list the names from the

// you must add the Oracle.jdbc library to the applications path.
class JavaAccessDB2ThinDriver
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
Connection conn = null;
System.out.println("Loading Oracle thin driver");
// Load the Oracle JDBC driver
System.out.println("Driver manager created");
//-----------------------------------------------------------------------// Connect to the database.

// You must put a database name after the @ sign in the connection URL.
// You can use either the fully specified SQL*net syntax or a short cut
// syntax as <host>:<port>:<sid>. The example uses the short cut syntax.
//------------------------------------------------------------------------
60

Driver (2/3)
Example2.
Use the JDBC Thin Driver to create a ResultSet
holding the SSN and name of each employee in the
COMPANY database.
try {
conn =
DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:XE",
if (conn != null) {
System.out.println("Connection created for CSUPERSON/EUCLID");
} else {
System.out.println("***No connection was created");
}
// Create a Statement
Statement stmt = conn.createStatement ();
System.out.println("***Statement created");
// Select the FNAME column from the EMPLOYEE table
ResultSet rset = stmt.executeQuery (
" select (FNAME || ' ' || LNAME) as FullName " +
"
from EMPLOYEE " +
" where SEX in ('F', 'M') ");
System.out.println("***OK. Query executed");
// Iterate through the result and print the employee names
while (rset.next ()) {
System.out.println (Full name:
" + rset.getString (1));
}
}
61

Driver (3/3)
Example2.
Use the JDBC Thin Driver to create a ResultSet
holding the SSN and name of each employee in the
COMPANY database.
catch (SQLException e) {
System.out.println("Test threw a " + e.getClass() + "\n with message: " +
e.getMessage() + " : Error code (" +
e.getErrorCode() + ")" );
}
// Clean up connection is there was one
finally
{
if(conn != null) {
conn.close();
System.out.println("***Connection closed");
}
else {
System.out.println("***There was no connection");
}
}
}
}
//TRY THIS:
//connecting to a remote Oracle11g (ORCL) server located in SANCHO.CSUOHIO.EDU
conn =
DriverManager.getConnection( "jdbc:oracle:thin:@sancho.csuohio.edu:1521:ORCL",
Connection Strings
62
Connecting to a Remote ORACLE Server

//connecting to a remote Oracle11g (ORCL) server located
in SANCHO.CSUOHIO.EDU
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@sancho.csuohio.edu:1521:ORCL",
Connection Strings
63
Connecting to a Remote MS-SQL2005 Server accepting Windows Authentication and Trusted

SQL connections
// Load the Microsoft JDBC driver for SQL2005 Server
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//Connecting to local version of SQL2005 running in local computer
// String connectionUrl = "jdbc:sqlserver://localhost:1433; "user=csuperson2;"
"password=euclid2; +
//
"trusted connection=false;" + "databaseName=Company;";
// connecting to remote SQL2005 Server called SANCHO.CSUOHIO.EDU

String connectionUrl = "jdbc:sqlserver://sancho.csuohio.edu:1433;"
+ "user=csuperson2;"
+ "password=euclid2;"
+ "trusted connection=false;"
+ "databaseName=Company;";
Connection con = DriverManager.getConnection(connectionUrl);
64
Example3. Calling a
PL/SQLFunction
Example3.
Use1/5
the JDBC Thin Driver to invoke a PL/SQL function
that accepts a SSN value and returns the employees
full name
The following PL/SQL function accepts a Social Sec. Number, if

a match is found it returns the employees full name.
FUNCTION getName ( theSSN
IS
theTotal NUMBER;
theName VARCHAR2(40);
IN
NUMBER ) RETURN VARCHAR2
BEGIN
select count(*) into theTotal from employee
where SSN = theSSN;
if (theTotal = 0) then
RETURN('');
else
select (FName || ' ' || Lname) into theName
from employee
where SSN = theSSN;
RETURN (theName);
end if;
EXCEPTION
when others then
return ('***ERROR***');
END;
65
Example3. Calling a
PL/SQLFunction
package JavaAccessDB3StoredProc;
Example3.
Use2/5
full name
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class JavaAccessDB3StoredProc
{
public static void main(String[] args) throws SQLException, ClassNotFoundException
{
/* ------------------------------------------------------------------------Calling a stored procedure/function from Java.
The query string can be in Oracle format or standard JDBC callable syntax.
1. If you are using the Oracle syntax, the query string is:
BEGIN ? := getName(?); END;
2. If you are using the JDBC syntax, the query string is:
{ call ? := getName(?) }
OUT parameter must be registered to be of type OracleTypes.CURSOR.
You must use the method: getObject() to retrieve the result set.
---------------------------------------------------------------------------*/
66
Example3. Calling a
PL/SQLFunction
Example3.
Use3/5
full name

System.out.println("***Attempting to load Oracle thin driver");
System.out.println("***Driver manager created");
try
{
conn =
DriverManager.getConnection
("jdbc:oracle:thin:@localhost:1521:XE,"csuperson","euclid");
if (conn != null) {
System.out.println("***Connection created for CSUPERSON/EUCLID ***");
} else {
}
String myQuery = " BEGIN ? := getName(?); END; ";
CallableStatement stmt = conn.prepareCall(myQuery);
// register the type of the OUT parameter using an Oracle specific type
//Example: stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.registerOutParameter(1, OracleTypes.VARCHAR);
67
Example3. Calling a
PL/SQLFunction
Example3.
Use
4/5the JDBC Thin Driver to invoke a PL/SQL function
full name
// set the IN parameter

int SSN = 123456789;
stmt.setInt(2, SSN);
// execute statement and retrieve returned string
System.out.println("***Ready to call procedure.");
stmt.execute();
String empName = (String) stmt.getObject(1);
// print the results
System.out.println("***Emp name: " + empName);
} //end try
68
Example3. Calling a
PL/SQLFunction
Example3.
Use
5/5the JDBC Thin Driver to invoke a PL/SQL function
full name
System.out.println("Test threw a " + e.getClass() +
"\n with message: " +
}
finally
{
if(conn != null) {
conn.close();
}
else {
}
}
}
}
Example4. Call PL/SQL Receive a

ResultSet 0/5
69
Example4.
Use the JDBC Thin Driver to invoke a PL/SQL stored
procedure. The proc. accepts a dept nunmber and returns
a list of employees on that dept.
This PL/SQL procedure accepts a Dept. No. and returns an

Oracle cursor holding a list of employees :<SSN,
LNAME,DNO,SALARY,BDATE>
function getPeopleByDept ( theDnoNo in number ) return
Sys_RefCursor as
PeopleCursor sys_refCursor;
begin
open PeopleCursor for
select SSN, Lname, Dno, Salary, Bdate from employee
where (dno = theDnoNo);
return PeopleCursor;
end getpeoplebydept;

ResultSet 1/5
70
Example4.
package JavaAccessDB4StoredProcCursor;
import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
public class JavaAccessDB4StoredProcCursor
{
public static void main(String[] args) throws SQLException,
ClassNotFoundException
{
/* --------------------------------------------------------------------------Calling a stored procedure/function from Java.
The query string can be in Oracle format or standard JDBC callable syntax.
1. If you are using the Oracle syntax, the query string is:
BEGIN ? := getName(?); END;
2. If you are using the JDBC syntax, the query string is:
{ call ? := getName(?) }
OUT parameter must be registered to be of type OracleTypes.CURSOR.
You must use the method: getObject() to retrieve the result set.
-----------------------------------------------------------------------------*/

ResultSet 1/5
Example4.
71

System.out.println ("***Attempting to load Oracle thin driver");
System.out.println("***Driver manager created");
try
{
conn =
DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:XE",
if (conn != null) {
System.out.println("Connection created for CSUPERSON/EUCLID");
} else {
}
String myQuery = " BEGIN ? := getPeopleByDept(?); END; ";
CallableStatement stmt = conn.prepareCall(myQuery);

ResultSet 1/5
Example4.
72
// register type of the out param with Oracle specific type

stmt.registerOutParameter(1, OracleTypes.CURSOR);
// set the in param
int theDeptNo = 4;
stmt.setInt(2, theDeptNo);
// execute and retrieve the result set
System.out.println("***Ready to call procedure.");
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);
// print the results: <SSN, Lname, Dno, Salary, Bdate>
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" +
rs.getString(2) + "\t" +
rs.getInt(3) + "\t" +
rs.getFloat(4) + "\t" +
rs.getDate(5).toString());
}

ResultSet 1/5
Example4.
73
System.out.println("Test threw a " + e.getClass() +
"\n with message: " +
}

finally
{
if(conn != null) {
conn.close();
}
else {
}
}
}
}
74
Adding JDBC drivers to your

program
75
Example5: Updatable
ResultSet
package OracleJDBCDemo5.client;
import java.sql.*;
import oracle.jdbc.*;
//update salary (increase by $1)
public class OracleJDBCDemo5 {
public static void main(String[] args) throws SQLException,
ClassNotFoundException {
System.out.print("Connecting to the database...");
try {
System.out.println("Driver manager created");
//you may replace "localhost" by "127.0.0.1" (or real TCP
address)
conn =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:X
E",
"csuperson",
"euclid");
if (conn != null) {
System.out.println("Connection created for
CSUPERSON/EUCLID");
} else {
return;
}
//Create a statement returning data for UPDATE
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE,
ResultSet.CLOSE_CURSORS_AT_COMMIT);
System.out.println("Updateble ResultSet was created");
ResultSet rset = stmt.executeQuery("select Salary, Fname from
employee");
while(rset.next()){
//modify the employee's salary (add $1 )
System.out.println("Name ==> " +
rset.getString("Fname"));
double oldSalary = rset.getDouble("Salary");
System.out.println("Old salary ==> " + oldSalary);
double newSalary = oldSalary + 1;
rset.updateDouble("Salary", newSalary);
rset.updateRow();
System.out.println("New salary ==> " + newSalary);
}
// close the result set, the statement and connect
rset.close();
stmt.close();
conn.close();
System.out.println("Adios.");
} //end try
System.out.println(e.getMessage());
} //end catch
} //end main
} //end class
76
Example6. Prepared
Statement
package JavaAccessDB5PreparedStmt;
while (rset.next()){
import java.sql.*;
System.out.println(rset.getInt("dno") + " " +
public class JavaAccessDB5PreparedStmt {
rset.getString("fName"));
//------------------------------------------------------------------//Using a Prepared Statement to retrieve female employees
}//end whileloop
//------------------------------------------------------------------public static void main(String[] args) {

try {
}
catch (Exception e) {
String url = "jdbc:oracle:thin:@sancho.csuohio.edu:1521:ORCL";
String user = "CSUPERSON";
System.out.println(e);
}
String pwd = "EUCLID";

Connection con = DriverManager.getConnection(url, user, pwd);
String mySql = "select * from employee where sex = ? and dno
= ?";
PreparedStatement stmt = con.prepareStatement(mySql,
ResultSet.TYPE_SCROLL_SENSITIVE,
//set gender value and department number
stmt.setString(1, "F");
stmt.setInt(2, 4);
stmt.setFetchSize(10);
ResultSet rset = stmt.executeQuery();
}//end main
}//end class
References
77
JDBC 3.0 SUN Web Page

http://java.sun.com/javase/6/docs/technotes/guides/jdbc/
Basic Tutorial
http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html
Advanced Tutorial
http://java.sun.com/developer/Books/JDBCTutorial/index.html
Rowset Tutorial
http://java.sun.com/j2se/1.5/pdf/jdbc-rowset-tutorial-1.5.0.pdf
Getting Started with the JDBC API

http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/GettingStartedTOC.fm.html
JDBC 4.0 Specification JSR 22, by Lance Andersen, Specification Lead. Sun
Microsystems. November 7, 2006 November 2006 Final v1.0
JDBCtm API Tutorial and Reference, Second Edition: Universal Data Access
for the Javatm 2 Platform, published by Addison Wesley as part of the Java
series, ISBN 0-201-43328-1.
Wikipedia.com Page: JDBC, Consulted on 1-March-2008.
78
Appendix A.
Creating a Trusted User for Logging on a
SQL-Server
1. Log on the SQL-Server
2. Click on Security > Logins
3. Right-click on Logins, select New login
79
Appendix A.
SQL-Server
4.
Enter Login name

(csuperson)
5.
Select option: SQL Server

Authentication
6.
Enter (confirm) Password

(euclid)
7.
Uncheck option Enforce

password policy
8.
Choose Default Database:

Company
9.
Select (left top pane) option

User Mapping
80
Appendix A.
SQL-Server
10. Under column Database

check option for Company
database
11. Under Database Roles
check the following options:
db datareader,
db datawriter,
db ddladmin,
db public.
12. Click the OK button.
13. The user: csuperson / euclid
can now log into the SQLserver as a trusted user.
14. Use the credentials provided
by this user / password to
setup a JDBC connection
object.
81
Appendix A.
SQL-Server
15. Allow multiple login modes on

the SQL server. Right-click on
the topmost database icon
(identifying the current
connection. In this example
CARIBE (SQL )
16. Select Properties
82
Appendix A.
SQL-Server
17. Under Select a Page choose

the entry labeled: Security.
18. Make sure to select the
option labeled: SQL Server
and Windows
Authentication mode.
17. Click the OK button.
18. You are ready to try Windows
logon as well as Trusted
User mode.

Oracle Applications Using Java & JDBC

Uploaded by

Copyright:

Available Formats

Oracle Applications Using Java & JDBC

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Oracle Applications Using Java & JDBC

Uploaded by

Copyright:

Available Formats

ORACLE

JDBC (Java database connectivity) is an API for the

It provides methods for querying and updating data in

JDBC is oriented towards relational databases.

It was originally included in the Java 2 Platform,

What is JDBC ? Components

JDBC connections support creating and executing statements.

Statements may be action commands such as: SQL CREATE,

Stored procedures may also be invoked through a statement.

Statements are one of the following types:

Statement the statement is sent to the database server each and

PreparedStatement the statement is cached and then the execution

CallableStatement used for executing stored procedures on the

What is JDBC ? Components

Update statements such as INSERT, UPDATE and

Query statements return a JDBC row ResultSet.

The row result set is used to traverse the result set.

Individual columns in a row are retrieved either by name or by

There may be any number of rows in the result set.

What is JDBC ? Components

A ResultSet is a Java object that contains the rows

A Brief Example How do JDBC apps

A JDBC based application makes possible

Establish a connection with a data source

A Brief Example How JDBC apps

Typical Code Fragment

Statement stmt = con.createStatement();

Download JDBC Driver

(MS SQL Server) http://msdn2.microsoft.com/en-us/data/aa937724.aspx

Setting Up The SQL2005 JDBC

CLASSPATH =.;C:\Program Files\Microsoft SQL Server

Setting Up The SQL2005 JDBC

When the driver is loaded, you can establish a connection by using a

There are four types of JDBC drivers. Each type

Type 1: JDBC-ODBC Bridge

JDBC Drivers. Type1: JDBCODBC Bridge

The JDBC-ODBC Bridge allows access to almost any database.

Performance is compromised since JDBC calls go through the

JDBC Drivers - JDBC-ODBC

JDBC Drivers - Type 4: Nativeprotocol/all-Java driver

JDBC driver type 4 converts JDBC calls into vendor-specific

Type 4 JDBC drivers dont have to translate database

JDBC Drivers Type 4: Native Protocol

JDBC DriverManager Object

The DriverManager typically registers

This method explicitly loads the driver

JDBC Connection Object

JDBC Connection Object

Example: This fragment illustrates the loading of the

JDBC Statement Object

The JDBC API provides three interfaces for sending

CallableStatement objects are used to execute SQL stored

JDBC Statement Object

Methods supporting Statement objects

createStatement methods-for a simple

JDBC Statement Object

Creating Statement Objects

JDBC Statement Object