PostgreSQL表中字段由字符串改为数组

需求：PostgreSQL数据库中的一张表的某些字段，之前存的是字符串，由于业务需求变更，需要存储多条数据，字段类型要改为数组，并保留原来的数据。

具体实现：

1. 修改表中字段类型：把 etl_flow_template 表中的 protocol_document_path_list字段和 protocol_document_file_name_list 字段修改为数组类型，并保留原来的值：

   alter table etl_flow_template alter column protocol_document_path_list type text[] using array[protocol_document_path_list]::text[];
   
   alter table etl_flow_template alter column protocol_document_file_name_list type text[] using array[protocol_document_file_name_list]::text[];

   修改后表中字段效果：
   

2. 修改映射文件中数据类型：

   <resultMap id="BaseResultMap" type="com.hikvision.idatafusion.hdiwebsite.dto.template.TemplateView">
           <id column="id" jdbcType="BIGINT" property="id"/>
           <result column="template_name" jdbcType="VARCHAR" property="templateName"/>
           <result column="icon_path" jdbcType="VARCHAR" property="iconPath"/>
           <result column="template_path" jdbcType="VARCHAR" property="templatePath"/>
           <result column="template_file_name" jdbcType="VARCHAR" property="templateFileName"/>
           <result column="protocol_document_path_list" jdbcType="VARCHAR" property="protocolDocumentPathList"
                   typeHandler="com.hikvision.idatafusion.dhidata.commons.typeHandler.ArrayTypeHandlerPg"/>
           <result column="protocol_document_file_name_list" jdbcType="VARCHAR" property="protocolDocumentFileNameList"
                   typeHandler="com.hikvision.idatafusion.dhidata.commons.typeHandler.ArrayTypeHandlerPg"/>
   </resultMap>

   其中 ArrayTypeHandlerPg 是转换的工具类：

   public class ArrayTypeHandlerPg implements TypeHandler<List<?>> {
   
       @Override
       public void setParameter(PreparedStatement ps, int i, List<?> parameter, JdbcType jdbcType) throws SQLException {
           //用于pg数组
           if (parameter == null) {
               try {
                   ps.setNull(i, JdbcType.ARRAY.TYPE_CODE);
               } catch (SQLException e) {
                   throw new TypeException("Error setting null for parameter #" + i + " with JdbcType " + jdbcType + " . "
                           + "Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. "
                           + "Cause: " + e, e);
               }
           } else {
               try {
                   ps.setArray(i, ps.getConnection().createArrayOf(jdbcType.name(), parameter.toArray()));
               } catch (Exception e) {
                   throw new TypeException("Error setting non null for parameter #" + i + " with JdbcType " + jdbcType
                           + " . "
                           + "Try setting a different JdbcType for this parameter or a different configuration property. "
                           + "Cause: " + e, e);
               }
           }
       }
   
       @Override
       public List<?> getResult(ResultSet rs, String columnName) throws SQLException {
           List<?> result;
           try {
               Array array = rs.getArray(columnName);
               result = array == null ? null : new ArrayList<>(Arrays.asList((Object[]) array.getArray()));
           } catch (Exception e) {
               throw new ResultMapException(
                       "Error attempting to get column '" + columnName + "' from result list.  Cause: " + e, e);
           }
           if (rs.wasNull()) {
               return null;
           } else {
               return result;
           }
       }
   
       @Override
       public List<?> getResult(ResultSet rs, int columnIndex) throws SQLException {
           List<?> result;
           try {
               Array array = rs.getArray(columnIndex);
               result = array == null ? null : new ArrayList<>(Arrays.asList((Object[]) array.getArray()));
           } catch (Exception e) {
               throw new ResultMapException(
                       "Error attempting to get column #" + columnIndex + " from result list.  Cause: " + e, e);
           }
           if (rs.wasNull()) {
               return null;
           } else {
               return result;
           }
       }
   
       @Override
       public List<?> getResult(CallableStatement cs, int columnIndex) throws SQLException {
           List<?> result;
           try {
               Array array = cs.getArray(columnIndex);
               result = array == null ? null : new ArrayList<>(Arrays.asList((Object[]) array.getArray()));
           } catch (Exception e) {
               throw new ResultMapException(
                       "Error attempting to get column #" + columnIndex + " from callable statement.  Cause: " + e, e);
           }
           if (cs.wasNull()) {
               return null;
           } else {
               return result;
           }
       }
   }

3. 修改增删改查数据sql语句：
   增、查时，resultMap?引用上面的映射文件的 BaseResultMap 便可 resultMap="BaseResultMap"，改时需要注意进行转换：

   <update id="updateProtocolData" parameterType="com.hikvision.idatafusion.hdiwebsite.model.EtlFlowTemplate">
           update etl_flow_template set
           <if test="protocolNames != null and protocolNames.size() > 0">
               protocol_document_file_name_list = #{protocolNames, jdbcType=VARCHAR, typeHandler=com.hikvision.idatafusion.dhidata.commons.typeHandler.ArrayTypeHandlerPg},
           </if>
           <if test="protocolPaths != null and protocolPaths.size() > 0">
               protocol_document_path_list = #{protocolPaths, jdbcType=VARCHAR, typeHandler=com.hikvision.idatafusion.dhidata.commons.typeHandler.ArrayTypeHandlerPg}
           </if>
           where id = #{templateId}
   </update>

   
   ?