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 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 or true.

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’s STRING column type instead of VARCHAR. 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 to TIMESTAMP WITH TIME ZONE. Set this property to true to make TIMESTAMP WITHOUT TIME ZONE the default. Alternatively invoke NuoDBDialect.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() and log10 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() from Dialect. We have restored similar functionality in NuoDBDialect from V23.0.5-hib6 and V23.1.0-hib6.

NuoDBDialect.getFunctionNames() returns an unmodifiable list of available function names in alphabetical order.

NuoDBDialect.getFunctions() returns an unmodifiable Map of function name, com.nuodb.hibernate.SQLFunction pairs. Here is the definition of our custom SQLFunction 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 in V23.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:

6.6 Javadoc
6.2 Javadoc

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 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 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 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 or PESSIMISTIC_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 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). 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:

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. Some features require NuoDB versions 6 or 7. See the main NuoDB release notes.