Does JDBI accept UUID parameters?

[Fernando Correia]

As asked in StackOverflow ( http://stackoverflow.com/questions/12022452/does-jdbi-accept-uuid-parameters ):

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

[Steve Nelson]

Are you using SQL server? I had quite a bit of trouble with this, I ended up just treating them as strings. But I am just passing them back and forth through REST services anyway so I didn't really need them to be UUIDs at that level. The SQL server driver seemed to handle them fine as strings.

[Fernando Correia]

Yes, I'm using SQL Server. Passing those parameters as string works. I'm trying to find out if there's a way to pass them natively as UUIDs, whitout converting to Strings.

You seem to have had the same issue I'm having.

2012/8/19 Steve Nelson <sne...@iowaballetacademy.com>

[Tatu Saloranta]

On Sun, Aug 19, 2012 at 12:01 PM, Fernando Correia
<fernando...@gmail.com> wrote:
> Yes, I'm using SQL Server. Passing those parameters as string works. I'm
> trying to find out if there's a way to pass them natively as UUIDs, whitout
> converting to Strings.

At some point, conversion is needed, but all things considered it's
unlikely that this has measurable impact on performance (wrt
conversions).

In theory, support by DB could benefit iff there is native UUID type,
to benefit from its 128-bit size. I don't know which DBs have such
native type however.
But even in those cases, as long as table definition uses UUIDs,
conversions back and forth should be efficient -- there is no magic in
there, standard 36-char representation to/from UUID is easy.

-+ Tatu +-

[Fernando Correia]

I understand. I'm not concerned about performance; that would make a very minor difference. But all my keys are UUIDs and right now my DAO interfaces only accept strings:

TemplateDto selectSingle(@Bind("tenantId") String tenantId, @Bind("templateId") String templateId);

That means I have to convert the values to strings when using these APIs, which is a bit cumbersome and error-prone:

TemplateDto templateDto = templatesDao.selectSingle(tenantId.get().toString(), templateId.get().toString());

Is there a way to write a custom mapper on JDBI so I could write the interface method accepting UUIDs, but have it convert them to string when passing the parameter to the SqlQuery? Something like:

TemplateDto selectSingle(@Bind("tenantId")  UUID  tenantId, @Bind("templateId") UUID templateId);

So the API would base based explicitly on the data types of the DTOs.

But again, without necessarily passing them along to the database as UUIDs, just converting them to string before binding the parameters. I don't know JDBI enough to understand if that's possible or how difficult it would be.

2012/8/19 Tatu Saloranta <tsalo...@gmail.com>

[Brian McCallister]

On Sun, Aug 19, 2012 at 2:50 PM, Fernando Correia <fernando...@gmail.com> wrote:

I understand. I'm not concerned about performance; that would make a very minor difference. But all my keys are UUIDs and right now my DAO interfaces only accept strings:

TemplateDto selectSingle(@Bind("tenantId") String tenantId, @Bind("templateId") String templateId);

That means I have to convert the values to strings when using these APIs, which is a bit cumbersome and error-prone:

TemplateDto templateDto = templatesDao.selectSingle(tenantId.get().toString(), templateId.get().toString());

Is there a way to write a custom mapper on JDBI so I could write the interface method accepting UUIDs, but have it convert them to string when passing the parameter to the SqlQuery? Something like:

TemplateDto selectSingle(@Bind("tenantId")  UUID  tenantId, @Bind("templateId") UUID templateId);

So the API would base based explicitly on the data types of the DTOs.

But again, without necessarily passing them along to the database as UUIDs, just converting them to string before binding the parameters. I don't know JDBI enough to understand if that's possible or how difficult it would be.

Responded on SO with:

JDBI only exposes explicite type based bindings for the types which JDBC exposes them for. JDBC does not expose a UUID type for binding, os 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, seehttps://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 withhttp://jdbi.org/sql_object_api_argument_binding/

-Brian

[Fernando Correia]

Thank you very much, Briand! No more toString() when calling my DAO methods.

For the record, this is my implementation:

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

    }

}

public class UUIDArgument implements Argument {

    private UUID value = null;

    

    public UUIDArgument(UUID value) {

        this.value = value;

    }

    @Override

    public void apply(int position, PreparedStatement statement, StatementContext ctx) throws SQLException {

        statement.setString(position, value.toString());

    }    

}

Registered as:

db.registerArgumentFactory(new UUIDArgumentFactory());

Tested and working with SQL Server.