Menu
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.
491
An optional parameter in Java, as the name implies, refers simply to a parameter that may be optional for a method invocation! It describes the possibility to call a method without specifying some of the arguments used in its definition! Parameters are variables that you can pass to a method or a function!
Using Java Database Interface (jDBI) with PostgreSQL.
Optional container object to have Java optional parameters In case if we expect some null values, we can use the ofNullable() method of this class. It returns an empty Optional object and does not throw an exception.
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");
...
...
}
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) "
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
