Driver Manager

loads Database drivers and manages connections between appliation and driver.

Driver

translates API calls into database specific calls.

Connection

session between application and database

Statement

SQL statement

Metadata

info about returned data, database and driver.

ResultSet

retured data from an SQL querry....returned in form of a table (rows and columns)

The Process (see below for example)

1. Import classes
2. Use DriverManager to load the JDBC driver
• Class.forName("X.jdbc.odbc.Z");
• X.jdbc.odbc.Z is a class that must be on your system and registered by your Operating System's ODBC manager
3. Create Connection object to specific database
• String s = "jdbc:odbc:dbname"
• Connection c = DriverManager.getConnection(s)
4. Create Statement object to make SQL calls
5. Execute query using Statement from 4.
• ResultSet r = statement.executeQuery("SELECT * FROM table0"); //just an example
6. Results are returned in a ResultSet object (e.g. r in 5)
7. Process results as desired
8. Repeat steps 4-7
9. Close ResultSet
10. Close Statement
11. Close Connection

1) Have your database administrator load the Java JDBC driver for the database you are using.(you will use OCI for a Java Application and Thin for an Applet...see above)

2) You  with your DBA (database administrator) set up your database table(s) and enter in the data as desired (note you can also if you wish to have data entry take place through an interface to your Java program).

3)  Now write the code.   Steps in the code (SEE EXAMPLE):

1)Import java.sql.*;
2)Load JDBC Driver using Class.forName() method.
3)Connect to Driver using DriverManager.getConnection(...);

If one of the drivers you loaded in step 2 recognizes the JDBC URL supplied to the method DriverManager.getConnection , that driver will establish a connection to the DBMS specified in the JDBC URL. The DriverManager class, true to its name, manages all of the details of establishing the connection for you behind the scenes. Unless you are writing a driver, you will probably never use any of the methods in the interface Driver , and the only DriverManager method you really need to know is DriverManager.getConnection .

4)Create an instance of the Statement class using connection's createStatement()
method
5) Using this instance of Statement,  execute various SQL querries using executeQuerry(.)  method.  Results are returned as a table, which is stored in an instance of the class ResultSet.
6) Process the information in the instance of ResultSet as desired.   See methods such as next(),   getString(.)

Examples Application Connection

Using OCI-Driver Connecting to Oracle DB (2-tier)

Version 2: SAME as above but, different connect URL

Using Thin Driver Connecting to Oracle DB (2-tier)

 

Examples Applet Connection

Using THIN Driver Connecting to Oracle DB (2-tier) (will only work if DB on same server as web-server)

Version 2: SAME but With Security Privilege Granting

see it working

 

Example Servlet Connection

code and results

IMPORTANT: you must add the get the appropriate Oracle jdbc jar file for the version of oracle from Oracle.com and upload the jar file to your WEB-INF/lib and configure your project properties to include it as a jar file under libraries in Eclipse (for Oracle 11.0.2.* ojdbc6.jar)

• Once you compile your code, you will need to run it and include in the classpath any additional classes that are used in the creation of Your databases JDBC driver. 
• EXAMPLE:   In our Oracle Database case,  we have a file called classes111.zip (OCI DRIVER) OR classes111b.zip (THIN DRIVER) that comes with the installation of Oracle and contains the Oracle driver class called oracle.jdbc.driver.OracleDriver.

 
 
FOR APPLICATIONS USING OCI DRIVER (if use THIN DRIVER substitute classes111b.zip above for classes111.zip file)

PC Case   Option 1:  use the classpath option to the java virtual machine java: if you already have a classpath environmental variable named CLASSPATH ....AND you have copied the classes111.zip to the same directory as your code. java -classpath %classpath%;classes111.zip   YourApplicationClass   if you do not have a classpath environmental variable yet   AND PPP is the path to where your classes111.zip resides. java -classpath .;PPP/classes111.zip   YourApplicationClass Option 2:  edit your autoexec.bat to have or append to current environment variable CLASSPATH such that it includes: PPP/classes111.zip     (where PPP is entire path up to classes111.zip file) Note: if you do not have a classpath environment variable yet (you should), also make sure that you include the current directory symbol .   and also include any other paths to the standard JDK,etc.

Unix Case Option 1:  use the classpath option to the java virtual machine java: java -classpath $;/apps/oracle/OraHome1/jdbc/lib/classes111.zip   YourApplicationClass  (note the /apps/oracle/OraHome1/jdbc/lib/   is the path where the classes111.zip file is located...this may change on your system...see your Oracle Database Administrator).  OR Copy the file to some directory you wish and put the entire path in the line above. Option 2: set the environment variables temporarily in the Unix shell you will be running the program from.  Subsequently, execute the virtual machine. setenv CLASSPATH   .:/apps/oracle/OraHome1/jdbc/lib/classes111.zip  (note the /apps/oracle/OraHome1/jdbc/lib/   is the path where the classes111.zip file is located...this may change on your system...see your Oracle Database Administrator). java YourApplicationClass Option 3: edit your .cshrc file to have or append to current environment variable CLASSPATH as follows.  Then relogin and execute the java virtual mahcine. Edit your .cshrc file to include: setenv CLASSPATH .:/apps/oracle/OraHome1/jdbc/lib/classes111.zip (note the /apps/oracle/OraHome1/jdbc/lib/   is the path where the classes111.zip file is located...this may change on your system...see your Oracle Database Administrator). Relogin to your account to activate the changes in your .cshrc file Execute the java virtual machine: java YourAppliationClass

   .


FOR APPLETS USING THIN DRIVER
 

It is somewhat simpler for us in the case of applets.  It does not differ for PCs or Unix machines.   Here is what you need to do: NOTE: Web-server and Database server must be the SAME!!!!!!!!! Security restriction, unless you request permission from user. OPTION 1 place classses111b.zip in the same directory as your Applet code on your web-server. Inside of your HTML code containing the Applet, myApplet.class, this is what the code will look like: <applet codebase="." archive="classes111b.zip"  code="myApplet" width=500 height=200> </applet>   OPTION 2 unzip the classes111b.zip in the same directory as your Applet on your local machine. zip up everything into a file called f.zip and transfer this up the f.zip class to the web-server Inside of your HTML code containing the Applet, myApplet.class, this is what the code will look like: <applet codebase="." archive="f.zip"  code="myApplet" width=500 height=200>         </applet>

 

OPTION 1: DriverManager.getConnection ("jdbc:oracle:oci8:@labdb", "userid", "password");

• URL = jdbc:oracle:oci8:@labdb

  labdb    represents the identity of the DBMS stored in a client-side configuration file.

  this can only be used on Applications that have client-side configuration files setup

  
  

OPTION 2: DriverManager.getConnection ("jdbc:oracle:oci8:@host : port : sid", "userid", "password");

• URL = jdbc:oracle:thin:@host:port:sid

  host:port:sid     represents the identity of the DBMS.

  
  should be used for Applications or Applets using Thin Driver where do not wish to have client-side configuration files setup.