SpringBoot整合Mybatis Plus处理Postgresql的复杂字段

---

前言

使用Mybatis Plus时，复杂字段的实体类和数据库的映射处理，使用自定义的typeHandler。如果相应的handler代码出现红线，可以把pom的数据库驱动的scope注释掉

 <dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<!--<scope>runtime</scope>-->
</dependency>

参考：

• https://blog.csdn.net/qq_42651904/article/details/106936392
• https://blog.csdn.net/sgambler/article/details/106921634

---

一、Json字段的映射

自定义typeHandler

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @ClassName JSONTypeHandlerPg.java
 * @Description
 * @createTime 2021-07-14
 */

@MappedTypes(JSONObject.class)
public class JSONTypeHandlerPg extends BaseTypeHandler<JSONObject> {

    //引入PGSQL提供的工具类PGobject
    private static final PGobject jsonObject = new PGobject();
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JSONObject param, JdbcType jdbcType) throws SQLException {
        //转换的操作在这里!!!
        jsonObject.setType("json");
        jsonObject.setValue(param.toString());
        ps.setObject(i, jsonObject);
    }

    @Override
    public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String sqlJson = rs.getString(columnName);
        if (null != sqlJson){
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }
    //根据列索引，获取可以为空的结果
    @Override
    public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String sqlJson = rs.getString(columnIndex);
        if (null != sqlJson){
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }

    @Override
    public JSONObject getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String sqlJson = cs.getString(columnIndex);
        if (null != sqlJson){
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }
}

对相应的进行处理

@TableField(typeHandler = JSONTypeHandlerPg.class)
private JSONObject attribute;

二、数组类型的字段类似

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.apache.ibatis.type.TypeException;
import java.sql.*;
/**
 * @ClassName JSONTypeHandlerPg.java
 * @Description
 * @createTime 2021-07-14
 */

@MappedTypes(String[].class)
public class ArrayTypeHandlerPg extends BaseTypeHandler<Object[]> {

    private static final String TYPE_NAME_VARCHAR = "varchar";
    private static final String TYPE_NAME_INTEGER = "integer";
    private static final String TYPE_NAME_BOOLEAN = "boolean";
    private static final String TYPE_NAME_NUMERIC = "numeric";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object[] parameter, JdbcType jdbcType) throws SQLException {
        String typeName = null;
        if (parameter instanceof Integer[]) {
            typeName = TYPE_NAME_INTEGER;
        } else if (parameter instanceof String[]) {
            typeName = TYPE_NAME_VARCHAR;
        } else if (parameter instanceof Boolean[]) {
            typeName = TYPE_NAME_BOOLEAN;
        } else if (parameter instanceof Double[]) {
            typeName = TYPE_NAME_NUMERIC;
        }

        if (typeName == null) {
            throw new TypeException("ArrayTypeHandler parameter typeName error, your type is " + parameter.getClass().getName());
        }

        // 这3行是关键的代码，创建Array，然后ps.setArray(i, array)就可以了
        Connection conn = ps.getConnection();
        Array array = conn.createArrayOf(typeName, parameter);
        ps.setArray(i, array);
    }

    @Override
    public Object[] getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return getArray(resultSet.getArray(s));
    }

    @Override
    public Object[] getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return getArray(resultSet.getArray(i));
    }

    @Override
    public Object[] getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return getArray(callableStatement.getArray(i));
    }

    private Object[] getArray(Array array) {
        if (array == null) {
            return null;
        }
        try {
            return (Object[]) array.getArray();
        } catch (Exception e) {
        }
        return null;
    }
}

实体类：

@ApiModelProperty(value = "坐标")
@TableField(typeHandler = ArrayTypeHandlerPg.class)
private String[] location;

三、对应字段插入没问题，查询为null

在对应的书体类上标注

@TableName(value = "pz_survey", autoResultMap = true)

例如：

@Data
@EqualsAndHashCode(callSuper = false)
//加上这句，解决自定义typeHandler查询时无法set问题
@TableName(value = "pz_survey", autoResultMap = true) 
public class PzSurvey extends BaseEntity implements Serializable {

    private static final long serialVersionUID = 1L;
    
    @TableId(value = "survey_id", type = IdType.ASSIGN_ID)
    private String surveyId;

    private String projectId;

    private String zoneId;

    private String surveyName;

    private Integer motorways;

    private Integer sidewalks;

    private Boolean isPartition;

    private String pipelineType;

    private String pipelineLocation;

    @TableField(typeHandler = ArrayTypeHandlerPg.class)
    private String[] location;
}