Using Hibernate Object/Relational Persistence with CA IDMS

Document ID : KB000051640
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

This document describes how to use the Hibernate open-source object/relational persistence and query service with CA IDMS Server and CA IDMS. Hibernate eases development of Java applications by allowing developers to work with data objects rather than SQL tables, rows and columns. Rather than having to write SQL statements and calls to JDBC drivers, developers write code to invoke Hibernate services for all data access. In most instances, a Java developer need not know about CA IDMS specifics, or even that CA IDMS is the DBMS. Hibernate is available as a download from Red Hat, Inc.

Solution:

Overview of Hibernate

Hibernate is an open-source software product that provides an Object-Relational Mapping (ORM) solution for Java developers. With Hibernate, developers are able to work with data as Java objects rather than as the rows and columns of a relational database. Developers generally do not have to code JDBC calls or SQL statements and don't have to be concerned with the syntax and behavior of a particular DBMS. The application code thus becomes DBMS-agnostic. A special Java class known as a dialect, unique to a particular DBMS, specifies the data types, functions and features supported by the DBMS.

Implicit in an ORM solution is the concept of persistence. As it applies to Hibernate, persistence describes the process of retrieving data from a DBMS (almost always relational), caching it for use, and ultimately writing new or updated data to the DBMS prior to Java program termination.

Much of the difficulty of persisting Java objects is that the business data model usually does not coincide with the SQL schema. When designing the business data model, programmers take advantage of Java capabilities, such as inheritance and polymorphism, that do not exist in the relational model. This means that a single row in a relational table can contain multiple entities, or business objects. A side effect of this is that entities can have far more relationships with other entities than are represented by constraints in the SQL schema. Hibernate has sophisticated features, including "automatic dirty checking" and "cascading save", which hide this mismatch in data models from the Java programmer.

In most cases, Hibernate takes care of building SQL statements and making the appropriate calls to the JDBC driver. The developer invokes Hibernate methods to load and save data as well as to acquire sessions and start transactions. The developer can also code JDBC calls and SQL statements if needed for special cases. A simplified query language called Hibernate Query Language (HQL) provides the ability to code custom queries in a DBMS-agnostic way. Using the dialect associated with the DBMS, Hibernate translates an HQL query into the appropriate SQL syntax.

The developer must also provide mapping metadata that relates each Java class representing a business entity to its respective relational tables and columns. This metadata can be written either in XML files or directly within the Java code using the Annotations feature of Java 5.0 or greater. Hibernate provides tools that automate this for existing databases. Connection properties are supplied either in XML format or as Java properties.

Other Benefits of Using Hibernate with CA IDMS

Aside from the coding advantages discussed above, use of Hibernate with CA IDMS provides some additional functionality which is not available in current releases of CA IDMS (r17) and CA IDMS Server (r17), specifically:

  • Detached Objects - Data is loaded from CA IDMS as Java objects and remains in Hibernate's persistent cache for as long as a Java program maintains a reference to it. Modified data is automatically written back to CA IDMS at the end of a transaction. This occurs when the commit() method is invoked on the Hibernate Transaction object.

  • Optimistic Concurrency - This concurrency control method is based on the assumption that there is very little risk of conflicting updates among concurrently executing database transactions. Rather than locking rows in the database as would be typical for "pessimistic" concurrency, optimistic concurrency employs a versioning technique to detect the possibility of lost updates, and gives the programmer an opportunity to take corrective action. Optimistic concurrency is particularly applicable when using detached objects because of the deadlocks that could result from holding locks for the relatively long period that objects could be detached.

  • Repeatable Read - Because persistent entity instances reside in a Hibernate cache for the duration of a unit of work, they can be loaded from the cache when requested by a Java program, and no database request takes place. This is true even when using queries such as HQL. In addition to providing efficiencies in network and DBMS utilization, this technique simulates repeatable reads.

  • Named Parameter Binding on Queries - A named parameter is specified in an SQL command string by an identifier prefixed with ":". This is in contrast to the usual practice of using a "?" marker for each parameter. In either case, the actual value must be bound to each parameter by calling the appropriate "setXXXX" method on the PreparedStatement object. The advantage of named parameters is that the setXXXX methods can specify the ":" name instead of an ordinal index corresponding to a parameter marker's sequence in the SQL command string. This is of particular value when dealing with SQL commands that have large numbers of parameters, in which case it's easy to specify an incorrect index in a setXXXX method call. Hibernate provides named parameter binding for all HQL and native SQL queries, transparently to the JDBC driver. Note that named parameters on CALL statements for procedures are supported natively by CA IDMS Server r17.

