User Defined Functions (UDFs)

Apache MetaModel allows for extending the query language using User Defined Functions. In fact, the functions that are built into Apache MetaModel are also "just" built-in UDFs.

Functions in MetaModel come in two forms:

Scalar functions

A scalar function is a function that provides a result on each row of the dataset that it is applied to. For instance, the built-in function TO_NUMBER will convert each value to a java.lang.Number instead of some other data type.

Normally you can apply the Scalar function TO_NUMBER like this:

   1 Query q = dataContext.query().from(table).select(FunctionType.TO_NUMBER, "id").toQuery();

If you dig into FunctionType.TO_NUMBER you will find out that it is simply an object that implements ScalarFunction. Let's imagine you wanted to implement instead now a hash code function. We can implement it like this:

   1 public class HashCodeFunction implements ScalarFunction {
   2 
   3     @Override
   4     public ColumnType getExpectedColumnType(ColumnType type) {
   5         return ColumnType.INTEGER;
   6     }
   7 
   8     @Override
   9     public String getFunctionName() {
  10         return "HASH_CODE";
  11     }
  12 
  13     @Override
  14     public Object evaluate(Row row, SelectItem operandItem) {
  15         Object value = row.getValue(operandItem);
  16         return value == null ? null : value.hashCode();
  17     }
  18 }

As you can see the implementation part here is pretty easy. We need only to provide a name, a data type and implement the evaluate(...) method. Now to apply the function to our query:

   1 Query q = dataContext.query().from(table).select(new HashCodeFunction(), "id").toQuery();

Aggregate functions

Aggregate functions are used to make calculations which span multiple rows of the dataset. Typically used on a complete dataset or in combination with a GROUP BY condition.

In a similar way to scalar functions, you can also implement your own aggregate functions. Let's say we wanted to implement a DISTINCT_COUNT function (ie. a count of distinct/unique values), we could do it like this:

   1 public class DistinctCountFunction implements AggregateFunction {
   2 
   3     @Override
   4     public String getFunctionName() {
   5         return "DISTINCT_COUNT";
   6     }
   7 
   8     @Override
   9     public ColumnType getExpectedColumnType(ColumnType type) {
  10         return ColumnType.INTEGER;
  11     }
  12 
  13     @Override
  14     public Object evaluate(Object... values) {
  15         AggregateBuilder<?> aggregateBuilder = createAggregateBuilder();
  16         for (Object value : values) {
  17             aggregateBuilder.add(value);
  18         }
  19         return aggregateBuilder.getAggregate();
  20     }
  21 
  22     @Override
  23     public AggregateBuilder<?> createAggregateBuilder() {
  24         return new AggregateBuilder<Integer>() {
  25             
  26             private Set<Object> uniqueSet = new HashSet<>();
  27             
  28             @Override
  29             public void add(Object o) {
  30                 uniqueSet.add(o);
  31             }
  32 
  33             @Override
  34             public Integer getAggregate() {
  35                 return uniqueSet.size();
  36             }
  37         };
  38     }
  39 }

(you may choose to extend DefaultAggregateFunction which will save you the effort of implementing evaluate(...))

And again you can apply your function in a query like this:

   1 Query q = dataContext.query().from(table).select(new DistinctCountFunction(), "type").toQuery();