Issue inserting localdate type field using JDBI

Question

I am currently using JDBI to persist my POJO into my database. I am using MS SQL Server as my database. I am currently not able to insert a row into the table because of an issue with mapping no value for LocalDate variable.

scoreDate field is LocalDate type, is non mandatory and I am not passing any value for this field. But while trying to run the below test I get the below error.

Please can you advise what the issue could be and how it can be fixed?

Test Class

@Test
    public void testInsertClientScore() throws Exception {
        final DBI dbi = databaseResource.getDBI();
        final ClientScoreDao clientScoreDao = dbi.onDemand(ClientScoreDao.class);

        final ClientScore clientScore = ImmutableClientScore.builder()
                .id(1L)
                .ClientName("TESTCLIENT")
                .build();

        assertEquals(1,clientScoreDao.insert(clientScore));

    }

ERROR TRACE

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.microsoft.sqlserver.jdbc.SQLServerException: 
Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query. [statement:"insert into ICEBERG.ClientScore (scoreId, clientname, scoredate) values (:id, :ClientName, :scoreDate)", located:"insert into ICEBERG.ClientScore (scoreId, clientname, scoredate) values (:id, :ClientName, :scoreDate)", rewritten:"insert into ICEBERG.ClientScore (scoreId, clientname, scoredate) values (?, ?, ?)", arguments:{ positional:{}, named:{ClientName:'TESTCLIENT',id:1,scoreDate:null}, finder:[]}]

    at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1338)
    at org.skife.jdbi.v2.Update.execute(Update.java:56)
    at org.skife.jdbi.v2.sqlobject.UpdateHandler$2.value(UpdateHandler.java:67)

.....
....

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)

Database Table definition

-- ClientScore table
CREATE TABLE ICEBERG.ClientScore (
   ScoreId                 INTEGER                                         NOT NULL,
   ClientName              VARCHAR(50)                                  NOT NULL,
   ScoreDate               DATE                                            NULL,
   CONSTRAINT PK_CLIENTSCORE_TEST PRIMARY KEY (ScoreId)
)

POJO

import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.mercuria.dali.jdbi.Jdbi;
import com.mercuria.dali.jdbi.JdbiOptions;
import org.immutables.value.Value;

import javax.annotation.Nullable;
import java.io.Serializable;
import java.time.LocalDate;

@Value.Immutable
@Jdbi(tableName = "ICEBERG.ClientScore")
@JsonSerialize(as = ImmutableClientScore.class)
@JsonDeserialize(as = ImmutableClientScore.class)
public interface ClientScore extends Serializable {

    @JsonProperty("scoreid")
    @JdbiOptions(columnName = "scoreId")
    Long id();

    @JsonProperty("clientname")
    @JdbiOptions(columnName = "clientname")
    String ClientName();

    @JsonProperty("scoredate")
/*    @JsonDeserialize(using = LocalDateDeserializer.class)
    @JsonSerialize(using = LocalDateSerializer.class)*/
    @JdbiOptions(columnName = "scoredate")
    @Nullable
    LocalDate scoreDate();  
}

I have mappers setup for LocalDate data type while configuring my JDBI connection.

 public static void configureDbi(DBI dbi) {
        // Register argument factories
        dbi.registerArgumentFactory(new NullDoubleArgumentFactory());
        dbi.registerArgumentFactory(new UuidArgumentFactory());
        dbi.registerArgumentFactory(new LocalDateArgumentFactory());
        dbi.registerArgumentFactory(new InstantArgumentFactory(Optional.of(TimeZone.getTimeZone("UTC"))));
        dbi.registerColumnMapper(new InstantMapper(Optional.of(TimeZone.getTimeZone("UTC"))));

        dbi.registerArgumentFactory(new OptionalArgumentFactory("com.microsoft.sqlserver.jdbc.SQLServerDriver"));
        dbi.registerArgumentFactory(new OptionalIntArgumentFactory());
        dbi.registerArgumentFactory(new OptionalDoubleArgumentFactory());

        // Register column mappers
        dbi.registerColumnMapper(new UuidColumnMapper());
        dbi.registerColumnMapper(new LocalDateMapper());
    }

Answer 1

I ran into his issue as well and discovered that the way the Dropwizard LocalDateArgumentFactory class determines whether or not to create the Argument class to deal with the value is using instanceof check:

public boolean accepts(Class<?> expectedType, Object value, StatementContext ctx) {
    return value instanceof LocalDate;
}

Since value is null it returns false, I worked around the issue by creating this:

public class NullAwareLocalDateArgumentFactory extends LocalDateArgumentFactory {
    @Override
    public boolean accepts(Class<?> expectedType, Object value, StatementContext ctx) {
        if(value == null){
            //Look at the expected type. Tbh. not sure why it isn't simply doing this by default ...
            return LocalDate.class.isAssignableFrom(expectedType);
        }
        else {
            return super.accepts(expectedType, value, ctx);
        }
    }
}