CA IDMS Software and Configuration Recommendations

  • Prerequisite Software

    CA IDMS SQL and CA IDMS Server are required. Customers should use CA IDMS Server r17 and CA IDMS r17. CA IDMS Server r17 includes the Hibernate dialect as part of the product's idmsjdbc.jar file. CA IDMS r17 supports the standard outer join syntax that Hibernate generates. Prior releases of CA IDMS provide some limited join functionality - see the section below on Join Syntax in CA IDMS r16 and earlier.

  • Setup

    See the CA IDMS Server r17 User Guide for information on how to set Hibernate properties for the CA IDMS Server JDBC driver and dialect.

    The following CA IDMS Server options are recommended for most Hibernate applications. See the CA IDMS Server User Guide for further information.
    CloseCommit=1TxnIsolation=2Readonly=0
    Hibernate makes frequent use of JDBC metadata methods. The getTables() method, for one, causes a query to be run against the SYSCA.ACCESSIBLE_TABLES view in the CA IDMS CV. For performance reasons, it may be advisable to create one or more clones of this view, customized for particular applications. The customized view can then be referenced in the JDBC driver by using the CatalogTable option. Note that for most Java applications, CA IDMS Server options are specified using standard Java properties files; however, when using the CA IDMS Server JDBC driver as a type 2 driver, options are normally specified in an ODBC-style data source configured using the CA IDMS Server ODBC Administrator.

  • Connection Pooling and Statement Caching

    For compatibility with CA IDMS, connection pooling properties, such as used with the open-source C3P0 software, should be configured so that the "minimum connections" property is set to 0. In C3P0, the relevant property is hibernate.c3p0.min_size.

