How to set connection properties (includeSynonyms)

Question

My DB configuration in Spring Boot is done using yaml file:

# Common
spring:
  jpa:
    properties:
      hibernate:
        show_sql: true
        use_sql_comments: truecat
        format_sql: false
        ddl-auto: validate
        synonyms: true
        connection:
          includeSynonyms: true
#        CONNECTION_PROPERTY_INCLUDE_SYNONYMS_DEFAULT: 'true'
    database: ORACLE
  datasource:
    tomcat:
      max-active: 10
      max-idle: 10

# template only
---
spring:
  profiles: dev
  datasource:
    url: jdbc:oracle:thin:@//localhost:1521/DEV
    username: DEV
    password: ...

Initially I was facing

Schema-validation: missing table

So I added hibernate.synonyms=true, now I'm getting

Schema-validation: missing column

To solve that I'd need to alter connection like this https://gist.github.com/uggds/c3f0fa71037bc5177f60

Can I do that in configuration?

What I tried and didn't work

hibernate.connection.CONNECTION_PROPERTY_INCLUDE_SYNONYMS_DEFAULT: true
hibernate.connection.includeSynonyms: true

Related: DatabaseMetaData.getColumns returning an empty ResultSet for synonyms

I tried to google for that, but I found nothing...

edit:

Additional test based on M. Deinum's answer:

I strongly believe, that

spring:
  jpa:
    properties:
      hibernate.synonyms: true

and

spring:
  jpa:
    properties:
      hibernate:
        synonyms: true

is the same, at least it had the same effect, when I used one or the other (both resulting in Schema-validation: missing column

I added

  datasource:
    tomcat:
      max-active: 10
      max-idle: 10
      connection-properties:
        includeSynonyms: true

but I'm getting the same error

Schema-validation: missing column

When I completely removed

spring:
  jpa:
    properties:
      hibernate:
        synonyms: true

I'm getting

Schema-validation: missing table

edit 2:

Trying setting properties on hikari connection pool

spring:
  jpa:
    properties:
      hibernate:
        show_sql: true
        use_sql_comments: true
        format_sql: false
        ddl-auto: validate
        synonyms: true
    database: ORACLE
  datasource:
    hikari:
      connection-properties:
        includeSynonyms: true

I'm still getting:

missing column [zone_definitionpk_id]

Just to test, when I replace synonym with view it's fine. Also when I turn off validation it's fine. I mean the functionality works.

edit 3

Take care of connection-properties vs. datasource-properties and with synonyms the validation takes longer (at least for me).

user_2696260 · Answer

Setting the hibernate.connection properties is useless. You should be setting them on the datasource instead. To set additional, non default, properties you can use the connection-properties property on spring.datasource.tomcat.

EDIT: You aren't using Tomcat connection pool but the default HikariCP. So setting tomcat properties is pretty much useless.

Adding the following in your properties

spring.datasoure.hikari.datasource-properties.includeSynonyms=true

or yaml

spring:
  datasource:
    hikari:
      datasource-properties:
        includeSynonyms: true

You could also set the corresponding hibernate property, however not as you do. You need to set it through spring.jpa.properties. This is due to the fact that the synonyms isn't exposed as a property on the properties object used by Spring Boot. Hence you need to set it as a additional property.

NOTE it is importantto use the full property name hibernate.synonyms in this section, don't format it as YAML as that won't work!.

spring:
  jpa:
    properties:
      hibernate.synonyms: true

EDIT (1): As the pool used is Hikari and not Tomcatc changed to Hikari namespace and properties.

How to set connection properties (includeSynonyms)

1 Answers