EclipseLink/Examples/REST/GettingStarted/DatabaseModel

Overview

The following database model will be used for this example. This particular database stores customer related information. I am using Oracle Database XE, but you could use almost any database with a JDBC driver.

CUSTOMER Table

CREATE TABLE "CUSTOMER" 
 ( "ID" NUMBER NOT NULL ENABLE, 
 "FIRST_NAME" VARCHAR2(50), 
 "LAST_NAME" VARCHAR2(50), 
 CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("ID") ENABLE
 )
/

ADDRESSS Table

CREATE TABLE "ADDRESS" 
 ( "ID" NUMBER NOT NULL ENABLE, 
 "STREET" VARCHAR2(50), 
 "CITY" VARCHAR2(50), 
 CONSTRAINT "ADDRESS_PK" PRIMARY KEY ("ID") ENABLE, 
 CONSTRAINT "ADDRESS_FK" FOREIGN KEY ("ID")
 REFERENCES "CUSTOMER" ("ID") ENABLE
 )
/

 

PHONE_NUMBER Table

CREATE TABLE "PHONE_NUMBER" 
 ( "ID" NUMBER NOT NULL ENABLE, 
 "TYPE" VARCHAR2(50), 
 "NUM" VARCHAR2(50), 
 "ID_CUSTOMER" NUMBER, 
 CONSTRAINT "PHONE_NUMBER_PK" PRIMARY KEY ("ID") ENABLE, 
 CONSTRAINT "PHONE_NUMBER_FK" FOREIGN KEY ("ID_CUSTOMER")
 REFERENCES "CUSTOMER" ("ID") ENABLE
 )
/

 

JDBC Resource & Connection Pool

Next we need to configure a connection pool on our application server. I will be using GlassFish Server Open Source Edition version 3.0.1. These steps will vary depending on the application server you are using.

1. Copy the JDBC driver (ojdbc14.jar) to <glassfish_home>/glashfish/lib
2. Launch the Administration Console
3. Create a Connection Pool:
   1. Name = CustomerService
   2. Resource Type = 'javax.sql.ConnectionPoolDataSource'
   3. Database Vendor = Oracle (or whatever database vendor is appropriate to your database)
4. Click Next and fill in the following "Additional Properties":
   1. User (e.g. CustomerService)
   2. Password (e.g. password)
   3. URL (e.g. jdbc:oracle:thin:@localhost:1521:XE)
   4. Use the "Ping" button to test your database connection
5. Create a JDBC Resource called "CustomerService"
   1. JNDI Name = CustomerService
   2. Pool Name = CustomerService (name of the connection pool you created in the previous step)

Next Steps

In part 2 we will examine how to use the Java Persistence Architecture (JPA) to expose the database data as entities (POJOs).