Adding aggregate function to external H2 database

Question

I'm trying to create an aggregate function in my H2 database using Java. The function should return a custom median calculation from the given Double column. This calculation consist in using only the values that are close enough to the average value using the max_variance int value. For this I created the class:

package custommedian;

public class CustomMedian implements org.h2.api.AggregateFunction{
    final int max_variance = 7;
    java.util.LinkedList<Double> values = new java.util.LinkedList<Double>();

    @Override
    public void init(java.sql.Connection cnctn) throws java.sql.SQLException {
        // I ignored this
    }

    @Override
    public int getType(int[] ints) throws java.sql.SQLException {
       return java.sql.Types.DOUBLE;
    }

    @Override
    public void add(Object o) throws java.sql.SQLException {
        values.add((Double)o);
    }

    @Override
    public Object getResult() throws java.sql.SQLException {
        double average = 0;
        java.util.Iterator<Double> i;
        java.util.LinkedList<Double> properValues = new java.util.LinkedList<Double>();

        // Get average value
        for( i = values.iterator(); i.hasNext(); ) {
            average += i.next();    
        }
        average = average / values.size();

        // Filter out invalid values
        for( i = values.iterator(); i.hasNext(); ) {
            double value = i.next();
            if (value - max_variance < average && value + max_variance > average){
                properValues.add(value);
            }
        }

        // Sort list
        java.util.Collections.sort(properValues);

        // Return median
        int size = properValues.size();
        if (size > 0){
            int pos = ((int) size/2);
            // Odd size
            if ((size%2) == 1 ) return properValues.get(pos);
            // Even size
            else return ( properValues.get(pos-1) + properValues.get(pos) ) / 2;
        }
        else 
            return null;
    }
}

And now I'm supposed to compile it so it's accessible for the database and use this command:

CREATE AGGREGATE MEDIAN FOR "custommedian.CustomMedian";

How am I supposed to do that?

I tried putting the .jar file in the same folder as the database file is but it doesn't seem to find the class:

Class "custommedian.CustomMedian" not found; SQL statement:

Anyone could please tell me what else I have to do to make this work?

Thanks a lot in advance!

EDIT:

Solved:

As Lukas suggested, I ended up embedding the H2 jar into my application and this way there are no classpath issues.

Answer 1

Have you added your class to the classpath of the H2 database process? I.e. when starting up the H2 database?

Another way to do this is to fork H2 and build it including your custom aggregate function. Or just put your .class file inside the H2 jar file, depending on how often you have to update your own code, this might be ok, too. Then it will certainly be on the classpath...

In the worst case, the H2 user group is quite active:

http://groups.google.com/group/h2-database

Answer 2

When running the H2 server as an external process, the classes can be added to the classpath using java -cp <dir> ....

When using the h2.sh or h2.bat to start the H2 server, you can add the directory to the environment variables %H2DRIVERS% or %CLASSPATH%.