HqlInterface

HQL

General

• HQL (Hibernate Query Language) is a query language with which classes and objects can be addressed directly without knowing their names in the database (e.g. "project.pr_id" instead of "DI001001 FROM DT461").
• Queries are translated in SQL statements and run internally.
• HQL names can be read from the Data Dictionary, entity names (for tables), or DI Python IDs (for columns).

• Via HQL, only SQL queries are transmitted directly to the database. Further PLANTA Project functionalities like, e.g., dependent saving or deletion is therefore not possible.
• If, e.g., a project is deleted via HQL, the subordinate project structures are not deleted automatically as well.
• At the moment, PLANTA project generally uses Hibernate version 4.3.4 with the contained functionalities including HQL: http://hibernate.org/orm/documentation/4.3.

Module HQL

• In order to be able to use HQL, the hql module must be imported first.

from hql import hql

or (preferred)

import hql

Methods

• There are three functions available for HQL.

search_record

• This method returns a list of DTP records

hql.search_record(String hql)

before = hql.search_record("from Project project where project.pr_id='{}'".format(id))

query

• This method returns a list of data (in list form) according to db_select().

hql.query(String hql)

before = hql.query("select project.pr_id, project.project_note from Project project where project.pr_id='{}'".format(id))

modify

• With hql.modify you can carry out update or delete statements. The number of changes made to the DB is returned, according to db_modify().

hql.modify(String hql)

hqlString="update Project project set project.project_name='{}' where project.pr_id='{}'".format(project2.rec.project_name.get_value(), project1.pr_id)
hql.modify(hqlString)

hqlDelete = "delete Module m where m.id_ = '{}'".format(module.id.get_value())
result = hql.modify(hqlDelete)

• In HQL properties are only identified via Python IDs.
• Error rendering macro 'fontawesome-macro' : Page loading failed
  Names like "id","class", "enum", "const" are reserved for Hibernate and Java. All Python IDs that use this name can be accessed in HQL with Python ID and underscore, e.g. ("id_")
• Instead of table names, the names of entities are used.

HQL Documentation

• In HQL, the entity name is used instead of the table name, and instead of the column name, the property name is used.
  • You can look up the entity name in the Data Dictionary (Entity name field). If it is not listed, the entity name is DTxxx.
  • Currently, the entities in PLANTA project are not yet linked via associations. I.e., joins between them cannot be used.
• The property name is the same as that of the DI Python ID. If it is not listed, the property name is DIxxxxx.

hql.search_record(" from Project p where p.pr_id = '4711' " )

• In HQL, UUIDs are to be handed over as strings

 import hql
from ppms import ppms
 
project_record = ppms.search_record(461, ['4711'], ['uuid'])
uuid = project_record.uuid.get_value()
 
query = "SELECT project.project_name from Project project where project.id = '{uuid}'"
 
result = hql.query(query.format(uuid=uuid))
 
ppms.ui_message_box(result) # [['Elektrohängebahn']]

• For further information, refer to the official HQL documentation.

Advantages of hql.search_record and ppms.search_record

ppms.search_record()

• Only leads to the database if the record does not exist entirely.

hql.search_record()

• Search on more than one record.
• Search is not restricted to the primary key but can do everything that is also possible in an HQL where-clause.