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:
Aggregate functions, which implement the
org.apache.metamodel.query.AggregateFunction
interface.Scalar functions, which implement the
org.apache.metamodel.query.ScalarFunction
interface.
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:
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:
public class HashCodeFunction implements ScalarFunction { @Override public ColumnType getExpectedColumnType(ColumnType type) { return ColumnType.INTEGER; } @Override public String getFunctionName() { return "HASH_CODE"; } @Override public Object evaluate(Row row, SelectItem operandItem) { Object value = row.getValue(operandItem); return value == null ? null : value.hashCode(); } }
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:
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:
public class DistinctCountFunction implements AggregateFunction { @Override public String getFunctionName() { return "DISTINCT_COUNT"; } @Override public ColumnType getExpectedColumnType(ColumnType type) { return ColumnType.INTEGER; } @Override public Object evaluate(Object... values) { AggregateBuilder<?> aggregateBuilder = createAggregateBuilder(); for (Object value : values) { aggregateBuilder.add(value); } return aggregateBuilder.getAggregate(); } @Override public AggregateBuilder<?> createAggregateBuilder() { return new AggregateBuilder<Integer>() { private Set<Object> uniqueSet = new HashSet<>(); @Override public void add(Object o) { uniqueSet.add(o); } @Override public Integer getAggregate() { return uniqueSet.size(); } }; } }
(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:
Query q = dataContext.query().from(table).select(new DistinctCountFunction(), "type").toQuery();