mybatis和mybatisplus映射postgresql数组类型字段，查询与插入操作

mybatis版本为3.4.0
mybatisplus版本为3.4.0

一、mybatis

1.数据结构

DROP TABLE IF EXISTS "md_error_code";
CREATE TABLE "md_error_code" (
  "err_code" varchar(15) COLLATE "pg_catalog"."default" NOT NULL,
  "ori_code" varchar[] COLLATE "pg_catalog"."default",
  "system" varchar(20) COLLATE "pg_catalog"."default",
  "err_desc_en" varchar(300) COLLATE "pg_catalog"."default",
  "err_desc_cn" varchar(300) COLLATE "pg_catalog"."default",
  "usr_desc_en" varchar(300) COLLATE "pg_catalog"."default",
  "usr_desc_cn" varchar(300) COLLATE "pg_catalog"."default",
  "udate" timestamp(6),
  "oper_user" varchar(255) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Primary Key structure for table md_error_code
-- ----------------------------
ALTER TABLE "md_error_code" ADD CONSTRAINT "md_error_code_pkey" PRIMARY KEY ("err_code");

2.实体

public class MdErrorCode {
	private String errCode;
	private String[] oriCode;
	private String system;
	private String errDescEn;
	private String errDescCn;
	private String usrDescEn;
	private String usrDescCn;
	private String operUser;
	private LocalDateTime updateTime = LocalDateTime.now();
}	

3.mapper.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mytest.errorcode.dao.mapper.MdErrorCodeMapper">
	<resultMap id="mdErrorCodeResultMap" type="com.mytest.errorcode.dao.entity.MdErrorCode">
 		<id property="errCode" column="err_code" />
 		<result property="oriCode" column="ori_code" jdbcType="ARRAY" typeHandler="com.mytest.errorcode.config.ArrayTypeHandler"/>
 		<result property="system" column="system" />
 		<result property="errDescEn" column="err_desc_en" />
 		<result property="errDescCn" column="err_desc_cn" />
 		<result property="usrDescEn" column="usr_desc_en" />
 		<result property="usrDescCn" column="usr_desc_cn" />
 		<result property="operUser" column="oper_user" />
 		<result property="updateTime" column="udate" />
 		
	</resultMap>
	
	<select id="findByOriCode" resultMap="mdErrorCodeResultMap" >
		select * from md_error_code where ori_code @> array[#{oriCode}]
	</select>
	
	
	<insert id="insertErrorCode" parameterType="com.mytest.errorcode.dao.entity.MdErrorCode">
		insert into md_error_code 
		("err_code","ori_code","system","err_desc_en","err_desc_cn",
		"usr_desc_en","usr_desc_cn","oper_user","udate") 
		values 
		(#{errorCode.errCode},
		#{errorCode.oriCode, jdbcType=ARRAY, typeHandler=com.mytest.errorcode.config.ArrayTypeHandler},
		#{errorCode.system},#{errorCode.errDescEn},
		#{errorCode.errDescCn},#{errorCode.usrDescEn},#{errorCode.usrDescCn},#{errorCode.operUser},
		#{errorCode.updateTime})
	</insert>
	
	
	<update id="updateErrorCode">
		update md_error_code 
		<set>
			ori_code=#{errorCode.oriCode,jdbcType=ARRAY, typeHandler=com.myteset.errorcode.config.ArrayTypeHandler},
			system=#{errorCode.system},err_desc_en=#{errorCode.errDescEn},err_desc_cn=#{errorCode.errDescCn},
			usr_desc_en=#{errorCode.usrDescEn},usr_desc_cn=#{errorCode.usrDescCn},oper_user=#{errorCode.operUser},
			udate=#{errorCode.updateTime} 
		</set>
		where err_code=#{errorCode.errCode}
	</update>
	
</mapper>

a.注意oriCode字段的映射的配置
b.作为查询条件时"@>"表示包含的意思ori_code@>“oriCode”
c.插入和更新时oriCode也需要特殊配置

4.ArrayTypeHandler类

public class ArrayTypeHandler extends BaseTypeHandler<Object[]>{
	
	private static final Logger LOGGER = LoggerFactory.getLogger(ArrayTypeHandler.class);
	
	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("arraytypehandler parameter typename error, your type is " + parameter.getClass().getName());
        }
        
        // 这2行是关键的代码，创建array，然后ps.setarray(i, array)就可以了
        Array array = ps.getConnection().createArrayOf(typename, parameter);
        ps.setArray(i, array);
	}

	@Override
	public Object[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
		return getArray(rs.getArray(columnName));
	}

	
	@Override
	public Object[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		return getArray(rs.getArray(columnIndex));
	}

	
	@Override
	public Object[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
		return getArray(cs.getArray(columnIndex));
	}

	private Object[] getArray(Array array) {

		if (array == null) {
			return null;
		}

		try {
			return (Object[]) array.getArray();
		} catch (SQLException e) {
			LOGGER.error("ArrayTypeHandler getArray SQLException",e);
		}
		return null;

	}
}

二、mybatis plus

这里就不对mybatis plus的使用进行说明了

1.数据结构与mybatis一致

2.实体

@TableName(value = "md_error_code",autoResultMap = true)
public class MdErrorCode {
	@TableId
	private String errCode;
	@TableField(value = "ori_code",typeHandler = ArrayTypeHandler.class,jdbcType = JdbcType.ARRAY)
	private String[] oriCode;
	private String system;
	private String errDescEn;
	private String errDescCn;
	private String usrDescEn;
	private String usrDescCn;
	@TableField(value = "udate",fill = FieldFill.UPDATE)
	private LocalDateTime updateTime = LocalDateTime.now();
}	

注：这里的ArrayTypeHandler是mybatis的，不需要自己实现

3.查询方法

public interface MdErrorCodeMapper extends BaseMapper<MdErrorCode>{
	@Select("select * from md_error_code where ori_code @> array[#{oriCode}]")
	public List<Map<String, Object>> queryErrorCodeListByOriCode(@Param("oriCode")String oriCode);
}

a.在使用mybatis的ArrayTypeHandler情况下，这里最好用List<Map<String, Object>>来接收结果，不然的话oriCode无法映射结果。目前没有找到解决的方法，如果有更好的方法请评论。
使用自定义的ArrayTypeHandler不知道能不能解决这个问题，没有尝试。方法和mybatis一致。

4.插入和更新方法直接使用mybatis plus的方法就可以