001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.samples;
007:
008: import java.sql.Connection;
009: import java.sql.DriverManager;
010: import java.sql.PreparedStatement;
011: import java.sql.ResultSet;
012: import java.sql.SQLException;
013: import java.sql.Statement;
014: import java.sql.Types;
015:
016: import org.h2.tools.SimpleResultSet;
017:
018: /**
019: * User defined functions can return a result set,
020: * and can therefore be used like a table.
021: * This sample application uses such a function to convert
022: * polar to cartesian coordinates.
023: */
024: public class FunctionMultiReturn {
025:
026: public static void main(String[] args) throws Exception {
027: Class.forName("org.h2.Driver");
028: Connection conn = DriverManager.getConnection("jdbc:h2:mem:",
029: "sa", "");
030: Statement stat = conn.createStatement();
031: stat
032: .execute("CREATE ALIAS P2C FOR \"org.h2.samples.FunctionMultiReturn.polar2Cartesian\" ");
033: PreparedStatement prep = conn
034: .prepareStatement("SELECT X, Y FROM P2C(?, ?)");
035: prep.setDouble(1, 5.0);
036: prep.setDouble(2, 0.5);
037: ResultSet rs = prep.executeQuery();
038: while (rs.next()) {
039: double x = rs.getDouble(1);
040: double y = rs.getDouble(2);
041: System.out.println("result: (x=" + x + ", y=" + y + ")");
042: }
043:
044: stat
045: .execute("CREATE TABLE TEST(ID IDENTITY, R DOUBLE, A DOUBLE)");
046: stat
047: .execute("INSERT INTO TEST(R, A) VALUES(5.0, 0.5), (10.0, 0.6)");
048: stat
049: .execute("CREATE ALIAS P2C_SET FOR \"org.h2.samples.FunctionMultiReturn.polar2CartesianSet\" ");
050: rs = conn.createStatement().executeQuery(
051: "SELECT * FROM P2C_SET('SELECT * FROM TEST')");
052: while (rs.next()) {
053: double r = rs.getDouble("R");
054: double a = rs.getDouble("A");
055: double x = rs.getDouble("X");
056: double y = rs.getDouble("Y");
057: System.out.println("(r=" + r + " a=" + a + ") : (x=" + x
058: + ", y=" + y + ")");
059: }
060:
061: stat
062: .execute("CREATE ALIAS P2C_A FOR \"org.h2.samples.FunctionMultiReturn.polar2CartesianArray\" ");
063: rs = conn.createStatement().executeQuery(
064: "SELECT R, A, P2C_A(R, A) FROM TEST");
065: while (rs.next()) {
066: double r = rs.getDouble(1);
067: double a = rs.getDouble(2);
068: Object o = rs.getObject(3);
069: Object[] xy = (Object[]) o;
070: double x = ((Double) xy[0]).doubleValue();
071: double y = ((Double) xy[1]).doubleValue();
072: System.out.println("(r=" + r + " a=" + a + ") : (x=" + x
073: + ", y=" + y + ")");
074: }
075:
076: rs = conn
077: .createStatement()
078: .executeQuery(
079: "SELECT R, A, ARRAY_GET(E, 1), ARRAY_GET(E, 2) FROM (SELECT R, A, P2C_A(R, A) E FROM TEST)");
080: while (rs.next()) {
081: double r = rs.getDouble(1);
082: double a = rs.getDouble(2);
083: double x = rs.getDouble(3);
084: double y = rs.getDouble(4);
085: System.out.println("(r=" + r + " a=" + a + ") : (x=" + x
086: + ", y=" + y + ")");
087: }
088:
089: conn.close();
090: }
091:
092: /**
093: * Convert polar coordinates to cartesian coordinates. The function may be
094: * called twice, once to retrieve the result columns (with null parameters),
095: * and the second time to return the data.
096: *
097: * @param r the distance from the point 0/0
098: * @param alpha the angle
099: * @return a result set with two columns: x and y
100: */
101: public static ResultSet polar2Cartesian(Double r, Double alpha)
102: throws SQLException {
103: SimpleResultSet rs = new SimpleResultSet();
104: rs.addColumn("X", Types.DOUBLE, 0, 0);
105: rs.addColumn("Y", Types.DOUBLE, 0, 0);
106: if (r != null && alpha != null) {
107: double x = r.doubleValue() * Math.cos(alpha.doubleValue());
108: double y = r.doubleValue() * Math.sin(alpha.doubleValue());
109: rs.addRow(new Object[] { new Double(x), new Double(y) });
110: }
111: return rs;
112: }
113:
114: /**
115: * Convert polar coordinates to cartesian coordinates. The function may be
116: * called twice, once to retrieve the result columns (with null parameters),
117: * and the second time to return the data.
118: *
119: * @param r the distance from the point 0/0
120: * @param alpha the angle
121: * @return an array two values: x and y
122: */
123: public static Object[] polar2CartesianArray(Double r, Double alpha)
124: throws SQLException {
125: double x = r.doubleValue() * Math.cos(alpha.doubleValue());
126: double y = r.doubleValue() * Math.sin(alpha.doubleValue());
127: return new Object[] { new Double(x), new Double(y) };
128: }
129:
130: /**
131: * Convert a set of polar coordinates to cartesian coordinates. The function
132: * may be called twice, once to retrieve the result columns (with null
133: * parameters), and the second time to return the data.
134: *
135: * @param conn the connection
136: * @param query the query
137: * @return a result set with the coordinates
138: */
139: public static ResultSet polar2CartesianSet(Connection conn,
140: String query) throws SQLException {
141: SimpleResultSet result = new SimpleResultSet();
142: result.addColumn("R", Types.DOUBLE, 0, 0);
143: result.addColumn("A", Types.DOUBLE, 0, 0);
144: result.addColumn("X", Types.DOUBLE, 0, 0);
145: result.addColumn("Y", Types.DOUBLE, 0, 0);
146: if (query != null) {
147: ResultSet rs = conn.createStatement().executeQuery(query);
148: while (rs.next()) {
149: double r = rs.getDouble("R");
150: double alpha = rs.getDouble("A");
151: double x = r * Math.cos(alpha);
152: double y = r * Math.sin(alpha);
153: result
154: .addRow(new Object[] { new Double(r),
155: new Double(alpha), new Double(x),
156: new Double(y) });
157: }
158: }
159: return result;
160: }
161:
162: }
|