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.

You should check [jdbi community](http://jdbi.org/community.html) — zloster, Nov 07 '14 at 19:08

score 6 · Answer 1 · answered Nov 25 '14 at 12:27

6

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

answered Nov 25 '14 at 12:27

Natan

2,816
20
37

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. – nckturner Nov 25 '14 at 18:39
I see. Then you can simply copy the code from the java8 solution and change the Optionals to Guava. see these two: https://github.com/dropwizard/dropwizard-java8/blob/master/dropwizard-java8-jdbi/src/main/java/io/dropwizard/java8/jdbi/DBIFactory.java https://github.com/dropwizard/dropwizard-java8/blob/master/dropwizard-java8-jdbi/src/main/java/io/dropwizard/java8/jdbi/args/OptionalArgumentFactory.java – Natan Nov 25 '14 at 20:32
I only get `org.skife.jdbi.v2.MappingRegistry$1: No mapper registered for java.util.Optional` for optional return values. – Michael Piefel Jan 13 '15 at 09:54
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 (https://github.com/dropwizard/dropwizard-java8/blob/master/dropwizard-java8-jdbi/src/main/java/io/dropwizard/java8/jdbi/OptionalContainerFactory.java) for return values. – Natan Jan 13 '15 at 13:47

score 2 · Answer 2 · answered Apr 01 '22 at 21:04

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

answered Apr 01 '22 at 21:04

turtlepick

2,694
23
22

Optional jDBI parameter

2 Answers2