Skip to main content

8.2 Custom Type Handlers

When the type handlers provided by dbVisitor do not meet your needs, you can create your own custom type handler.

Extend AbstractTypeHandler<T> and implement the 4 abstract methods:

Example: write a String as a Timestamp to the database
package net.demos.dto;

public class MyDateTypeHandler extends AbstractTypeHandler<String> {
    public void setNonNullParameter(PreparedStatement ps, int i,
            String parameter, Integer jdbcType) throws SQLException {
        try {
            Date date = new SimpleDateFormat("yyyy-MM-dd").parse(parameter);
            ps.setTimestamp(i, new Timestamp(date.getTime()));
        } catch (ParseException e) {
            throw new SQLException(e);
        }
    }

    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return fmtDate(rs.getTimestamp(columnName));
    }

    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return fmtDate(rs.getTimestamp(columnIndex));
    }

    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return fmtDate(cs.getTimestamp(columnIndex));
    }

    private String fmtDate(Timestamp sqlTimestamp) {
        if (sqlTimestamp != null) {
            return new SimpleDateFormat("yyyy-MM-dd").format(new Date(sqlTimestamp.getTime()));
        }
        return null;
    }
}

Explicit Reference​

Explicit reference is the most common usage—you specify the type handler directly in SQL or code.

Use a custom type handler in parameter passing
String time = "2019-10-11";
jdbc.queryForList("select * from users where create_time = #{arg0, typeHandler=net.demos.dto.MyDateTypeHandler}", time);
  • Uses #{...} syntax with positional naming to pass arguments.
  • Uses the typeHandler parameter option to set MyDateTypeHandler for reading and writing the parameter.
Use a custom type handler in object mapping
public class User {
    @Column(typeHandler = MyDateTypeHandler.class)
    private String myTime;

    // getters and setters omitted
}

// Query
jdbc.queryForList("select * from users where id > ?", 2, User.class);
Use a custom type handler in entity mapping XML
<!DOCTYPE mapper PUBLIC "-//dbvisitor.net//DTD Mapper 1.0//EN"
                        "https://www.dbvisitor.net/schema/dbvisitor-mapper.dtd">
<mapper namespace="net.demos.dto">
    <entity table="users" type="net.demos.dto.User">
        ...
        <mapping column="my_time" property="myTime" typeHandler="net.demos.dto.MyDateTypeHandler"/>
        ...
    </entity>
</mapper>
Use a custom type handler in resultMap XML
<!DOCTYPE mapper PUBLIC "-//dbvisitor.net//DTD Mapper 1.0//EN"
                        "https://www.dbvisitor.net/schema/dbvisitor-mapper.dtd">
<mapper namespace="net.demos.dto">
    <resultMap id="user_resultMap" type="net.demos.dto.User">
        ...
        <result column="my_time" property="myTime" typeHandler="net.demos.dto.MyDateTypeHandler"/>
        ...
    </resultMap>
</mapper>

Implicit Reference​

Implicit reference is used to replace the default type handlers provided by dbVisitor, or to add default support for a new type. This is achieved through registration, without needing to explicitly specify the handler at each usage point.

Replace the default StringTypeHandler with a custom handler
// Custom handler
@MappedJavaTypes(String.class)
public class MyStringTypeHandler extends AbstractTypeHandler<String> {
    ...
}

// Register via registerHandler, which automatically reads the annotation and binds to String.class
TypeHandlerRegistry.DEFAULT.registerHandler(MyStringTypeHandler.class, new MyStringTypeHandler());

// Now all String type reading/writing in dbVisitor will use MyStringTypeHandler
// User class no longer needs to specify typeHandler explicitly
jdbc.queryForList("select * from user_table where name = ?", arg, User.class);
info

TypeHandlerRegistry.DEFAULT is the global type handler registry. Create a new instance if you need custom behavior.

Type Binding​

Bind to a Java Type​

Query example
select * from users where name = #{name, javaType=java.lang.String}
Register programmatically
public class MyStringTypeHandler extends AbstractTypeHandler<String> {
    ...
}

