Spring data jpa default schema for native query in repository

Question

I am using Spring-dat-jpa in Spring Boot Application and trying to execute a native query in repository. It works fine when I append schema name with table name but it does not work independently and won't pick the schema name from application.properties file as it does for Entity classes I defined

I am using below property to set default schema it works fine for entity classes and Named queries. But when I write any native query in @query it starts complaining about schema name:

spring.jpa.properties.hibernate.default_schema=NPS_WO

Below is my query:

@Repository
public interface TestRepository extends JpaRepository<TypeRef,Long> {


    @Query(value="SELECT t.* from Type_Ref t")
    public List<Object[]> findAllTypes();

}

In my actual scenario this query will be replaced by a complex native query.

Any help will be really appreciated.

Answer 1

You can access the default schema name within native queries by using {h-schema}.

So your query should look like that:

@Query(value="SELECT t.* from {h-schema}Type_Ref t")
public List<Object[]> findAllTypes();

There is no dot between the h-schema and the table name, this is added automatically.