使用 Mybatis 的 TypeHandler 存取 PostgreSQL jsonb 类型_is of type jsonb but expression is of type charact

为什么要使用PostgreSQL jsonb 类型 ？

使用 PostgreSQL 的 jsonb 类型涉及查询效率、数据类型的灵活性和数据操作的便利性等原因。以下是使用 jsonb 的一些主要原因：

1. 数据类型灵活性：jsonb 允许我们在同一列中存储不同结构的数据，这提供了很大的灵活性。我们可以存储简单的键值对，也可以存储嵌套的对象和数组。
2. 查询效率：jsonb 是二进制格式的 JSON，它允许 PostgreSQL 对其内容进行索引，从而大大提高了查询效率。相比之下，json 类型是文本格式，不能对其进行有效的索引，因此查询效率较低。
3. 索引支持：jsonb 支持 GIN（Generalized Inverted Index）和 GiST（Generalized Search Tree）索引，这意味着我们可以对 jsonb 列中的特定字段创建索引，从而加快查询速度。
4. 数据操作的便利性：jsonb 提供了一系列的操作符和函数，允许我们轻松地查询、修改和提取 JSON 数据。例如，我们可以使用 ->、->> 操作符来提取 JSON 对象中的特定字段，或使用 jsonb_set 函数来修改 JSON 对象中的字段。
5. 存储效率：jsonb 以二进制格式存储数据，因此与 json 类型相比，它通常占用更少的存储空间。
6. 扩展性：由于 jsonb 的数据结构非常灵活，因此它可以轻松地与其他数据结构和数据类型集成，使得应用程序能够轻松扩展。

总的来说，jsonb 类型在 PostgreSQL 中提供了强大的功能和高效的性能，使其成为存储和查询 JSON 数据的理想选择。

使用 TypeHandler 存取 PostgreSQL jsonb 类型

首先定义个 tb_department 表，表中分别定义了 user_info 和 user_list 这两个 jsonb 类型，以及 user_ids 数组类型。

create table tb_department
(
    id          varchar(36) not null, --部门ID
    user_info   jsonb, --部门领导
    user_list   jsonb, --部门成员
    file_ids    varchar(36)[], --部门资料ID数组
    create_time timestamp   not null default now(), -- 创建时间
    constraint pk_tb_user_info primary key (id)
);
1
2
3
4
5
6
7
8
9

然后创建 tb_department 表对应的 DepartmentEntity 实体类：

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
import org.apache.ibatis.type.ArrayTypeHandler;
import org.apache.ibatis.type.JdbcType;

@TableName(value = "tb_department", autoResultMap = true)
public class DepartmentEntity {

    @TableId("id")
    private String id;

    @TableField("user_info", typeHandler = JacksonTypeHandler.class)
    private UserInfo userInfo ;

    @TableField(value = "user_list", typeHandler = JacksonTypeHandler.class)
    private List<UserInfo> userList;

