JpaRepository native query: invalid table name

Question

When I'm trying to select table by parameter:

@Repository
public interface IParametersRepository extends JpaRepository<Parameters, Long> {

    @Query(value = "SELECT param_def_id FROM :param", nativeQuery = true)
    Optional<List<String>> GetParameterDefIDs(@Param("param") String param);
}

I get invalid table name error:

Hibernate: SELECT param_def_id FROM ?
2021-03-18 09:12:20.734 TRACE 980 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : 
binding parameter [1] as [VARCHAR] - [PARAM_APP]
2021-03-18 09:12:20.787  WARN 980 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : 
SQL Error: 903, SQLState: 42000
2021-03-18 09:12:20.787 ERROR 980 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : 
ORA-00903: invalid table name

But when the query is written manually I get all data from db.

@Query(value = "SELECT param_def_id FROM PARAM_APP", nativeQuery = true)
Optional<List<String>> GetParameterDefIDs(@Param("param") String param);

This is my request: localhost:8080/api/app/parameters?param=PARAM_APP

Look at this answers https://stackoverflow.com/questions/40956013/how-to-replace-table-name-with-value-from-parameter-while-using-spring-data-jpa — SinisaT90, Mar 18 '21 at 09:21

score 0 · Accepted Answer · answered Mar 18 '21 at 14:09

Hello (I can't reply on your comment @SinisaT90) but to build on your answer, @RileyRiley, you could use the Custom Repository of Spring as shown here https://www.baeldung.com/spring-data-composable-repositories#3-custom-repositories

In other word, you could have something like that:

public interface CustomRepository {
    List<String> getData(String tableName);
}

and

@Repository
public class CustomRepositoryImpl implements CustomRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<String> getData(String tableParam) {
        String sqlQuery = "SELECT string FROM " + tableParam; 
        return entityManager.createQuery(sqlQuery).getResultList();
    }
}

and in your service

@Autowired
CustomRepository customRepository;

Yes, that solution worked for me. Thank you – RileyRiley Mar 18 '21 at 23:45 — RileyRiley, Mar 18 '21 at 23:45

JpaRepository native query: invalid table name

1 Answers1