Hibernate Dialect Considerations
This section provides information on using NuoDB with Hibernate. As mentioned previously we recommend you use JPA (the Jakarta Persistence API) rather than Hibernate directly.
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 the JDBC driver, you will need the dialect support to work with Hibernate.
Dialect Resolution
From V23.0.4-hib6 and V22.0.5-hib5 dialect resolution can be used.
-
Previously you had to specify the dialect explicitly using the
hibernate.dialect
property.
Dialect resolution allows Hibernate to determine the dialect automatically based on the underlying database.
-
To enable resolution, you must not set
hibernate.dialect
any more; otherwise it takes precedence and resolution does not occur.
If using Hibernate 5, you must also set the following two properties in your hibernate.properties
file or resolution will not occur:
hibernate.dialect_resolvers=com.nuodb.hibernate.NuoDbDialectResolver
hibernate.temp.use_jdbc_metadata_defaults=true
As a side effect of resolution, Hibernate has used JDBC to query the database metadata so it "knows" the version of NuoDB it is running against.
This information is passed to the NuoDialect
constructor that accepts DatabaseResolutionInfo
.
-
The default
NuoDBDialect
constructor is now deprecated and logs a warning if used. If you get the warning, unsethibernate.dialect
. -
The
NuoDBDialect
now knows whether specific features are only available in particular versions of NuoDB and generates SQL accordingly. -
The dialect offers a
getVersion()
method so you access the database version in your own code. -
The
checkVersion()
method inherited fromDialect
, has been overridden to log helpful information if the current version is older than the minimum specified for the dialect (currently anything before NuoDB 5.0.2). -
A new method,
NuoDBDialect.isIncompatible()
, returnstrue
if an older version of NuoDB is used once theNuoDBDialect
instance has been created.
Using Automatically Generated Identifiers
NuoDB recommends using the IDENTITY
strategy for auto-generated keys.
For full details, see page on Auto-Generating Keys.
Customizing Behavior
Various properties are described in this document that control how NuoDBDialect
behaves.
These properties are all defined on com.nuodb.hibernate.NuoDbEnvironment
along with some helpful methods to get their value.
In all cases, the property can be defined:
-
Either as Java System Property (on the command line or in code very early during application startup)
-
Or in the Operating System environment as an environment variable before the application starts.
-
NuoDbEnvironment.value()
checks for both. -
The property should be set to
TRUE
ortrue
.
Properties are:
ENABLE_TRACE_COMMENTS
-
When enabled, comments are inserted into the generated SQL indicating how it was generated. NuoDB Support may ask you to enable this as part of debugging a problem.
SUPPRESS_VERSION_WARNING
-
The first time a
NuoDBDialect
instance is created in an application a message is logged like the one below. To suppress the message, set this property.INFO: Using NuoDB Dialect 23.1.0-hib6 for Hibernate 6.6 & JPA 3.1
USE_NUODB_STRING_COLUMN_TYPE
-
When set to
true
, string columns are defined using NuoDB’sSTRING
column type instead ofVARCHAR
.STRING
has the advantage that you do not have to specify a maximum length. See also Defining STRING Columns below. USE_TIMESTAMP_WITHOUT_TIME_ZONE
-
NuoDB’s
TIMESTAMP
is equivalent toTIMESTAMP WITH TIME ZONE
. Set this property totrue
to makeTIMESTAMP WITHOUT TIME ZONE
the default. Alternatively invokeNuoDBDialect.useTimestampWithoutTimezone
very early in your application before Hibbernate is initialized.
Supported Functions
In addition to the SQL and JPQL functions expected by JPA 3.1, several NuoDB SQL functions are also available for use in JPQL queries. In general, functions take the same arguments (if any) as their SQL equivalents.
-
For a list of recently added functions, and any dependencie on NuodB version, refer to the Hibernate driver release notes.
-
NuoDB does not currently support
ln()
,log()
andlog10
functions so they are not available to JPQL either.
To see all the available functions:
- Hibernate 5
-
Information is directly available using
Dialect.getFunctions()
:Map<String, SQLFunction> functions = new TreeMap<String, SQLFunction>(new NuoDBDialect().getFunctions()); for (String fname : functions.keySet()) { System.out.println(fname); }
Refer to the Hibernate 5 Javadocs for details of their SQLFunction class.
- Hibernate 6
-
Hibernate 6 removed
getFunctions()
fromDialect
. We have restored similar functionality inNuoDBDialect
fromV23.0.5-hib6
andV23.1.0-hib6
.NuoDBDialect.getFunctionNames()
returns an unmodifiable list of available function names in alphabetical order.NuoDBDialect.getFunctions()
returns an unmodifiableMap
of function name,com.nuodb.hibernate.SQLFunction
pairs. Here is the definition of our customSQLFunction
class, containing similar function information to Hibernate 5’s original class:class SQLFunction { public final String name; public final String signature; public final boolean requiresParentheses; public final FunctionKind functionKind; }
Defining Your Own Functions
This facility requires JAR V23.0.5-hib6 or V23.1.0-hib6 or later.
|
To make your own User Defined Function (UDF) available to JPQL there are two steps:
-
Implement the following interface (defined on
NuoDBFunctionFactory
) to register your own function(s):public static interface FunctionRegistrar { public void register(BasicTypeRegistry basicTypeRegistry, SqmFunctionRegistry functionRegistry, TypeConfiguration typeConfiguration); }
-
Then pass an instance to
NuoDBDialect.registerCustomFunctions(FunctionRegistrar)
.-
Note that
registerCustomFunctions
has changed API since it was introduced inV23.0.3-hib6
to avoid subclassing.
-
- Example
-
-
For a UDF called
triple
that takes a single string parameter.public class TripleRegistrar implements com.hibernate.nuodb.NuoDBFunctionFactory.FunctionRegistrar { @Override public void register(BasicTypeRegistry basicTypeRegistry, SqmFunctionRegistry functionRegistry, TypeConfiguration typeConfiguration) { BasicType<String> stringType = basicTypeRegistry.resolve(StandardBasicTypes.STRING); functionRegistry.patternDescriptorBuilder("triple", "triple(?1)") // .setExactArgumentCount(1) // Only 1 .setInvariantType(stringType) // The return type .setArgumentListSignature("(STRING arg)") // The parameter type .register(); }
SqmFunctionRegistry
has several alternative builder methods for defining functions: -
Register the function:
nuoDbDialect.registerCustomFunctions(new TripleRegistrar());
-
Defining STRING Columns
NuoDB offers a custom STRING
column type as an alternative to VARCHAR(n)
.
In particular there is no need to specify a maximum length, making such columns easier and more flexible to use.
By default String
data-members of persistent entities (classes) are mapped to VARCHAR(255)
because 255 is the default value of the length
property in JPA’s @Column
annotation.
However you can enable mapping to STRING
columns instead by first calling an API or by setting a property.
You need at least V21.0.0 of the NuoDB Hibernate JAR.
Options:
-
Invoke
NuoDBDialect.useStringInsteadOfVarchar(true)
very early in your application (before Hibernate is initialized). In a Spring Boot application you can put this call inmain()
. -
Alternatively set the Environment variable or Java System property:
USE_NUODB_STRING_COLUMN_TYPE=true
.
Handling Unsupported Syntax
Even with our dialect, Hibernate may occasionally generate SQL syntax that NuoDB does not support.
Fortunately, it is always possible to use your own SQL, but still get Hibernate to map the result to Java entities.
- Example
-
Consider a store selling books, represented by
Store
andBook
entity classes. A one-to-many relationship exists between them using@OnetoMany
. By default this is implemented by adding a join table calledBook_Store
.To find books with at least one store, Hibernate may sometimes generate SQL queries such as this, which (prior to NuoDB 5.1.2) do not parse and cause a SQL grammar exception:
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
The SQL contains joins groups (the parenthesized
INNER JOIN
syntax) and is typically generated due to aJOIN FETCH
or the use of eager loading.
Although the issue was resolved in NuoDB 5.1.2 and later, the following workaround can be used when generated SQL will not work with NuoDB. This is a useful technique in other cases where the generated SQL will not work with NuoDB.
- Solution
-
It’s possible to rewrite the query directly in SQL and run using the native query support provided by JPA:
/* Rewritten query, removing the join group syntax */ 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)"; /* Hibernate runs your custom SQL, creates Book entities from the results */ entityMgr.createNativeQuery(rewrittenQueryString, Book.class) ...
This runs your modified SQL but still creates
Book
instances from the results.
SQL Keywords
CONTAINING
, VER
and KEY
are reserved words in NuoDB SQL so should not be used as a column, view, or table name.
Although VER is a reserved word, the use of VERSION is not restricted.
|
As mentioned above, STRING
is also a reserved word.
Any data-member called 'string' must be mapped to an alternative column name.
-
In all these cases, you could use them if surrounded by double quotes or square brackets, such as
"VER"
ir[VER]
, but that is very tedious and best avoided if possible.
Unicode Support
NuoDB uses Unicode by default so CHAR
/ NCHAR
and VARCHAR
/ NVARCHAR
(and so on) are actually equivalent.
However, the setNxxx()
methods of PreparedStatement
(such as setNString()
and setNCharacterStream()
) are not currently implemented.
Similarly the getNxxx()
methods of ResultSet
are not implemented either.
Simply use the non-nationalized versions instead.
For a full list of keywords reserved in NuoDB, see SQL Keywords.
NuoDB Query Optimizer Hints
Hibernate allows you to add comments to a query using Query.setHint(QueryHints.HINT_COMMENT, commentText)
.
-
You must also set the Hibernate property
hibernate.use_sql_comments=true
or they will be ignored. -
The NuoDB dialect has always supported adding comments.
-
The comment is added to the front of the generated SQL.
-
NuoDB allows query optimizer hints to be passed to the query optimiser when processing SQL.
Query optimizer hint(s) must be provided as a comment after SELECT
in the format /* + query-hint(s) */
— note the leading plus sign in the comment.
From V22.0.0, the NuoDB dialect allows NuoDB query optimizer hints to be specified as a special case of a QueryHints.HINT_COMMENT
.
For example:
/* Request that an ordered index is used. Note the optimizer hint MUST start with a plus. */
TypedQuery<Product> query = entityManager.createQuery("SELECT p FROM Product p", Product.class);
query.setHint(QueryHints.HINT_COMMENT, "+ ORDERED");
Generates SQL like the following:
SELECT /*+ ORDERED */ product0_.id as id1_0_, product0_.name as name4_0_, ...
FROM Products product0_
-
The query optimizer hint will be added after the first
SELECT
found in the query. -
The query optimizer hint is output regardless of whether
hibernate.use_sql_comments
is enabled.
Controlling Lock Upgrade
NuoDB supports locking selected rows:
-
SELECT … FOR UPDATE SKIP LOCKED
- only rows that can be locked without conflict will be locked. -
SELECT … FOR UPDATE NOWAIT
- give up immediately if any of the rows cannot be locked, causing an exception. -
From NuoDB 6.0.x (where x >= 2)
SELECT … FOR UPDATE WAIT <timeout>
can also be used. NuoDB will wait the specified time (in seconds) and then give up, again raising an exception.-
If an earlier release of NuoDB is being used,
WAIT
is not supported, so it reverts to the default, which is to wait-forever. From V23.0.4-hib6 and V22.0.5-hib5, a warning comment is logged and also added to the generated SQL.
-
Examples:
/* Don't wait at all - NOWAIT. */
TypedQuery<Product> query =
entityManager.createQuery("SELECT p FROM Product p", Product.class);
query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
query.setHint(AvailableSettings.JPA_LOCK_TIMEOUT, LockOptions.NO_WAIT);
/* Only lock the rows that don't conflict - SKIP_LOCKED. */
TypedQuery<Product> query =
entityManager.createQuery("SELECT p FROM Product p", Product.class);
query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
query.setHint(AvailableSettings.JPA_LOCK_TIMEOUT, LockOptions.SKIP_LOCKED);
/* WAIT for 5000ms = 5s. NOTE: The timeout hint is in milliseconds. */
TypedQuery<Product> query =
entityManager.createQuery("SELECT p FROM Product p", Product.class);
query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
query.setHint(AvailableSettings.JPA_LOCK_TIMEOUT, 5000);
The constant AvailableSettings.JPA_LOCK_TIMEOUT
has the value "javax.persistence.lock.timeout"
(JPA 2) or "jakarta.persistence.lock.timeout"
(JPA 3).
-
The default value is -1 (wait forever).
-
NO_WAIT
has the value zero . -
SKIP_LOCKED
the value -2. -
WAIT
expects a positive value - treated as milliseconds - Hibernate divides the value you specify by 1000 and rounds to the nearest integer. Therefore a wait time of 500ms or less will be treated as 0 orNOWAIT
.
Query hints are JPA provider dependent so you will need to import two Hibernate classes:
import org.hibernate.LockOptions;
import org.hibernate.cfg.AvailableSettings;
Global lock timeouts can be specified in two ways.
-
Set the Hibernate configuration property
jakarta.persistence.lock.timeout
(Hibernate 6) orjavax.persistence.lock.timeout
(Hibernate 5) to a value in milliseconds. As above, it will be rounded down to the nearest whole seconds. -
Run a native query and set NuoDB’s
LOCK_WAIT_TIMEOUT
property - seeSET
command. Note that the timeout is in seconds.
For SQL commands, specify timeout in seconds. For the Hibernate hints and properties, specify timeout in milliseconds. |
LIKE Escape Character
When using a LIKE
expression in a WHERE
clause, you can specify an escape character by adding ESCAPE x
.
This allows you to match strings containing any of the wildcard characters expected by LIKE
.
For example, an underscore normally matches any single character.
To match a column whose value contains an underscore it needs escaping — for example, employees with an underscore in their username:
SELECT ... FROM Employees WHERE username LIKE '%\_%' ESCAPE '\'
Previously, the escape character must be specified explicitly, a bind variable (?) was not supported. However, for NuoDB 5.0.4 and later, a bind variable is supported. |
This is especially important if using Spring Data generated repositories with JPA, as it uses a bind variable in the ESCAPE
expression when implementing the JPQL for method names that require LIKE
expressions — such as startsWith
, contains
and endsWith
.
Using a bind variable generates an invalid SQL error at runtime on older NuoDB releases.
Upgrade NuoDB to 5.0.4 or later to resolve.
For a full list of methods implemented using LIKE
, refer to Query Lookup Strategies in the Spring Data documentation.
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
orPESSIMISTIC_READ
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 prior to 6.0.2 does not support right outer joins but Hibernate may create queries using them. Rewrite the HQL query to use a left join instead. If necessary, write SQL directly and use JPA’s native query support (as described above). Similarly
OUTER JOINS
are not supported before NuoDB 7.0, -
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.
Hibernate 5 Specific
Defining TIMESTAMP Columns
By default TIMESTAMP
columns in NuoDB contain the time zone a well.
They are equivalent to TIMESTAMP WITH TIME ZONE
in other SQL dialects.
From version 5, NuoDB also supports TIMESTAMP WITHOUT TIME ZONE
but Hibernate 5 cannot use it.
The NuoDB Hibernate 5 dialect maps all timestamp fields to TIMESTAMP(6)
(with time zone).
Hibernate provides hibernate.jdbc.time_zone
to set the time zone when saving dates and times.
Recommended practice is to set it to UTC, so all dates are normalized to a known time zone.
If you wish to use TIMESTAMP WITHOUT TIME ZONE
columns, you must create the underlying table for your entity manually.
You can set the time zone used by NuoDB (by the TEs) using:
-
-
This only applies to the current connection (typically the current transaction) but can be different for different connections.
-
-
TimeZone=XXX
connection property, which will apply to all connections.
Hibernate 6 Specific
JPA Version 3
The JPA 3.0 standard contains no new features, but it is a completely breaking change:
-
All interfaces and enums have moved from
javax.persistence
tojakarta.persistence
, in line with Java EE becoming the open source Jakarta EE. -
For those of you using it, the XML namespace also changed.
JPA 3.1 introduces a small number of new features.
Due to these package changes, the NuoDB Hibernate 5 JAR cannot support JPA 3 and is, in turn, incompatible with Spring 6 and Spring Boot 3.
Hibernate 6 support is provided from V23.0.0-hib6
onwards.
NuoDB 5.0.2 or later is required to fully use the NuoDB Hibernate 6 dialect. Some features require NuoDB versions 6 or 7. See the main NuoDB release notes. |