Spring Boot JOOQ sql dialect not picked up from application.properties

Question

Background: i am hosting the trial version of jooq 3.9.1 (proprietary eg oracle db compatible) in my nexus repository - all the dependencies in my pom relating to jooq point to that.

i have this line in my application.properties

jooq.sql-dialect=ORACLE

but when i inspect the injected dslContext the dialect is set to "DEFAULT" and not ORACLE as expected/desired.

I am currently getting round it by autowiring the datasource rather than the dslcontext and then setting the sql dialect (as shown below) - but wondering why autowiring the dslcontext directly doesnt work as expected

@Autowired
private DataSourceConnectionProvider dataSource;



public static final SQLDialect sqlDialect = SQLDialect.ORACLE;

public DSLContext getDSL(){
    return DSL.using(dataSource, sqlDialect);
}

Answer 1

Lukas' comment Spring Boot JOOQ sql dialect not picked up from application.properties is correct.

Here is an example how to do it and test:

Inside application.properties

spring.jooq.sql-dialect = Postgres

And tested with an integration test ConfigIT:

@RunWith(SpringRunner.class)
@JdbcTest
@ImportAutoConfiguration(JooqAutoConfiguration.class)
public class ConfigIT {

    @Autowired
    private DSLContext dSLContext;

    @Test
    public void dialectShouldBePickedUp() {
        assertThat(dSLContext.configuration().dialect(), is(SQLDialect.POSTGRES));
    }
}

You'll find the working and tested example in the repositories of http://springbootbuch.de here: https://github.com/springbootbuch/database_examples

What's important ist to choose the right, case sensitive name. In my example, it's Postgres, in your example it should be Oracle and you must use the right property. Sadly, those names vary across different tool sets. For jOOQ you'll find the constants in org.jooq.SQLDialect

Answer 2

Spring Boot by default uses the org.jooq dependency, which is the Maven groupId for the jOOQ Open Source Edition:

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
</dependency>

However, the Oracle SQLDialect is contained only in the commercial distributions of jOOQ, which are available under different groupId (and not from Maven Central but from here (trial) and here (express, professional, enterprise edition)):

<groupId>org.jooq.pro</groupId>        <!-- for commercial editions -->
<groupId>org.jooq.pro-java-6</groupId> <!-- for commercial editions with Java 6 support -->
<groupId>org.jooq.trial</groupId>      <!-- for the free trial edition -->

The distributions are almost completely binary compatible, so you should be able to simply replace the <groupId> in your own pom.xml for Spring Boot to work with jOOQ and Oracle.