04 November 2009

JDBC Realm Setup with Glassfish v3 (build 70) and Netbeans 6.8 (beta) JEE6

As I am about to undertake this and I can't find instructions using the glassfish GUI admin, I might as well blog it:

Create Database
I'm going to use the Derby/JavaDB database that came with Netbeans:

Click on the 'services' tab in Netbeans
Right click on JavaDB under Databases and select 'Create Database..'
Input details for the database, I used 'security' for database name , username and password (ironically).

Create Connection Pool for Database
Then in the Glassfish admin panel:
Resources->JDBC->Connection Pools, select 'New'
Name: security
Resource Type: javax.sql.DataSource
Database Vendor: JavaDB
Scroll down to additional properties:
DatabaseName: security
User: security
Password: security
ServerName: localhost

NB! Delete all other properties.

then try 'ping' it

make sure your database is up and running, compare properties to other loaded connection pools..

Create JDBC Resource
In the Glassfish admin panel:
Resources->JDBC->JDBC resources, select 'new'
JNDI Name: jdbc/security
Pool Name: security

Create Tables
In Netbeans 'Services' tab:
Database->jdbc:derby://localhost:1527/security, right click and select 'Execute Command'

create table usertable (
        username varchar(128) NOT NULL CONSTRAINT USER_PK PRIMARY KEY ,
        password varchar(128) NOT NULL

create table grouptable(
        username varchar(128) NOT NULL,
        groupid  varchar(128) NOT NULL,
        CONSTRAINT GROUP_PK PRIMARY KEY(username, groupid),
        CONSTRAINT USER_FK FOREIGN KEY(username) REFERENCES usertable(username)
and populate:

insert into usertable(username,password) values ('admin', '21232f297a57a5a743894a0e4a801fc3');
insert into grouptable(username,groupid) values ('admin', 'USER');
insert into grouptable(username,groupid) values ('admin', 'ADMIN');

scripts modified slightly from http://blogs.sun.com/foo/resource/createschema.sql from the article http://blogs.sun.com/foo/entry/mort_learns_jdbc_realm_authentication

Which is pretty much what I am doing..

Create New
In the Glassfish admin panel:
Configuration->Security->Realm, select <new>
Name: security
Class Name: the one with 'jdbc' in it

JAAS Context: jdbcRealm
JNDI: jdbc/security
User Table: usertable
User Name Column: username
Password Column: password
Group Table: grouptable
Group Name Column: groupid
Digest Algorithm: MD5

time to test..

Web Config
Roll Mapping
In Netbeans in the projects tab:
<your war project>->Configuration Files->sun-web.xml and open it
click on <security>
<Add Security Role Mapping>
Security Role Name: USER
<Add Group>
Group Name: USER

and do the same for the admin role/group

As I am using JEE 6, I have had no need for the web.xml file as yet, so now I need to create it:
right click on the war project
new->other->Web->Standard Deployment Descriptor

now edit it:
select the 'Security' button/tab
under 'Login Configuration' select Basic
Realm Name: security

then under Security Roles <Add..>
Role Name: USER
do the same for ADMIM role

Now <Add Security Constraint>
Display Name: Test Constraint

Web Resource Collection <Add..>
Resource Name: test
URL Pattern(s): /test/*

tick 'Enable Authentication Constraint'
Role Name(s): USER

and save..

create a directory 'test' with a jsp file in it and try access it..

Worked for me, yay me!

22 May 2009

Creating a MySQL Database on an External Drive - Things I will forget [002]

The harddrive on my laptop is filling up and I have a client who's database runs into the gigs (per dataset). So I created a MySQL database on an external harddrive a while ago, but now need to do it again (the harddrive is flaky) and have forgotten how to do it. I seem to be looking in the wrong places, so maybe if I write this blog other people searching for the same thing will find it and be able to use it. I will be able to do it next time without searching too!

Well, its actually quite easy. I first tried to create a separate configuration file like I did the previous occasion, but this means not being able to store the config file on the disk: mysql requires that the config file not be world writeable, but by default the external hardrive is mounted with all files world read/writeable. So rather than mess about with the fstab and change the way it is mounted, I opted for specifying the options on the command line.

So this is what I had to do:

First I created I directory where the MySQL database would reside, all further commands are run from that directory.

I then created a data directory store the databases:

mkdir data

I the ran mysql_install_db to initialize the MySQL database tables:
mysql_install_db --ldata=data

Then I could start the database (remember to shutdown your other mysql database if it is running, else specify a different port for this databse to run on):

mysqld --pid-file=./mysqld.pid --socket=./mysqld.sock --datadir=./data

I put this into a 'start.sh' script.

The socket file will be created in the data directory (data/mysqld.sock.
You can then connect to the database like this:

mysql --user=root --socket=./data/mysqld.sock

If you want access to the MYSQL tables use --user=root, else create your own users etc. If you want to use the MySWL Administrator and MySQL Query Browser graphical front ends, make sure you specify the socket file:

Shutdown the database using the following command:

sudo mysqladmin shutdown -S ./data/mysqld.sock

It bothers me that I have to shut it down using sudo, I'll look for a work around when it starts to really irritate me.

Now to wait for the 8 Gig of data to load.

On a side note: I don't think Acid Mothers Temple is the best band to listen to while programming, my brother might disagree..

 <<Blog as memory>>

06 May 2009

Creating a JEE application in Netbeans 6.5

This is the first thing I do when giving a course on JEE: I open up Netbeans, create a JEE project, create a JavaDB database, create a JPA persistance unit, create an entity bean, create a session bean, create a servlet, create a JSP page and create a cutsom tag (all associated with the entity bean created).

This is a great help to the students as it gives them the overview of what we will be working with and shows them how quick and easy it is to create a JEE application in Netbeans - It takes the complexity fear out of them (to a certain extent).

I thought it would be a great help if this were available as a blog for others to follow. Comments will be most welcome, but not that this is pretty much off the cuff and bares no relevence to a real application.

I'll provide a time line to give you an idea of speed, my slow laptop and making it up as I go along should handicap my normal coding dexterity :)

10:51 - music selection, hmm, Black Keys, Brian Eno and David Byrne, Cake.
Ahh, Thickfreakness!

10:55 - Project creation
New Project -> Java EE -> Enterprise Application
I'm calling it BooksDemo
Using the defaults: Glassfish V2, Java EE 5, Create EJB Module, Create Web Application Module

This will create three projects - BooksDemo 'containing' BooksDemo-ejb and BooksDemo-war

11:02 - Create the Datastore

Under Databases in your 'services' tag, right click on 'Java DB' and select 'Create Database' and create a database called 'BooksDemo' with username and password set to 'books' (I've found it doesn't work so well to not have username and password - go figure).

The creation will also start the database, if you go back to the services tab, you will be able to open and view the database.

Back to the project tab: select 'BooksDemo-ejb', right click -> new -> other -> Persistance -> Persistence Unit - > Next
I use the default unit name (BooksDemo-ejbPU) and for datasource select 'New Data Source' at the bottom of the dropdown. Then select the BooksDemo database you created earlier and make the JNDI name 'jdbc/booksDemo', leave the rest as default and finish.

Now for the Entity Object:
Back to the project tab: select 'BooksDemo-ejb', right click -> new -> other -> Persistance -> Entity Class -> Next

Classname 'Book', package 'com.example.book.model' the rest default and finish.

In the Book class, create a new field 'name' as a String field: <alt>INSERT -> Add Property: make the name 'name' and select OK.

11:15 - Create the SessionBean

(Brian Eno is a genius)

In the project tab: select 'BooksDemo-ejb', right click -> new -> other ->Java EE -> Session Bean -> Next

Call it 'BookService', package 'com.example.book.service', the rest default.

Now we are going to create two 'business' methods: getAll and add for retrieving all books and add a book to the datastore.

<ALT>INSERT -> Add Business Method with name 'getAll' with return type 'List<Book>'

<ALT>INSERT -> Add Business Method with name 'add' and add a parameters called 'book' of type 'Book'.

Next fix the imports - <CTRL>-<SHIFT>-I (in the interface as well)

Next we need a handle on the persistance unit: Right click in the BookServiceBean -> Persistence -> Use Entity Manager

You will now have something like the diagram above.

Now we need to use the EntityManager to store and retrieve the books: for the getAll method:

return em.createQuery("select b from Book b").getResultList();

for the add method:


11:31 - The Web

We are going to edit the index.jsp ading a form to add a book and retrieve a list of books.

hmm, we need a Servlet to act as the action storing the book first:

On the 'BooksDemo-war' (note its 'war', dammit) right click -> new -> other -> Web -> Servlet -> Next

Class Name: 'BookAdd'
package: 'com.example.book.web.action'

the rest is default, click 'next' and 'finish' (note that the servlet will be called using '/BookAdd' in the URL.

Add the SessionBean by <ALT>-INSERT (in the servlet class) -> Call Enterprise Bean and select the BookServiceBean and OK.

Edit the 'processRequest' method, take everything out and replace it with the following:

Book book = new Book();

We can see if we are on the right track so far: in the project tab, select the 'BooksDemo' projec, right click and 'Run'

This took me 1 min 6 sec..

You should see 'Hello World!' in your browser, this is the index.jsp we haven't edited yet.

Add 'BookAdd?name=test' to the URL in the browser navigation thingy, you should be redirected to the index page, so you will not see anything of value in the browser. this is good. if you see stack traces, bummer.

Go to the Services tab, select Database, select the BooksDemo database, Tables, Book -> right click -> View Data, you should see a record in the table with the name 'test', Hurrah!

11:50 - The index.jsp

Under 'Web Pages' in the 'BooksDemo-war' project you will find the index.jsp page, open it and add the following code after '<h1>Hello World!</h1>':

<form method="post" action="BookAdd">
<input name="name"/>
<button type="submit">add</button>

Save it, reload your browser and you should see a form with one input field and an 'add' button, enter a name for a new book and press 'add'. Check that you have a new record in the database.

Custom Tag

On the 'BooksDemo-war' right click -> new -> other -> Web -> Tag Library Descriptor -> next

TLD Name: books
default for the rest and click 'finish'
On the 'BooksDemo-war' right click -> new -> other -> Web ->Tag Handler -> Next

Class Name: BookTag
Package: com.example.book.web.tag
default for the rest and click 'Next'

TLD File: WEB-INF/tlds/books.tld (browse to it)
Tag Name: book
default for the rest and click 'finish'

In the BookTag class, <ALT>-INSERT -> Call Enterprise Bean and select BookServiceBean.

Next, remove everything from the 'doTag' method and add the following:

BookServiceLocal bookServiceBean = lookupBookServiceBean();
List<Book> books = bookServiceBean.getAll();
for(Book book: books){
getJspContext().setAttribute("book", book);

You will also need to throw the IOException.

Back to index.jsp,at the top of the page add the following directive:

under the '<%@page' directive,
<%@taglib uri="/WEB-INF/tlds/books" prefix="b"%>

Note: you can <CTRL>-SPACE to auto complete the uri.

(sheep go to heaven, goats go to hell)

Then add the following under the form:


If you reload your browser you should have a list of your book under the form:
And at 12:09, thats a wrap.
At just under two hours, I don't think its that bad for getting a trivial application up and running (bear in mind I did not prepare for this, so some time was spent thinking each step through), if you had to do it by hand it would be more of a headache. Netbeans adds a lot of value to creating these applications.

The project seems to slow down once we reach the web side of things, but maybe one day we'll have a better way of creating web applications :)

24 April 2009

Real world GAEJ Application

OK, this is a very sketchy view of developing a first time application with GAEJ. It is a rewrite (not a port) of an application I wrote a while back in Java using Spring and JPA. The application is a timetracker recording time I spend on various client projects, I will publish the URL once I've cleaned up a few things and I want to add GWT to the front end.

This is my experience, warts and all, maybe it will help others. I'm using the eclipse plugin for development.


In my previous hack, I used JDO to see what it was like, I didn't much like it and decided to move back to JPA. I found JDO quite verbouse to use and especially did not like the way you have to declsre parameters for queries.

The first step was to create the persistance.xml file, as the eclipse plugin only provides the jdo configuration as default. Its easy enough, put it in the same place as your jdoconfig.xml file (in the META-INF directory).

You will need to identify the Entity classes by specifying them in the persistenace.xml file using the <class> tag.

See Setting Up JPA in the docs. Hmm, the line
EntityManager em = EMF.get().getEntityManager();
should be changed to:
EntityManager em = EMF.get()createEntityManager();

Wow! Changed the JDO persisted class to a JPA entity class by changing the annotations and implementing Serializable and.. it work!!


Edit the deployment descriptor (web.xml to you and me) and include the
security-constraint tag. There is an example in the docs called Security and Authentication.

See Google Accounts Java API Overview in the docs.

OK, then how does one access the user principal using EL in a JSP page?

Looking at the user principal I see it is implemented as: com.google.apphosting.utils.jetty.

UserServiceFactory.getUserService().getCurrentUser() which return a com.google.appengine.api.users.User object. There mus be some relationship to AppEnginePrincipal, but its not via inheritance.

Some digging around and I see AppEnginePrincipal holds an instance of User, so I can do this in JSP:

apart from:

(of course, once the user is logged in)

Any resources specified in a security-constraint will cause a redirect to the google login page. Any User specified as a developer on the application will have admin role, not sure what role everybody else gets or how to get more roles.

The Turn Around

Under the persistance section I mentioned moving from JDO to JPA, I really don't like JDO from a usablility point of few, the stench is too high - I don't want to be that aware of my datastore.

But now I find myself going back to JDO for a number of reasons:
  1. Not enough documentation on GAEJ about using JPA
  2. The JPA implementation is basically JDO under the hood and I get the feeling not everything translates well.
  3. JDO is simpler for storing objects outside your domain (the User object springs to mind)
  4. There is a general ".. I don't know, use JDO.." attitude on the mailing list
  5. I plan to use GWT in the project and there are serious issues regarding serializing Entities - GWT-RPC broken in GAE/J.
bugger, I really don't want to do this, humph.

The DataNucleus Enhancer is starting to T me off, keeps on popping up in the console after you save any class in your project, perhaps it should only be kicked off when saving persistant objects or not output unless on error.

Ah, got it: under your project properties -> google -> appengine -> ORM you can specify the specific file/ packages you want enhanced. Nice.

And we are back with JDO. The data hasn't changed, the same data was accessed by JDO, then JPA, then back to JDO without issue.

Then there is a gap where I modify my J2ME MIDlet, faf around with getting data from the phone and add a domain to my application and run out of bandwidth. This is not a happy period.

Implicit params in the JDO queries will be supported in the next release. Meh

The other 'Duh' drops

Spent a lot of time trying to figure out relationships and how to implement it with GAE. Did some more reading and recommend these articles: Re-thinking Object-Relational Mapping in a Distributed Key-Store world and How I Learned to Stop Worrying and Love Using a Lot of Disk Space to Scale. I've simplified my initial design dramatically and geared it towards my end goals and a (gasp!) de-normalized the design.

The thing that bothers me most is using JDO/JPA for something like this.I'm going to have a look at the lower level APIs and see if they make more sense (at some stage)

It would be nice to include a restart button for the local web host within eclipse.


Session support is not enabled in appengine-web.xml by default. To enable sessions, put <sessions-enabled>true</sessions-enabled> in that file.

I've read somewhere that read only session attributes are supported out the box, I guess that is why you have access to the UserPrincipal.

And while I'm on session:: it seems objects stored in the session must be serializable, not obvious under the test server, but going live will throw  exceptions.

And you can view your applications sessions via the data viewer in the application administrator.

I finally understand why a lot of people are against pure servlet/JSP/taghandler development, they were probably using eclipse. A NetBeans GAEJ module would be nice.

Conclusion of Sorts

The major headache is around data access at the moment. Working with BigTable requires a mind change if your are used to relational databases.

10 April 2009

Google Appengine Java Test Ride

Time to climb on the appengine-java-blog train.

9:45 OK, Bootsy Collins 'If 6 was 9' playing, check.

I've created a an app using appengine with python and as much as writting in python is fun, I don't like the maintenance aspect of it (trying to remember what methods classes had without looking it up again etc).

I really like the idea of appengine especially the ease of use in deploying and versioning your application, but especially the zero cost to try aspect. I'm dying to try amazon's service, but I can't try it without entering my credit card details and I don't have one at the moment.

So I've been looking forward to writing for appengine in java. Here is my first experience with it I timestamped to give you an idea of the time it takes to create and app. I've been programming in Java since 1995 and Servlets since 2000, so your experience may vary ;)

The kids are watching Asterix in the study, bit distracting but I have headphones, hence the musical references

10:02 So, eclipse plugin loaded, lets create a servlet and send it live.

10:04 Project created without GWT, creates a default servlet, bleh.

10:05 Simple single button project publishing, must create new application on appengine first.

The deploy dialog doesn't display application setting by default, need to open 'App Engine project settings..', not quite intuitive. Weird email completion too (got it wrong first time).

10:09 Up and running! Not bad, under ten minutes to create a new application and send it live, pretty painless.

Next: index.jsp -> link to servlet -> forward to index.jsp

hmm, creating a servlet using the wizard doesn't work, needs a web project and I can't enter one. Oh well, back to creating by hand, will look into that later..

rrr, prefer netbeans

10:19 editing web.xml -> added TestServlet, changed welcome file to index.jsp

10:21 Re-deploying the app I have to re-enter password, that could be improved.

10:22 Tested and working, yay!

Next lets try persisting some data (as soon as 'Maggot Brain' is finish)

JDO or JPA: I've used JDO long time we go, been using JPA since it came out, but there seems to be more documentation for JDO on the appengine site and I can't resist trying unfamiliar technology, so JDO it is.

Let me try vertical development and implement an object first without relationships and do some basic CRUD. later (10:28)

10:30 Going to read the docs about created JPA Entities in eclipse..

Once I've added the annotaions to the class, saving it triggers the enhancer as reported in the output: 'DataNucleus Enhancer completed with success', cool.

10:38 Created the persistent object with id, date, and string fields, painless. Now to look at the PersistenceManagerFactory, which google recommends wrapping as a singleton, I hope to use some DI framework in the project (Guice perhaps), but for the moment I'm going to hack it right into the servlets.

10:43 Alright, singleton copied from google code and modified slightly. Now to add some data.

I'll create a form on the index page with fields and submit it to an action servlet which will persist it (I won't create a service/business tier just yet) and forward back to the index page.

I'll also adding the created object to the request before the forward.
Eclipse: oh the irony of your name. hmm, no EL completion in JSP, mff, have to google for request attributes (requestScope, duh).

11:03 and deploy (and enter password)

11:06 Oops, overwrote doGet instead of doPost, fix and publish

The persistence seems to work, except the EL variable ${requestScope.object.field} is being displayed in plain text and not being interpreted, maybe I've missed something - back to the docs.

From the mailing list I discovered kindred soles: one needs to add <%@page isELIgnored="false" %> in the JSP, guess it is true by default. Its a pity they don't turn scriplets off by default, I see the docs show extensive use of scriplets within the JSP - there are beginners opening up a world of pain with scriplets in JSP.

Started using the local server for development.

11:22 yay! working

Next up, create a custom tag for displaying all records.

mothership connection coming to take back the pyramids - having a look at some of the mailing list queries.

11:27 SimpleTagHandler here we go. Ah, man: have to write the TLD by hand (well copy and paste and edit by hand).

do you remember September?

11:39 Almost there, need to look at JDO queries and add Tony Allen to the play list.

11:49 Working locally, now to publish..

11:52 And working live, nice. I think a little break, OK?

I'm impressed and looking forward to writing applications in java on appengine, I might even port my previous application from python to java. Apparently python and java can run side by side and against the same data.

Tweaks I'd like to see so far:
  • Use of EL by default in JSP pages
  • Saving password when deploying (at least for a session)
  • Deploy dialog to include fields for app name/version
  • Email field in the dialog box to be fixed
  • Maybe some better integration between the wizards and GAEJ (e.g. create servlets)

25 March 2009

Streaming Result with JDBC and MySQL

This is another memory extension:

I'm working on a legacy application with a HUGE amount of meta data (in one table). My poor laptop isn't the best spec'd machine so I'm fairly aware of memory usage of the applications I'm working on and generally don't just throw memory at a problem.

Anyways, I'm working on part of the application that interigates the data and writes output to a file sequentially: basically read-record, write-to-file, read-record, write-to-file, so when I get the OutOfMemoryError exception I take a closer look and see that the entire result set is read into memory on the statement executeQuery before you can read the ResultSet.

This creates an itch, there must be a way of specifying read on row from the database, then use the result and then read the next row. After a bit of searching I found this blog: MySQL JDBC Memory Usage on Large ResultSet and it is also buried in the MySQL connector reference.

And here is the relevant code:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,

Works like a charm.

05 February 2009

Things I will forget [001]

This is for manually inserting a record into a postreSQL database that has been generated by hibernate (JPA).

use '\ds' to sanity check the sequence name ("hibernate_sequence")

In the insert statement's values section, use 'nextval('hibernate_sequence')' as the ids value, like:

insert into tablename (id, name) values(nextval('hibernate_sequence'), 'bob')

<<Blog as memory>>