JPA comes with a number of built in functions. These are:

• CONCAT(string1, string2): Concatenates two string fields or literals.
• SUBSTRING(string, startIndex, length): Returns the part of the string argument starting at startIndex (1-based) and ending at length characters past startIndex.
• TRIM([LEADING | TRAILING | BOTH] [character FROM] string: Trims the specified character from either the beginning (LEADING), the ending (TRAILING), or both (BOTH) the beginning and ending of the string argument. If no trim character is specified, the space character will be trimmed.
• LOWER(string): Returns the lower-case of the specified string argument.
• UPPER(string): Returns the upper-case of the specified string argument.
• LENGTH(string): Returns the number of characters in the specified string argument.
• LOCATE(searchString, candidateString [, startIndex]): Returns the first index of searchString in candidateString. Positions are 1-based. If the string is not found, returns 0.
• ABS(number): Returns the absolute value of the argument.
• SQRT(number): Returns the square root of the argument.
• MOD(number, divisor): Returns the modulo of number and divisor.
• CURRENT_DATE: Returns the current date.
• CURRENT_TIME: Returns the current time.
• CURRENT_TIMESTAMP: Returns the current timestamp.

Hibernate adds second(…), minute(…), hour(…), day(…), month(…), and year(…) for extracting various components from dates or times. Some database dialects supports a number of functions, but they’re undocumented, and are not supported across databases.

Unfortunately, if you need something more complicated, you are out of luck with the base Hibernate. Fortunately, you can add support for custom functions by creating custom dialects. This job is made easier by the fact that you can inherit from existing Dialect classes, but it does also mean that you have to provide classes for every Hibernate Dialect that you want to support.

[sourcecode lang=”java5″]package com.example.db.dialect;

import org.hibernate.dialect.SQLServer2012Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;

public class MySQLServer2012Dialect extends SQLServer2012Dialect {
public MySQLServer2012Dialect() {
registerFunction(“DATE_ADD”, new SQLFunctionTemplate( StandardBasicTypes.DATE, “DATEADD(?3, ?2, ?1)” ));
registerFunction(“DATE_SUB”, new SQLFunctionTemplate( StandardBasicTypes.DATE, “DATEADD(?3, -?2, ?1)” ));

}
}
[/sourcecode]This registers two functions for adding and subtracting to the various components in a date. It can be used like:

[sourcecode lang=”sql”]date_sub(event.startDate, event.remindDaysBefore, day) <= current_date()[/sourcecode]This results in the following SQL:

[sourcecode lang=”sql”]DATEADD(day, -event.remindDaysBefore, event.start_date) <= getdate()[/sourcecode]Here’s the dialect for HSQLDB:

[sourcecode lang=”java5″]package com.example.db.dialect;

import org.hibernate.dialect.HSQLDialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;

import java.sql.Types;

public class MyHSQLDialect extends HSQLDialect{

public MyHSQLDialect() {
registerFunction(“DATE_ADD”, new SQLFunctionTemplate( StandardBasicTypes.DATE, “( ?1 + CAST(?2 AS INTERVAL ?3))” ));
registerFunction(“DATE_SUB”, new SQLFunctionTemplate( StandardBasicTypes.DATE, “( ?1 – CAST(?2 AS INTERVAL ?3))” ));
}
}
[/sourcecode]registerFunction()takes two parameters, the function name, and a SQLFunction implementation. Fortunately, Hibernate provides a number of default implementations that make this easy to use.

The simplest implementation is NoArgSQLFunction, which is for functions that take no argument, like CURRENT_DATE()

[sourcecode lang=”java5″]registerFunction( “current_time”, new NoArgSQLFunction( “getdate”, StandardBasicTypes.TIME ) );[/sourcecode]The first argument is the SQL function name and the second is the return type from the function.

Next up is StandardSQLFunction which is for functions that are simple functions, with a name and multiple parameters:

[sourcecode lang=”java5″]registerFunction( “lower”, new StandardSQLFunction( “lower” ) );[/sourcecode]There is an optional second parameter which is the return type of the function. The one parameter constructor assumes that the return type is the same type as the first parameter.

If you require a little more complicated call, you can use SQLFunctionTemplate: 

[sourcecode lang=”java5″]registerFunction( “trim”, new SQLFunctionTemplate( StandardBasicTypes.STRING, “ltrim(rtrim(?1))” ) );[/sourcecode]This allows you to use a template to generate the SQL. The implementation will substitute $1, $2 etc with the parameters to the function. You can see a more complicated version at the start of the article.

The final implementation you might want to use is VarArgsSQLFunction. This allows a little more flexibility over StandardSQLFunction when you have a variable number of parameters.

[sourcecode lang=”java5″]registerFunction( “concat”, new VarArgsSQLFunction( StandardBasicTypes.STRING, “(“, “+”, “)” ) );[/sourcecode]If any of these options do not provide the flexibility that you require, you can implement the SQLFunction interface yourself. The vital function to implement is render(Type firstArgumentType, List arguments, SessionFactoryImplementor factory), which returns the query fragment string. Here is an example from Hibernate itself that uses the native charindex() and char_length() as used by the Sybase and SQL Server dialects.

[sourcecode lang=”java5″]public class CharIndexFunction implements SQLFunction {
@Override
public boolean hasArguments() {
return true;
}

@Override
public boolean hasParenthesesIfNoArguments() {
return true;
}

@Override
public Type getReturnType(Type columnType, Mapping mapping) throws QueryException {
return StandardBasicTypes.INTEGER;
}

@Override
public String render(Type columnType, List args, SessionFactoryImplementor factory) throws QueryException {
final boolean threeArgs = args.size() > 2;
final Object pattern = args.get( 0 );
final Object string = args.get( 1 );
final Object start = threeArgs ? args.get( 2 ) : null;

final StringBuilder buf = new StringBuilder();
buf.append( “charindex(” ).append( pattern ).append( “, ” );
if (threeArgs) {
buf.append( “right(” );
}
buf.append( string );
if (threeArgs) {
buf.append( “, char_length(” ).append( string ).append( “)-(” ).append( start ).append( “-1))” );
}
buf.append( ‘)’ );
return buf.toString();
}

}[/sourcecode]