当前位置：首页 > news >正文

Springboot+PostgreSQL+MybatisPlus存储JSON或List、数组（Array）数据

news 来源：原创 2024/9/30 7:16:57

项目架构

Springboot+PostgreSQL+MybatisPlus
从Mongodb转过来的项目，有存储json数据的需求，但是在mybatis-plus上会出点问题
报错： Error updating database. Cause: org.postgresql.util.PSQLException 字段 “” 的类型为 jsonb, 但表达式的类型为 character varying 建议：你需要重写或转换表达式

实体类定义：

@EqualsAndHashCode(callSuper = true)
@Data
@TableName("tb_user_role")
@FieldDefaults(level = AccessLevel.PRIVATE)
public class TbUserRole extends BaseEntity {@TableField("user_id")String userId;@JsonInclude(JsonInclude.Include.NON_EMPTY)List<String> roles; // 存储为 JSONB@JsonInclude(JsonInclude.Include.NON_EMPTY)List<String> permissions; // 存储为 JSONB
}

SQL

CREATE TABLE tb_user_role (id VARCHAR(255) PRIMARY KEY, -- 主键ID，继承自 BaseEntityuser_id VARCHAR(255) NOT NULL, -- 用户IDroles JSONB, -- 角色，存储为 JSONB 类型permissions JSONB, -- 权限，存储为 JSONB 类型deleted BOOLEAN DEFAULT FALSE, -- 逻辑删除标识create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 更新时间
);

前端传入的结构

     @Schema(description = "角色，传入的对象需要从Role接口获取")List<String> roles;@Schema(description = "权限，传入的对象需要从Permission接口获取")List<String> permissions;

存储时的问题

Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1c1e6475]
2024-09-27 17:48:43.398 ERROR [tid] [sid] [pid] [nio-8050-exec-1] c.s.w.h.GlobalExceptionHandler@viewExceptionHandler:66 : 
### Error updating database.  Cause: org.postgresql.util.PSQLException: 错误: 字段 "roles" 的类型为 jsonb, 但表达式的类型为 character varying建议：你需要重写或转换表达式位置：117
### The error may exist in com/sgcchg/data/mapper/UserRoleMapper.java (best guess)
### The error may involve com.sgcchg.data.mapper.UserRoleMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO tb_user_role  ( id, user_id, roles, permissions, deleted, create_time, update_time )  VALUES (  ?, ?, ?, ?, ?, ?, ?  )
### Cause: org.postgresql.util.PSQLException: 错误: 字段 "roles" 的类型为 jsonb, 但表达式的类型为 character varying建议：你需要重写或转换表达式位置：117
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 错误: 字段 "roles" 的类型为 jsonb, 但表达式的类型为 character varying建议：你需要重写或转换表达式位置：117 location: com.sgcchg.business.impl.user.UserServiceImpl:101

问题解决

修改Entity定义

@EqualsAndHashCode(callSuper = true)
@Data
@TableName("tb_user_role")
@FieldDefaults(level = AccessLevel.PRIVATE)
public class TbUserRole extends BaseEntity {@TableField("user_id")String userId;@TableField(typeHandler = ListToStringTypeHandler.class)@JsonInclude(JsonInclude.Include.NON_EMPTY)List<String> roles; // 存储为 JSONB@TableField(typeHandler = ListToStringTypeHandler.class)@JsonInclude(JsonInclude.Include.NON_EMPTY)List<String> permissions; // 存储为 JSONB
}

添加TypeHandler

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;public class ListToStringTypeHandler extends BaseTypeHandler<List<String>> {private static final ObjectMapper objectMapper = new ObjectMapper();//    @Override
//    public void setNonNullParameter(PreparedStatement ps, int i, List<String> parameter, JdbcType jdbcType) throws SQLException {
//        // 将 List 转为 JSON 字符串存储
//        try {
//            ps.setString(i, objectMapper.writeValueAsString(parameter));
//        } catch (JsonProcessingException e) {
//            throw new SQLException("Could not convert list to JSON", e);
//        }
//    }@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, List<String> parameter, JdbcType jdbcType) throws SQLException {try {String jsonString = objectMapper.writeValueAsString(parameter);ps.setObject(i, jsonString, JdbcType.OTHER.TYPE_CODE); // 使用 JDBC 的 OTHER 类型插入 JSONB} catch (JsonProcessingException e) {throw new SQLException("Could not convert list to JSON", e);}}@Overridepublic List<String> getNullableResult(ResultSet rs, String columnName) throws SQLException {String json = rs.getString(columnName);return parseJsonToList(json);}@Overridepublic List<String> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {String json = rs.getString(columnIndex);return parseJsonToList(json);}@Overridepublic List<String> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {String json = cs.getString(columnIndex);return parseJsonToList(json);}private List<String> parseJsonToList(String json) {if (json == null || json.trim().isEmpty()) {return Collections.emptyList();}try {return objectMapper.readValue(json, List.class);} catch (JsonProcessingException e) {return Collections.emptyList();}}}