How to set default schema name for Spring Data JPA queries?

Question

I want to set default schema for postgresql. When I try to set property("hibernate.default_schema") and also update table anotation with schema prop, hibernate still generates query without any schema name like below;

select log0_.id as id1_0_ from log log0_

When I change table name to "dbo.log", I can get resultSet without any error.

 @Table(name = "dbo.log")

Is there any way to set default schema for postgresql queries?

@Configuration
@EnableJpaRepositories(
    basePackages = "com.test.repository.postgresql",
    entityManagerFactoryRef = "postgresqlEntityManagerFactory",
    transactionManagerRef = "postgresqlTransactionManager"
)
public class PostgreSQLConfig {

    ....

    @Bean(name = "postgresqlEntityManagerFactory")
    public EntityManagerFactory entityManagerFactory(@Qualifier("postgresqlDatasource") DataSource postgresqlDatasource) {

        Properties jpaProperties = new Properties();
        jpaProperties.setProperty("hibernate.dialect", hibernateDialect);
        jpaProperties.setProperty("hibernate.show_sql", hibernateShowSql);
        jpaProperties.setProperty("hibernate.ddl-auto", hibernateDDLAuto);
        jpaProperties.setProperty("hibernate.naming.physical-strategy", hibernatePhysicalStrategy);
        jpaProperties.setProperty("hibernate.default_schema", "dbo");

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setDatabase(Database.POSTGRESQL);
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setJpaProperties(jpaProperties);
        factory.setPackagesToScan("com.test.entity.postgresql");
        factory.setDataSource(postgresqlDatasource);
        factory.afterPropertiesSet();
        return factory.getObject();
    }

}

Here is my entity class;

@Entity
@Table(name = "log", schema = "dbo")
public class Log{
    ....
}

Answer 1

if you are using application.properties

spring.datasource.url=jdbc:postgresql://myserver:5432/mydb

spring.jpa.properties.hibernate.default_schema=myschema

spring.datasource.username=myuser

spring.datasource.password=mypassword

spring.datasource.driver-class-name=org.postgresql.Driver

Answer 2

That should be declard in the JDBC Connection URL in the DataSource. The URL schema looks like: jdbc:postgresql://host:port/database, where database is the schema.

If that's not enough set also in the URL the currentSchema.

Specify the schema (or several schema separated by commas) to be set in the search-path. This schema will be used to resolve unqualified object names used in statements over this connection.

Here are the docs for the JDBC Driver: https://jdbc.postgresql.org/documentation/head/connect.html