Runtime Considerations

  • Join syntax in CA IDMS r16 and earlier

    Table join syntax in CA IDMS r16 and earlier does not conform to ANSI/ISO/IEC 1999 standards. Inner joins do conform to the theta-style join syntax in which tables to be joined are separated by a comma and matching criteria are specified in a WHERE clause. Accordingly, theta-style joins within Hibernate will work with CA IDMS r16 and earlier.

    In CA IDMS r16 and earlier, left and right outer joins (full outer joins are not supported) are accomplished using the PRESERVE parameter:
    SELECT E.FIRST_NAME, E.LAST_NAME,  S. FIRST_NAME, S.LAST_NAME  FROM EMPLOYEE E, RELATION S  WHERE E.EMP_ID = S.EMP_ID  PRESERVE E
    The equivalent ANSI syntax would be:
    SELECT E.FIRST_NAME, E.LAST_NAME, S. FIRST_NAME, S.LAST_NAME FROM EMPLOYEE E LEFT OUTER JOIN RELATION S ON E.EMP_ID = S.EMP_ID
    The PRESERVE parameter is a CA IDMS extension to standard SQL and cannot be generated by the dialect or related classes. This unique syntax presents problems to the Hibernate programmer when loading entities and running queries, but there are ways around them.

  • In most instances, Hibernate employs lazy fetching or lazy loading, meaning that data is loaded from the DBMS only when absolutely necessary. This is particularly relevant when dealing with collections or associations of objects which span multiple tables in the DBMS. An example of this might be a department entity plus all of the employee entities assigned to that department. In the DBMS, this would equate to a row from the DEPARTMENT table plus all the rows from the EMPLOYEE table which had a foreign key equal to the primary key of the DEPARTMENT row. With lazy fetching, the department entity would generally be fetched separately from the employee entities, and this would work fine with CA IDMS. The problem can come when a programmer employs eager fetching. For instance, the programmer could decide that when a department entity is loaded that all of the associated employee entities should be loaded as well, perhaps to make all of the data available for use as detached objects. This can result in a left outer join operation that will not work with CA IDMS. There are two ways of getting around this problem.

  • Specify fetch="select" in the metadata for associations or collections. This causes Hibernate to use a separate SELECT statement to load the association or collection.

  • Write a native SQL statement to perform the join and place it in one of the metadata xml files, e.g.:
    <sql-query name="loadDept">    <return alias="d" class="demoEmpl.Department"/>       <return-join alias="e" property="d.employees"/>         select {d.*}, {e.*}             from demoempl.department d,         demoempl.employee e          where d.dept_id = e.dept_id          and d.dept_id = ?         preserve d</sql-query>
  • Queries which contain outer joins cannot be accomplished either with the Criteria API, in which selection criteria are supplied as arguments to the methods of the API, or with HQL. However, outer joins can still be done using native SQL queries. Hibernate permits programmers to completely code an SQL statement and execute it through the Hibernate API, thereby gaining complete flexibility of the SQL syntax, but keeping the other benefits that Hibernate offers. Here is an example:
    String sql = "SELECT * FROM DEPARTMENT D, " +          "EMPLOYEE E " +          "WHERE E.DEPT_ID = D.DEPT_ID " +         "AND E.STATUS = 'L' " +         "PRESERVE D";Iterator pairs = session.createSQLQuery(sql)         .addEntity("d", Department.class)         .addJoin("e", "d.employees")         .list()          .iterator();
  • Here we see that the SQL command has been completely coded by the programmer, including the PRESERVE clause, but instead of retrieving a result set as would be the case with JDBC and SQL, the selected department rows and any corresponding employee rows are retrieved as objects into a Java Iterator object, as would typically be the case had the query been done with HQL or the Criteria API. From this point, the programmer can work with the objects as usual.

  • Keys and ID generation

    CA IDMS is designed to work with natural keys (part of the business data) as opposed to surrogate key types such as Sequence or Identity. However, automatically generated keys can still be supported using Hibernate's TableHiLoGenerator class. Developers can implement this by specifying the following in their mapping metadata files for id properties:
    <generator class="org.hibernate.id.TableHiLoGenerator"><param name="table">schema.table</param>
    schema.table specifies the name of a table in CA IDMS consisting of a single column named next_hi. Performance may be enhanced by defining a unique table of this type for each application.

  • Transaction isolation and locking

    Hibernate's optimistic concurrency works well with CA IDMS data, preferably with either a version or timestamp property defined in the mapping metadata for the entity. This permits CA IDMS data to be referenced as detached objects using a session-per-request access strategy, i.e. the original "fetching" session is closed and a later one opened if the data is to be updated.

    Developers working with CA IDMS are likely to be using legacy schemas which cannot be easily changed, making it difficult to add columns to contain version or timestamp properties. It is still possible to utilize optimistic concurrency without modifying the existing schema by defining an entity's mapping metadata with optimistic-lock="all" or optimistic-lock="dirty". This will only work within the bounds of a single session, so it's not possible to use this technique with detached objects. Note that these optimistic-lock settings also require dynamic-update="true", since Hibernate must generate SQL UPDATE statements dynamically instead of building them at startup.

    There are some instances in which a Hibernate programmer might want to explicitly lock rows in the database by invoking the session.lock or session.load methods and specifying LockMode.UPGRADE. This technique relies on the FOR UPDATE and FOR UPDATE OF column-name clauses of the SELECT statement. CA IDMS uses these clauses to determine the SQL access plan and for positioned update, but not for locking. The result is that it's not possible for CA IDMS to support this feature. When CA IDMS Server connections are set up with the options recommended above, CA IDMS will apply a shared lock to the only row of a single-row result set, regardless of whether FOR UPDATE is specified or not; for a multiple row result set, the shared lock is applied to the most recently-fetched row. This technique, referred to as cursor stability, was designed with positioned update in mind, not explicit locking.

  • Network databases

    One of the most powerful features of CA IDMS is the ability to use SQL DML commands to access legacy network databases. The Accessing Non-SQL-Defined Databases chapter in the CA IDMS/DB SQL Option Reference manual contains detailed information about SQL access to network databases.

    An important consideration is the handling of sets in network databases. CA IDMS uses sets to define relationships between network records. Although these are roughly equivalent to referential constraints, sets are implemented with pointers and a member of the set (analogous to a row in a referencing table) does not generally contain an embedded foreign key. Rather, a member accesses its owner record, or vice-versa, through the set pointers. As a result, an SQL syntax extension must be used in the WHERE clause to identify the set when joining tables:
         SELECT * FROM EMPLOYEE, COVERAGE     WHERE "EMP-COVERAGE" AND EMP_ID_0415 = ?
    This extension is of limited value when updating, deleting or inserting records and requires native SQL coding. A better alternative is to actually embed foreign keys in member records and redefine the sets to include primary and foreign key specifications. Although this requires restructuring the database and changing application programs that reference the affected records, it allows the use of standard SQL to join tables, and to update, delete and insert rows. Adding primary and foreign keys in this manner also greatly improves the usefulness of the reverse engineering tool, as discussed below.

  • Miscellaneous

    CA IDMS does not support comments within SQL statements. You must set the Hibernate use_sql_comments property to false.
    CA IDMS does not support sub-queries in the SQL Select column projection list. The following syntax is invalid:
        SELECT    (SELECT COUNT(*) FROM ITEM I    WHERE I.ITEM_ID = ITEM_ID)    FROM BID

