MyBatis+PostgreSQL处理pg中json及_varchar字段类型数据（插⼊及查询）
MyBatis+PostgreSQL处理pg中json及_varchar字段类型数据（插⼊及查询）
MyBatis+PostgreSQL
postgrel中存在json和数组类型的字段，⽽mybatis原⽣并不⽀持这种类型（即jdbcType不存在JSON或者数组类型），如果想要将json或者数组格式的数据插⼊到pg数据库，那么mybatis提供了BaseTypeHandler已供开发者⾃⼰扩展，开发者需要根据下⾯将展⽰把List<String> 和 JSONObject类型（java）的数据插⼊到pg数据库。
1.Array
peHandler
import org.sult.ResultMapException;
import org.pe.JdbcType;
import org.pe.TypeException;
import org.pe.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(), Array()));
} 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()));
} 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()));
} 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;
}
}
@Overridefastjson字符串转数组
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()));
} 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.l
<!--1.插⼊数据: 其中字段next_users的类型为(_varchar：字符串数组)-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id"
parameterType="ity.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=peHandler.ArrayTypeHandlerPg} </if>
</trim>
)
</insert>
<!--2.查询数据-->
<resultMap id="BaseResultMap" type="ity.ProcessInfo">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="next_users" property="nextUsers" jdbcType="VARCHAR"
typeHandler="peHandler.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>
注意事项：
1. 如果想要把pg数据库表中的字符串数组字段的值映射到java的pojo对象中，必须使⽤resultMap标签来映射，不然此字段的值为null。
2.Json
peHandler
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import org.pe.BaseTypeHandler;
import org.pe.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.Json(parameter));
ps.setObject(i, jsonObject);
}
@Override
public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
String(columnName), clazz);
}
@Override
public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String(columnIndex), clazz);
}
@Override
public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String(columnIndex), clazz);
}
private String toJson(T object) {
try {
JSONString(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.l
<!--1.插⼊数据: 其中字段url的类型为(json：json格式)-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id"
parameterType="ity.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=peHandler.JSONTypeHandlerPg}
</if>
</trim>
)
</insert>
<!--2.查询数据-->
<resultMap id="BaseResultMap" type="ity.ProcessInfo">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="url" property="nextUsers" jdbcType="OTHER"
typeHandler="peHandler.JSONTypeHandlerPg"/>
</resultMap>
<select id="getById" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT
id,
url
FROM APPLY_INFO
WHERE id = #{id}
</select>
注意事项：
1. 如果想要把pg数据库表中的json字段类型的值映射到java的pojo对象中，必须使⽤resultMap标签来映射，不然此字段的值为null。如果使⽤Mybatis-plus，Bean对象写法
bean;
JSONTypeHandlerPg;
import com.alibaba.fastjson.JSONObject;
import batisplus.annotation.IdType;
import batisplus.annotation.TableField;
import batisplus.annotation.TableId;
import batisplus.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;
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论