0

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?

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
user1862354
  • 117
  • 11

1 Answers1

0

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • I think I didn't explain my problem clearly. As mentioned above, I am transferring data from Oracle to MySQL. I have defined DataSource, Dialect beans for each database. Also, I have annotated @EnableJdbcRepositories for those classes with proper arguments. When a request comes, it is picking the proper connection and trying to execute a query. As I have annotated MySQL as primary, it always uses the identifier processing of MySQL dialect. Because of that the oracle queries always have backquote character (backquote is the default for MySQL DB) which creates a problem. – user1862354 Apr 16 '21 at 11:02