Thursday, January 19, 2012

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

12 comments:

  1. I appreciate your efforts because it conveys the message of what you are trying to say. It's a great skill to make even the person who doesn't know about the subject could able to understand the subject . Your blogs are understandable and also elaborately described.

    rpa training in bangalore
    best rpa training in bangalore
    RPA training in bangalore
    RPA courses in bangalore

    ReplyDelete
  2. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.
    I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    Java training in Chennai
    Java training in Bangalore
    Java online training
    Java training in Pune






    ReplyDelete
  3. Good Post, I am a big believer in posting comments on sites to let the blog writers know that they ve added something advantageous to the world wide web.

    python training in chennai
    python course in chennai
    python training in bangalore

    ReplyDelete
  4. Excellent blog, I wish to share your post with my folks circle. It’s really helped me a lot, so keep sharing post like this
    AWS Training in Bangalore

    ReplyDelete
  5. This post is so interactive and informative.keep update more information...
    Java Training in Tambaram
    java course in tambaram

    ReplyDelete