前置

• mybatis plus
• postgresql

问题

最近使用pgsql时使用了数组类型和json类型，但是在插入数据或查询的时候不像mysql那么方便，需要特别指定类型。

解决方法

1. 首先先自定义一个TypeHandler

@MappedJdbcTypes(JdbcType.ARRAY)
public class ArrayType2Handler 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("ArrayType2Handler 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;
    }
}

2. 指定字段插入时的数据映射类型

	@TableField(el = "tags, typeHandler=com.sysssc.zydj.config.ArrayType2Handler")
	private String[] tags;

mybatis plus 提供的字段注解 @TableField，源码如下：

这样就完成了插入数据时的字段数据类型映射，插入的数据结果如下：

3. 指定查询返回结果字段的映射类型

@Select("<script>" +
            sql +
            "<where>${ew.sqlSegment}</where>\n" +
            "ORDER BY t.id DESC" +
            "</script>"
    )
    @Results(
            @Result(column = "tags", property = "tags", typeHandler = ArrayType2Handler.class)
    )

结果如下：

josn类型

josn数据类型是相同的方式

@MappedTypes(Object.class)
public class JSONTypeHandlerPg extends BaseTypeHandler<Object> {
	
    private static final PGobject jsonObject = new PGobject();
    
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        jsonObject.setType("json");
        jsonObject.setValue(parameter.toString());
        ps.setObject(i, jsonObject);
    }
 
    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getString(columnIndex);
    }
 
    @Override
    public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return cs.getString(columnIndex);
    }
 
    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getString(columnName);
    }
 
}

参考自:https://blog.csdn.net/qq_18298439/article/details/80499703