CODEWITHSUNDEEP
javahibernatespring-bootoracle11g
Anand
Anand

Reputation: 391

jdbcTemplate unable to resolve {h-schema} on runtime

I am trying to get db schema name on runtime using spring.jpa.properties.hibernate.default_schema property of spring but it is not resolving the {h-schema} on runtime.

DemoApplication.java

public class DemoApplication 
{
    public static void main(String[] args) 
    {
        ApplicationContext context = SpringApplication.run(DemoApplication.class, args);
        Test test = context.getBean(Test.class);
        System.err.println("The required count is = "+test.getCount());
    }
}

Test.java

@FunctionalInterface
public interface Test 
{
    public BigDecimal getCount();
}

TestImpl.java

@Service
public class TestImpl implements Test
{

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public BigDecimal getCount() 
    {        
        return (BigDecimal) jdbcTemplate.queryForList("select count(*) from {h-schema}test").get(0).values().toArray()[0];
    }

}

application.properties

spring.datasource.url=jdbc:oracle:thin:@XXX:YYY/ZZZ
spring.jpa.database-platform=org.hibernate.dialect.Oracle12cDialect
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.username=X1
spring.datasource.password=Y1
spring.jpa.properties.hibernate.default_schema=Z1

#hibernate config
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true
spring.jpa.hibernate.ddl-auto=none

logging.level.org.springframework.jdbc.core = TRACE

ErrorLog

Executing SQL query [select count(*) from {h-schema}test]

Exception in thread "main" org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [select count(*) from {h-schema}test]; SQL state [99999]; error code [17034]; Non supported SQL92 token at position: 22; nested exception is java.sql.SQLException: Non supported SQL92 token at position: 22

I'm not sure how to resolve the spring.jpa.properties.hibernate.default_schema variable from properties file into {h-schema}. please provide me any appropriate approach or some alternative to the issue.

[Edit] : After lots of debugging i found that spring provides two propeties namely spring.datasource.hikari.schema and spring.datasource.hikari.connection-init-sql which may suffice some of the needs but they kind of alter the session as well which means i cannot query the data from the schema i logged in therefore i still would like someone to suggest something related to concept of resolving {h-schema} from properties file so that i can switch schema on need basis in native queries without hardcoding schema names or changing schema sessions explicitly.

Upvotes: 3

Views: 1316

Answers (1)

Taner
Taner

Reputation: 4569

In short; since the default schema information declared in your application.properties (or .yml) file, you may get this value with @Value annotation and can use it in your SQL statement.

For "show me the code" ones, your TestImpl.java class should become as following;

@Service
public class TestImpl implements Test
{

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Value("${spring.jpa.properties.hibernate.default_schema}")
    private String defaultSchema;

    @Override
    public BigDecimal getCount() 
    {        
        return (BigDecimal) jdbcTemplate.queryForList("select count(*) from  " + defaultSchema + ".test").get(0).values().toArray()[0];
    }

}

Note: Don't miss adding the "." before your table name.

Upvotes: 1

Related Questions