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.

Using Automatically Generated Identifiers

NuoDB recommends using the IDENTITY strategy for auto-generated keys. For full details, see Auto Generating Keys.

Supported Functions

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

For a list, run:

/* List supported functions in alphabetical order */
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.

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:

  1. 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().

  2. Alternatively set the Environment variable or Java System property: USE_NUODB_STRING_COLUMN_TYPE=true.

Using Inner Joins

Hibernate may generate SQL containing a parenthesized INNER JOIN syntax that NuoDB does not yet support.

For example, consider a store selling books, represented by Store and Book entity classes. If defined as a one-to-many relationship using @OnetoMany, the default implementation is to define a join table called Book_Store.

To find books with at least one store, Hibernate may sometimes generate 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

Prior to NuoDB V5.1, NuoDB did not implement join grouping with parentheses and expected a subquery 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 directly in SQL and run using the native query support provided by JPA:

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)";

/* Run custom SQL, but create Book entities from the results */
entityMgr.createNativeQuery(rewrittenQueryString, Book.class) ...

This would run your modified SQL but still create Book instances from the results.

SQL Keywords

VER and KEY are reserved words in NuoDB SQL so cannot 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.

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

Lock Upgrade

NuoDB supports SELECT …​ FOR UPDATE SKIP LOCKED and SELECT …​ FOR UPDATE NOWAIT. Although JPA does not currently support these lock modes directly, either can be achieved using a combination of pessimistic locking and a timeout. You need at least V22.0.0 of the NuoDB Hibernate JAR.

Here are three equivalent examples for skip locked:

  1. Using a query:

    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);

  2. Using query annotations:

    @QueryHints(@QueryHint(name = AvailableSettings.JPA_LOCK_TIMEOUT, value = SKIP_LOCKED))
@Lock(LockModeType.PESSIMISTIC_WRITE)

  3. Using find():

    entityManager.find(Product.class, 1L, LockModeType.PESSIMISTIC_WRITE,
      Collections.singletonMap(AvailableSettings.JPA_LOCK_TIMEOUT, LockOptions.SKIP_LOCKED));

    In each case the generated SQL will end with FOR UPDATE SKIP LOCKED.

Specify LockOptions.NO_WAIT to generate FOR UPDATE NOWAIT instead.

  • For example:

    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);

Query hints are JPA provider dependent so you will need to import two Hibernate classes:

import org.hibernate.LockOptions;
import org.hibernate.cfg.AvailableSettings;

The constant AvailableSettings.JPA_LOCK_TIMEOUT has the value "javax.persistence.lock.timeout" (JPA 2) or "jakarta.persistence.lock.timeout" (JPA 3).

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, from V5.0.4 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 V5.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 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 V5, 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 V5.0.2 or later is required to use the NuoDB Hibernate 6 dialect.