Hibernate Dialect Considerations

This section provides information on using NuoDB with Hibernate 5. The NuoDB Hibernate Dialect is the main component of the NuoDB Hibernate jar. Even if you choose not to use our Hibernate specific extension to JDBC, you will need the dialect support to work with Hibernate.

Using Automatically Generated Identifiers

Identifiers uniquely identify each specific entity.

NuoDB Auto-Generation Syntax

NuoDB SQL supports the following syntax variations for automatic identifier generation:

CREATE TABLE t1 (id BIGINT GENERATED ALWAYS AS IDENTITY ...
When using this syntax, NuoDB always generates the identifier automatically. 
CREATE TABLE t1 (id BIGINT GENERATED BY DEFAULT AS IDENTITY ...
When using this syntax, NuoDB only generates the identifier automatically when you do not provide a value in your INSERT statement.

In both cases NuoDB automatically generates a sequence (called t1$IDENTITY_SEQUENCE) that is used to generate the keys. Defining the sequence manually is also possible (see next section).

NuoDB’s Hibernate dialect supports autogeneration of keys of type BIGINT, DECIMAL, INTEGER and STRING. In all cases the key is a 64-bit integer stored appropriately for the type.

Recommendations

Java Persistence API (JPA) defines 4 auto-generation strategies. Here are our recommendations for the best usage with our dialect.

For further details and background, each is discussed below. In particular, unlike with most other RDBMS, we do not recommend using the SEQUENCE strategy directly (see Generation Type SEQUENCE).

  1. Use @GeneratedValue(strategy = GenerationType.IDENTITY)

    • Automatically implemented using a dedicated sequence for each table.

      • You can override this by defining the table manually if you prefer.

    • Key generation occurs in the TE and you get full advantage of NuoDB’s distributed sequence implementation (see discussion on SEQUENCE strategy).

    • The ID column is declared as GENERATED BY DEFAULT so you can always provide a key explicitly if you wish.

    • This option may preclude batched inserts (Hibernate 5.3 or earlier) - see below.

  2. The sequence has a default starting value of 1 and a quantum size of 100. To override declare the table and its sequence manually. For example:

    CREATE SEQUENCE seq1 START WITH 1000, QUANTUM SIZE 200;
CREATE TABLE tab1 ( id BIGINT GENERATED BY DEFAULT(seq1), ...);

    If you prefer, use id BIGINT GENERATED ALWAYS AS IDENTITY(seq1).

  3. Another reason to control the underlying sequence might be to ensure unique identifier values across multiple tables. Manually declare 2 or more tables sharing the same sequence:

    CREATE SEQUENCE seq1 ...;
CREATE TABLE tab1 ( id BIGINT GENERATED ALWAYS AS IDENTITY(seq1), ...);
CREATE TABLE tab2 ( id BIGINT GENERATED ALWAYS AS IDENTITY(seq1), ...);
...

Unless you have a particular reason for not doing so, the IDENTITY strategy (possibly using manual table definitions) is the best overall implementation.

Background

Here is how each of the 4 JPA strategies is implemented when using NuoDB, with points to consider for each one.

Generation Type AUTO

Suppose you have the following identifier declaration in your Java entity class:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;

If you use Hibernate’s ability to run DDL and create tables for you, NuoDB’s Hibernate dialect creates tables with the ID column declared as ID INTEGER NOT NULL. In addition Hibernate creates a sequence called hibernate_sequence to generate the identifier values automatically upon insert.

Considerations

  • Hibernate uses the same hibernate_sequence to create keys for all entities using the AUTO strategy. See Generation Type IDENTITY for an alternative.

  • Every new entity requires execution of two SQL statements - first to get the next value from the sequence, the second to insert the new row, using that id.

Generation Type IDENTITY

This is our recommended strategy.

If you specify strategy=GenerationType.IDENTITY and request Hibernate to create tables for you, NuoDB’s Hibernate dialect creates tables with the ID column defined as ID INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL.

The identifier may be specified in Java as an Integer, a Long, a BigDecimal or a String

  • The ID should always be an object, not a primitive type, so it can be null if the entity has no ID yet.

The identifier is a positive 64-bit integer, cast as necessary for the ID column type. Even when the identifier is a string it still holds an auto-generated numeric value.

Considerations

  • Persisting an entity requires just a single INSERT statement. The new key value is returned from the insert using Statement.getGeneratedKeys().

  • By default, each table gets its own sequence for key generation - see above.

  • Prior to Hibernate 5.3, an INSERT is run as soon as EntityManager.persist() is invoked (to set the id in the new entity). This behaviour prevents the use of SQL batching.

  • From Hibernate 5.4, Hibernate is more optimized and, if the id is not used, delays invoking EntityManager.persist() to the end of the transaction, thus allowing batching to be supported.

    • Should this enhancement cause problems, you can disable it by setting hibernate.id.disable_delayed_identity_inserts=true, but this will prevent batching also.

Generation Type SEQUENCE

Sequences in NuoDB are optimized for use in a distributed system.Instead of an INCREMENT BY option, it supports a QUANTUM SIZE. This allows each TE to get batches of values to use, reducing contention for the shared sequence. See CREATE SEQUENCE for full details.

As a result, the sequence values returned by SELECT NEXT VALUE depend on which TE you are connected to and how many other clients that TE is also supplying values to.

Example:

  • Two TEs share a sequence with default quantum size of 100.

  • Both get 100 values to use.

    • TE1 gets 1-100 and TE2 gets 101-200.

  • TE1 is serving two clients fetching values from the sequence.

  • So the values returned to client-1 might be 1, 2, 5, 7 …​ because the other client is getting intermediate values.

  • If client-1 is multi-threaded and getting values from both TE1 and TE2, the values returned might be 1,101,2,5,102,7,103 …​

Hibernate uses sequences with increment > 1 in a similar way to reduce round trips to the database. If a sequence has increment 100, and SELECT NEXT VALUE returns 200, then Hibernate knows that any other client getting a value from that sequence will get a value > 299. Therefore it has a "pool" of values 200-299 that it can use to supply unique keys.

Hibernate calles these "Pooled Sequences" and they are (from V5) the default implementation for the SEQUENCE strategy. When a new entity is persisted, the next value from the pool is set as the id in the entity and in the new row inserted into the corresponding table.

As you can see from the example, this assumption of a contiguous batch of values does not hold with NuoDB so our dialect tells Hibernate that it does not support Pooled Sequences. As a result, Hibernate chooses an alternative implementation using an underlying table to simulate a pooled sequence (equivalent to strategy=GenerationType.TABLE) requiring two database accesses to persist one entity (just like the AUTO strategy) plus an extra write per pool (to increment the next-value held in the table for the next pool).

Considerations

  • The table used for key generation can become a contention hot spot with concurrent inserts by different clients. (NuoDB’s sequences are implemented as they are to avoid precisely this problem.)

  • Requires two (occasionally three) SQL statements to persist one object.

  • Other strategies should be used whenever possible (see Recommendations above).

Generation Type TABLE

This is the explicit JPA strategy to use a table to simulate a sequence. It allows multiple entities to use the same table to implement their "sequence". Each pseudo-sequence is defined by a different row in the same table.

Considerations

  • The table used for key generation can become a contention hot spot with concurrent inserts by different clients.

    • It is recommended to use a different table for each entity (this can be requested via the @TableGenerator.table attribute).

  • Requires two database accesses to persist one entity (just like the AUTO strategy) plus an extra write per pool (to increment the next-value held in the table for the next pool)

  • Other strategies should be used whenever possible (see Recommendations above).

Using Inner Joins

Hibernate may generate SQL containing a parenthesized INNER JOIN syntax that NuoDB does not yet support.

For example, consider a store selling books, represented by Store and Book entity classes. If defined as a one-to-many relationship using @OnetoMany, the default implementation is to define a join table called Book_Store.

To find books with at least one store, Hibernate generates SQL queries such as this:

SELECT book.id AS id ... FROM Book book INNER JOIN
      (Book_Store bookstore INNER JOIN
            Store store ON bookstore.stores_id = store.id)
      ON book.id = bookstore.Book_id

Currently, NuoDB does not implement join grouping with parentheses and expects a sub-query with SELECT after the open bracket:

    …  INNER JOIN (SELECT ... FROM Book_Store bookstore ...

So the generated SQL will cause a SQL grammar exception.

It’s possible to rewrite the query directly in SQL and run using the native query support provided by the Java Persistence API (JPA):

String rewrittenQueryString =
  "SELECT book.id ... from Book book join" +
  " (SELECT * from Book_Stores stores INNER JOIN Store store " +
  "      ON stores.stores_id=store.id) AS stores " +
  "ON book.id=stores.Book_id and (stores.address is not null)";

entityMgr.createNativeQuery(rewrittenQueryString, Book.class) ...

This would run your modified SQL but still recreate Book instances from the results.

SQL Keywords

VER is a reserved word in NuoDB SQL so it cannot be used as a column name.

Although VER is a reserved word, the use of VERSION is not restricted.

STRING is also a reserved word. Any data-member called 'string”' must be mapped to an alternative column name.

NuoDB uses Unicode by default so CHAR / NCHAR and VARCHAR / NVARCHAR are actually equivalent. However, PreparedStatement.setNString() and PreparedStatement.setNCharacterStream() are not currently implemented. Simply use the non-nationalized versions instead.

For details of keywords reserved in NuoDB, see SQL Keywords.

Additional Considerations

Result Sets

Result sets returned by the NuoDB JDBC Driver are always forward only result sets. This means that you can iterate through it only once and only from the first row to the last row.

Unsupported Features

  • NuoDB does not support the ALL operator in WHERE clauses, so do not use it in your criteria queries. Typically you can rewrite the query to achieve the same result.

  • NuoDB does not support LOCK IN SHARED MODE because it uses MVCC by default so in effect data is always shared.

  • The Criteria Query API may generate multi-column sub-queries which are not supported. The generated SQL looks like this:

    SELECT id, name, weight, height FROM Person
    WHERE (weight, height) =
        (select weight, height FROM Players WHERE name = ?)

    You could rewrite this using INNER JOIN or EXISTS.

  • NuoDB does not support CREATE TYPE but you can use a Hibernate UserType instead.

  • NuoDB does not support right outer joins but Hibernate may create queries using them. Reimplement the query using SQL and use JPA’s native query support (as described above).

  • Double negatives can occur in dynamic applications that generate criteria queries on the fly. This can result in generated SQL containing NOT NOT which NuoDB does not support. However, NOT(NOT …​) is supported. Try to rewrite removing both negative conditions.