Hibernate Dialect Considerations

This section provides information on using NuoDB with Hibernate 5.

Using Automatically Generated Identifiers

Identifiers uniquely identify each specific entity. NuoDB SQL supports the following variations of automatic identifier generation:

CREATE TABLE t1 (id BIGINT GENERATED ALWAYS AS IDENTITY ...

Note: When using this syntax, NuoDB always generates the identifier automatically.

CREATE TABLE t1 (id BIGINT GENERATED BY DEFAULT AS IDENTITY ...

Note: When using this syntax, NuoDB only generates the identifier automatically when you do not provide a value in your INSERT statement.

Now, 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 it creates a hibernate_sequence sequence to generate the identifier values automatically upon insert.

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, or a string. Even when the identifier is a string it still holds an auto-generated numeric value.

Using Sequences

Pooled Sequences, that is sequences with increment > 1, are not implemented by NuoDB in the same way as other RDBMS. Instead, sequence values are managed in batches (quantums) by each TE rather than by the client application.

So instead of using @SequenceGenerator, use @GeneratedValue(strategy = GenerationType.IDENTITY) which is implemented using a sequence in the TEs. The sequence uses a default quantum size of 100. Multiple TEs can use the same sequence to determine their next unique block of 100 key values, enabling concurrent inserts. Simply create the table using id BIGINT GENERATED ALWAYS AS IDENTITY for the id column.

Or, if you wish to occasionally set the id value manually, use GENERATED BY DEFAULT instead. For example, when ingesting data from an external system, where the identifier values are already set, but then adding new data allowing NuoDB to set the value.

To control the underlying sequence (for example you may wish to ensure unique identifier values across multiple tables) use the following:

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

NuoDB’s Hibernate dialect disables support for Hibernate pooled sequences for this reason.

NuoDB supports sequence generators but the allocation size is always 1. For example:

@SequenceGenerator(name="seq1", initialValue=1000)

If using Hibernate’s ability to run DDL and create the sequence for you, it generates:

CREATE SEQUENCE seq1 START WITH 1000

If you set the allocationSize property, it is ignored but an error message is output.

Using Inner Joins

Hibernate may generate SQL containing an 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 you can then run using JPA’s native query support:

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.

Note: 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.

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 Players WHERE name = ?)

You could rewrite this using INNER JOIN or EXISTS.

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

For simple type aliases, NuoDB does support CREATE DOMAIN.

NuoDB does not support Right outer joins but Hibernate may create queries using them.

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.

INSERT DEFAULT VALUES may not work with NuoDB 3.2 or earlier.