Hibernate Tools

The Hibernate tools include a schema generation and export utility, as well as several reverse engineering utilities that read an existing schema and generate both the mapping metadata files as well as the Java source code for the entity classes. While all of these tools offer value for CA IDMS users, there are some issues to consider when using them.

CA IDMS does not currently support the standard DDL that is generated by the schema generation utility. You can export the generated DDL to a file, modify it to be compatible with CA IDMS, and use the CA IDMS Batch Command Facility to define the SQL schema and tables on the CA IDMS CV. Some of the things to consider are:

  • CA IDMS does not support the PRIMARY KEY clause in the CREATE TABLE statement. Primary keys are defined using CREATE UNIQUE CALC or CREATE UNIQUE INDEX statements instead.

  • Hibernate generates most primary key columns with a type of "ID", which is not supported. An alternative is to use a numeric type such as LONGINT.

  • CA IDMS does not support table and constraint names >18 chars.

  • VARCHAR column lengths are limited by page size.

  • Primary keys are limited to 256 characters, which can be a problem for composite primary keys generated by Hibernate.

  • CA IDMS uses ADD/DROP CONSTRAINT instead of ALTER TABLE for adding and deleting constraints.

  • CA IDMS does not support the UNIQUE clause in a column definition. A column which doesn't permit duplicates must be specified with CREATE UNIQUE CALC or CREATE UNIQUE INDEX.

  • CA IDMS does not support the FOREIGN KEY clause in the constraint definition.

Note that these considerations are based on the DDL syntax supported by CA IDMS r17. Future releases may provide enhancements which eliminate some of these incompatibilities.

The reverse engineering tools are more straightforward to use. The main concern here is with the jdbcconfiguration and hbm2hbmxml tools which actually read the legacy schema(s) and build the mapping metadata xml files. Once these files have been built (and any necessary manual corrections have been made), the hbm2java tool, which reads the xml files and builds the Java source code for entity classes, will work perfectly fine.

  • Reverse engineering from SQL schemas

    Relationships between tables in SQL schemas are defined by referential constraints. The Hibernate tools use the primary and foreign key metadata functions supported by CA IDMS Server to discover these relationships and build the associated xml elements. However, if a table contains no unique index or calc key, the tools will not discover a primary key and will instead build a composite key composed of all of the columns in the table. This may or may not be appropriate. All xml files should be reviewed prior to use to verify that satisfactory values have been created by the tools.

  • Reverse engineering from network schemas

    Relationships between records in network schemas are defined by sets, which are not recognized as referential constraints unless they contain primary and foreign key definitions as described above. When network sets contain these definitions reverse engineering should work as it does for SQL schemas. If not, the set relationships will not be reflected in the xml output, i.e. no set, one-to-one or many-to-one elements will be generated.