JDBI是否接受UUID参数? [英] Does JDBI accept UUID parameters?

问题描述

使用SQL Object参数绑定时， JDBI 是否可以直接使用UUID参数进行工作?

我有这样的方法:

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

绑定到接收如下参数的SQL Server存储过程:

@myField uniqueidentifier

执行时，将引发此异常:

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

如果我将JDBI上的参数类型更改为String，并使用UUID对象中的toString()方法调用它，那么它将起作用:

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

有没有办法编写我的接受UUID参数的DAO方法，并在绑定之前将它们转换为字符串?

解决方案

JDBI仅公开针对JDBC公开的基于类型的显式绑定. JDBC不会公开用于绑定的UUID类型，因此默认情况下会将其设置为Object.不幸的是，JDBC没有提供显式的UUID绑定机制，因此遍历String可能是最可移植的方式:-(

如果要在Java中将其绑定为UUID并在内部将其转换为String，则有两条路径.首先，如果您总是要将UUID绑定为字符串，则是使用ArgumentFactory，请参见 http ://jdbi.org/sql_object_api_argument_binding/

使用ArgumentFactory实现从UUID到字符串的全局绑定的示例:

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

注册:

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

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?

解决方案

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

这篇关于JDBI是否接受UUID参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！