Java + JDBC + Oracle

(viewing version)

Java + JDBC + Oracle

Java:   An object-oriented procedural programming language

Oracle:   A table-based declarative interface to data (SQL)

JDBC:   A bridge between these two worlds ... Java + persistent data


Alternatives:   ObjectStore,   PJama,   J++ & ODBC,   ...

JDBC Architecture


[Diagram:Pic/architecture]

Java/DB Interaction

import java.sql.*;

...

Statement s = ...

// Run SQL query to fetch data
ResultSet r = s.executeQuery("select name from Employees");

// Iterate through result set
while (r.next())
    System.out.println(r.getString("name"));
...

... Java/DB Interaction

import java.sql.*;

...

Statement s = ...

// Generate update command
int    lowPay = 20000;
String sackWorkers =
       "delete Employees where salary < "+lowPay;

// Run SQL update to modify data
int nr = s.executeUpdate(sackWorkers);

// Indicate update effect
System.out.println(nr + " employees laid off");
...

Getting Connected

  1. Register driver 

       DriverManager.registerDriver(
                 new oracle.jdbc.driver.OracleDriver);

    or 

       Class.forName("oracle.jdbc.driver.OracleDriver");

  2. Make connection through driver 

       Connection conn =
      DriverManager.getConnection(URL, Username, Password);

... Getting Connected

Username and Username are the ones for your Oracle account.

The URL specifies:

For the CSE environment: 

   jdbc:oracle:thin:@dingo:1521:cse1

or 

   jdbc:oracle:oci7:@(DESCRIPTION =
      (ADDRESS_LIST=(PROTOCOL=TCP)(Host=dingo)(Port=1521))
      (CONNECT_DATA=(SID=cse1)))

Making a Statement

Each database operation has to be done via a Statement.

Each Statement is attached to a Connection. 

   Statement s = conn.createStatement();

Multiple Statements can be handled by a Connection.

Transactions are also handled by the Connection. 

   conn.commit();

All transactions are committed and all Statements are closed when a Connection closes.

Environment Settings

For JDBC Thin Driver: 

   export CLASSPATH=/home/oracle/jdbc/lib/classes111.zip:.

For JDBC OCI7 Driver, also need: 

   export LD_LIBRARY_PATH=/home/oracle/jdbc/lib
   export NLS_LANG=AMERICAN_AMERICA.US7ASCII


Reminder: use setenv form csh or tcsh.

Further Information