MyBatis+PostgreSQL处理pg中json及_varchar字段类型数据（插入及查询）

2021-07-14 06:42:18

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>

注意事项：

  1. 如果想要把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>

注意事项：

  1. 如果想要把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;



}

MyBatis+PostgreSQL处理pg中json及_varchar字段类型数据（插入及查询）

上一篇：GeoServer连接Sql Server


下一篇：查看数据库所有表数据占用的空间大小