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

您所在的位置：网站首页 › pgsql数组查询 › mybatis和mybatisplus映射postgresql数组类型字段，查询与插入操作

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

2024-07-12 01:41| 来源: 网络整理| 查看: 265

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文件 select * from md_error_code where ori_code @> array[#{oriCode}] 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}) update md_error_code 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} where err_code=#{errorCode.errCode}

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

4.ArrayTypeHandler类 public class ArrayTypeHandler extends BaseTypeHandler{ 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{ @Select("select * from md_error_code where ori_code @> array[#{oriCode}]") public List queryErrorCodeListByOriCode(@Param("oriCode")String oriCode); }

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

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

【本文地址】

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

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

今日新闻

推荐新闻