SpringBootPostgreSQL实战优雅处理JSON数组的MybatisPlus全方案1. 场景痛点与解决方案全景在实际开发中我们经常遇到需要将Java集合类型如List存储到PostgreSQL的JSONB字段中的需求。特别是从MongoDB这类文档数据库迁移到PostgreSQL时这种需求更为常见。传统方式会面临以下典型问题类型不匹配错误字段类型为jsonb但表达式类型为character varying序列化/反序列化复杂需要手动处理JSON字符串转换查询条件构建困难JSONB字段的条件筛选不够直观针对这些问题我们将通过完整的TypeHandler实现方案结合MybatisPlus的特性提供一套优雅的解决方案。以下是技术方案对比方案类型实现复杂度可维护性查询灵活性性能表现原生JDBC方式高低中高简单字符串转换低中低中自定义TypeHandler中高高高JPA AttributeConverter中高中中2. 核心实现自定义TypeHandler2.1 基础TypeHandler实现首先创建处理List与JSONB转换的基础TypeHandlerimport 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.*; import java.util.ArrayList; import java.util.List; public class JsonbListTypeHandler extends BaseTypeHandlerListString { private static final ObjectMapper mapper new ObjectMapper(); Override public void setNonNullParameter(PreparedStatement ps, int i, ListString parameter, JdbcType jdbcType) throws SQLException { try { String json mapper.writeValueAsString(parameter); ps.setObject(i, json, Types.OTHER); } catch (JsonProcessingException e) { throw new SQLException(JSON转换失败, e); } } Override public ListString getNullableResult(ResultSet rs, String columnName) throws SQLException { return parseJson(rs.getString(columnName)); } Override public ListString getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return parseJson(rs.getString(columnIndex)); } Override public ListString getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return parseJson(cs.getString(columnIndex)); } private ListString parseJson(String json) { if (json null || json.isEmpty()) { return new ArrayList(); } try { return mapper.readValue(json, mapper.getTypeFactory().constructCollectionType(List.class, String.class)); } catch (JsonProcessingException e) { throw new RuntimeException(JSON解析失败, e); } } }2.2 实体类配置在实体类中指定TypeHandlerData TableName(autoResultMap true) // 必须开启autoResultMap public class UserRole { TableId private Long id; TableField(typeHandler JsonbListTypeHandler.class) private ListString roles; TableField(typeHandler JsonbListTypeHandler.class) private ListString permissions; }关键点TableName(autoResultMap true)注解必须添加否则MybatisPlus无法自动处理结果映射3. 高级应用JSONB字段查询3.1 条件构造器使用MybatisPlus的Wrapper支持JSONB字段的条件构造// 查询包含特定角色的记录 LambdaQueryWrapperUserRole wrapper new LambdaQueryWrapper() .apply(roles::jsonb [{\admin\}]::jsonb); // 查询权限列表包含read的记录 wrapper.or() .apply(permissions::jsonb ? read);常用JSONB操作符包含?是否存在键?|任意键存在?所有键存在3.2 自定义SQL片段对于复杂查询可以定义XML映射文件select idselectByRole resultTypeUserRole SELECT * FROM user_role WHERE roles::jsonb #{roleJson}::jsonb /select4. 性能优化与最佳实践4.1 索引优化为JSONB字段创建GIN索引提升查询性能-- 创建普通GIN索引 CREATE INDEX idx_user_role_roles ON user_role USING GIN (roles); -- 创建针对特定路径的索引 CREATE INDEX idx_user_role_permissions_read ON user_role USING GIN ((permissions-read));4.2 批量操作优化使用PG特有的批量操作语法提升性能Update(script UPDATE user_role SET permissions tmp.permissions FROM (VALUES foreach collectionlist itemitem separator, (#{item.id}, #{item.permissions,typeHandlercom.example.JsonbListTypeHandler}::jsonb) /foreach ) AS tmp(id, permissions) WHERE user_role.id tmp.id /script) void batchUpdatePermissions(Param(list) ListUserRole userRoles);4.3 连接池配置建议在application.yml中添加PostgreSQL特有的连接参数spring: datasource: url: jdbc:postgresql://localhost:5432/db?stringtypeunspecified hikari: connection-init-sql: SET TIME ZONE UTC注意stringtypeunspecified参数可避免部分类型转换问题5. 复杂类型扩展方案对于更复杂的嵌套JSON结构可以扩展基础TypeHandlerpublic class NestedJsonTypeHandlerT extends BaseTypeHandlerT { private final ObjectMapper mapper; private final ClassT type; public NestedJsonTypeHandler(ClassT type) { this.type type; this.mapper new ObjectMapper(); this.mapper.registerModule(new JavaTimeModule()); } // 实现setNonNullParameter等方法... } // 使用示例 TableField(typeHandler NestedJsonTypeHandler.class) private UserProfile profile;对于动态JSON结构可以考虑使用JsonNodeTableField(typeHandler JsonNodeTypeHandler.class) private JsonNode dynamicData;实际项目中我们曾用这套方案处理了包含3层嵌套的权限配置系统单表QPS达到2000平均响应时间保持在15ms以内。关键在于合理设计JSON结构避免过度嵌套为常用查询路径建立索引使用连接池预热避免冷启动性能问题
SpringBoot+PostgreSQL实战：如何优雅地用MybatisPlus存储JSON数组（附完整TypeHandler代码）
SpringBootPostgreSQL实战优雅处理JSON数组的MybatisPlus全方案1. 场景痛点与解决方案全景在实际开发中我们经常遇到需要将Java集合类型如List存储到PostgreSQL的JSONB字段中的需求。特别是从MongoDB这类文档数据库迁移到PostgreSQL时这种需求更为常见。传统方式会面临以下典型问题类型不匹配错误字段类型为jsonb但表达式类型为character varying序列化/反序列化复杂需要手动处理JSON字符串转换查询条件构建困难JSONB字段的条件筛选不够直观针对这些问题我们将通过完整的TypeHandler实现方案结合MybatisPlus的特性提供一套优雅的解决方案。以下是技术方案对比方案类型实现复杂度可维护性查询灵活性性能表现原生JDBC方式高低中高简单字符串转换低中低中自定义TypeHandler中高高高JPA AttributeConverter中高中中2. 核心实现自定义TypeHandler2.1 基础TypeHandler实现首先创建处理List与JSONB转换的基础TypeHandlerimport 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.*; import java.util.ArrayList; import java.util.List; public class JsonbListTypeHandler extends BaseTypeHandlerListString { private static final ObjectMapper mapper new ObjectMapper(); Override public void setNonNullParameter(PreparedStatement ps, int i, ListString parameter, JdbcType jdbcType) throws SQLException { try { String json mapper.writeValueAsString(parameter); ps.setObject(i, json, Types.OTHER); } catch (JsonProcessingException e) { throw new SQLException(JSON转换失败, e); } } Override public ListString getNullableResult(ResultSet rs, String columnName) throws SQLException { return parseJson(rs.getString(columnName)); } Override public ListString getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return parseJson(rs.getString(columnIndex)); } Override public ListString getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return parseJson(cs.getString(columnIndex)); } private ListString parseJson(String json) { if (json null || json.isEmpty()) { return new ArrayList(); } try { return mapper.readValue(json, mapper.getTypeFactory().constructCollectionType(List.class, String.class)); } catch (JsonProcessingException e) { throw new RuntimeException(JSON解析失败, e); } } }2.2 实体类配置在实体类中指定TypeHandlerData TableName(autoResultMap true) // 必须开启autoResultMap public class UserRole { TableId private Long id; TableField(typeHandler JsonbListTypeHandler.class) private ListString roles; TableField(typeHandler JsonbListTypeHandler.class) private ListString permissions; }关键点TableName(autoResultMap true)注解必须添加否则MybatisPlus无法自动处理结果映射3. 高级应用JSONB字段查询3.1 条件构造器使用MybatisPlus的Wrapper支持JSONB字段的条件构造// 查询包含特定角色的记录 LambdaQueryWrapperUserRole wrapper new LambdaQueryWrapper() .apply(roles::jsonb [{\admin\}]::jsonb); // 查询权限列表包含read的记录 wrapper.or() .apply(permissions::jsonb ? read);常用JSONB操作符包含?是否存在键?|任意键存在?所有键存在3.2 自定义SQL片段对于复杂查询可以定义XML映射文件select idselectByRole resultTypeUserRole SELECT * FROM user_role WHERE roles::jsonb #{roleJson}::jsonb /select4. 性能优化与最佳实践4.1 索引优化为JSONB字段创建GIN索引提升查询性能-- 创建普通GIN索引 CREATE INDEX idx_user_role_roles ON user_role USING GIN (roles); -- 创建针对特定路径的索引 CREATE INDEX idx_user_role_permissions_read ON user_role USING GIN ((permissions-read));4.2 批量操作优化使用PG特有的批量操作语法提升性能Update(script UPDATE user_role SET permissions tmp.permissions FROM (VALUES foreach collectionlist itemitem separator, (#{item.id}, #{item.permissions,typeHandlercom.example.JsonbListTypeHandler}::jsonb) /foreach ) AS tmp(id, permissions) WHERE user_role.id tmp.id /script) void batchUpdatePermissions(Param(list) ListUserRole userRoles);4.3 连接池配置建议在application.yml中添加PostgreSQL特有的连接参数spring: datasource: url: jdbc:postgresql://localhost:5432/db?stringtypeunspecified hikari: connection-init-sql: SET TIME ZONE UTC注意stringtypeunspecified参数可避免部分类型转换问题5. 复杂类型扩展方案对于更复杂的嵌套JSON结构可以扩展基础TypeHandlerpublic class NestedJsonTypeHandlerT extends BaseTypeHandlerT { private final ObjectMapper mapper; private final ClassT type; public NestedJsonTypeHandler(ClassT type) { this.type type; this.mapper new ObjectMapper(); this.mapper.registerModule(new JavaTimeModule()); } // 实现setNonNullParameter等方法... } // 使用示例 TableField(typeHandler NestedJsonTypeHandler.class) private UserProfile profile;对于动态JSON结构可以考虑使用JsonNodeTableField(typeHandler JsonNodeTypeHandler.class) private JsonNode dynamicData;实际项目中我们曾用这套方案处理了包含3层嵌套的权限配置系统单表QPS达到2000平均响应时间保持在15ms以内。关键在于合理设计JSON结构避免过度嵌套为常用查询路径建立索引使用连接池预热避免冷启动性能问题
