当前位置：首页 > news >正文

java处理pgsql的text[]类型数据问题

news 2025/8/27 17:55:20

背景

公司要求使用磐维数据库，于是去了解了这个是基于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">