Java中使用pgsql的array类型

1.首先定义实体类的类型

    @ApiModelProperty(value = "教材封面文件路径")
    @Column(name = "thumbnail")
    @ColumnType(typeHandler = MyArrayTypeHandler.class)
    private String[] thumbNail;

2.定义mybaties里的resultMap

    <resultMap type="com.tianwen.springcloud.microservice.base.entity.book.Book" id="bookInfo">
        <result column="thumbnail" jdbcType="ARRAY" property="thumbNail"
             typeHandler="com.tianwen.springcloud.microservice.handler.MyArrayTypeHandler"/>
    </resultMap>

3.使用，在insert时指定类型

    <insert id="insert" parameterType="com.tianwen.springcloud.microservice.base.entity.book.Book">
        INSERT INTO T_E_BOOK(
             thumbnail )
        VALUES (
             #{thumbNail,jdbcType=ARRAY,typeHandler =com.tianwen.springcloud.microservice.handler.MyArrayTypeHandler},
        )
    </insert>
      

MyArrayTypeHandler的类实现

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package com.tianwen.springcloud.microservice.handler;

import com.tianwen.springcloud.microservice.exception.EcrException;
import java.math.BigDecimal;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

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

    public MyArrayTypeHandler() {
    }

    public void setNonNullParameter(PreparedStatement ps, int i, Object[] parameter, JdbcType jdbcType) throws SQLException {
        String typeName = null;
        if (parameter instanceof Integer[]) {
            typeName = "integer";
        } else if (parameter instanceof String[]) {
            typeName = "varchar";
        } else if (parameter instanceof Boolean[]) {
            typeName = "boolean";
        } else if (parameter instanceof Double[]) {
            typeName = "numeric";
        } else if (parameter instanceof BigDecimal[]) {
            typeName = "numeric";
        }

        if (typeName == null) {
            throw new EcrException("ArrayTypeHandler parameter typeName error, your type is " + parameter.getClass().getName(), new Object[0]);
        } else {
            Connection conn = ps.getConnection();
            Array array = conn.createArrayOf(typeName, parameter);
            ps.setArray(i, array);
        }
    }

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

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

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

    private Object[] getArray(Array array) throws SQLException {
        return array == null ? null : (Object[])((Object[])array.getArray());
    }
}