How to replace StatementLocator/StatementRewriter

[vszakd]

Hello,
the example in this post: https://manikandan-k.github.io/2015/06/04/Multi_tenant_applications_in_jdbi.html allowed for an easy way to selectively rewrite queries for additional parameter substitution:

public class SchemaReWriter implements StatementLocator {
    @Override
    public String locate(String sql, StatementContext ctx) throws Exception {
        if (nonNull(Schema.name.get())) {
            sql = sql.replaceAll(":schema", Schema.name.get());
        }
        return sql;
    }
}

@OverrideStatementLocatorWith(SchemaReWriter.class)
public interface UserDao {
...

Unfortunately, this does not work anymore with jdbi 3+, because StatementLocator has been removed.

What would the equivalent be in jdbi 3?

Thanks.

[stevenschlansker]

Hi @vszakd , can you define() an attribute and use statement templating instead? I am writing pseudocode and did not fully test this, but something like

class SchemaDefiner implements StatementCustomizer {
    void beforeTemplating(PreparedStatement stmt, StatementContext ctx) {
        ctx.define("schema", schema.name());
    }
}

@UseTemplateEngine(DefinedAttributeTemplateEngine.class)
@SqlQuery("select * from <schema>.table where foo = bar")
MyRow list();

[vszakd]

Thanks for the answer, that is exactly what I was looking for.
There is only one problem: using the angle bracket in the query, IntelliJ disables the SQL query autocomplete.

Given that DefinedAttributeTemplateEngine is coupled to <> from its lexer, I created this naive Engine (that seem to work):

public class CustomSchemaTemplateEngine implements TemplateEngine.Parsing {
    @Override
    public Optional<Function<StatementContext, String>> parse(String template, ConfigRegistry config) {
        String schemaAwareTemplate = template.replace(":schema_name", "public");
        return Optional.of(ctx -> schemaAwareTemplate);
    }
}

@UseTemplateEngine(CustomSchemaTemplateEngine.class)
@SqlQuery("select from :schema_name.table ...")
DataObject findAll() {
...

Aside from the dumb String.replace(), is this the right approach?
Specifically, is there any way to reuse the built-in mechanism for parameter substitution (with ":param_name")?

Thanks.

[stevenschlansker]

No, unfortunately that is generally not possible, and this is not a JDBI limitation.
If you use even pure JDBC and attempt to bind parameters like select * from ?.table you will get an error.
Parameters are limited and cannot be used for schema names, table names, or often other ancillary information like offset or limit. (at least in Postgres, which I am most familiar with)

[vszakd]

Renouncing to the safety of parameter binding, I came close to a "solution", which would be to define a custom TemplateEngine that instead of what is in DefinedAttributeTemplateEngine uses the code from ColonPrefixSqlParser and the ColonStatementLexer.

Unfortunately, that cannot be done in the application code (for it needs antlr for the lexer), but such TemplateEngine would have to be present in jdbi itself; of course, that would be less than desirable, because it would introduce ambiguity between the actual binding sintax and this surrogate (because they would both use the colon). And this, I believe, is why the <> syntax was introduced in the first place.

So I guess the choice is between IntelliJ SQL autocomplete and linearity/correctness of the implementation.

[stevenschlansker]

In that case, I think the simple string replace you showed above should be OK. Sorry IntelliJ has so much trouble with the angle brackets.