Example of Java Swing Application with Oracle Database - 1

Today we're beginning a series of blog entries that will culminate in a small example Java application for a library management, for desktop environments and using Java Swing classes, and accessing an Oracle database (DB).

This first article addresses the creation of a small Oracle database. It was used Oracle Express 10g, which can be freely downloaded from http://www.oracle.com/technetwork/database/express-edition/downloads/102xewinsoft-090667.html. There is a more recent version of Oracle Express, the 11g release 2, which may be downloaded from http://www.oracle.com/technetwork/database/express-edition/downloads/index.html and with which the example shall also execute correctly.

After installing Oracle, we may access the database administration tool by going to button Start --> All Programs --> Oracle Database 10g Express Edition --> Go to the Database Home Page  or, in a browser, go to http://localhost:8080/apex.

Then, it shall appear a page for us to login in the administration tool:

We shall login with user SYSTEM and the password that we defined for that user during the instalation process.
After logging in the work space appears:

At this point, we must, in the first place, create a new user (Administration --> Database Users --> Create User). We have created user libraryuser, which will then be used to create the database tables, and also to access the database from the Java application. It is needed to grant the new user privileges for CREATE TABLE and CREATE SEQUENCE, and we may also grant him privileges to CREATE VIEW, CREATE PROCEDURE, and CREATE TRIGGER.

The user name of the user with which the database tables are created will then be set as a prefix to the database tables (schema) and, depending on the privileges granted to him, he/she may be restricted to his/her "own" tables.


The reference to a table column, in Oracle, has the following structure:

"schema"."table"."column"

The schema name may have 30 bytes, the table name another 30 bytes, and the column name still more 30 bytes.
The total length of a column identifier may, then, be up to 98 bytes (if we count the dots and commas).

We, then, shall not use the user SYSTEM to create user tables in the database, and even less to access the database from a software application.

Of course, in this toy application this isn't an important issue.

The next step is to logout from the SYSTEM account, in the administration tool, and login as the newly created user (libraryuser).

Now we can create the tables that we need for our application's database, which will be created within schema libraryuser, because that is the user that we'll be using in their creation.

We want to create tables according to the following model:

 Now, let's create the tables.

 Here is the process for creating the table AUTHOR:

Table Author has a Primary Key, which we want to be populated from a new sequence:

ORACLE will create the new sequence and a trigger for populating the primary key.

Table AUTHOR has no foreign keys, nor other check constraints.

But, note that, in our database schema, table BookAuthor must have two foreign keys (referencing Book and Author), and table BookCopy must have one foreign key referencing Book.

After all tables being created...

In the next article we'll create a Java class for accessing the newly created database.
If you have any trouble, please put you questions as comments to this article.

Other related articles:
     - Example of Java Swing Application with Oracle Database - 2
     - Example of Java Swing Application with Oracle Database - 3
     - Example of Java Swing Application with Oracle Database – 4