36

I want to set hibernate.format_sql in a Spring Boot app. I want to set it using environment variables.

Spring Boot rather handily converts all environment variables from, for example, FOO_BAR_BAZ to properties called foo.bar.baz inside the Spring context.

How can I set a property that has an underscore in the target name, in Spring Boot, using environment variables? Presumably HIBERNATE_FORMAT_SQL will be translated to hibernate.format.sql?

DeejUK
  • 12,891
  • 19
  • 89
  • 169
  • 1
    The name of the property for hibernate doesn't matter, what matters is the name you use to retrieve it (why do you keep them the same?). Just give it a name that will work regardless of _ or . – M. Deinum Dec 09 '15 at 12:27
  • "why do you keep them the same?": Because he wants to set the Hibernate property. – dunni Dec 09 '15 at 12:30
  • That doesn't matter. You can name the property foo in your environment and then still set the `hibernate.format_sql` property for hibernate. They don't need to be the same. – M. Deinum Dec 09 '15 at 12:36
  • The `foo` thing was an example. I do not want to set a property called `foo`. I want to set `hibernate.format_sql` using environment variables. – DeejUK Dec 09 '15 at 12:39

3 Answers3

58

This is an old question but I'll answer it in case somebody else (like me) ends up here looking for this information.

HIBERNATE_FORMAT_SQL should do the trick

Actually it is not the OS environment variable that is "translated" but rather the Spring property name that is.

The name is translated in several ways and looked up against available environment variables. E.g. "hibernate.format.sql" is looked up as:

  1. hibernate.format.sql (as is)
  2. hibernate_format_sql (dots replaced with underscores)
  3. hibernate_format_sql (dashes replaced with underscores, the same in your case)
  4. hibernate_format_sql (dashes & dots replaced with underscores, the same in your case)

Then the same with UPPERCASE:

  1. HIBERNATE.FORMAT.SQL (as is)
  2. HIBERNATE_FORMAT_SQL (dots replaced with underscores)
  3. HIBERNATE_FORMAT_SQL (dashes replaced with underscores, the same again)
  4. HIBERNATE_FORMAT_SQL (dashes & dots replaced with underscores, the same again)

Although you cannot set an environment variable with a dot in the name with the set or export commands it is however possible with the env command. I defer judgement whether this is a good idea or not:

env "my.dotted.name=\"a value\"" the-command-you-want-to-run

Have a look at SystemEnvironmentPropertySource.java for details. I link to a specific version but you should make sure to look at the version you are using.

To troubleshoot these kinds of problems in a production environment you could try turning on debug logging for the property resolving code:

logging:
  level:
    org.springframework.core.env: DEBUG

... or by setting the appropriate environment variable :)

Edit: I highly recommend being familiar with the relevant Spring Boot documentation topic: https://docs.spring.io/spring-boot/docs/current/reference/html/spring-boot-features.html#boot-features-external-config

For the more tricky examples in the comments here, e.g. spring.jpa.properties.hibernate.criteria.literal_handling_mode, there might be different solutions available depending on how you launch you application.

You could set the variable as JSON, embedded in an environment variable.

env SPRING_APPLICATION_JSON='{"spring":{"jpa":{"properties":{"hibernate":{"criteria":{"literal_handling_mode":"BIND"}}}}}}' ./gradlew bootRun

Simply setting the variable as is might work also:

env spring.jpa.properties.hibernate.criteria.literal_handling_mode=BIND ./gradlew bootRun

Both of the above worked in my setup in so far as I was able to get the value in the running Spring Boot application this way:

@Value("${spring.jpa.properties.hibernate.criteria.literal_handling_mode}")
private String testSettingThroughEnvVariable;

Hope this helps! YMMV

Max
  • 821
  • 10
  • 11
  • Case in the property name doesn't matter - the whole name is simply uppercased. – Niel de Wet May 19 '17 at 11:10
  • I want to set spring.jpa.properties.hibernate.criteria.literal_handling_mode=BIND via ENV parameter, but it does not work. When I use underscores throughout the ENV setting they become dots when arriving in the service (and of course they are not picked up properly then by hibernate) – hecko84 May 11 '20 at 14:31
  • We have the same issue with spring.jpa.properties.hibernate.globally_quoted_identifiers. I ended up setting it directly in Java on SpringApplicationBuilder. While it's ok for that property, it's not for others. @hecko84 did you find a solution for the issue? – Marceau Sep 02 '20 at 09:51
  • 1
    @Marceau, no sorry no solution for being able to override it with ENV settings. I have it now set in the application.properties that are bundled along with the jar in the docker image. In the end, for this parameter, it is ok like that, as there should be anyways no reason why you would want to override it. Furthermore, by that, we prevent our OPs guys from messing with the service :D – hecko84 Sep 02 '20 at 12:24
  • 2
    This doesn't answer the question. The question was about `hibernate.format_sql`, but this answer for some reason presumes `hibernate.format.sql` – Christopher Schneider Dec 01 '21 at 16:30
1

I had a same issue, environment variable spring_jpa_properties_hibernate_default_schema translated by Spring as spring.jpa.properties.hibernate.default.schema but Hibernate needs hibernate.default_schema.

For sertain reason (devops problems) i could not change it just to spring.jpa.properties.hibernate.default_schema - it`s work on local machine.

I solved it like this

@Component
public class HibernateConfig implements HibernatePropertiesCustomizer {

    private final static String HIBERNATE_CUSTOM_DEFAULT_SCHEMA_PARAM = "hibernate.default.schema";

    @Override
    public void customize(Map<String, Object> hibernateProperties) {
        if (hibernateProperties.containsKey(HIBERNATE_CUSTOM_DEFAULT_SCHEMA_PARAM)) {
            hibernateProperties.put(AvailableSettings.DEFAULT_SCHEMA, hibernateProperties.get(HIBERNATE_CUSTOM_DEFAULT_SCHEMA_PARAM));
        }
    }
}
Volatile
  • 411
  • 4
  • 7
0

Turns out SPRING_JPA_SHOW_SQL=true works just fine. I don't know whether this means that names for that particular property are overloaded, or whether Spring is doing something more clever. It solved my issue and gave the desired behaviour for the example I asked about though.

DeejUK
  • 12,891
  • 19
  • 89
  • 169