Finding exactly zero or one result in jdbi3

[yufengwng]

I'm new to Jdbi and just started to use it for one of my projects. I have a use case that I think is pretty common but not sure the best way to solve it using jdbi3. The use case is when querying for data, I want to return a Optional<T> when there is 0 or 1 result row, but throw an exception when there are multiple rows in the result set.

I can use findOnly(), then catch the IllegalStateException and match on the message, but that feels like a hack. There's also findFirst(), but it stops on the first row and doesn't check if there are actually multiple rows in the result set. And I don't think I want to incur the overhead of collecting into a list and then do checks on that... Does anyone also have this use case and a good solution for it?

On the other hand, this seems like a low-hanging fruit. Is there a reason why there isn't an API for it in jdb3?

[frederick...@frimastudio.com]

This should already work out of the box, jdbi should throw "java.lang.IllegalStateException: Multiple values for optional: ..." when trying to map multiple values into an optional.

final Optional<Whatever> opt = handle.select("your query here")

        .collectInto(new GenericType<Optional<Whatever>>() {});

[yufengwng]

Thanks for the tip, that does the trick! However, if I'm understanding the code, the collectInto() approach will always run your mapper before the OptionalBuilder does the check for multiple values. It will be nice to have something like findOnly() that checks iter.hasNext() so that the mapper doesn't even run a second time. Then I can just do this:

final Optional<String> opt = handle.select("my query here")

        .mapTo(String.class)

        .findOnlyOptional();

where findOnlyOptional() might look like this:

default T findOnlyOptional() {

    try (ResultIterator<T> iter = iterator()) {

        if (!iter.hasNext()) {

            return Optional.empty();

        }

        final T r = iter.next();

        if (iter.hasNext()) {

            throw new IllegalStateException("Multiple elements found in 'only optional'");

        }

        return r;

    }

}

Anyways, thanks for reply!

[Steven Schlansker]

> On Feb 2, 2018, at 11:57 AM, yufengwng <yufeng...@gmail.com> wrote:
>
> Thanks for the tip, that does the trick! However, if I'm understanding the code, the collectInto() approach will always run your mapper before the OptionalBuilder does the check for multiple values. It will be nice to have something like findOnly() that checks iter.hasNext() so that the mapper doesn't even run a second time.

This is intended to catch bugs, rather than limit retrieved rows for performance reasons.
If you want to limit the number of processed results, prefer adding a 'LIMIT 1' to your SQL query, rather than using JDBI to do so.