Open topic with navigation
This section provides information on using NuoDB with Hibernate 5.
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
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.
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
@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
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:
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.
Hibernate may generate SQL containing an
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 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.
VER is a reserved word in NuoDB SQL so it cannot be used as a column name.
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
NVARCHAR are actually equivalent. However,
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.
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
NuoDB does not support
CREATE TYPE but you can use Hibernate UserType instead.
For simple type aliases, NuoDB does support
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.