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).
|
-
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 asGENERATED 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.
-
-
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)
. -
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 theAUTO
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 noID
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 usingStatement.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 asEntityManager.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 thenext-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 inWHERE
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
orEXISTS
. -
NuoDB does not support
CREATE TYPE
but you can use a HibernateUserType
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.