Holy Grails and Powerful Oracle
by
·
Join the DZone community and get the full member experience.
Join For Free
1) Start Groovy/Grails Tool Suite (GGTS can be downloaded from SpringSource)
2) Create a Grails Project. File->New->Grails Project
3) Create a Project called “EmailTutorial”
4) Once complete, run grails run-app from the Grails Command Prompt
5) Open browser to http://localhost:8080/EmailTutorial (GGTS has an embedded web browser and will display the link in GGTS’s console)
6) You will only see the DbDocController, click on it. Next you will see “Changelog changelog.groovy not found”
(New in Grails 2.1.1 - The dbdoc from database migration plugin is used to generate static HTML files to view changelog or database change information. There isn’t a database configured for dbDoc, thus nothing is output)
Now you have a bare bones Grails application and it’s responding to http requests. The real objective here is to pull information from the Oracle database. How?
See more information on database tables at then end of the article.
7) Stop the running app. Add the oracle jdbc driver to your lib directory in your project (this is done by copying the file into the lib directory).
The Oracle 11g JDBC drivers are available directly from Oracle.
8) Next, configure the database connection pool from your DataSource.groovy file.
Grails has 3 notions of environments: development (running the app from the IDE), test (running tests from the IDE, grails test-app), and production (a deployed application on an application server, grails war). We have the ability to configure data sources for each of these environments. I’ll add the development and test data source configurations:
development {
dataSource {
pooled = true
dialect = org.hibernate.dialect.Oracle10gDialect
driverClassName = 'oracle.jdbc.OracleDriver'
username = '' // YOUR USERNAME AND PASS
password = ''
url = 'jdbc:oracle:thin:@<servername>:<port>:<database>'
dbCreate = 'validate'
}
}
test {
dataSource {
pooled = true
dialect = org.hibernate.dialect.Oracle10gDialect
driverClassName = 'oracle.jdbc.OracleDriver'
username = '' // YOUR USERNAME AND PASS
password = ''
url = 'jdbc:oracle:thin:@<servername>:<port>:<database>'
dbCreate = 'validate'
}
}
- create - Drops the existing schemaCreates the schema on startup, dropping existing tables, indexes, etc. first.
- create-drop - Same as create, but also drops the tables when the application shuts down cleanly.
- update - Creates missing tables and indexes, and updates the current schema without dropping any tables or data. Note that this can't properly handle many schema changes like column renames (you're left with the old column containing the existing data).
- validate - Makes no changes to your database. Compares the configuration with the existing database schema and reports warnings.
Grails DataSource Documentation
9) Once the datasource is configured, it’s time to create a service object that uses the data source. Create a service object called edu.nocccd.banner.services.EmailService. Select File -> New -> Service from GGTS.
Type the name of the Service in the name field in the create-service dialog and click “Finish”
This will create the EmailService under services in the Project Explorer.
NOTICE: Along with the service object a unit test was created under test/unit. A unit test is used for testing small portions of code. Since EmailService is a service object, I want to create an integration test for the service. Integration tests differ from unit tests, in that they make the whole environment accessible to the tests, i.e. dataSources, unit tests don’t.
10) Delete the unit test for the service and create an integration test. Right click test/integration and select New->Create Grails Integration test.
Create a Test named
edu.nocccd.banner.services.EmailServiceTests
11) Run grails test-app
**THIS SHOULD (OR MAY) BLOW UP with and Exception
java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver
Adding the ojdbc6.jar to the lib directory, didn’t place the jar in the test environment.
12) Run grails clean then grails compile --refresh-dependencies
This cleans the runtime environment of any compiled groovy files and then recompiles them adding any dependencies needed to the application’s classpath. The IDE could possibly complain that there are issues because we’ve cleaned the project (it can’t find needed class files), but continue and execute the compile task if it does complain.
13) Run grails test-app again. This time the environment should run showing the failed test that is in the testSomething() method that was generated when the test was created. Time to fix this issue.
14) Add a reference to the dataSource to EmailService (static dataSource). Next, change the name of the serviceMethod() to getEmail(). Here’s some sample code for
the getEmail() method version 1
import groovy.sql.Sql;
class EmailService {
static dataSource
def getEmail(String username) {
assert dataSource != null, "Datasource is null! No Good!!!"
def sql = Sql.newInstance(dataSource)
def query = """select last_name from person where
username = '@00000000’""" // USE YOUR ID
try {
def row = sql.firstRow(query)
return row.last_name
} catch(Exception e) {
log.error "Exception EmailService.getAddress() ${query} - ${e}"
}
log.debug("Username - ${username} Email - ")
return null
}
}
In the database, usernames are stored as eight digit ids prepended with an ‘@’.
For more information on groovy.sql.Sql see http://groovy.codehaus.org/api/index.html?groovy/sql/Sql.html.
15) Change the integration test to use the EmailService and then write a test, testGetEmail(), replacing the generated testSomething() method.
def emailService // add as a property of the EmailServiceTests
void testGetEmail() {
assert emailService != null, "EmailService is null! BAD!"
// USE YOUR USERNAME AND YOUR LAST NAME - Test for VERSION 1 getEmail()
assert emailService.getEmail("YOUR USERNAME") == "Rippe", "EmailService.getEmail() FAIL!"
}
Assuming my username is ‘@00000000’ and my last name is stored as ‘Rippe’ in the database. Let’s assume every username begins with ‘@’. **It would be much easier just to include the ‘@’ as a parameter to the methods, but I’d like to demonstrate how to concatenate characters in Groovy, thus, the reason I’m leaving the code as is.
// Final Assertion in the Integration Test looks something like
assert emailService.getEmail("MY_ID") == "my@email.com"
16) Run grails test-app
The tests run and complete without any failures.
17) If the tests are successful, you know data access is occurring and it is time to continue. In the service getEmail(String username) there is a parameter username that we are not using. The username is hard coded in the query that should be parameterized. We need to specify the parameter for retrieving the email address. Let’s change the EmailService query and the use a parameterize the query instead of hard coding a banner id.
def query = """select email_address from person
left join email
on person.pid = email.pid
where username = ?
and email_status_ind = 'A' and
email_preferred_ind = 'Y'"""
try {
def row = sql.firstRow(query, [username])
return row.email_address
The '?' in the query, is a placeholder for our parameterized username. The call to firstRow() that specifies the variable username that is passed to the getEmail method is evaluated at runtime. This allows us to specify the username of the person we’d like the email address of. The columns email_status_ind determine if a particular email address is active and the email_preferred_ind determines if a person has multiple email addresses if this particular address is the preferred address to use for communication.
18) Run the tests again, grails test-app. The tests should fail. The username doesn’t have the ‘@’ sign. Add
username = "@${username}" // concatenates a '@' in front of the username
log.debug("getEmail address for username ${username}")
19) Run the tests again, grails test-app.. Now all is well, the test should successfully complete. The service works and is communicating with Oracle. The integration tests help ensure that the service object is working correctly before code is written at any other level utilizing the service.
20) Time to use the new Service component in the web app that was generated for us when creating the project. Create a controller, edu.nocccd.data.controllers.EmailController, for the application. Right click the controller icon in the Project Explorer, select New -> Controller
Type the name of the controller, edu.nocccd.data.controllers.EmailController, and click “Finish”
21) Add the following code to the index() method in the EmailController:
def index() {
render "Hello everyone!!!"
}
22) Run grails run-app and navigate to the main controller page, http://localhost:8080/EmailTutorial/email/index. You should see the following text.
At this point, you should have an app that connects to an Oracle database using a database connection pool, via dataSource and a controller that responds to user requests via the browser. We now need to put the two together.
23) Add the following property to your Controller (edu.nocccd.data.controllers.EmailController):
def emailService
24) Add the following code to the body of your Controller’s (edu.nocccd.data.controllers.EmailController) index() method:
if(emailService == null) {
log.error("Missing EmailService - need to implement EmailService")
response.status = 500
render "Missing my work buddy EmailService!!!"
} else {
def email = emailService.getEmail('00000000') // your username
render email
}
25) Run grails run-app, navigate to the http://localhost:8080/EmailTutorial/email/index. This should output your email address.
26) In the call to getEmail() in the Service object, the username ‘0000000’ is hard coded and not parameterized. This needs to be modified to use the params map that is provided to each controller in a grails application. This means that the controller can accept parameters and forward those to the service object to retrieve the proper email address. Change the method call
def email = emailService.getEmail('00000000') // your username
to
def email = emailService.getEmail(params.username)
In the code from 24.
**NOTE: You can make changes to the classes in the application and save them. You will see that GGTS re-compiles the class(es) and makes them available to the application without starting and stopping the application.
27) Navigate to http://localhost:8080/EmailTutorial/email/index. You should get a NullPointerException in your Console window and the browser will output null. Why? How do we fix it?
28) The easy fix is to change
def email = emailService.getEmail(params.username)
to
def email = emailService.getEmail(params.id)Now call the index() method in the controller by navigating to http://localhost:8080/EmailTutorial/email/index/00000000 (using your id in place of 00000000). This should give you your email address. Why?
If you open UrlMappings.groovy from your conf directory, you will see the following:
"/$controller/$action?/$id?" {
constraints {
// apply constraints here
}
}
This configuration is defining the controller to be called, EmailController, the action (method) to be invoked, index(), and the parameter defined as id, $id? to be sent to the method. If we wanted to define any constraints on parameters, we could do so here. This file creates urls that map back to controllers and actions in your application. The request in #27 didn’t work because there isn’t a parameter called “username” defined in the UrlMapping.groovy.Let’s change the UrlMapping to something more appropriate for the application and name the parameter username.
29) Modify the UrlMapping.groovy to contain the following code:
static mappings = {
"/getEmail/$username?"(controller:"email", action:"getEmail")
}
Save your file. Looking at this code we see that we are creating a new mapping to
http://localhost:8080/EmailTutorial/getEmail/00000000
where 00000000 is defined as the parameter username. The EmailController will respond by calling the action “getEmail()”. Make a request to the uri http://localhost:8080/EmailTutorial/getEmail/00000000 using your username. This request should give you a 404 error. What’s the issue (don’t look)?
30) The problem is there isn’t a defined action “getEmail()” in the controller. We were using index(). The solution is to define a getEmail action. Change the index action to:
def getEmail = {
if(emailService == null) {
log.error("Missing EmailService - need to implement EmailService")
response.status = 500
render "Missing my work buddy EmailService!!!"
} else {
log.debug("Getting some oracle data for ${params.username}")
def email = emailService.getEmail(params.username)
render email
}
}
31) Save the EmailController.groovy file and navigate to http://localhost:8080/EmailTutorial/getEmail/00000000 using your id. Try navigating to the uri using a different username. This should give you a different email address.
32) Most applications don’t simply respond with the data a user is looking for. Most usually respond with XML or JSON (Grails has support for both). These are the two most popular ways of returning data from a uri. We will modify our application to return JSON (Javascript Object Notation) responses. Add the following import statement after your package statement in the EmailController.groovy file:
import grails.converters.JSON;
33) In the getEmail closure, change the line
render email
to
render email as JSON
and save. For more on converters see http://grails.org/Converters+Reference.
34) Navigate to http://localhost:8080/EmailTutorial/getEmail/00000000 using your id. This should output a org.codehaus.groovy.runtime.typehandling.GroovyCastException on the line modified in step 33.
As it turns out, a java.lang.String isn’t enough information to create JSON or XML. Thus, the reason for the error. How do we resolve this issue (don’t look)?
35) To resolve our issue, we need to create a domain object to store our email address. This will give the convert a property name and the value of the property. Right click on domain, select New, and choose Domain Class
Fill in the class name edu.nocccd.data.domain.Email and click “Finish”
36) Add the address and mapWith property to the Email domain class:
class Email {
static mapWith = "none" // no persistence
def address
static constraints = {
}
}
(We’ll keep it simple, no constraints) and save the file.
37) Modify the EmailServicegetEmail method to create an Email object and return it as a result. Import the Email class into the EmailService
import edu.nocccd.data.domain.Email;
38) update the getEmail method to
def getEmail(String username) {
assert dataSource != null, "Datasource is null! No Good!!!"
def sql = Sql.newInstance(dataSource)
def query = """select email_address from person
left join email
on person.pid = email.pid
where username = ?
and email_status_ind = 'A' and
email_preferred_ind = 'Y'"""
username = "@${username}"
log.debug("getEmail address for username ${username}")
Email email = new Email()
try {
def row = sql.firstRow(query, [username])
email.address = row.email_address
} catch(Exception e) {
log.error "Exception EmailService.getEmail() ${query} - ${e}"
}
log.debug("Username - ${username} Email - ${email.address}")
return email
}
Save the file.
39) Navigate to the page, http://localhost:8080/EmailTutorial/getEmail/00000000 where 00000000 is your username. If the app doesn’t respond, stop the grails application and run grails run-app. This will make sure that we are running the latest code.
You should see something similar to
Progress although not the output that we really want for our application. We need to tell the JSON converter what properties we want in the output. Turns out you can do this in your conf/BootStrap.groovy file. We can register object marshaller with the JSON converter telling it what properties we want output. In the output above, we want to get rid of the class and the id. These aren’t data we are looking for in our output. We only want the email address output.
40) Open your conf/BootStrap.groovy file and add the following imports
import grails.converters.JSON;
import edu.nocccd.data.domain.Email;
41) In your conf/BootStrap.groovy, in the init closure add the following:
JSON.registerObjectMarshaller(Email) {
def returnArray = [:]
returnArray['email'] = it.address
return returnArray
}
Save the BootStrap.groovy. Stop and run grails run-app again.
42) Navigate to the page, http://localhost:8080/EmailTutorial/getEmail/00000000 where 00000000 is your username. You should see something similar to
What did I cover
- Creating a datasource with a pooled connection
- Creating service objects for database retrieval using connection pool
- Creating integration tests and running those tests
- Mapping urls to controller actions
- Parameterizing urls
- Creating domain objects for data storage (part of the MVC design pattern)
- Converting domain object to JSON output from a controller
This should be sufficient for our purposes of creating a basic web service for getting an email address from Oracle. There are a whole bunch of other issues I didn’t discuss like logging or the connection pool configuration. I’ll save these for some other tutorial. For now, I'll leave this tutorial as way of wetting your Grails and Oracle whistle (The tutorial code can be downloaded at https://github.com/brippe/EmailTutorial). Enjoy!
Database Tables
In Oracle, the tutorial is concerned with two tables
1) PERSON - the table where user information is stored
2) EMAIL - the table where email information is stored
The two are linked by a common id called a PID from the PERSON table which is a foreign key for the EMAIL table.
SQL for Tables
-- PERSON TABLE
CREATE TABLE "PERSON" ("PID" NUMBER(8,0),
"LAST_NAME" VARCHAR2(60 CHAR),
"FIRST_NAME" VARCHAR2(60 CHAR),
"USERNAME" VARCHAR2(60 CHAR)) ;
CREATE UNIQUE INDEX "PERSON_PK" ON "PERSON" ("PID") ;
ALTER TABLE "PERSON" ADD CONSTRAINT "PERSON_PK" PRIMARY KEY ("PID") ENABLE;
ALTER TABLE "PERSON" MODIFY ("PID" NOT NULL ENABLE);
ALTER TABLE "PERSON" MODIFY ("LAST_NAME" NOT NULL ENABLE);
ALTER TABLE "PERSON" MODIFY ("FIRST_NAME" NOT NULL ENABLE);
ALTER TABLE "PERSON" MODIFY ("USERNAME" NOT NULL ENABLE);
-- EMAIL TABLE
CREATE TABLE "EMAIL" ("PID" NUMBER(8,0),
"EMAIL_ADDRESS" VARCHAR2(60 CHAR),
"EMAIL_STATUS_IND" VARCHAR2(1 CHAR),
"EMAIL_PREFERRED_IND" VARCHAR2(1 CHAR)) ;
CREATE UNIQUE INDEX "EMAIL_PK" ON "EMAIL" ("EMAIL_ADDRESS", "PID");
ALTER TABLE "EMAIL" ADD CONSTRAINT "EMAIL_PK" PRIMARY KEY ("EMAIL_ADDRESS", "PID") ENABLE;
ALTER TABLE "EMAIL" MODIFY ("PID" NOT NULL ENABLE);
ALTER TABLE "EMAIL" MODIFY ("EMAIL_ADDRESS" NOT NULL ENABLE);
ALTER TABLE "EMAIL" MODIFY ("EMAIL_STATUS_IND" NOT NULL ENABLE);
ALTER TABLE "EMAIL" MODIFY ("EMAIL_PREFERRED_IND" NOT NULL ENABLE);
ALTER TABLE "EMAIL" ADD CONSTRAINT "EMAIL_PERSON_FK1" FOREIGN KEY ("PID") REFERENCES "PERSON" ("PID") ENABLE;
-- PERSON insert into person values (0, 'Rippe', 'Brad', '@00000000') insert into person values (1, 'Doe', 'John', '@00000001') insert into person values (2, 'Doe', 'Jane', '@00000002') -- EMAIL insert into email values(0, 'brippe@somewhere.com', 'A', 'Y'); insert into email values(0, 'brippe@somewhereelse.com', 'I', 'N'); insert into email values(1, 'jdoe@somewhere.com', 'A', 'Y'); insert into email values(2, 'jane_doe@somewhere.com', 'A', 'Y');
Opinions expressed by DZone contributors are their own.
Comments