Optional jDBI parameter

Question

Is it possible to have optional (null) parameters with jDBI queries? I'm attempting to get optional parameters working in a database query. I am working with dropwizard.

@SqlQuery("SELECT * \n" +
          "FROM posts \n" +
          "WHERE (:authorId IS NULL OR :authorId = author_id)")
public List<Post> findAll(@Bind("authorId") Optional<Long> authorId);

The query works when an authorId is passed, but gives me this error when it is NULL:

org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1

This is the resource route I am calling from:

@GET
public ArrayList<Post> getPosts(@QueryParam("authorId") Long authorId)
{
    return (ArrayList<Post>)postDao.findAll(Optional.fromNullable(authorId));
}

From what I've read, this is possible to do, so I'm guessing I am missing something or have an obvious mistake. Any help would be greatly appreciated!

FYI - I have also tried it without guava Optional (which is supported by dropwizard) -- just sending a authorId as a Long that is null. This also works as long as it's not null.

Interlace

You should check jdbi community – Emerald

Thanks @zloster, I posted this there as well. – Interlace

Answer 1

You need to use java8 version of DBIFactory on your application class. It provides java 8 optional support as well as joda LocalDateTime.

Gradle dependency: (convert it to maven, if you're using maven)

compile 'io.dropwizard.modules:dropwizard-java8-jdbi:0.7.1'

and make sure you import io.dropwizard.java8.jdbi.DBIFactory on Applicaiton class and use it under run.

public void run(T configuration, Environment environment) throws Exception {
    final DBIFactory factory = new DBIFactory();
    final DBI jdbi = factory.build(environment, configuration.getDatabase(), "database");
    ...
    ...
}

Lott

Thanks, I'll give this a try. The only issue is that I have used guava optional throughout my application. As long is dropwizard and JDBI are fully compatible with java 8 optional I don't see it being a problem. – Interlace

I see. Then you can simply copy the code from the java8 solution and change the Optionals to Guava. see these two: github.com/dropwizard/dropwizard-java8/blob/master/… github.com/dropwizard/dropwizard-java8/blob/master/… – Lott

I only get org.skife.jdbi.v2.MappingRegistry$1: No mapper registered for java.util.Optional for optional return values. – Exhilarate

Are you using the DBIFactory directly or have you copied the solution from github? If you're doing it manually, then you need to register OptionalContainerFactory as well (github.com/dropwizard/dropwizard-java8/blob/master/…) for return values. – Lott

Answer 2

What fixed for me was adding the type hints when using the optional inside my query template.

Example:

 "AND (:columnName::uuid IS NULL OR columnName= :columnName::uuid) "

Blastema