View source | Discuss this page | Page history | Printable version   

HQL Coding Conventions

Contents

Overview

This document gives a description of the HQL coding standards and coding principles used in the development of Openbravo.

There is a tool that checks for possible problems of HQL/SQL injection that only works with normal Strings. In OB is mandatory to use always normal Strings, no StringBuilders/StringBuffers.

Formatting

Formatting HQL is important for readability. It's important to set formatter off and on for every formatted HQL string query.

Bad example(avoid):

      final String qryStr = "select t.table.id, wa.editableField"
          + " from ADTab t" + "  left join t.window w"
          + "  left join w.aDWindowAccessList wa" + " where wa.role.id= :roleId";
      final Query<Object[]> qry = SessionHandler.getInstance()
          .createQuery(qryStr, Object[].class)
          .setParameter("roleId", getRoleId());
      final List<Object[]> tabData = qry.list();

Good example(recommended):

      // @formatter:off
      final String qryStr = 
              "select t.table.id, wa.editableField" +
              " from ADTab t" +
              "  left join t.window w" +
              "  left join w.aDWindowAccessList wa" +
              " where wa.role.id= :roleId";
      // @formatter:on
      final Query<Object[]> qry = SessionHandler.getInstance()
          .createQuery(qryStr, Object[].class)
          .setParameter("roleId", getRoleId());
      final List<Object[]> tabData = qry.list();

Indentation and formatting convention

These are the indentation and formatting rules that we use:

Joins, and, or, having and subqueries are indented relative to their current block, for example, if a join follows a from, from is indented with 2 whitespaces, then the join is indented with 4 whitespaces(2 from from + 2 from join).

Formatted HQL query following this convention:

    //@formatter:off
    String hql =
            "select attr1 as at1, attr2.innerAttr, attr3, attr4 " +
            "  from ADTable table1" +
            "    inner join ADTab1 " +
            " where table1.active=false " +
            "   and ro.role.id=:roleId" +
            "   and ro.organization.active=true " +
            " group by attr1, attr2... " +
            "   having attr1=true " +
            "     and attr2='something'" +
            " order by attr2, attr4 ";
    //@formatter:on

Formatted HQL query with subquery:

    //@formatter:off
    String hql =
            "select attr1 as at1, attr2.innerAttr, attr3, attr4 " +
            "  from ADTable table1" +
            "    inner join (" +
            "      select subAttr1, subAttr2 as sub2, subAttr3 " +
            "        from ADSub sb " +
            "       where sb.attr1='something' " +
            "         and sb.attr2='anotherThing' " +
            "       group by subAttr1" +
            "        having count(subAttr1) > 0" +
            "         and sb.attr = ... " +
            "       order by subAttr1, ... )" +
            " where table1.active=false " +
            "   and ro.role.id=:roleId" +
            "   and ro.organization.active=true " +
            " group by attr1, attr2... " +
            "   having attr1=true " +
            "     and attr2='something'" +
            " order by attr2, attr4 ";
    //@formatter:on

SQL Injection (Report if found any)

To avoid SQL injections it is recommended to always use bind parameters. It is totally forbidden to concatenate parameters in SQL.

Bad example(avoid):

 
   private static boolean hasProcessingColumn(String strTableId) {
     ....
     String hql = " select count(AD_Column_ID) from ADColumn where table.id = '" + strTableId + "' "
     + " and lower(dBColumnName) = 'processing'";
     Query<Long> query = OBDal.getInstance().getSession().createQuery(hql, Long.class);
     ....
   }

Good example(recommended):

 
   private static boolean hasProcessingColumn(String strTableId) {
     ....
  String hql = 
          "select count(AD_Column_ID)" +
          " from ADColumn" +
          " where table.id = :tableId " +
          "   and lower(dBColumnName) = 'processing'";
  Query<Long> query = OBDal.getInstance().getSession().createQuery(hql, Long.class);
  query.setParameter("tableId", strTableId);
     ....
   }

IMPORTANT: Those are CRITICAL to NEVER ignore. It does NOT matter if the value looks safe. Use parameter always.

Generated constants

Avoid the use of generated constants. When in need of generated constants, use the value directly in HQL query.

Bad example(avoid):

    final Query<String> qry = SessionHandler.getInstance()
        .createQuery("select o.id from " + Organization.class.getName() + " o where " + "o."
            + Organization.PROPERTY_CLIENT + "=:client", String.class);

Good example(recommended):

    /* Removed Organization.class.getName() and PROPERTY_CLIENT constant */ 
    // @formatter:off
    final String orgQryStr = 
            "select o.id" +
            " from Organization o" +
            " where o.client=:client";
    // @formatter:on

Other

Retrieved from "http://wiki.openbravo.com/wiki/HQL_Coding_Conventions"

This page has been accessed 8,085 times. This page was last modified on 7 February 2020, at 08:14. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.