MyBatis+PostgreSQL处理pg中json及_varchar字段类型数据(插入及查询)
MyBatis+PostgreSQL处理pg中json及_varchar字段类型数据(插入及查询)
MyBatis+PostgreSQL
postgrel中存在json和数组类型的字段,而mybatis原生并不支持这种类型(即jdbcType不存在JSON或者数组类型),如果想要将json或者数组格式的数据插入到pg数据库,那么mybatis提供了BaseTypeHandler已供开发者自己扩展,开发者需要根据自己的业务实现(implements)或者继承(extends)BaseTypeHandler。
下面将展示把List<String> 和 JSONObject类型(java)的数据插入到pg数据库。
1.Array
1.1.typeHandler
import org.apache.ibatis.executor.result.ResultMapException;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;
import org.apache.ibatis.type.TypeHandler;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class ArrayTypeHandlerPg implements TypeHandler<List<?>> {
@Override
public void setParameter(PreparedStatement ps, int i, List<?> parameter, JdbcType jdbcType) throws SQLException {
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;
}
}
}
1.2.mapper.xml
<!--1.插入数据: 其中字段next_users的类型为(_varchar:字符串数组)-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id"
parameterType="com.qin.entity.ApplyInfo">
INSERT INTO APPLY_INFO (
<trim prefixOverrides=",">
<if test="nextUsers!=null">,next_users</if>
</trim>
)
VALUES
(
<trim prefixOverrides=",">
<if test="nextUsers!=null">
,#{nextUsers,jdbcType=VARCHAR,typeHandler=com.qin.common.typeHandler.ArrayTypeHandlerPg}
</if>
</trim>
)
</insert>
<!--2.查询数据-->
<resultMap id="BaseResultMap" type="com.qin.entity.ProcessInfo">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="next_users" property="nextUsers" jdbcType="VARCHAR"
typeHandler="com.qin.common.typeHandler.ArrayTypeHandlerPg"/>
</resultMap>
<select id="getById" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT
id,
next_users
FROM APPLY_INFO
WHERE id = #{id}
</select>
<!--3.查询数据:数组中是否包某个字符串元素-->
<select id="getById" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT
id,
next_users
FROM APPLY_INFO
WHERE next_users @> array[#{userName}] ::_varchar
</select>
注意事项:
- 如果想要把pg数据库表中的字符串数组字段的值映射到java的pojo对象中,必须使用resultMap标签来映射,不然此字段的值为null。
2.Json
2.1.typeHandler
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.postgresql.util.PGobject;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JSONTypeHandlerPg<T extends Object> extends BaseTypeHandler<T> {
private static final PGobject jsonObject = new PGobject();
private Class<T> clazz;
public JSONTypeHandlerPg(Class<T> clazz) {
if (clazz == null) throw new IllegalArgumentException("Type argument cannot be null");
this.clazz = clazz;
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
jsonObject.setType("json");
jsonObject.setValue(this.toJson(parameter));
ps.setObject(i, jsonObject);
}
@Override
public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
return this.toObject(rs.getString(columnName), clazz);
}
@Override
public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return this.toObject(rs.getString(columnIndex), clazz);
}
@Override
public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return this.toObject(cs.getString(columnIndex), clazz);
}
private String toJson(T object) {
try {
return JSON.toJSONString(object, SerializerFeature.WriteNullListAsEmpty);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
private T toObject(String content, Class<?> clazz) {
if (content != null && !content.isEmpty()) {
try {
return (T) JSON.parseObject(content,clazz);
} catch (Exception e) {
throw new RuntimeException(e);
}
} else {
return null;
}
}
}
2.2.mapper.xml
<!--1.插入数据: 其中字段url的类型为(json:json格式)-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id"
parameterType="com.qin.entity.ApplyInfo">
INSERT INTO APPLY_INFO (
<trim prefixOverrides=",">
<if test="url!=null">,url</if>
</trim>
)
VALUES
(
<trim prefixOverrides=",">
<if test="url!=null">
,#{nextUsers,jdbcType=OTHER,typeHandler=com.qin.common.typeHandler.JSONTypeHandlerPg}
</if>
</trim>
)
</insert>
<!--2.查询数据-->
<resultMap id="BaseResultMap" type="com.qin.entity.ProcessInfo">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="url" property="nextUsers" jdbcType="OTHER"
typeHandler="com.qin.common.typeHandler.JSONTypeHandlerPg"/>
</resultMap>
<select id="getById" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT
id,
url
FROM APPLY_INFO
WHERE id = #{id}
</select>
注意事项:
- 如果想要把pg数据库表中的json字段类型的值映射到java的pojo对象中,必须使用resultMap标签来映射,不然此字段的值为null。
如果使用Mybatis-plus,Bean对象写法
package cc.oyz.bean;
import cc.oyz.common.JSONTypeHandlerPg;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
/**
* @description: 视角
* @author: libin.hao
* @time: 2021/5/18 9:27
*/
@Data
@TableName(autoResultMap = true)
public class ViewingAngle implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "id",type = IdType.AUTO)
@ApiModelProperty(value = "主键ID")
private Integer id;
@ApiModelProperty(value = "名称")
private String name;
@ApiModelProperty(value = "目的地")
@TableField(typeHandler = JSONTypeHandlerPg.class)
private JSONObject destination;
@ApiModelProperty(value = "定向")
@TableField(typeHandler = JSONTypeHandlerPg.class)
private JSONObject orientation;
}
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析