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.

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");
    ...
    ...
}

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