Extensibility

Mapping Custom SQL Functions to Java

Setup requires to add 2 dependencies, fluent-jpa and sql-grammar. fluent-jpa implements the transpiler, and sql-grammar declares all the SQL grammar. fluent-jpa does not depend on sql-grammar and knows nothing about it, the whole SQL is an "extension" for it. But how does it know what to do? There is another very small library called jpa-notations, which declares just interfaces and annotations. When sql-grammar declares a method, it must be annotated with an appropriate annotation. Let's see an example (copied from the real code):

@Function(omitParentheses = true, parameterContext = ParameterContext.SELECT)
static SelectClause SELECT(Comparable<?>... expressions) {
    throw new UnsupportedOperationException();
}

// overloaded variant
@Function(omitParentheses = true, parameterContext = ParameterContext.SELECT)
static SelectClause SELECT(Object expression,
                           Comparable<?>... expressions) {
    throw new UnsupportedOperationException();
}

That's what we need to declare SELECT:

• write the signature
• annotate it with @Function
• and throw an Exception for the implementation (we don't intend it to run in Java!)

In this way you can declare anything, from vendor specific clauses to custom functions or operators. Note, that some annotations or their parameters might not be obvious to apply. When you declare something, go to definition of a similar existing function, copy it and change its name to yours. And don't forget about static imports!

Sometimes invoking a method requires weird workarounds. For those cases FluentJPA offers direct SQL injection using injectSQL(). For example:

@Local
public static Keyword declareNumberToBoolean() {
    // @formatter:off
    return injectSQL("FUNCTION number_to_boolean_(i NUMBER)\r\n" + 
            "  RETURN NUMBER\r\n" +
            "  IS\r\n" +
            "    b BOOLEAN;\r\n" +
            "  BEGIN\r\n" +
            "    -- Actual function call\r\n" +
            "    b := number_to_boolean(i);\r\n" +
            "     \r\n" +
            "    -- Translation to numeric result\r\n" +
            "    RETURN CASE b WHEN TRUE THEN 1 WHEN FALSE THEN 0 END;\r\n" +
            "  END number_to_boolean_;");
    // @formatter:on
}

and then

WITH(declareNumberToBoolean());

declareNumberToBoolean() is annotated with @Local since we don't want to translate to SQL the body of this method, but want to evaluate it in Java and insert just the resulting Keyword into our statement.

injectSQL() can be used anywhere in the SQL statement.

Mapping Java Methods to SQL Functions

There are 2 options, both covered in Java Language Support:

• Custom Functions
• Custom Mappings