Openbravo ERP HQL prototyping: Hibernate Tools

Note: In Openbravo 3 we upgraded Hibernate library to 3.6.x version. It seems this new version of Hibernate is not already supported by Hibernate Tools Eclipse’s plugin. So till they support it, Openbravo developers will not be able to use it 😦

 

With the inclussion of DAL in 2.50, Openbravo ERP provides a hibernate layer for accessing database. Complex queries can be executed in database using HQL. The purpose of this post is to explain how to configure Hibernate Tools to help to write HQL queries for Openbravo model.

Why

Before DAL, Openbravo’s database was always accessed using sqlc; this consists on writing SQL statements in a XML file that is transformed into a Java class to execute these queries exactly as they were written. Queries were typically prototyped in a database client (such as pgAdmin or SQL Developer) before moving them to XSQL files. These clients offer some advantages such as syntax checking, code completion and query execution which allows you to see whether the query works as you expected.

Now, with DAL there are other ways of prototyping.

HQL Query Tool is an Openbravo ERP module which facilitates this task using DAL. It is a nice tool where you can prototype HQL queries, execute them to see the result and see the properties each entity in Openbravo ERP has.

Hibernate Tools is an Eclipse plugin which can be easily configured to work together with Openbravo ERP. In addition to the advantages HQL Query Tool module has, this plugin offers code completion, which is really great. It also shows while you are typing your HQL query, the SQL that Hibernate will execute.

Hibernate Tools installation

The installation is really simple:

  1. Go to Help > Install New Software… > Add… and include this update site http://download.jboss.org/jbosstools/updates/stable.
  2. Select Hibernate tools and install it.

You can read additional information in the Hibernate Tools Reference Guide.

Configuration

Now it’s time to configure it to work with Openbravo ERP.

  1. Edit Openbravo.properties file and set hibernate.hbm.file property. This will generate the mapping file, if it is not set it is generated in memory but not persisted. You must set here a file within your Openbravo Eclipse’s project, for example path_to_your_OB_sources/config/ob.hbm.xml. Compile the application (ant smartbuild) or copy Openbravo.properties to WebContent/WEB-INF.
  2. Start Openbravo ERP, this will generate the file set in the previous step. Refresh project if you don’t have auto refresh set.
  3. Configure hibernate.cfg.xml file. There is a template for this file in config/eclipse directory, just copy it and edit to set your database properties. Note for Oracle users: hibernate.default_schema property must be set like your database username in upper case.
  4. Open Hibernate Perspective. Window > Open Perspective > Other… > Hibernate.
  5. In Hibernate Config view click on Add Configurationbutton. Complete the following parameters:
    • Project: openbravo
    • Configuration file: select your hibernate.cfg.xml file.
    • In Mappings tab add your hbm file (the one configured in step 1).

Prototyping HQL

Now you can start prototyping HQL. Click on the Open HQL Editor button in the Hibernate Config view and a HQL editor form will be open, there you can type your query with code completion, execute it to view the results (Hibernate Query Result view), view the generated SQL (Hibernate Dynamic SQL Preview view), see all the entities with their properties (Hibernate Config view, Configuration folder), etc.