jdbi: Incompatibility between v2 and v3 when using superfluous parameters map

Hello @jdbi Team 😃

In JDBI v2 we were using construction like this:

@SqlQuery("SELECT COUNT(*) FROM account a WHERE <where>")
int count(@Define("where") String where, @BindMap Map<String, Object> params);

The where is being build in runtime. In some cases it’s empty (simply 1 = 1) and in some cases it contains some values provided by user on GUI filter. For example:

SELECT COUNT(*) FROM account a WHERE 1 = 1;

With:

where = '1 = 1'
params = { }

Or:

SELECT COUNT(*) FROM account a WHERE 1 = 1 AND a.status = :status;

When user provided status in a GUI filter:

where = '1 = 1 AND a.status = :status'
params = { status = 2 }

Right now, when empty params argument is provided, this throws an exception. For example:

com.vividgames.swim.akka.exception.MessageProcessingException: org.jdbi.v3.core.statement.UnableToCreateStatementException: Superfluous named parameters provided while the query declares none: '{positional:{}, named:{}, finder:[{}]}'. [statement:"SELECT count(*) FROM test t WHERE <where>", rewritten:"/* templated */ SELECT count(*) FROM test t WHERE value > 0", parsed:"ParsedSql{sql='/* CountDao.count */ SELECT count(*) FROM `dev-abc.1`.test t WHERE value > 0', parameters=ParsedParameters{positional=false, parameterNames=[]}}", arguments:{positional:{}, named:{}, finder:[{}]}]
	at org.jdbi.v3.core.statement.ArgumentBinder.bindNamed(ArgumentBinder.java:58)
	at org.jdbi.v3.core.statement.ArgumentBinder.bind(ArgumentBinder.java:29)
	at org.jdbi.v3.core.statement.SqlStatement.internalExecute(SqlStatement.java:1429)
	at org.jdbi.v3.core.result.ResultProducers.lambda$getResultSet$2(ResultProducers.java:68)
	at org.jdbi.v3.core.result.ResultIterable.lambda$of$0(ResultIterable.java:53)
	at org.jdbi.v3.core.result.ResultIterable.findFirst(ResultIterable.java:116)
	at org.jdbi.v3.sqlobject.statement.internal.ResultReturner$CollectedResultReturner.mappedResult(ResultReturner.java:266)
	at org.jdbi.v3.sqlobject.statement.internal.SqlQueryHandler.lambda$configureReturner$0(SqlQueryHandler.java:54)
	at org.jdbi.v3.sqlobject.statement.internal.CustomizingStatementHandler.invoke(CustomizingStatementHandler.java:155)
	at org.jdbi.v3.sqlobject.statement.internal.SqlQueryHandler.invoke(SqlQueryHandler.java:26)
	at org.jdbi.v3.sqlobject.SqlObjectFactory.lambda$null$13(SqlObjectFactory.java:163)
	at org.jdbi.v3.core.ConstantHandleSupplier.invokeInContext(ConstantHandleSupplier.java:52)
	at org.jdbi.v3.sqlobject.SqlObjectFactory.lambda$createInvocationHandler$14(SqlObjectFactory.java:162)

I found a flag in SqlStatements which can be used to allow unused bindings:

/**
 * Sets whether or not an exception should be thrown when any arguments are given to a query but not actually used in it. Unused bindings tend to be bugs or oversights, but can also just be convenient. Defaults to false: unused bindings are not allowed.
 *
 * @see org.jdbi.v3.core.argument.Argument
 */
public SqlStatements setUnusedBindingAllowed(boolean allowUnusedBindings) {
    this.allowUnusedBindings = allowUnusedBindings;
    return this;
}

But I cannot find any way to use this method 😦 In JDBI source code I found only usages, but no place where it’s being set.

I can rewrite our DAOs and introduce new methods which takes no params, e.g.:

@SqlQuery("SELECT COUNT(*) FROM account a")
int countAll();

@SqlQuery("SELECT COUNT(*) FROM account a WHERE <where>")
int countFiltered(@Define("where") String where, @BindMap Map<String, Object> params);

But this would have to be done in a number of existing DAOs and to be honest I would rather like to change one line and magically make it work again.