How to configure JOOQ settings using Spring-Boot config?

Question

I want to tell JOOQ not to quote the identifiers in the SQL it generates as per the answer here: https://stackoverflow.com/a/28146263/924597

But given I'm letting Spring Boot autoconfigure JOOQ - where do I put this setting?

It seems like the basic spring boot config only supports setting spring.jooq.sql-dialect in the application.properties, as shown in the JOOQ-spring-boot-example.

I tried putting spring.jooq.renderNameStyle=AS_IS in the application.properties but it didn't have any effect on the SQL.

Is there was way customise the Spring boot JOOQ configuration without having to configure JOOQ myself?

I am using SpringBoot 2.1.7.RELEASE and JOOQ 3.11.12.

Answer 1

Note that the answer from Lukas worked for configuring the rendering style, but resulted in JOOQ no longer participating in Spring transaction handling.

Below is the code I've added to my project to customise the identifier rendering.

import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.RenderNameStyle;
import org.jooq.conf.Settings;
import org.jooq.impl.DataSourceConnectionProvider;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.DefaultDSLContext;
import org.jooq.impl.DefaultExecuteListenerProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;


/**
 Override spring-boot config so JOOQ settings can be customised.
 See: https://stackoverflow.com/q/57542573/924597

 Spring-boot config taken from:
 https://github.com/eugenp/tutorials/blob/master/spring-jooq/src/test/java/com/baeldung/jooq/springboot/InitialConfiguration.java
*/
@Component
@Configuration
public class JooqConfig {
  @Autowired DataSource dataSource;

  @Bean
  public DataSourceConnectionProvider connectionProvider() {
    return new DataSourceConnectionProvider(
      new TransactionAwareDataSourceProxy(dataSource));
  }

  @Bean
  public DSLContext dsl() {
    return new DefaultDSLContext(configuration());
  }

  public DefaultConfiguration configuration() {
    DefaultConfiguration config = new DefaultConfiguration();
    config.set(connectionProvider());
    config.set(SQLDialect.POSTGRES);
    config.set(new Settings().
      withRenderNameStyle(RenderNameStyle.AS_IS ));
    config.set(new DefaultExecuteListenerProvider(
      new JooqExceptionTranslator() ));
    return config;
  }
}

Note: The above code appears to work, but results in my IDE telling me that I have multiple beans registered for the DSLContext type. I also added JooqAutoConfigration to my list of excluded spring-boot auto-config.

@EnableAutoConfiguration(exclude = {
  SecurityAutoConfiguration.class,
  DataSourceAutoConfiguration.class,
  JooqAutoConfiguration.class
})