How to modify SQL statements before they get executed in JDBI 3 (SQL Objects)

Question

I would like to intercept and modify SQL statements before they get executed in JDBI 3 (SQL Objects). The reason for it is to replace custom token placeholders with schema names. I found this thread but it is for JDBI 2 (Using Dropwizard & JDBI to query database with multiple schemas?). I tried the approach below but I got stuck on how to modify the final SQL query before it's executed.

public class Product {
    private int id = -1;
    private String name = "";

    public Product() {
    }

    public Product(int id, String name) {
       this.id = id;
       this.name = name;
    }

    public int getId() {
       return this.id;
    }

    public void setId(int id) {
       this.id = id;
    }

    public String getName() {
       return this.name;
    }

    public void setName(String name) {
       this.name = name;
    }        
}

public class ProductMapper implements RowMapper<Product> {

    @Override
    public Product map(ResultSet r, StatementContext ctx) throws SQLException {
        Product product = new Product();

        product.setId(r.getInt("id"));
        product.setName(r.getString("name"));

        return product;
    }
}

@SchemaRewriterFactory
@UseClasspathSqlLocator
public interface ProductDao {

    /**
    * The sql query is located in resources ([package]/listProducts.sql)
    * listProducts.sql: SELECT PRODUCT_ID ID, NAME FROM :schema.PRODUCTS
    * @return
    */
   @SqlQuery
   @RegisterRowMapper(ProductMapper.class)
   List<Product> listProducts();

}


@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
@SqlStatementCustomizingAnnotation(SchemaRewriterFactory.SchemaRewriter.class)
public @interface SchemaRewriterFactory {

    public class SchemaRewriter implements SqlStatementCustomizerFactory {

        @Override
        public SqlStatementCustomizer createForMethod(Annotation annotation, Class<?> sqlObjectType, Method method) {
            return null;
        }

        @Override
        public SqlStatementParameterCustomizer createForParameter(Annotation annotation, Class<?> sqlObjectType, Method method, Parameter param, int index, Type paramType) {
            return null;
        }

        @Override
        public SqlStatementCustomizer createForType(Annotation annotation, Class<?> sqlObjectType) {
            return q -> q.addCustomizer(new StatementCustomizer() {

                @Override
                public void beforeBinding(PreparedStatement stmt, StatementContext ctx) throws SQLException {}

                @Override
                public void beforeExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException {                          
                    System.out.println(stmt.toString());
                    //TODO: HOW DO I MODIFY SQL (REPLACE TOKENS - :schema) BEFORE IT GETS EXECUTED?
                }

                @Override
                public void afterExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException { }


             });

        }

    }
}

I also found this thread which deals with custom tag replacements using @Define (Dynamic Order in JDBI SQL Object Queries). If possible, I would like to avoid passing extra parameters on every call. Any help would be appreciated!

If I got it right, wouldn't be easier and more secure to use StringTemplate? — Spasa Mihajlovic, Oct 03 '18 at 12:28
Easier in what sense? If I use the StringTemplate approach, then I would have to pass that schema name parameter to all my functions, that's exactly what I am trying to avoid. In the example above with JDBI 2, it was possible to intercept and modify SQL queries before they get executed. Was that functionality completely removed from JDBI 3? Handling potential security issues (SQL injection) before the execution could be done in one place where the token replacement occurs. — The Code Guy, Oct 04 '18 at 13:28
@TheCodeGuy, did you find any solution that avoids passing the schema name parameter to all the functions? — Lerdal, Mar 08 '21 at 11:41
@TheCodeGuy Did you find any solution to this ever? I have similar requirement where I have to prepend query with a comment before execution. — Prince Bansal, Sep 10 '21 at 12:13

score 0 · Answer 1 · answered Oct 03 '18 at 12:37

You could use StringTemplate for this.

/**
  * The sql query is located in resources ([package]/listProducts.sql)
  * listProducts.sql: SELECT PRODUCT_ID ID, NAME FROM <schemaName>.PRODUCTS
  * @return
*/
@SqlQuery
@RegisterRowMapper(ProductMapper.class)
List<Product> listProducts(@Define("schemaName") String schemaName);

Just be carefull to sanitize schemaName parameter (possible SQL injection)

score 0 · Answer 2 · answered Sep 10 '21 at 17:41

I found a way to intercept the query and modify it before execution.

You have to build a StatementBuilderFactory instance with a custom StatementBuilder. Here's how:

private fun getCustomStatementBuilderFactory(): StatementBuilderFactory =
            StatementBuilderFactory {
                object : DefaultStatementBuilder() {
                    override fun create(conn: Connection?,
                            sql: String?,
                            ctx: StatementContext?): PreparedStatement {
                        //Perform the modification here
                        val modifiedSql = "--Sample\n$sql"
                        return if (ctx!!.isReturningGeneratedKeys) {
                            val columnNames = ctx.generatedKeysColumnNames
                            if (columnNames != null && columnNames.size > 0) conn!!.prepareStatement(
                                    modifiedSql,
                                    columnNames) else conn!!.prepareStatement(sql, 1)
                        } else {
                            if (ctx.isConcurrentUpdatable) conn!!.prepareStatement(modifiedSql,
                                    1003,
                                    1008) else conn!!.prepareStatement(modifiedSql, 1003, 1007)
                        }
                    }
                }
            }

And now set this StatementBuilderFactory instance to your jdbi instance that you use to execute queries.

Jdbi.create(dataSource).setStatementBuilderFactory(getCustomStatementBuilderFactory())

Now as per the above example every query executed will have a comment --Sample at the start.

How to modify SQL statements before they get executed in JDBI 3 (SQL Objects)

2 Answers2