JdbcRepository - support ability to tokenize and inject table name in @Query strings - particularly useful for inheritance

[mshannongit]

Feature description

Consider a set of tables with a common set of base columns and a common set of queries that would apply to said columns. Should the queries associated with the said columns not be achievable using the smart query finder method naming strategy, then one needs to leverage @Query("SELECT XXX FROM YYY WHERE ZZZ") style annotations.

It would be extremely useful if we could tokenize the table YYY piece above and automatically substitute with the entity model classes table name and thus not have to hardcode the table name in the query.

It is particularly relevant when using JdbcRepository inheritance ... e.g.

---

public interface RetryableOperationsTaskRepository<E extends RetryableOperationsTask, I extends Long> extends PageableRepository<E, I> {

@Executable
int updateTaskVisible(@Id I id, @Version Integer version, LocalDateTime visible);

List<RetryableOperationsTask> findAndLockApplicableTasks(final LocalDateTime visibleBefore, final int limit);

}

---

@JdbcRepository public abstract class RetryableOperationsSalesTaskRepository implements RetryableOperationsTaskRepository<RetryableOperationsSalesTask, Long> {

@Query("SELECT * FROM RETRYABLE_OPERATIONS_SALES_TASK t WHERE t.VISIBLE < :visibleBefore ORDER BY t.ID ASC LIMIT :limit FOR UPDATE SKIP LOCKED")
@Executable
@Override
public abstract List<RetryableOperationsTask> findAndLockApplicableTasks(LocalDateTime visibleBefore, final int limit);

}

---

@JdbcRepository public abstract class RetryableOperationsMarketingTaskRepository implements RetryableOperationsTaskRepository<RetryableOperationsMarketingTask, Long> {

@Query("SELECT * FROM RETRYABLE_OPERATIONS_MARKETING_TASK t WHERE t.VISIBLE < :visibleBefore ORDER BY t.ID ASC LIMIT :limit FOR UPDATE SKIP LOCKED")
@Executable
@Override
public abstract List<RetryableOperationsTask> findAndLockApplicableTasks(LocalDateTime visibleBefore, final int limit);

}

---

The only difference between the two overriding findAndLockApplicableTasks methods above is the table name.

Ideally we would not have needed to override these methods and instead could have inherited directly from the top-level interface.

However we currently have no way in the interface @Query to have some type of dynamic tokenized table_name that can be substituted in.

We are looking for something like the following in the top-level interface -

@Query("SELECT * FROM ${REPOSITORY_TABLE} t WHERE t.VISIBLE < :visibleBefore ORDER BY t.ID ASC LIMIT :limit FOR UPDATE SKIP LOCKED")
@Executable
@Override
List<RetryableOperationsTask> findAndLockApplicableTasks(LocalDateTime visibleBefore, final int limit);

[dstepanov]

JDBC and drivers don't support parametrizing the table name

[mshannongit]

Doesn't Micronaut data take care of taking the supplied Query value and generating a prepared statement of sorts? If so, can Micronaut data not substitute the table name token with the table name value from the associated entity class - just as it would with any other query defined in the repository that did not use an @Query annotation?

[dstepanov]

It does it at the compilation time, we try to avoid doing this kind of stuff at runtime

[mshannongit]

Can above not be achieved at compile too though?
We are simply wanting to replace some token like $REPOSITORY_TABLE$ in the @Query string with the table name extracted from the entity?

[dstepanov]

Where do you store the token?

[mshannongit]

The developer would put something static like @Query(select * from $REPOSITORY_TABLE$) in the source code. At compile time as part of the annotation processing, I'm hoping Micronaut Data processors could detect the static token string $REPOSITORY_TABLE$ and work out from the Entity associated with the repo its table name annotation value and substitute in the table name to the compiled/generated code.