HQL

General

• HQL (Hibernate Query Language) is a query language, with the help of which you can directly address classes and objects without having to know 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).

Notes

• 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 either.
• 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.

HQL Module

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

Example (classic)

from hql import hql

import hql

Methods

Information

• There are three functions available for HQL.

search_record

Details

• This method returns a list of DTP records.

hql.search_record(String hql)

Example

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

query

Details

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

hql.query(String hql)

Example

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

modify

Details

• 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)

Update example:

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)

Delete example:

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

Notes

• In HQL, properties are only identified via Python IDs.
• 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, z.B. ("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.
  • In PLANTA project, entities are currently not linked via associations. I.e., joints between them cannot be used.
• The property name equates to that of the DI Python ID. If it is not listed, the property name is DTxxx.

Example

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) # [['Electronic Overhead Conveyor']]

Note

• For further information on that topic, please see the official HQL documentation.

Advantages of hql.search_record and ppms.search_record

ppms.search_record()

• Only accesses the database in case the record does not exist.

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.