java - H2用户自定义函数被多次调用

Question

我使用的是 h2 v1.3.176。
我有执行 RECURSIVE 查询的用户定义函数。

public static ResultSet getChildCategories(Connection connection, long categoryId) throws SQLException {
    String sql = 
            "WITH RECURSIVE r(CATEGORY_ID, PARENT_ID) AS (\n" +
            "    SELECT   CATEGORY_ID\n" +
            "            ,PARENT_ID\n" +
            "    FROM     CATEGORY\n" +
            "    WHERE    CATEGORY_ID = " + categoryId + "\n" +
            "    UNION ALL\n" +
            "    SELECT   CATEGORY.CATEGORY_ID\n" +
            "            ,CATEGORY.PARENT_ID\n" +
            "    FROM     CATEGORY, r\n" +
            "    WHERE    CATEGORY.PARENT_ID = r.CATEGORY_ID\n" +
            ")\n" +
            "SELECT CATEGORY_ID FROM r";
    ResultSet resultSet = connection.createStatement().executeQuery(sql);
    SimpleResultSet rs = new SimpleResultSet();
    rs.addColumn("CATEGORY_ID", Types.INTEGER, 12, 0);
    try {
        while(resultSet.next()) {
            rs.addRow(resultSet.getLong(1));
        }
    } finally {
        resultSet.close();
    }
    return rs;
}

我已经按照SQL注册了这个函数。

create alias GET_CHILD_CATEGORIES for "com.myapp.db.function.Functions.getChildCategories";

我的问题是，当我执行以下查询时，getChildCategories 函数将被多次调用。

SELECT DISTINCT  B.BOOK_ID
                ,B.SERIES_ID
                ,B.TITLE
                ,B.ISBN
                ,B.VOLUME
                ,(
                    SELECT  MAX(SAME_SERIES.VOLUME)
                    FROM    BOOK SAME_SERIES
                    WHERE   SAME_SERIES.SERIES_ID = B.SERIES_ID
                    AND     SAME_SERIES.VOLUME IS NOT NULL
                ) AS VOLUME_COUNT
                ,B.PAGE_COUNT
                ,B.FILE_PATH
                ,B.SORTABLE_FILE_NAME
                ,B.SIZE
                ,B.HASH
                ,B.COVER_IMAGE_TYPE
                ,B.COVER_PAGE_NO
                ,B.COVER_LARGE_IMAGE_URL
                ,B.COVER_SMALL_IMAGE_URL
                ,B.COVER_CROP_COORD
                ,B.IS_ENCRYPT
                ,B.PUBLISHER_ID
                ,B.PUBLISHED_DATE
                ,B.CREATION_TIME
                ,B.LAST_MODIFIED_TIME
                ,B.NOTE
                ,B.IS_ISBN_SEARCH
                ,S.CATEGORY_ID
                ,S.TITLE
                ,BA.AUTHOR_ID
                ,BT.TAG_ID
FROM             BOOK AS B
INNER JOIN       SERIES AS S ON S.SERIES_ID = B.SERIES_ID
LEFT OUTER JOIN  BOOK_TAG AS BT ON BT.BOOK_ID = B.BOOK_ID
LEFT OUTER JOIN  BOOK_AUTHOR AS BA ON BA.BOOK_ID = B.BOOK_ID
WHERE 
(
    S.CATEGORY_ID IN (SELECT CATEGORY_ID FROM GET_CHILD_CATEGORIES(106))
    And 
    S.IS_COMPLETION = 1
)
ORDER BY  BA.AUTHOR_ID

为什么这个函数会被多次调用？

Answer 1

摘自H2 documentation

A function that returns a result set can be used like a table. However, in this case the function is called at least twice: first while parsing the statement to collect the column names (with parameters set to null where not known at compile time). And then, while executing the statement to get the data (maybe multiple times if this is a join). If the function is called just to get the column list, the URL of the connection passed to the function is jdbc:columnlist:connection. Otherwise, the URL of the connection is jdbc:default:connection.

第一次调用仅检索结果集列类型。然后你必须检查连接 URL 是否为“jdbc:columnlist:connection”。如果为 true，则必须返回带有列列表的空结果集。

连接url测试为:

connection.getMetaData().getURL().equals("jdbc:columnlist:connection");