Mybatis-Plus+JPA+postgresql存储复杂类型、TypeHandle使用

在使用Mybatis-Plus + PostgreSQL开发时，我们会遇到一种情况，需要将Object类型或者List<Object>类型数据存入数据库，这时候我们需要将数据转化为jsonString以字符串的形式存入数据库，我在学习过程中发现，List格式的jsonString在直接存入postgreSql时会报错。那么有没有一种好的方式可以在存入和取出时自动对Object和List<Object>进行格式化呢？Mybatis-Plus为我们提供了TypeHandle接口，供用户进行自定义属性转换。下面列举了一些例子。

一、Entity定义

package com.xia.xiatest.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.vladmihalcea.hibernate.type.array.IntArrayType;
import com.vladmihalcea.hibernate.type.array.StringArrayType;
import com.vladmihalcea.hibernate.type.json.JsonBinaryType;
import com.vladmihalcea.hibernate.type.json.JsonStringType;
import com.xia.xiatest.typeHandle.JsonTypeHandle;
import lombok.Data;
import org.apache.ibatis.type.ArrayTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import org.hibernate.annotations.TypeDefs;
import javax.persistence.*;
import java.util.List;

@Data
@Entity
@Table(name = "banner")
@TableName(value = "banner",autoResultMap = true)
@TypeDefs(value = {
        @TypeDef(name = "string-array", typeClass = StringArrayType.class),
        @TypeDef(name = "int-array", typeClass = IntArrayType.class),
        @TypeDef(name = "json", typeClass = JsonStringType.class),
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})

public class Banner {
    @TableId(value = "id", type = IdType.AUTO)
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @TableField(value = "num_arr", jdbcType = JdbcType.ARRAY, typeHandler = ArrayTypeHandler.class)
    @Type(type = "int-array")
    @Column(columnDefinition = "int[]")
    private Integer[] numArr;

    @TableField(value = "str_arr", jdbcType = JdbcType.ARRAY, typeHandler = ArrayTypeHandler.class)
    @Type(type = "string-array")
    @Column(columnDefinition = "varchar[]")
    private String[] strArr;

    @TableField(value = "json_obj", jdbcType = JdbcType.VARCHAR, typeHandler = JsonTypeHandle.class)
    @Type(type = "jsonb")
    @Column(columnDefinition = "varchar")
    private List<Object> jsonObj;
}

1. @TableName(value = "banner",autoResultMap = true) autoResultMap必须设置为true，这里是mybatis-plus序列化使用。

2. @Type要设置为jsonb，@Column设置为varchar。这里是给JPA自动建表用。

二、序列化与反序列化静态方法编写

package com.xia.xiatest.utils;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.xia.xiatest.exception.http.SystemException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.util.List;

@Component
public class GenericAndJson {
    private static ObjectMapper mapper;

    @Autowired
    private ObjectMapper mapper1;

    @PostConstruct
    public void setMapper(){
        mapper = mapper1;
    }

//    两种静态属性注入方式
//    @Autowired
//    public void setMapper(ObjectMapper mapper) {
//        GenericAndJson.mapper = mapper;
//    }

    public static <T> String objectToJson(T obj) {
        try {
            String str = mapper.writeValueAsString(obj);
            return str;

        } catch (JsonProcessingException e) {
            e.printStackTrace();
            throw new SystemException(500);
        }
    }
    public static <T> T jsonToObject(String s, TypeReference<T> tr) {
        try {
            if(s == null) {
                return null;
            }
            T t = mapper.readValue(s, tr);
            return t;
        } catch (JsonProcessingException e) {
            e.printStackTrace();
            throw new SystemException(500);
        }
    }
//    两种方法,其实有上面那个就够了
    public static <T> List<T> jsonToList(String s) {
        try {
            if(s == null) {
                return null;
            }
            List<T> t = mapper.readValue(s, new TypeReference<List<T>>(){
            });
            return t;
        } catch (JsonProcessingException e) {
            e.printStackTrace();
            throw new SystemException(500);
        }
    }
}

三、自定义TypeHandle

package com.xia.xiatest.typeHandle;

import cn.hutool.core.util.ObjectUtil;
import com.fasterxml.jackson.core.type.TypeReference;
import com.xia.xiatest.utils.GenericAndJson;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JsonTypeHandle<T> extends BaseTypeHandler<T> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, T t, JdbcType jdbcType) throws SQLException {
        if(ObjectUtil.isNotEmpty(t)){
            String str = GenericAndJson.objectToJson(t);
            preparedStatement.setString(i, str);
        }else{
            preparedStatement.setString(i, null);
        }

    }

    @Override
    public T getNullableResult(ResultSet resultSet, String s) throws SQLException {
        String str = resultSet.getString(s);
        if(str == null){
            return null;
        } else {
            T result = GenericAndJson.jsonToObject(str, new TypeReference<T>() {
            });
            return result == null? null : result;
        }

    }

    @Override
    public T getNullableResult(ResultSet resultSet, int i) throws SQLException {
        String str = resultSet.getString(i);
        if(str == null){
            return null;
        } else {
            T result = GenericAndJson.jsonToObject(str, new TypeReference<T>() {
            });
            return result == null? null : result;
        }
    }

    @Override
    public T getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        String str = callableStatement.getString(i);
        if(str == null){
            return null;
        } else {
            T result = GenericAndJson.jsonToObject(str, new TypeReference<T>() {
            });
            return result == null? null : result;
        }
    }
}

这样就可以在Entity中使用啦

 @TableField(value = "json_obj", jdbcType = JdbcType.VARCHAR, typeHandler = JsonTypeHandle.class)

四、测试一下

存入截图

返回数据截图

五、优缺点总结

1. 优点：通用，不用定义多个TypeHandle。

2. 缺点：这种通用方式反序列化出来的对象没有类型约束，如图jsonObject反序列化出来的类型为LinkedHashMap,如果有类型约束需求，则需要给不同的类型定义不同的TypeHandle。