Hibernate Dialect Considerations

This section provides information on using NuoDB with Hibernate. As mentioned previously NuoDB recommend to 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 an application chooses not to use our Hibernate specific extension to the JDBC driver, it 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 the hibernate.dialect property was needed to explicitly specify the database dialect Hibernate should use to generate compatible SQL.

Dialect resolution allows Hibernate to determine the dialect automatically based on the underlying database.

  • To enable resolution, do not set hibernate.dialect any more; otherwise it takes precedence and resolution does not occur.

If using Hibernate 5, set the following two Hibernate properties 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 uses JDBC to query the database metadata so it "knows" the version of NuoDB it is running against. This information is passed to the NuoDBDialect constructor that accepts DatabaseResolutionInfo.

  • The default NuoDBDialect constructor is now deprecated and logs a warning if used. To avoid this warning, unset hibernate.dialect and let dialect resolution do its job.

  • 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 to access the database version in 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 custom 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.

    • To enable, set the property to TRUE or true.

    • Any other value (or if the property is not set) is treated as disabled.

Properties are:

ENABLE_TRACE_COMMENTS

When enabled, comments are inserted into the generated SQL indicating how it was generated. NuoDB Support may request enabling 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 there is no need 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 an application before Hibernate 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 dependencies 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 {
    // The function name.
    public final String name;

    // Full call signature of the function. If the function's return type depends
    // on one of its parameters, the return type is set to 'ParamType'.
    public final String signature;

    // Does the function require parentheses? Only applicable to functions with no
    // mandatory parameters.
    public final boolean requiresParentheses;

    // What type of function is this? Most functions are 'NORMAL'. Other values
    // are AGGREGATE, ORDERED_SET_AGGREGATE and WINDOW
    public final FunctionKind functionKind;
}

Defining Custom Functions

This facility requires JAR V23.0.5-hib6 or V23.1.0-hib6 or later.

To make an existing User Defined Function (UDF) available to JPQL there are two steps:

  1. Implement the following interface (defined on NuoDBFunctionFactory) to register the function(s):

        public static interface FunctionRegistrar {
        public void register(BasicTypeRegistry basicTypeRegistry,
            SqmFunctionRegistry functionRegistry, TypeConfiguration typeConfiguration);
    }

  2. Then pass an instance to NuoDBDialect.registerCustomFunctions(FunctionRegistrar).

    • Note that registerCustomFunctions has changed API, since it was originally 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);

         // Define the function. ?1 represents the single expected parameter
         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. NuoDBDialect supports mapping to STRING columns instead, either via an API or by setting a property.

  • Requires at least V21.0.0 of the NuoDB Hibernate JAR.

Options:

  1. Invoke NuoDBDialect.useStringInsteadOfVarchar(true) very early in the application (before Hibernate is initialized). In a Spring Boot application, put this call in main().

  2. 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 for developers to make Hibernate use their own custom, hand-crafted SQL instead, 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 did not parse in earlier versions of NuoDB and caused 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 is resolved, the following workaround is a good example of a more general and useful technique:

Problem

What to do when generated SQL will not work with NuoDB?

Solution

Rewrite the query directly in SQL and use 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 the custom SQL, creates Book entities from the results
entityMgr.createNativeQuery(rewrittenQueryString, Book.class) ...

This runs the supplied SQL but still creates Book instances from the results.

The only requirement is that the SQL must return all the same columns that Hibernate expects to build its entities from.

SQL Keywords

CONTAINING, KEY and VER are reserved words in NuoDB SQL and cannot be used as a column, view, or table name.

Although VER is a reserved word, the use of VERSION is not restricted. VER will no longer be a reserved word from NuoDB version 8.

As mentioned above, STRING is also a reserved word. Any data-member called 'string' must be mapped to an alternative column name.

All these reserved words can be used if enclosed in double quotes or square brackets, for example, "VER" or [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 yet implemented by the NuoDB JDBC Driver. Similarly the getNxxx() methods of ResultSet are not implemented either. Simply use the non-nationalized versions instead.

Versions of the NuoDB JDBC Driver after V24.2.1 will support these methods.

For a full list of keywords reserved in NuoDB, see SQL Keywords.

NuoDB Query Optimizer Hints

Hibernate supports adding comments to a query using Query.setHint(QueryHints.HINT_COMMENT, commentText).

  • For this to work, also set the Hibernate property hibernate.use_sql_comments=true or they will be ignored.

  • The NuoDB dialect has always supported adding comments.

    • NuoDB adds the comment at the beginning 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 immediately after /*, with no space.

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.

    • WAIT 0 is not supported, use NOWAIT explicitly.

    • 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" (from 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 specified 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 and may require importing 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, Hibernate supports specifying an escape character by adding ESCAPE x. This enables matching 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 '\'

If using Spring Data generated repositories with JPA, they use the ESCAPE expression when implementing the JPQL for method names that require LIKE expressions — such as startsWith, contains and endsWith.

  • 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 iteration can occur 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 criteria queries. Typically, 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 = ?)

    Instead, rewrite this using INNER JOIN or EXISTS.

  • NuoDB does not support CREATE TYPE, 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.

To use TIMESTAMP WITHOUT TIME ZONE columns, create the underlying table for the entity manually.

To set the time zone used by NuoDB (by the TEs) use:

  • 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.

  • The XML namespace used by persistence.xml files has 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.