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.
Identifiers uniquely identify each specific entity.
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
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
In all cases the key is a 64-bit integer stored appropriately for the type.
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
@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).
IDcolumn is declared as
GENERATED BY DEFAULTso you can always provide a key explicitly if you wish.
This option may preclude batched inserts (Hibernate V5.3 or earlier) - see below.
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).
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.
Here is how each of the 4 JPA strategies is implemented when using NuoDB, with points to consider for each one.
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.
Hibernate uses the same
hibernate_sequenceto create keys for all entities using the
AUTOstrategy. 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.
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
BigDecimal or a
IDshould always be an object, not a primitive type, so it can be null if the entity has no
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.
Persisting an entity requires just a single
INSERTstatement. The new key value is returned from the insert using
By default, each table gets its own sequence for key generation - see above.
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.
Sequences in NuoDB are optimized for use in a distributed system.Instead of an
INCREMENT BY option, it supports a
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.
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
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
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).
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).
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.
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
Requires two database accesses to persist one entity (just like the
AUTOstrategy) plus an extra write per pool (to increment the
next-valueheld in the table for the next pool)
Other strategies should be used whenever possible (see Recommendations above).
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
Book entity classes.
If defined as a one-to-many relationship using
@OnetoMany, the default implementation is to define a join table called
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.
VER is a reserved word in NuoDB SQL so it cannot be used as a column name.
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
NVARCHAR are actually equivalent.
PreparedStatement.setNCharacterStream() are not currently implemented.
Simply use the non-nationalized versions instead.
For details of keywords reserved in NuoDB, see SQL Keywords.
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.
NuoDB does not support the
WHEREclauses, 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 MODEbecause 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
NuoDB does not support
CREATE TYPEbut you can use a Hibernate
For simple type aliases, NuoDB does support
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 NOTwhich NuoDB does not support. However,
NOT(NOT …)is supported. Try to rewrite removing both negative conditions.