|
As part of our JavaEE development
efforts, we need a database to persist our data.
Before we move on, let's do a
quick recapture of what we have done so far:
- We decided to do some Java EE development
- We downloaded, installed and configured GlassFish
application server , which is an open source (beta) reference
implementation of the Java EE specifications
- We downloaded and installed NetBeans
- We configured NetBeans to use GlassFish application
server; we will use this setup to write code in NetBeans and deploy it on
GlassFish
- This blog entry talks about how to install the
database (will be used to persist data as part of developing JPA code)
In my first blog about Java EE
development, I had mentioned that I was going to use Oracle XE for that. The
installation and configuration of Oracle XE is really easy on windows platform.
You simply download Oracle XE from http://www.oracle.com/technology/products/database/xe/index.html,
double click the exe, follow the prompts and you are done.
Oracle XE gives a nice browser
based GUI tool to manage the database. This was easy, let's quickly create the
user (schema) we will be using for Java EE development. Note the following
about this db user:
- This user is the application user that will be used
to configure data source in GlassFish server for our application to
interact with the database
- This user will have connect and resource privileges
on the database. Read more about privileges at http://www.oracle.com/pls/xe102/homepage
- Connect privilege allows the user to connect to the
database. Usually, applications users are given this privilege BUT we
want our application user to be able to create tables (as part of JPA
annotated Entity classes)
- Resource privilege allows the user to create certain
type of schema objects in his own schema (e.g. create tables, views
etc.). The specific types of objects this user is allowed to create can
be selected using the 'Direct Grant System Privileges' list. As shown in
the screen shot below, we have selected all the options in the list
- DBA privilege enables the user to perform administrative
functions. We will not give javaee user this privilege
To create the application user, go
to Home > Administration > Manage Database Users, Click Create, Fill in the data as
shown below (I am using username=javaee and password=javaee):
This step created the database schema.
In Oracle, the terms user and schema are used interchangeably but I will not
get into those details here. The javaee schema we just created will eventually
hold all the tables, views and other database objects.
Just to make sure everything went
well, we will now connect to the database and run a sql from command line:
- Open a command prompt by selecting Run SQL Command
Line from the Oracle start menu entry
- Login as the user using connect javaee/javaee
command. You should see a "connected" message
- Save the following sql as address.sql
|
drop table ADDRESS;
create table ADDRESS (
"ADDRESS_ID" NUMBER NOT NULL,
"ADDRESS1" VARCHAR2(100) NOT NULL,
"ADDRESS2" VARCHAR2(100),
"CITY" VARCHAR2(50) NOT NULL,
"STATE_CODE" VARCHAR2(2) NOT NULL,
"ZIP" NUMBER NOT NULL
);
alter table ADDRESS add constraint pk_address primary key
(ADDRESS_ID);
|
- Run the address.sql file using SQL>@"<full_path_to_file>."
Note the following about running the sql:
- @ sign runs the sql in the file
- Double quotes are required if your path contains
spaces
- You will get an error "table or view does not
exist." This is because the first line of the script drops the table
but the table doesn't exist yet. Any subsequent runs of this file won't
display the error message
- Run desc ADDRESS. This will describe the table
ADDRESS
In the next installment we will
create an Address entity from the ADDRESS table. JPA also allows you to create
entity classes first and use them to create database table. We will use this
feature for some of the other entity classes.
|
