如何在JDBI 3（SQL对象）中执行SQL语句之前修改它们

Question

我想在JDBI 3 (SQL对象)中执行SQL语句之前拦截和修改它们。其原因是用模式名替换自定义令牌占位符。我发现这个线程，但它是为JDBI 2(使用Dropwizard

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 { }


             });

        }

    }
}

我还发现了这个线程，它使用@定义（JDBI中的动态顺序SQL对象查询）处理自定义标签替换。如果可能，我想避免每次调用都传递额外的参数。任何帮助都将不胜感激！

Answer 1

我找到了一种方法来截取查询并在执行之前对其进行修改。

您必须使用自定义 StatementBuilder 构建一个 StatementBuilderFactory 实例。方法如下：

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)
                        }
                    }
                }
            }

现在，将此 StatementBuilderFactory 实例设置为用于执行查询的 jdbi 实例。

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

根据上面的例子，每个执行的查询在开始都有一个注释< code> - Sample。

Answer 2

您可以为此使用String模板。

/**
  * 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);

只需小心清理架构名称参数（可能的SQL注入）