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, unset hibernate.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 from Dialect, 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(), returns true if an older version of NuoDB is used once the NuoDBDialect instance has been created.

Using Automatically Generated Identifiers

NuoDB recommends using the IDENTITY strategy for auto-generated keys. For full details, see 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.

Supported Functions

Many NuoDB SQL functions are available for use in JPQL queries also.

For a list, run:

Map<String, SQLFunction> functions = new TreeMap<String, SQLFunction>(new NuoDBDialect().getFunctions());

for (String fname : functions.keySet()) {
    System.out.println(fname);
}

Functions take the same arguments (if any) as their SQL equivalents.

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 in main().
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 and Book entity classes. A one-to-many relationship exists between them using @OnetoMany. By default this is implemented by adding a join table called Book_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 a JOIN FETCH or the use of eager loading.

Although the issue does not exist for 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

VER and KEY are reserved words in NuoDB SQL so should not be used as a column 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 three cases, you could use them if surrounded by double quotes, such as "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 or NOWAIT.

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) or javax.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 - see SET 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 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 FROM Players WHERE name = ?)
```
You could rewrite this using INNER JOIN or EXISTS.
NuoDB does not support CREATE TYPE but you can use a Hibernate UserType 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).
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:

SET TIME ZONE 'XXX'
- 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 to jakarta.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.