JDOQL : User Defined Functions for RDBMS

JDOQL is defined by the JDO2 specification and is explicit about what is supported. You can extend DataNucleus's capabilities using the plugin extension org.datanucleus.store_expression_scalarexpression . This is only for RDBMS

Plugin extension-point Key Description Location
org.datanucleus.store_expression_scalarexpression java.lang.Math Use of Math functions for JDO2 datanucleus-rdbms
org.datanucleus.store_expression_scalarexpression javax.jdo.JDOHelper Use of JDOHelper function for JDO2 datanucleus-rdbms
org.datanucleus.store_expression_scalarexpression Analysis Use of Analysis functions datanucleus-rdbms


When the JDOQL does not fulfill the querying needs, DataNucleus permits the user to extend it with user-defined methods. The below example shows how to extend the JDOQL language to invoke a custom operation Finance.pmt(..) . This custom method calculates payment amounts at interest rate per month. 

package org.datanucleus.samples.finance;

/**
 * Finance operations
 */
public class Finance
{
    /**
     * Calculate payment amount at interest rate per month
     * @param amount borrowed ammount
     * @param rate rate per payment basis (0.66 month = 8% year)
     * @param payments number of payments
     * @return
     */
    public static double pmt(double amount,double rate,int payments)
    {
        double r = rate / 100;
        return ( amount * rate ) * (Math.pow(1+r, payments) / (Math.pow(1+r, payments)-1));
    }
}

With the above class, we will mirror the code into expressions to be evaluted by JDOQL compiler. We have to implement a ScalarExpression class to generate the SQL statement. The rules are:

  • Extends org.datanucleus.store.expression.ScalarExpression .
  • Add constructor with org.datanucleus.store.expression.QueryExpression argument.
  • The method to be invoked must return a org.datanucleus.store.expression.ScalarExpression instance.
  • If the method received arguments, it must be type of org.datanucleus.store.expression.ScalarExpression .
  • The method name must be postfixed with the word Method . 

package org.datanucleus.samples.finance.expression;

import org.datanucleus.store.expression.NumericExpression;
import org.datanucleus.store.expression.QueryExpression;
import org.datanucleus.store.expression.ScalarExpression;
import org.datanucleus.store.mapping.JavaTypeMapping;

/**
 * Represents expressions of Finance 
 */
public class FinanceExpression extends ScalarExpression
{

    /**
     * @param qs The query statement
     */    
    protected FinanceExpression(QueryExpression qs)
    {
        super(qs);
    }

    /**
     * Calculate payment amount at interest rate per month
     * Generates a SQL like "(( amount * rate ) * (POWER(1+(rate/100), payments) / 
     *                        (POWER(1+(rate/100), payments)-1)))"
     * @param amount borrowed ammount
     * @param rate rate per payment basis (0.66 month = 8% year)
     * @param payments number of payments
     * @return
     */
    public ScalarExpression pmtMethod(ScalarExpression amount, ScalarExpression rate, 
        ScalarExpression payments)
    {    
        /*
         * double r = rate / 100;
         * double numerator = (Math.pow(1+r, payments)
         * double denominator = (Math.pow(1+r, payments)-1
         * return ( amount * rate ) * (numerator / denominator)
         */

        //define literal 100
        JavaTypeMapping m100 = qs.getStoreManager().getDatastoreAdapter().getMapping(Integer.class,
            qs.getStoreManager(),qs.getClassLoaderResolver());
        ScalarExpression literal100 = m100.newLiteral(qs, new Integer(100));

        //define literal 1
        JavaTypeMapping m1 = qs.getStoreManager().getDatastoreAdapter().getMapping(Integer.class,
            qs.getStoreManager(),qs.getClassLoaderResolver());
        ScalarExpression literal1 = m1.newLiteral(qs, new Integer(1));

        //double r = rate / 100;
        ScalarExpression r = rate.div(literal100).encloseWithInParentheses();

        //double numerator = (Math.pow(1+r, payments)
        ScalarExpression numerator = power(literal1.add(r), payments).encloseWithInParentheses();

        //double denominator =  (Math.pow(1+r, payments)-1
        ScalarExpression denominator = 
           power(literal1.add(r), payments).sub(literal1).encloseWithInParentheses();

        // return ( amount * rate ) * (numerator / denominator)        
        return amount.mul(rate).mul(
            numerator.div(denominator).encloseWithInParentheses()).encloseWithInParentheses();
    }

    /**
     * Creates the expression POWER(expr1,expr2). This method assume the database supports the 
     * function POWER.
     * @param expr1 the first argument
     * @param expr2 the second argument
     * @return the result
     */
    private ScalarExpression power(ScalarExpression expr1,ScalarExpression expr2)
    {
        ArrayList args = new ArrayList();
        args.add(expr1);
        args.add(expr2);
    
        return new NumericExpression("POWER", args);
    }
}

The FinanceExpression is registered by declaring it in the /plugin.xml file. The below code exemplifies it: 

<?xml version="1.0"?>
<plugin id="org.datanucleus" name="DataNucleus plug-ins" provider-name="My Company">
    <extension point="org.datanucleus.store_expression_scalarexpression">
        <scalar-expression literal-class="org.datanucleus.samples.finance.Finance" 
            scalar-expression-class="org.datanucleus.samples.finance.expression.FinanceExpression"/>
    </extension>
</plugin>

After that pack all the classes and the /plugin.xml into a jar and place it in the classpath.

In JDOQL invoke it with Literal.method(args) . The below exemplifies: 

Query query = pm.newQuery(org.datanucleus.samples.store.Payment.class);
query.setFilter("amount>Finance.pmt(100000,0.66,360)");
List results = (List)query.execute();