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.
|
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.
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. |
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
|
The Process (see below for example)
|
Oracle JDBC Drivers: OCI versus Thin
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.*;EXAMPLE - oracle DB |
|||
Running Compiled Example Above:
|
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
Identification
You identify an Oracle Database by the following information:
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:
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)
>SELECT * FROM system.javatest;