HQL Coding Conventions

Contents 1 Overview 2 Formatting 2.1 Indentation and formatting convention 3 SQL Injection (Report if found any) 4 Generated constants 5 Other

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:

• Reserved words should be in lowercase
• First line of HQL String must be on a new line
• Subqueries should have at least 2 more whitespaces as indentation relative to parent query and should start in a new line.
• Indentation is a must for readability, we normally use this indentation(In whitespaces):
  • Select: 0
  • From, joins: 2
  • Where, group by, order by: 1
  • And, or, having: 2
  • Subqueries: 2

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

• Use setMaxResults to limit the numbers of rows outputted by a query. It improves performance drastically in some situations.
• Don't use "toString()" in query call.