java - SQL - 在 DAO 中执行时列名无效

Question

我正在对 Spring 应用程序进行查询，该应用程序将根据此查询选择数据库中的数据:

SELECT * FROM
    (
        SELECT a.*, rownum r__
        FROM
        (
            SELECT * FROM SUBSCRIPTIONS WHERE status = 'active' and is_blocked = 'N'
        ) a
        WHERE rownum < ((1 * 3) + 1 )
    )
WHERE r__ >= (((1-1) * pageSize) + 1)

目标是简单地对结果进行分页。

当我在数据库查询窗口上执行代码时，我得到了预期的结果，但是当我像通常一样直接在 java 代码中执行时，我得到了这个错误:

Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM( SELECT a.*, rownum r__ FROM ( SELECT * FROM SUBSCRIPTIONS WHERE status = 'active' and is_blocked = 'N' ) a WHERE rownum < ((? * ?) + 1 ))WHERE r__ >= (((?-1) * ?) + 1)]; nested exception is java.sql.SQLException: Invalid column name

我不明白为什么会返回此错误，因为我的查询在 Oracle SQL 客户端上执行时有效...

要执行我的查询，我这样做:

MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("pageSize", paging.getItemsPerPage());
        params.addValue("pageNumber", paging.getPageToFetch());
        SqlQueryLogger.logDebugSqlQuery(selectActiveAndUnblockedSubscriptionPaged, params, log);

        List<Subscription> subscriptions = getNamedParameterJdbcTemplate().query(selectActiveAndUnblockedSubscriptionPaged, params, new SubscriptionRowMapper());
        SubscriptionPagingResult subscriptionPagingResult = new SubscriptionPagingResult();
        subscriptionPagingResult.setResult(subscriptions);

并且 selectActiveAndUnblockedSubscriptionPaged 变量是:

"SELECT * FROM" +
            "(" +
            "    SELECT a.*, rownum r__" +
            "    FROM" +
            "    (" +
            "        SELECT * FROM SUBSCRIPTIONS WHERE status = 'active' and is_blocked = 'N'" +
            "    ) a" +
            "    WHERE rownum < ((:pageNumber * :pageSize) + 1 )" +
            ")" +
            "WHERE r__ >= (((:pageNumber-1) * :pageSize) + 1)";

有人知道为什么这段代码不起作用吗？谢谢

Answer 1

只是建议您在 main from () 之后错过了表别名

SELECT t.* FROM
(
    SELECT a.*, a.rownum r__
    FROM
    (
        SELECT * 
        FROM SUBSCRIPTIONS 
        WHERE status = 'active' 
        and is_blocked = 'N'
    ) a
    WHERE a.rownum < ((1 * 3) + 1 )
) t 
WHERE t.r__ >= (((1-1) * t.pageSize) + 1)