Saturday, September 3, 2011

SQL select to web application in couple of seconds

Maybe you are not aware but if you just know how to write one sql select statement, you're just couple of seconds from having an web application and couple of minutes from enjoying it as a Liferay portlet.
The key ingredients for this recipe are: Openxava, Liferay and ... Minuteproject.

The equation is:
sql select statement (containing one field considered as unique) = Openxava (Webapp) + Liferay (Portlet)
in less than 5 minutes.

  • Download Openxava (4.2.2), Liferay, Minuteproject (
  • Set environment variables OX_HOME and MP_HOME to Openxava and Minuteproject home directories respectively.
  • Use Java 6
Create your statement
  • Create a sql select statement
  • Ensure that one of the return field can be considered as unique (if not add one)
  • Create a view from your statement
    • Ex: CREATE VIEW abc as
Why is it necessary to have a unique field?
It is to ensure that MP can place a @Id JPA2 annotation on one field of the generated entity.
It has to be unique to avoid issue dealing with first level cache.

Start Minuteproject console
  • Point to your model and fill your model name (ex: efg)
  • select your view abc (in customisation tab)
  • add the unique field as 'virtual' primary key. Your unique field is not a PK but it will acts as to uniquely identify a record entry.
  • Choose target openxava and click generate.
Release your application
  • go to the generated code
  • check environment variables (OX_HOME, MP_HOME) are set.
  • execute from a command prompt: build-efg.cmd/sh
  • depending on the OS from couple of seconds to a dozen of seconds and a browser should open to the address http://localhost:8080/egf/xava/homeMenu.jsp
Deploy on Liferay
  • go to OX_HOME/workspace.dist/efg.dist/efg.war
  • add ejb.jar and jdbc driver jar in LIFERAY_HOME/tomcat/lib
  • start Liferay
  • Upload your portlet
  • Install your portlet
  • Enjoy
Whether on the standalone tomcat app or on the Liferay deployed portlet, Openxava is only offering the user selection. It is normal since the DB object is a view, Minuteproject 4 Openxava suppress the Create/Update/Delete, just the Select functionality is kept.


As model I take the lazuly model project in version 0.1 it contains 13 tables.
The sql to create the schema on mysql can be downloaded at

Create your view from your sql select statement

Start MinuteProject console

Scope your model

Choose your technology: here Openxava, and click on Generate.
The code will be generated in MP_HOME/output/conference/OpenXava

Make a standalone Openxava app
Go to the generated code
Open a prompt
Check or set OX_HOME and MP_HOME
execute build-conference.cmd/sh
Couple of seconds later a browser should open to the model (conference) menu.
Report tab has one entry 'my conference cool report'.
When clicking on it here is a sample screen with data that you could see:

Deploy on Liferay
  • Add the datasource (the snippet for tomcat is in OX_HOME/workspace/conference/OpenXava/openxava/other/tomcat/snippet/conference_jndi_for_tomcat_snippet.txt is to be inserted into LIFERAY_HOME/tomcat/conf/context.xml)
  • Upload the portlet war (OX_HOME/workspace.dist/conference.dist/conference.war
  • Position it
  • Enjoy.

Other information

You can also find other information regarding Minuteproject 4 Openxava generation at

  • Your model is your technology tutorial
  • Productivity can be greatly improved
  • Data Quality and troubleshooting operations are quickly available
  • Keep on improving send feed-back to or tweet @minuteproject