@Define + optional @BindBean

[xak2000]

It is useful to have @Define and @BindBean on a single nullable argument of @SqlQuery.

E.g.:

class UserPageToken {
    Direction direction;
    Long userId;
    // getters, setters
}

public interface UserSearchDao {
    @SqlQuery
    @UseFreemarkerEngine
    @UseFreemarkerSqlLocator
    List<User> findAll(
            @BindBean("pageToken") @Define UserPageToken pageToken,
            @Bind int limit
    );
}

And SQL query:

SELECT
    *
FROM
    users u
WHERE
    1 = 1
<#if pageToken??>
        <#if pageToken.direction == "FORWARD">
            AND u.user_id < :pageToken.userId
        <#else>
            AND u.user_id > :pageToken.userId
        </#if>
</#if>
ORDER BY
<#if !pageToken?? || pageToken.direction == "FORWARD">
    u.user_id DESC
<#else>
    u.user_id ASC
</#if>
LIMIT
    :limit

The crucial part here is that pageToken argument is optional. It could be null that means it is the first page requested, so the WHERE part should not filter by userId at all. The caller can't pass a "dummy" pageToken bean, because to do this it needs to know last userId of the previous returned page (cursor/keyset pagination).

Currently an attempt to pass null leads to the exception:

java.lang.NullPointerException: Cannot invoke "Object.getClass()" because "bean" is null
	at org.jdbi.v3.core.statement.SqlStatement.bindBean(SqlStatement.java:193)
	at org.jdbi.v3.sqlobject.customizer.internal.BindBeanFactory.lambda$createForParameter$0(BindBeanFactory.java:39)
	at org.jdbi.v3.sqlobject.customizer.internal.PojoWarmingCustomizer$1.apply(PojoWarmingCustomizer.java:37)
	at org.jdbi.v3.sqlobject.statement.internal.CustomizingStatementHandler$1.apply(CustomizingStatementHandler.java:118)
	at org.jdbi.v3.sqlobject.statement.internal.CustomizingStatementHandler.lambda$applyCustomizers$11(CustomizingStatementHandler.java:203)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at org.jdbi.v3.sqlobject.statement.internal.CustomizingStatementHandler.applyCustomizers(CustomizingStatementHandler.java:201)
	at org.jdbi.v3.sqlobject.statement.internal.CustomizingStatementHandler.invoke(CustomizingStatementHandler.java:196)
	at org.jdbi.v3.sqlobject.statement.internal.SqlQueryHandler.invoke(SqlQueryHandler.java:27)
	at org.jdbi.v3.core.extension.ExtensionMetadata$ExtensionHandlerInvoker.lambda$invoke$0(ExtensionMetadata.java:345)
	at org.jdbi.v3.core.AbstractHandleSupplier.invokeInContext(AbstractHandleSupplier.java:36)
	at org.jdbi.v3.core.extension.ExtensionMetadata$ExtensionHandlerInvoker.call(ExtensionMetadata.java:363)
	at org.jdbi.v3.core.extension.ExtensionMetadata$ExtensionHandlerInvoker.invoke(ExtensionMetadata.java:346)
	at org.jdbi.v3.core.extension.ExtensionFactoryDelegate.lambda$attach$0(ExtensionFactoryDelegate.java:118)
	at org.jdbi.v3.core.internal.OnDemandExtensions.invoke(OnDemandExtensions.java:85)
	at org.jdbi.v3.core.internal.OnDemandExtensions.lambda$createProxy$2(OnDemandExtensions.java:71)
	at org.jdbi.v3.core.Jdbi.callWithExtension(Jdbi.java:560)
	at org.jdbi.v3.core.Jdbi.withExtension(Jdbi.java:547)
	at org.jdbi.v3.core.internal.OnDemandExtensions.lambda$createProxy$3(OnDemandExtensions.java:71)
	at jdk.proxy3/jdk.proxy3.$Proxy260.findAll(Unknown Source)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)

Looks like JDBI expects the @BindBean argument to always be non-null.

My proposition is to make it to skip binding if the argument is null.

Actually, if we wrap UserPageToken bean into another bean (let's say SearchQuery bean) and pass @BindBean @Define SearchQuery directly to @SqlQuery method, then UserPageToken (that will be inner bean) can be null and no exception will be thrown. So, this can be used as a workaround today.

What I mean:

class SearchQuery {
    UserPageToken pageToken; // +getter
}

public interface UserSearchDao {
    @SqlQuery
    @UseFreemarkerEngine
    @UseFreemarkerSqlLocator
    List<User> findAll(
            @BindBean @Define SearchQuery searchQuery,
            @Bind int limit
    );
}

And SQL query:

SELECT
    *
FROM
    users u
WHERE
    1 = 1
<#if searchQuery.pageToken??>
        <#if searchQuery.pageToken.direction == "FORWARD">
            AND u.user_id < :pageToken.userId
        <#else>
            AND u.user_id > :pageToken.userId
        </#if>
</#if>
ORDER BY
<#if !searchQuery.pageToken?? || searchQuery.pageToken.direction == "FORWARD">
    u.user_id DESC
<#else>
    u.user_id ASC
</#if>
LIMIT
    :limit

So, for inner beans optional binding works alredy.

[hgschmie]

Hi @xak2000,

Thank you for filing an issue with Jdbi. I will take a look at it.

[hgschmie]

Making this change makes a lot of sense. We will ship it in the next release.

[hgschmie]

shipped with 3.43.0