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.

 MORE ON SQL
 YET MORE ON SQL
 



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

  1. Driver Manager
    • loads Database drivers and manages connections between appliation and driver.
  2. Driver
    • translates API calls into database specific calls.
  3. Connection
    • session between application and database
  4. Statement
    • SQL statement
  5. Metadata
    • info about returned data, database and driver.
  6. 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("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
  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

 

 

 

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;


 
© Lynne Grewe 2000