背景

公司要求使用磐維數據庫，于是去了解了這個是基于PostgreSQL構建的，在使用時有場景一條圖片數據中可以投放到不同的頁面，由于簡化設計就放在數組中，于是使用了text[]類型存儲；

表結構

#這是一個簡化版表結構（id自增一般設置serial4=自增int4類型）
CREATE TABLE "public"."t_expand" ("id" int4 NOT NULL DEFAULT nextval('t_expand_id_seq'::regclass),"role_id" int4 DEFAULT NULL,"expand_role_ids" text[] COLLATE "pg_catalog"."default" DEFAULT NULL,"environment" varchar(64) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,"remark" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying
);

實體類

@Data
public class TExpand implements Serializable {/*** 主鍵id*/private Integer id;/*** 角色id*/private Integer roleId;/*** 外部角色id綁定*/private List<String> expandRoleIds;/*** 環境標識*/private String environment;/*** 備注*/private String remark;private static final long serialVersionUID = 1L;
}

mybatis 配置

注意：text[]不能直接對應java的List<String>類型，故需要自己轉換：
public class TextListTypeHandler extends BaseTypeHandler<List<String>> {@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, List<String> parameter, JdbcType jdbcType) throws SQLException {// 將 List<String> 轉換為 PostgreSQL text[] 類型Array array = ps.getConnection().createArrayOf("text", parameter.toArray());ps.setArray(i, array);}@Overridepublic List<String> getNullableResult(ResultSet rs, String columnName) throws SQLException {return toList(rs.getArray(columnName));}@Overridepublic List<String> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {return toList(rs.getArray(columnIndex));}@Overridepublic List<String> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {return toList(cs.getArray(columnIndex));}private List<String> toList(Array array) throws SQLException {if (array == null) {return null;}return Arrays.asList((String[]) array.getArray());}
}<resultMap id="BaseResultMap" type="com.domain.TExpand"><result property="id" column="id" jdbcType="INTEGER"/><result property="roleId" column="role_id" jdbcType="INTEGER"/><result property="expandRoleIds" column="expand_role_ids" jdbcType="ARRAY" javaType="java.util.List" typeHandler="com.TextListTypeHandler"/><result property="environment" column="environment" jdbcType="VARCHAR"/><result property="remark" column="remark" jdbcType="VARCHAR"/></resultMap>#在使用此參數查詢時也要指定類型，否則無法匹配類型<select id="queryAllByExpandRoleIds" resultMap="BaseResultMap">select<include refid="Base_Column_List" />from t_expandwhere expand_role_ids <![CDATA[&&]]> ARRAY[<foreach item="role" collection="roles" separator=",">#{role}::text</foreach>]::text[]</select>
#插入修改也是要匹配<insert id="insertSelective">insert into t_expand<trim prefix="(" suffix=")" suffixOverrides=","><if test="id != null">id,</if><if test="roleId != null">role_id,</if><if test="expandRoleIds != null">expand_role_ids,</if><if test="environment != null">environment,</if><if test="remark != null">remark,</if></trim><trim prefix="values (" suffix=")" suffixOverrides=","><if test="id != null">#{id,jdbcType=INTEGER},</if><if test="roleId != null">#{roleId,jdbcType=INTEGER},</if><if test="expandRoleIds != null">#{expandRoleIds,jdbcType=ARRAY,typeHandler=com.TextListTypeHandler},</if><if test="environment != null">#{environment,jdbcType=VARCHAR},</if><if test="remark != null">#{remark,jdbcType=VARCHAR},</if></trim></insert>

常見問題

IllegalArgumentException: invalid comparison: java.util.ArrayList and java.lang.string] with root ca
<if test="touchCode != null and touchCode != ''" >and s.touch_code <![CDATA[&&]]> ARRAY[<foreach item="code" collection="touchCode" separator=",">#{code}::text</foreach>]::text[]
</if>
這里and touchCode != ''會影響傳參應當去掉或者使用size函數
<if test="touchCode != null">
或者 
<if test="touchCode != null and touchCode.size>0">