FunctionMultiReturn.java

/*
 * Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
 * Version 1.0, and under the Eclipse Public License, Version 1.0
 * (http://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.samples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import org.h2.tools.SimpleResultSet;

/**
 * User defined functions can return a result set,
 * and can therefore be used like a table.
 * This sample application uses such a function to convert
 * polar to cartesian coordinates.
 */
public class FunctionMultiReturn {

    /**
     * This method is called when executing this sample application from the
     * command line.
     *
     * @param args the command line parameters
     */
    public static void main(String... args) throws Exception {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:", "sa", "");
        Statement stat = conn.createStatement();
        stat.execute("CREATE ALIAS P2C FOR \"org.h2.samples.FunctionMultiReturn.polar2Cartesian\" ");
        PreparedStatement prep = conn.prepareStatement("SELECT X, Y FROM P2C(?, ?)");
        prep.setDouble(1, 5.0);
        prep.setDouble(2, 0.5);
        ResultSet rs = prep.executeQuery();
        while (rs.next()) {
            double x = rs.getDouble(1);
            double y = rs.getDouble(2);
            System.out.println("result: (x=" + x + ", y="+y+")");
        }

        stat.execute("CREATE TABLE TEST(ID IDENTITY, R DOUBLE, A DOUBLE)");
        stat.execute("INSERT INTO TEST(R, A) VALUES(5.0, 0.5), (10.0, 0.6)");
        stat.execute("CREATE ALIAS P2C_SET FOR \"org.h2.samples.FunctionMultiReturn.polar2CartesianSet\" ");
        rs = conn.createStatement().executeQuery("SELECT * FROM P2C_SET('SELECT * FROM TEST')");
        while (rs.next()) {
            double r = rs.getDouble("R");
            double a = rs.getDouble("A");
            double x = rs.getDouble("X");
            double y = rs.getDouble("Y");
            System.out.println("(r="+r+" a="+a+") : (x=" + x + ", y="+y+")");
        }

        stat.execute("CREATE ALIAS P2C_A FOR \"org.h2.samples.FunctionMultiReturn.polar2CartesianArray\" ");
        rs = conn.createStatement().executeQuery("SELECT R, A, P2C_A(R, A) FROM TEST");
        while (rs.next()) {
            double r = rs.getDouble(1);
            double a = rs.getDouble(2);
            Object o = rs.getObject(3);
            Object[] xy = (Object[]) o;
            double x = ((Double) xy[0]).doubleValue();
            double y = ((Double) xy[1]).doubleValue();
            System.out.println("(r=" + r + " a=" + a + ") : (x=" + x + ", y=" + y + ")");
        }

        rs = stat.executeQuery("SELECT R, A, ARRAY_GET(E, 1), ARRAY_GET(E, 2) FROM (SELECT R, A, P2C_A(R, A) E FROM TEST)");
        while (rs.next()) {
            double r = rs.getDouble(1);
            double a = rs.getDouble(2);
            double x = rs.getDouble(3);
            double y = rs.getDouble(4);
            System.out.println("(r="+r+" a="+a+") : (x=" + x + ", y="+y+")");
        }
        rs.close();

        prep.close();
        conn.close();
    }

    /**
     * Convert polar coordinates to cartesian coordinates. The function may be
     * called twice, once to retrieve the result columns (with null parameters),
     * and the second time to return the data.
     *
     * @param r the distance from the point 0/0
     * @param alpha the angle
     * @return a result set with two columns: x and y
     */
    public static ResultSet polar2Cartesian(Double r, Double alpha) {
        SimpleResultSet rs = new SimpleResultSet();
        rs.addColumn("X", Types.DOUBLE, 0, 0);
        rs.addColumn("Y", Types.DOUBLE, 0, 0);
        if (r != null && alpha != null) {
            double x = r.doubleValue() * Math.cos(alpha.doubleValue());
            double y = r.doubleValue() * Math.sin(alpha.doubleValue());
            rs.addRow(x, y);
        }
        return rs;
    }

    /**
     * Convert polar coordinates to cartesian coordinates. The function may be
     * called twice, once to retrieve the result columns (with null parameters),
     * and the second time to return the data.
     *
     * @param r the distance from the point 0/0
     * @param alpha the angle
     * @return an array two values: x and y
     */
    public static Object[] polar2CartesianArray(Double r, Double alpha) {
        double x = r.doubleValue() * Math.cos(alpha.doubleValue());
        double y = r.doubleValue() * Math.sin(alpha.doubleValue());
        return new Object[]{new Double(x), new Double(y)};
    }

    /**
     * Convert a set of polar coordinates to cartesian coordinates. The function
     * may be called twice, once to retrieve the result columns (with null
     * parameters), and the second time to return the data.
     *
     * @param conn the connection
     * @param query the query
     * @return a result set with the coordinates
     */
    public static ResultSet polar2CartesianSet(Connection conn, String query) throws SQLException {
        SimpleResultSet result = new SimpleResultSet();
        result.addColumn("R", Types.DOUBLE, 0, 0);
        result.addColumn("A", Types.DOUBLE, 0, 0);
        result.addColumn("X", Types.DOUBLE, 0, 0);
        result.addColumn("Y", Types.DOUBLE, 0, 0);
        if (query != null) {
            ResultSet rs = conn.createStatement().executeQuery(query);
            while (rs.next()) {
                double r = rs.getDouble("R");
                double alpha = rs.getDouble("A");
                double x = r * Math.cos(alpha);
                double y = r * Math.sin(alpha);
                result.addRow(r, alpha, x, y);
            }
        }
        return result;
    }

}