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的方法就可以————————————————
