CODEWITHSUNDEEP
javahibernatejpaoracle11gspring-data-jpa
Petr Shypila
Petr Shypila

Reputation: 1509

How to add schema name to Query annotation in Spring Data JPA

Currently I have such piece of code, which doesn't work, since I have to add schema name before each table in a query(like DEV.DASHBOARDS_METADATA):

public interface DashboardMetadataDao extends CrudRepository<DashboardMetadata, Integer> {

    @Query("SELECT D FROM DASHBOARDS_METADATA D " + 
           "INNER JOIN FAC_DASHBOARDS_LINK DL ON D.ID = DL.DASHBOARD_ID " +
           "INNER JOIN FIRMS F ON DL.FAC_ID = F.FAC_UNIT_ID " + 
           "INNER JOIN USERS U ON U.FIRM_ID = F.FIRM_ID WHERE LOWER(U.USERID) = LOWER(:userid)")
    public Set<DashboardMetadata> findByUserId(@Param("userid") String userId);
}

The problem is that schema name differs from database to database (DEV/QA/PROD). Normally I use component's method which prepend schema's name to each table during query generation. How can do this using annotations?

Thanks!

Upvotes: 4

Views: 9682

Answers (2)

Giri
Giri

Reputation: 131

Below Code Snippet worked for me

@Query(
              value = "select *\n" + 
                    "from {h-schema}TABLE-A r left join {h-schema}TABLE-B p on r.ID=p.ID\n" + 
                    "left join {h-schema}TABLE-C pe on p.ID=pe.ID\n" + 
                    "left join {h-schema}TABLE-D e on pe.ENT_ID=e.ENT_ID\n" + 
                    "where r.role_type='Provisioning'", 
              nativeQuery = true)

Upvotes: 3

agilob
agilob

Reputation: 6223

Hibernate has a variable that can be used in native queries to get schema name called: {h-schema}

https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/chapters/query/native/Native.html#sql-global-catalog-schema You can just put {h-schema}table in your query. I think you have to convert it to a native query.

Upvotes: 5

Related Questions