TypeHandlerRegistry typeRegistry = ...;
typeRegistry.register(String.class, new MyStringTypeHandler());
Register via annotation
@MappedJavaTypes(String.class)
public class MyStringTypeHandler extends AbstractTypeHandler<String> {
    ...
}

TypeHandlerRegistry typeRegistry = ...;
typeRegistry.registerHandler(MyStringTypeHandler.class, new MyStringTypeHandler());

Bind to a JDBC Type​

Query example
select * from users where name = #{name, jdbcType=varchar}
Register programmatically
public class MyStringTypeHandler extends AbstractTypeHandler<String> {
    ...
}

TypeHandlerRegistry typeRegistry = ...;
typeRegistry.register(Types.VARCHAR, new MyStringTypeHandler());
Register via annotation
@MappedJdbcTypes(Types.VARCHAR)
public class MyStringTypeHandler extends AbstractTypeHandler<String> {
    ...
}

TypeHandlerRegistry typeRegistry = ...;
typeRegistry.registerHandler(MyStringTypeHandler.class, new MyStringTypeHandler());

Cross-Type Binding​

Query example
select * from users where name = #{name, jdbcType=nvarchar, javaType=java.lang.String}
Register programmatically
public class MyStringTypeHandler extends AbstractTypeHandler<String> {
    ...
}

TypeHandlerRegistry typeRegistry = ...;
typeRegistry.register(Types.NVARCHAR, String.class, new MyStringTypeHandler());
Register via annotation
@MappedCrossTypes(javaType = String.class, jdbcType = Types.NVARCHAR)
public class MyStringTypeHandler extends AbstractTypeHandler<String> {
    ...
}

TypeHandlerRegistry typeRegistry = ...;
typeRegistry.registerHandler(MyStringTypeHandler.class, new MyStringTypeHandler());

Handler Constructor Arguments​

dbVisitor allows custom type handlers to have a constructor that takes a Class parameter.

For example, when a query parameter is an enum or a result set maps to an enum field, the type handler needs to know how to translate the field value into an enum object.

  • Handler constructor arguments let the type handler know the concrete Java type being operated on (dbVisitor's built-in EnumTypeHandler uses this mechanism).
Usage
public class MyTypeHandler extends AbstractTypeHandler<Object> {
    public MyTypeHandler(Class<?> argType) {
        ...
    }
}

@NoCache Annotation​

The registry TypeHandlerRegistry has a caching mechanism to speed up TypeHandler creation and retrieval.

When a type handler uses a constructor argument, the cache may hit an already-created typeHandler while ignoring the same typeHandler with different arguments. For example:

Example: two queries use the same TypeHandler but different parameter types
select * from users 
where user_type = #{arg0, javaType= net.demos.dto.UserTypeEnum,
                          typeHandler=net.demos.dto.MyTypeHandler}


select * from users 
where auth_type = #{arg0, javaType= net.demos.dto.AuthTypeEnum,
                          typeHandler=net.demos.dto.MyTypeHandler}
  • Both queries use the same type handler MyTypeHandler for different Java enum types.
  • Without @NoCache, the second query would have dbVisitor treat the argument as UserTypeEnum, causing confusion.

Use @NoCache to avoid cache interference:

@NoCache
public class MyTypeHandler extends AbstractTypeHandler<Object> {
    public MyTypeHandler(Class<?> argType) {
        ...
    }
}
info

Any operation that manually registers a TypeHandler into TypeHandlerRegistry is not affected by @NoCache.

For example, the following registrations will successfully and permanently bind the MyTypeHandler instance to the corresponding type combination regardless of @NoCache:

typeRegistry.registerHandler(MyTypeHandler.class, new MyTypeHandler(String.class));
typeRegistry.register(String.class, new MyTypeHandler(String.class));
typeRegistry.register(Types.NVARCHAR, new MyTypeHandler(String.class));
typeRegistry.register(Types.NVARCHAR, String.class, new MyTypeHandler(String.class));

@NoCache only takes effect when the registry passively creates a TypeHandler via the createTypeHandler method.