I have a problem with my native query using spring data jpa

user_11115420 0
Viewed 509

I have a query that i can get result from oracle db, but when i use that query as native query in spring data jpa. I am getting exception as invalid table name

Here is my running query

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'

I am using it in spring data jpa code below

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);

For the parameter prQry i will generate query dynamically and have it in string and pass it as parameter since i will pass the value as

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

after iteration dynamically i will be getting like above.

After implementing when i run it, i am getting the exception as invalid table name. Can anyone help me on this issue to use it in different way or have i done anything wrong in my code.

jpa oracle spring database
1 Answers

Unfortunately you cannot achieve this with Spring Data Repositories, because the parms would translate as a String and in the FINAL query you you have:

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

This would show as string and invalid query. The @Param it is only for WHERE clause.

One way of achieving what you want is using the 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();
}

This is the idea anyway for dynamic queries when you change the FROM condition.

user_31548830
Related