JDBC overview — “thin” driver — OCI driver — connecting to Oracle — a generic database access class — insert, update and select examples — exercises.
The Java DataBase Connectivity API is a set of classes allowing a straightforward and vendor-neutral access to database management systems. We will review the basic features of JDBC and their use with Oracle.
Accessibility. JDBC provides a set of high-level classes that enable anyone acquainted with SQL and Java to write database applications. Considerations like networking and database protocols are transparent to the application programmer. These are handled by classes within JDBC drivers.
To achieve this, JDBC specifications are agreed upon within the Java community--and each database vendor is then left to implement these specification to work with their product.
Thin & OCI Drivers
Oracle provides two main types of drivers.
The OCI driver.
The OCI (type 2) driver consists of java wrappers to the low-level Oracle Call Interface (OCI) libraries used by utilities like SQL*Plus to access the database server. The OCI driver offers potentially better performance that the thin driver. It however requires the OCI libraries to be installed on the local machine.
Also referred to as type 4 driver, the thin driver is a pure Java implementation of Oracle's networking protocol (Net8). Being self-contained, it may be used on any machine with--or without Oracle installed--or even distributed with application classes in an applet.
We will review below how to access Oracle using both types of driver. First, we use the thin driver to connect to Oracle and execute an update statement. Note that the example below is only intended as an illustration. Try to understand the code. You will have opportunities for ``hands-on''
practice in the next section.
import java.io.*;
import java.sql.*;
public class OraThin {
public static void main(String[] args) {
try {
Connection con=null;
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection(
"jdbc:oracle:thin:@machine_name:1521:database_name",
"scott",
"tiger");
Statement s=con.createStatement();
s.execute("
INSERT INTO BOOKS VALUES
(
'A Tale of Two Cities',
'William Shakespeare',
4567891231,
'5-JAN-1962'
)
");
s.close();
con.close();
} catch(Exception e){e.printStackTrace();}
}
}
- The package java.sql containing the JDBC classes is imported.
- The class OracleDriver, in the package oracle.jdbc.driver is dynamically loaded into the Java runtime using Class.forName(...).
- A connection is requested to the database corresponding to the connection URL with the statement DriverManager.getConnection(...).
- scott/tiger is an Oracle user/password.
- We use instances of the Connection and Statement classes to perform a database operation.
- The resources hold by these are released using their close() method.
If we want use the OCI driver, the JDBC connection URL above needs to be altered. The only element now required is the service name of the database: database_name. The remainder is resolved using the local Oracle configuration file.
DriverManager.getConnection(
"jdbc:oracle:oci8:@database_name", "scott","tiger");
In this section we present the DbObject class that allows to connect to an Oracle database, and execute SQL statements. You will need to download the
file DbObjectClasses.jar into your home area and run the command (in UNIX or in a DOS box):
jar xvf DbObjectClasses.jar
This will extract the following files from the archive:
- DbObject.java and DbObject.class, the java source and class for our database access class
- DbObject.conf, the configuration file of DbObject
- DbTest.java and DbTest.class, the java source and class file for the test class DbTest.
Next you need to edit, change and save DbObject.conf so that:
DbObject.User=<your user name> and DbObject.Password=<your Oracle password>. By default the thin driver is used. You may want to experiment with the OCI driver by changing DbObject.DriverType to oci8.
You should now run DbTest using the command:
java DbTest
The programme will perform INSERT, UPDATE and SELECT statements. You should now read carefully both java source files.
- Write a DbTest2 class that inserts data in the book_reviews table.
- Write a DbTest3 class that displays the number of reviews for each book.
You can copy & paste the following URIs (note that you will need a username/password
to access Oracle's web site. You can use database@example.com/database):
JavaDoc documentation for the java.sql package:
//java.sun.com/j2se/1.3/docs/api/java/sql/package-summary.html
General JDBC reference:
Bruce Eckel. Thinking in Java, Prentice Hall, 2nd. ed., 2000, chap. 15. downloadable from:
//w2.syronex.com/jmr/eckel/
Oracle10i JDBC Developer's Guide and Reference:
//download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/toc.htm
|
|
