SpringBoot保存PostgreSQL的Json、Array字段

前言

我们在SpringBoot中使用Mybatis-plus操作Mysql是非常方便的，但使用PostgreSQL必须指定类型。
本文主要解决SpringBoot开发过程中使用PostgreSQL操作复杂对象，如Json、Array等数据的处理。

        <!-- PostgreSql -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
        </dependency>

常见错误

类型为 json, 但表达式的类型为 character varying

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: 错误: 字段 "json_data" 的类型为 json, 但表达式的类型为 character varying
  建议：你需要重写或转换表达式
  位置：245
### The error may exist in com/ray/mapper/DataTestMapper.java (best guess)
### The error may involve com.ray.mapper.DataTestMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO data_test ( id, json_data, array_data, create_by, create_time, update_by, update_time )  VALUES  ( ?, ?, ?, ?, ?, ?, ?)
### Cause: org.postgresql.util.PSQLException: 错误: 字段 "json_data" 的类型为 json, 但表达式的类型为 character varying
  建议：你需要重写或转换表达式
  位置：245
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 错误: 字段 "json_data" 的类型为 json, 但表达式的类型为 character varying
  建议：你需要重写或转换表达式
  位置：245
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy143.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)

自定义转换器

JsonTypeHandlerPg

package com.ruoyi.common.utils.pg;

import com.ruoyi.common.utils.StringUtils;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @ClassName: JsonTypeHandlerPg
 * @Description: json对象处理
 * @Author: Ray
 * @Date: 2022-10-17 10:34
 */
@MappedTypes(String.class)
public class JsonTypeHandlerPg extends BaseTypeHandler<String> {

    /**
     * 引入PGSQL提供的工具类PGobject
     */
    private static final PGobject JSON_OBJECT = new PGobject();
    public static final String JSON_TYPE = "json";

    /**
     * 关键位置!!!
     */
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String param, JdbcType jdbcType) throws SQLException {
        JSON_OBJECT.setType(JSON_TYPE);
        JSON_OBJECT.setValue(param);
        ps.setObject(i, JSON_OBJECT);
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String sqlJson = rs.getString(columnName);
        if (StringUtils.isNotBlank(sqlJson)) {
            return sqlJson;
        }
        return null;
    }

    //根据列索引，获取可以为空的结果
    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String sqlJson = rs.getString(columnIndex);
        if (StringUtils.isNotBlank(sqlJson)) {
            return sqlJson;
        }
        return null;
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String sqlJson = cs.getString(columnIndex);
        if (StringUtils.isNotBlank(sqlJson)) {
            return sqlJson;
        }
        return null;
    }
}

使用

修改实体类

package com.ruoyi.dt.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.ruoyi.common.core.domain.BaseEntity;
import com.ruoyi.common.utils.pg.JsonTypeHandlerPg;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * 测试对象
 *
 * @author ray
 * @date 2022-10-18
 */
@Data
@EqualsAndHashCode(callSuper = true)
@TableName("data_test")
public class DataTest extends BaseEntity {

    private static final long serialVersionUID = 1L;

    /**
     * 主键
     */
    @TableId(value = "id")
    private Long id;
    
    /**
     * 关键位置!!!
     */
    @TableField(typeHandler = JsonTypeHandlerPg.class)
    private String jsonData;
    

}

修改mapper文件

<?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.ruoyi.dt.mapper.DataTestMapper">

    <resultMap type="com.ruoyi.dt.domain.DataTest" id="DataTestResult">
        <result property="id" column="id"/>
		<!-- 关键位置!!! -->
        <result property="jsonData" column="json_data" typeHandler="com.ruoyi.common.utils.pg.JsonTypeHandlerPg"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="delTime" column="del_time"/>
    </resultMap>

   
</mapper>

业务代码

    /**
     * 新增数据
     */
    @Override
    public Boolean insertByAo(DataTestAo ao) {
        DataTest add = BeanUtil.toBean(ao, DataTest.class);
        // 关键位置!!!
		add.setJsonData(JSONUtil.toJsonStr(ao));
        validEntityBeforeSave(add);
        boolean flag = baseMapper.insert(add) > 0;
        if (flag) {
            ao.setId(add.getId());
        }
        return flag;
    }

ArrayTypeHandlerPg

数组类型同理

package com.ruoyi.common.utils.pg;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;

/**
 * @ClassName: ArrayTypeHandlerPg
 * @Description: array对象处理
 * @Author: Ray
 * @Date: 2022-10-17 10:34
 */
public class ArrayTypeHandlerPg extends BaseTypeHandler<Object[]> {

    private static final Logger LOGGER = LoggerFactory.getLogger(ArrayTypeHandlerPg.class);

    private static final String TYPE_NAME_VARCHAR = "varchar";
    private static final String TYPE_NAME_INTEGER = "integer";
    private static final String TYPE_NAME_LONG = "long";
    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;
        } else if (parameter instanceof Long[]) {
            typename = TYPE_NAME_LONG;
        }

        if (typename == null) {
            throw new TypeException("arraytypehandler parameter typename error, your type is " + parameter.getClass().getName());
        }

        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;
    }
}

使用

修改实体类

package com.ruoyi.dt.domain;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.ruoyi.common.core.domain.BaseEntity;
import com.ruoyi.common.utils.pg.JsonTypeHandlerPg;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * 测试对象
 *
 * @author ray
 * @date 2022-10-18
 */
@Data
@EqualsAndHashCode(callSuper = true)
@TableName("data_test")
public class DataTest extends BaseEntity {

    private static final long serialVersionUID = 1L;

    /**
     * 主键
     */
    @TableId(value = "id")
    private Long id;
    
    /**
     * 关键位置!!!
     */
    @TableField(typeHandler = ArrayTypeHandlerPg.class)
    private String[] arrayData;
    

}

修改mapper文件

<?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.ruoyi.dt.mapper.DataTestMapper">

    <resultMap type="com.ruoyi.dt.domain.DataTest" id="DataTestResult">
        <result property="id" column="id"/>
		<!-- 关键位置!!! -->
        <result property="arrayData" column="array_data" typeHandler="com.ruoyi.common.utils.pg.ArrayTypeHandlerPg"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="delTime" column="del_time"/>
    </resultMap>

   
</mapper>
# spring boot # postgresql
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

HAS2024：华为云以系统性创新加速千行万业智能化升级

avatar 华为云开发者联盟
cover

数仓的两种轻量级数据交换格式：json与jsonb

avatar 华为云开发者联盟
cover

如何使用云数据库GaussDB管理平台进行实例安装？

avatar 华为云开发者联盟