    @TableField(value = "file_ids", jdbcType = JdbcType.ARRAY, typeHandler = ArrayTypeHandler.class)
    private String[] fileIds ;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

下面解释下上面的代码：

1. @TableName 是表名注解，标识实体类对应的表。由于我们使用Mybatis-Plus的字段类型处理器，因此 @TableName 中的 autoResultMap = true 必须开启。

2. @TableField 是字段注解（非主键），其中的 jdbcType 用于指定JDBC类型，typeHandler 用于指定类型处理器，用于 JavaType 与 JdbcType 之间的转换 。例如：jdbcType = JdbcType.ARRAY 表示字段是数组类型，而typeHandler = ArrayTypeHandler.class 表示 类型处理器是 Mybatis 官方的数组类型处理器。

3. 比较特殊的是 userInfo 和 user_list 这两个字段的 typeHandler 都是用了 Mybatis-Plus 自带的 JacksonTypeHandler 使用 Jackson 实现 JSON 字段的类型处理器。

注意：以上代码示例中使用了 MyBatis-Plus

UserInfo 如下：

/**
 * 用户类型
 */
public class UserInfo {
  /**
     * 用户id
     */
    private String userId;
    /**
     * 用户名
     */
    private String username;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

常见的3个问题解决方法

报错1：column “” is of type jsonb but expression is of type character varying

当我们运行程序后，并执行上述代码后，会报以下错误：

解决办法：

在 JDBC URL参数中加入：stringtype=unspecified ，例如：

jdbc:postgresql://xxxxxxx:xxxx/db?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&stringtype=unspecified
1

官方对 stringtype 参数的解释：

stringtype ： String
Specify the type to use when binding PreparedStatement parameters set via setString(). If stringtype is set to VARCHAR (the default), such parameters will be sent to the server as varchar parameters. If stringtype is set to unspecified, parameters will be sent to the server as untyped values, and the server will attempt to infer an appropriate type. This is useful if you have an existing application that uses setString() to set parameters that are actually some other type, such as integers, and you are unable to change the application to use an appropriate method such as setInt().

当 stringtype=unspecified 时，statement.setString() 方法的参数将以未知的类型发送给Postgresql 数据库，由数据库根据表中字段的类型进行推定和自动转换。

报错2：java.lang.ClassCastException: class java.util.LinkedHashMap cannot be cast to class XXX (java.util.LinkedHashMap is in module java.base of loader ‘bootstrap’; XXXX is in unnamed module of loader ‘app’)

当我们在使用 DepartmentEntity 获取 userList 这个列表并进行遍历时，会报此错误。原因正是 userList 字段注解中的 typeHandler = JacksonTypeHandler.class 所导致。

@TableField(value = "user_list", typeHandler = JacksonTypeHandler.class)
private List<UserInfo> userList;
1
2

我们可以查看下 JacksonTypeHandler 的源代码：

@Slf4j
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class JacksonTypeHandler extends AbstractJsonTypeHandler<Object> {
    private static ObjectMapper OBJECT_MAPPER;
    private final Class<?> type;

    public JacksonTypeHandler(Class<?> type) {
        if (log.isTraceEnabled()) {
            log.trace("JacksonTypeHandler(" + type + ")");
        }
        Assert.notNull(type, "Type argument cannot be null");
        this.type = type;
    }

    @Override
    protected Object parse(String json) {
        try {
            return getObjectMapper().readValue(json, type);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    protected String toJson(Object obj) {
        try {
            return getObjectMapper().writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    public static ObjectMapper getObjectMapper() {
        if (null == OBJECT_MAPPER) {
            OBJECT_MAPPER = new ObjectMapper();
        }
        return OBJECT_MAPPER;
    }

    public static void setObjectMapper(ObjectMapper objectMapper) {
        Assert.notNull(objectMapper, "ObjectMapper should not be null");
        JacksonTypeHandler.OBJECT_MAPPER = objectMapper;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

@MappedTypes({Object.class}) 指定 TypeHandler 要映射的 Java 类型为 Object 类型。Jackson 会将 JSON 内容反序列化为 ArrayList 对象，但它不知道 ArrayList 对象中应该包含什么类型的元素。当 Jackson 尝试反序列化 JSON 中的对象，但没有给出目标类型信息时，它将使用默认类型：LinkedHashMap。因此，在反序列化之后，我们会得到一个 ArrayList<LinkedHashMap> 对象。在 Map中，键是属性的名称—例如，userId、 username等,这些值是相应属性的值…

为了解决这个问题，我们需要让 Jackson 知道元素的类型。为了方便，这里我将直接继承 mybatisplus 中的 AbstractJsonTypeHandler<T> 来实现解析 List<T> 的操作。

首先定义一个 ListTypeHandler， 并继承 AbstractJsonTypeHandler，代码如下：

import com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

import java.io.IOException;
import java.util.List;

@MappedTypes({List.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public abstract class ListTypeHandler<T> extends AbstractJsonTypeHandler<List<T>> {
    private static ObjectMapper OBJECT_MAPPER;

    @Override
    protected List<T> parse(String json) {
        try {
            return getObjectMapper().readValue(json, specificType());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    protected String toJson(List<T> obj) {
        try {
            return getObjectMapper().writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    public static ObjectMapper getObjectMapper() {
        if (null == OBJECT_MAPPER) {
            OBJECT_MAPPER = new ObjectMapper();
        }
        return OBJECT_MAPPER;
    }

    /**
     * 具体类型，由子类提供
     *
     * @return 具体类型
     */
    protected abstract TypeReference<List<T>> specificType();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

可以看到，我们在 parse(String json) 方法中通过 specificType() 这个抽象方法将相应的 TypeReference 对象传递给 objectMapper.readValue() 方法。

然后创建 UserInfoListTypeHandler 继承 ListTypeHandler，泛型的类型参数使用 UserInfo，代码如下：

public class UserInfoListTypeHandler extends ListTypeHandler<UserInfo> {

    protected  TypeReference<List<UserInfo>> specificType() {
        return new TypeReference<List<UserInfo>>() {
        };
    }
}
1
2
3
4
5
6
7

最后，我们将 DepartmentEntity 中的 userList 字段的 typeHandler 修改为 UserInfoListTypeHandler 即可：

@TableField(value = "user_list", typeHandler = UserInfoListTypeHandler.class)
private List<UserInfo> userList;
1
2

报错3：could not determine data type of parameter $1

like concat('%', #{keyword},'%')
1

如果我们在 Mybatis 的 Mapper.xml 中使用了 like concat() 这样的字符串连接函数，运行后就会提示以下错误：

Cause: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $7; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $7

这个错误正是因为我们在 JDBC URL 参数中加入了 stringtype=unspecified ，导致无法确定参数类型。

解决办法有两个：

like '%'||#{keyword}||'%'
1

或者使用 PostgreSQL 的 :: 操作符进行隐式转换，将参数转换为正确的数据类型：

like concat('%', #{keyword}::text,'%')
1

在上述示例中，我们使用 ::text 将 keyword 参数隐式转换为 text 类型。这样，PostgreSQL 就能正确地解析参数的数据类型，并执行查询操作。