Does JDBI accept UUID parameters?

Question

When using SQL Object argument binding, does JDBI work out-of-the-box with UUID parameters?

I have a method such as this:

@SqlQuery("EXECUTE [MyProcedure] :myField")
MyDto myMethod(@Bind("myField") UUID myField);

which is bound to a SQL Server stored procedure that receives a parameter like this:

@myField uniqueidentifier

When executed, this exception is thrown:

! com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.
! at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
! at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:1117)
! at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:991)

If I change the parameter type on JDBI to String, and call it using the toString() method from the UUID object, it works:

@SqlQuery("EXECUTE [MyProcedure] :myField")
MyDto trash(@Bind("myField") String myField);

Is there a way to write my DAO methods accepting UUID parameters and have them converted to strings before binding?

Answer 1

JDBI only exposes explicit type based bindings for the types which JDBC exposes them for. JDBC does not expose a UUID type for binding, so it is defaulting to setting it as an Object. Unfortunately, JDBC offers not explicit UUID binding mechanism, so going through String is probably the most portable way :-(

If you want to bind it as a UUID in Java and have it converted to a String internally, there are two paths. The first, if you always want to bind UUIDs as Strings is to use an ArgumentFactory, see https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/sqlobject/TestRegisterArgumentFactory.java for an example.

The second is, if you want to do it only in specific cases, to create a custom Binder, such as with http://jdbi.org/sql_object_api_argument_binding/

Example of implementation of a global binding from UUIDs to Strings using an ArgumentFactory:

UUIDArgumentFactory.java:

public class UUIDArgumentFactory implements ArgumentFactory<UUID> {

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

    @Override
    public Argument build(Class<?> expectedType, UUID value, StatementContext ctx) {
        return new UUIDArgument(value);
    }
}

UUIDArgument.java:

public class UUIDArgument implements Argument {
    private final UUID value;

    public UUIDArgument(UUID value) {
        this.value = value;
    }

    @Override
    public void apply(int position, PreparedStatement statement, StatementContext ctx) throws SQLException {
        statement.setString(position, value.toString());
    }    
}

Register:

final DatabaseFactory factory = new DatabaseFactory(environment);
final Database db = factory.build(configuration.getDatabaseConfiguration(), "sqlserver");
db.registerArgumentFactory(new UUIDArgumentFactory());