database - 我使用 spring data jpa 的 native 查询有问题

Question

我有一个查询，我可以从 oracle db 获取结果，但是当我将该查询用作 spring data jpa 中的 native 查询时。我收到异常，因为表名无效

这是我正在运行的查询

SELECT listagg(COLUMN_TWO,' ') within group (order by COLUMN_TWO) 
FROM (select 'RDIFP' as val from dual union all
      select 'RDIFP' as val from dual union all
      select 'DROWM' as val from dual
     ) v left join
     TABLE_NAME_ONE 
     on v.val = COLUMN_TWO, TABLE_NAME_TWO, TABLE_NAME_THREE
WHERE 
COLUMN_THREE = COLUMN_FOUR AND  
COLUMN_FIVE = COLUMN_SIX AND 
COLUMN_ONE = '176134'

我在下面的 spring data jpa 代码中使用它

static final String QUERY_VALUES =
            "SELECT listagg(COLUMN_TWO,' ') within group (order by COLUMN_TWO) 
FROM (:prQry) v left join
     TABLE_NAME_ONE 
     on v.val = COLUMN_TWO, TABLE_NAME_TWO, TABLE_NAME_THREE
WHERE 
COLUMN_THREE = COLUMN_FOUR AND  
COLUMN_FIVE = COLUMN_SIX AND 
COLUMN_ONE = '176134'";

@Query(value = QUERY_VALUES, nativeQuery=true)
String getValuesById(@Param("prQry") String prQry);

对于参数prQry，我将动态生成查询并将其放入字符串中并将其作为参数传递，因为我会将值传递为

String prQry= "select 'RDIFP' as val from dual union all
      select 'RDIFP' as val from dual union all
      select 'DROWM' as val from dual";

动态迭代后，我会像上面那样。

在我运行它时实现后，我收到无效表名的异常。任何人都可以在这个问题上帮助我以不同的方式使用它，或者我在我的代码中做错了什么。

Answer 1

不幸的是，您无法使用 Spring Data Repositories 实现此目的，因为 parms 将转换为字符串，并且在 FINAL 查询中您拥有:

SELECT listagg(COLUMN_TWO,' ') within group (order by COLUMN_TWO)
FROM ("select 'RDIFP' as val from dual ... ") v

这将显示为字符串和无效查询。 @Param 仅用于 WHERE 子句。

实现您想要的目标的一种方法是使用 EntityManager:

@Autowired 
EntityManager entityManager;

static final String QUERY_VALUES =
        "SELECT listagg(COLUMN_TWO,' ') within group (order by COLUMN_TWO) 
            FROM (:prQry) v left join
                 TABLE_NAME_ONE 
                 on v.val = COLUMN_TWO, TABLE_NAME_TWO, TABLE_NAME_THREE
            WHERE 
            COLUMN_THREE = COLUMN_FOUR AND  
            COLUMN_FIVE = COLUMN_SIX AND 
            COLUMN_ONE = '176134'";

public String getValuesById(String prQry) {
    Query q = entityManager.createNativeQuery(QUERY_VALUES.replace(":prQry", prQry), String.class);
    return q.getSingleResult();
}

无论如何，当您更改 FROM 条件时，这就是动态查询的想法。