Untitled Document

Java and JDBC

You can use Java to access a database over the Internet. JDBC, which stands for Java DataBase Connectivity, is the package implemented by Sun as part of JDK to connect to databases.

Reading Database SQL JDBC

Oracle Database

Reading

See suggested reading on outline.

Databases

Most of today's modern databases are what are called relational databases. Basically, this means that data is organized in tables. Each table consists of a set of columns, each column representing some item of information. For example, below is a table representing information about software distributors.

Name Street Address City State Distribute

Butch Grewe 100 Campus Dr. Seaside CA Microsoft, Adobe

Doug MacIntire 100 6th Street. NY NY Enterprise Suite

They keys here are "Name, Street Address, City, State, Distribute"

We can search a database by searching against certain key values which represent values for the entries we are interested in retrieving. For example, we may want to find all the entries in the Database that have the key State = CA. In this case, we would retrieve the Butch Grewe row. Similarly if we searched with the key State=FL we would retrieve no items. To search a database you must use the language the Database understands. Most databases understand a version of a scripting language called SQL.

SQL: Language to access Databases

In order to alter, insert or retrieve data from a database, you will use a specialized Database language. Most modern databases will allow you to do this via SQL or a version of SQL. SQL stands for Structured Query Language and is a standard for accessing and updating data in a database (once you are connected to it....you will connect either via a database tool or as we will do via Java&JDBC see below ).

SQL is a conceptually easy language to understand. We will not be teaching SQL in this class...but, only using simple commands to access it. Note below table is the name of the table you are accessing.

(using table above)

SOME SIMPLE SQL COMMANDS

SQL command Meaning

Select * from table select all the keys/fields from the table

Select Distribute from table select only the field Distribute from the table

Select * from table
where Name is 'Butch Grewe' Select all keys/fields from table where the key/field Name = Butch Grewe

Insert into table
values ('James Bond', '1 Eiffel Tower', 'Paris', 'France','Spy Software') Insert into the table, the data entry having the following key/field entries in order:
Name = James Bond
Street Address = 1 Eiffel Tower
City = Paris
State = France (huh???)
Distribute = Spy Software

Describe table List all of the keys/fields that makeup this table.

Accessing a Database via Java

Now, that you know to get at the data you have to use SQL commands/querries, you need to solve the problem of gaining access or in other words connecting to the database so you can issue these SQL commands. For us in Internet Programming, we will explore how we can do this with Java and JDBC. There are other solutions, some companies like Oracle will have their own special version of SQL (e.g. PL/SQL) and server software that can be invoked to connect to the database and issue SQL commands.

A nice feature of Java &JDBC is that with very little modification, you could change the database SW you are using and as long as it supports SQL (which most do) and you can find a "Java Driver" for it you have only to change one line in your Java code. This should be compared to using proprietary langauges provided by the Database manufacturer which would require a major re-writting of the code used.

Database Terms related to Java

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)

Import classes

Use DriverManager to load the JDBC driver

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

sun.jdbc.odbc.Jdbc.OdbcDriver is a class that must be on your system and registered by your Operating System's ODBC manager

Create Connection object to specific database

String s = "jdbc:odbc:dbname"

Connection c = DriverManager.getConnection(s)

Create Statement object to make SQL calls

Execute query using Statement from 4.

ResultSet r = statement.executeQuery("SELECT * FROM table0"); //just an example

Results are returned in a ResultSet object (e.g. r in 5)

Process results as desired

Repeat steps 4-7

Close ResultSet

Close Statement

Close Connection

Oracle JDBC Drivers: OCI versus Thin

You will use OCI for Java Applications
You will use Thin for Java Applet.

What you need to do:

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(...);
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(.)
EXAMPLE - oracle DB

Running Compiled Example Above:

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 that comes with the installation of Oracle and contains the Oracle driver class called oracle.jdbc.driver.OracleDriver.

FOR APPLICATIONS!!!!!!!!!!!!!!!!!!!!!!!

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 $;/u01/app/oracle/product/8.0.4/jdbc/lib/classes111.zip YourApplicationClass

(note the /u01/..../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 .:/u01/app/oracle/product/8.0.4/jdbc/lib/classes111.zip

(note the /u01/..../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 .:/u01/app/oracle/product/8.0.4/jdbc/lib/classes111.zip

(note the /u01/..../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

IF YOU HAVE AN APPLET INSTEAD !!!!!!!!!!!!!!!!!!!!!!!!!

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:

place classses111.zip in the same directory as your Applet code.

Inside of your HTML code containing the Applet, myApplet.class, this is what the code will look like:

<applet codebase="." archive="classes111.zip" code="myApplet" width=500 height=200>

</applet>

Oracle Database

In this class we will use a version of the Oracle Database. Oracle has been installed on our vermeer server.

Installation
It is during the installation of the Oracle Database and later using Oracle DBA tools that you create the number of tables and their configuration for the database you are creating. You will have only one tablespace per installation of Oracle.

Client Tools

SQLPlus = issues SQL commands to Oracle DB you connect to. Need ; at end of each command
Net8 = lets you alter Tnsnames.ora file containing configuration information to connect to database.
Oracle Replication Manager = lets you replicate an existing database
Oracle Enterprise Manager:

Admin Tool = loads all tools below in a toolbar.
Security = lets set up new accounts for accessing database.
Schema = where setup tables and their configuration (key descriptions --ints, floats, etc)
Worksheet = SQLPlus ...lets you save scripts (note: don't need ; at end of each command).
Instance = lets you change initialization of instances

Identification
You identify an Oracle Database by the following information:

Host IP = IP address where Oracle Database under consideration is installed.
Port = Port number of Oracle Database installation.
SID = This is the ID of the particular tablespace that describes the database you wish to access.

This information has been added to an Oracle configuration file names tnsnames.ora using the Net8 Assistant client SW Oracle provides to setup identification information for the purposes of connection. This information in the file appears as:

INTERNETPROG.WORLD =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP) (HOST = 207.62.129.150) (PORT = 1521))
        (CONNECT_DATA = (SID = intprog))
      )
This means that on the vermeer.monterey.edu (207.62.129.150) server under port number 1521, Oracle was installed with an SID = intprog to identify the table space under question and that TCP communications protocol should be used in accessing it.

Oracle Client Tool to edit Database
To connect to the Oracle Database using a client tools such as SQLPlus to enter data into a table, you need to know the following:

Configuration Name stored in tnsnames.ora. Example above, INTERNETPROG.
User ID/ Login = For our example, student
Password = For our example, cst336
Table Name = For our example, javatest
Table Space in which table exists = For our example, system

Once connected using the SQLPlus tool, you can now issue SQL commands to edit the table.
(note below system.javatest is like saying table_space.table)

>DESCRIBE system.javatest;

>SELECT * FROM system.javatest;