Support multiple @JdbcRepository annotations with different dialect and activate appropriate one based on criteria

[mshannongit]

Feature description

Engineering teams may often use H2 DB for testing, and say MYSQL or ORACLE DB for production runtime.

test datasource ...
datasources:
default:
driverClassName: org.h2.Driver
username: sa
password: ''
dialect: H2
baseUrl: jdbc:h2:mem:dev
url: jdbc:h2:mem:dev

production datasource ...
datasources:
default:
url: ...
username: ...
password: ...
dialect: MYSQL
driver-class-name: com.mysql.cj.jdbc.Driver

When defining a Micronaut repository though, you can specify just the one dialect , and also just a single @query per method based on this dialect ...

e.g.

@JdbcRepository(dialect = Dialect.MYSQL)
public interface XXXRepository extends CrudRepository<XXX, Long> {
...

@Query("SELECT * FROM XXX WHERE XYZ = :xyz FOR UPDATE SKIP LOCKED") 
List<XXX> findByBlah(String xyz);

}

It would be handy to not have to rely on H2 providing basic MYSQL compatability - and instead be able to specify multiple JdbcRepository annotations with additional criteria attributes contained within similar to the @requires attributes.

e.g.

@JdbcRepository(dialect = Dialect.H2, requiresEnv = Environment.TEST)
@JdbcRepository(dialect = Dialect.MYSQL, requiresProperty = "datasources.default.dialect", requiresPropertyValue = "MYSQL")

or something similar.

Additionally, the @query annotation could then be extended to supply a dialect as well, such that a method could have multiple @query annotations on it with different dialects (or default and an explicit dialect)

@Query(dialect = Dialect.H2, value="SELECT * FROM xxx WHERE xyz = :xyz for update") 
@Query(value="SELECT * FROM XXX WHERE XYZ = :xyz FOR UPDATE SKIP LOCKED") 
List<XXX> findByBlah(String xyz);

[radovanradic]

I suppose that could be done using @Requires and/or @Replaces

interface BookRepository extends CrudRepository<Book, String> {
}

@JdbcRepository(Dialect = Dialect.ORACLE)
@Requires(env = "prod")
interface OracleBookRepository extends BookRepository {
// Override methods if needed
}

for the test

@JdbcRepository(dialect = Dialect.H2)
@Replaces(OracleBookRepository.class) // maybe not needed because Requires would select this repo for the tests
@Requires(env = "test")
interface H2BookRepository extends BookRepository {
// Override if needed for tests
}

[dstepanov]

Exactly, I would just use @Requires(property = "datasources.default.dialect", value="ORACLE")

[graemerocher]

we could add meta-annotations to the core to make this easier:

@JdbcRepository(dialect=ORACLE)
@Requires(property = "datasources.default.dialect", value="ORACLE")
@interface OracleRepository {}