Spring data jdbc Identifier Processing

Question

Use case: Connecting MySQL and oracle database

Issue: If I annotate any one of the data sources as primary, it always uses the primary database Identifier processing and forms the query based on that.

MySQL

@Bean
@Primary
@Qualifier("mySqlJdbcConverter")
public JdbcConverter mySqlJdbcConverter(JdbcMappingContext mappingContext, @Lazy RelationResolver relationResolver,
        @Qualifier("mysqlJdbcOperationsReference") NamedParameterJdbcOperations mysqlJdbcOperationsReference) {
    DefaultJdbcTypeFactory jdbcTypeFactory = new DefaultJdbcTypeFactory(
            mysqlJdbcOperationsReference.getJdbcOperations());
    return new BasicJdbcConverter(mappingContext, relationResolver, mySqlJdbcCustomConversions(), jdbcTypeFactory,
            IdentifierProcessing.create(new Quoting("`"), LetterCasing.UPPER_CASE));
}

@Bean
@Primary
@Qualifier("mySqlJdbcDialect")
public Dialect mySqlJdbcDialect(final JdbcConverter JdbcConverter) {
    return MySqlDialect.INSTANCE;
}

Oracle

@Bean
@Qualifier("oracleJdbcConverter")
public JdbcConverter oracleJdbcConverter(JdbcMappingContext mappingContext, @Lazy RelationResolver relationResolver,
        @Qualifier("oracleJdbcOperationsReference") NamedParameterJdbcOperations oracleJdbcOperationsReference) {
    DefaultJdbcTypeFactory jdbcTypeFactory = new DefaultJdbcTypeFactory(
            oracleJdbcOperationsReference.getJdbcOperations());
    return new BasicJdbcConverter(mappingContext, relationResolver, oracleJdbcCustomConversions(), jdbcTypeFactory,
            IdentifierProcessing.create(new Quoting("\""), LetterCasing.UPPER_CASE));
}

@Bean
@Qualifier("oracleJdbcDialect")
@RequestScope
public Dialect oracleJdbcDialect(final JdbcMappingContext JdbcConverter) {
    return OracleDialect.INSTANCE;
}

In the above case, always query carries backquote character. Even though it is connecting to the oracle database, but the identifier is always backquote

Query:

SELECT `service`.`SERVICE_ID` AS `SERVICE_ID`, `service`.`SERVICE_NAME` AS `SERVICE_NAME` FROM `service`

May I know why it is happening?

Answer 1

The Dialect is not picked up as bean from the ApplicationContext. If you want to use your own Dialect you need to do the following:

1. implement your own Dialect.
2. implement a JdbcDialectProvider returning that Dialect.
3. register the provider by putting a file spring.factories in the META-INF folder of your class path and add the line org.springframework.data.jdbc.repository.config.DialectResolver$JdbcDialectProvider=<fully qualified name of your JdbcDialectProvider>

See https://spring.io/blog/2020/05/20/migrating-to-spring-data-jdbc-2-0#dialects

But really you shouldn't have to do that, since dialects for Oracle and MySql are already provided out of the box.