HQL

General

Information

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

Note

  • 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

Information

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

Example (classic)

from hql import hql
CODE

or (preferred)

import hql
CODE

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

Example

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

query

Details

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

Example

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

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

Example for update:

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

Example for delete:

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

Note

  • 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, e.g. ("id_")
  • Instead of table names, the names of entities are used.

HQL Documentation

Information

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

Example

hql.search_record(" from Project p where p.pr_id = '4711' " )
CODE
  • In HQL, UUIDs are to be handed over as strings

Example

 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']]
CODE

Note

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.