Java Persistent Objects - JPOX Queries

When the JDOQL does not fulfill the querying needs, JPOX 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.jpox.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.jpox.store.expression.ScalarExpression.
Add constructor with org.jpox.store.expression.QueryExpression argument.
The method to be invoked must return a org.jpox.store.expression.ScalarExpression instance.
If the method received arguments, it must be type of org.jpox.store.expression.ScalarExpression.
The method name must be postfixed with the word Method.

package org.jpox.samples.finance.expression;

import org.jpox.store.expression.NumericExpression;
import org.jpox.store.expression.QueryExpression;
import org.jpox.store.expression.ScalarExpression;
import org.jpox.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);
    }
}

After have created the class and deployed the classes in the classpath, the ScalarExpression must be registered before executing the queries. The below code exemplifies it:

org.jpox.store.query.JDOQLQuery.registerScalarExpression(org.jpox.samples.finance.Finance.class, 
                                                         org.jpox.samples.finance.expression.FinanceExpression.class);

From now on, JPOX will allow you to execute queries using the custom method:

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