pgsql中使用jsonb的mybatis-plus和jps的配置

在pgsql中使用jsonb类型的数据时，实体对象要对其进行一些相关的配置，而mybatis和jpa中使用各不相同。 

在项目中经常会结合 MyBatis-Plus 和 JPA 进行开发，MyBatis_plus对于操作数据更灵活，jpa可以自动建表，两者各取其长，就能减少开发和运维的工作量：

Mybatis-Plus和Spring Data JPA的比较

---

一、 添加依赖

 

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>org.example</groupId><artifactId>MybatisTest</artifactId><version>1.0-SNAPSHOT</version><properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><mybatisplus.version>3.5.12-SNAPSHOT</mybatisplus.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId></dependency><dependency><groupId>com.alibaba.fastjson2</groupId><artifactId>fastjson2</artifactId><version>2.0.23</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.8</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><version>2.7.8</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId><version>2.7.8</version></dependency><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-core</artifactId><version>5.6.15.Final</version></dependency><dependency><groupId>com.vladmihalcea</groupId><artifactId>hibernate-types-52</artifactId><version>2.16.0</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-jsqlparser-4.9</artifactId><version>${mybatisplus.version}</version></dependency></dependencies><dependencyManagement><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-dependencies</artifactId><version>2.7.18</version><type>pom</type><scope>import</scope></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>${mybatisplus.version}</version></dependency></dependencies></dependencyManagement><repositories><repository><id>aliyun</id><url>https://maven.aliyun.com/nexus/content/groups/public</url></repository><repository><id>oss-public</id><url>https://oss.sonatype.org/content/repositories/public</url></repository><repository><id>snapshots</id><url>https://central.sonatype.com/repository/maven-snapshots/</url><releases><enabled>false</enabled></releases><snapshots><enabled>true</enabled></snapshots></repository></repositories>
</project>

二、application.yml配置数据库以及jpa:

spring:datasource:driver-class-name: org.postgresql.Driverurl: jdbc:postgresql://127.0.0.1:5432/postgresusername: postgrespassword: 123456jpa:hibernate:ddl-auto: update  #自动生成数据库表properties:hibernate:dialect: org.hibernate.dialect.PostgreSQL9Dialectshow-sql: true # jpa配置，在控制台显示hibernate的sql

三、自定义类型处理器

创建处理 jsonb 类型的 TypeHandler：

package org.example.entity;import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.SQLException;@Slf4j
@MappedTypes({Object.class})
//  FastjsonTypeHandler 同理继承使用
// 更多使用查看文档 https://baomidou.com/guides/type-handler/#%E8%87%AA%E5%AE%9A%E4%B9%89%E7%B1%BB%E5%9E%8B%E5%A4%84%E7%90%86%E5%99%A8
public class JsonbTypeHandler extends JacksonTypeHandler {public JsonbTypeHandler(Class<?> type) {super(type);}// 自3.5.6版本开始支持泛型,需要加上此构造.public JsonbTypeHandler(Class<?> type, Field field) {super(type, field);}@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {if (ps != null) {PGobject jsonObject = new PGobject();jsonObject.setType("jsonb");jsonObject.setValue(toJson(parameter));ps.setObject(i, jsonObject);}}
}

四、  实体类配置

这里要注意四个地方
 1. TableName这里一定要指定autoResultMap，这里是让mybatis在查询 xml 时候需要映射 typeHandler 配合 @TableField(typeHandler = JsonbTypeHandler.class) 使用。

否则定义的jsonb对象查出来是空的。

2.写一个父类，JsonBaseEntity，定义json和jsonb的类型，让实体类去继承它。

@TypeDefs({@TypeDef(name = "json", typeClass = JsonStringType.class),@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
@MappedSuperclass
public class JsonBaseEntity {}

3. 在字段上使用 @TableField 指定类型处理器TypeHandler，此处是mybatis在添加，查询时使用。

4. 在字段上使用@Type，@Column来定议jpa字段的类型以及别名

@Type(type="jsonb")
@Column(columnDefinition = "jsonb",name="content_list")

 

package org.example.entity;import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;import lombok.Data;
import org.hibernate.annotations.Type;import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;
import java.util.List;/*** 一个测试对象** @author lyl* @version v1.0* @since 2025/4/18*/@Entity(name = "test_bb")
@Data
@TableName(autoResultMap = true, value = "test_bb")
public class TestEntity extends JsonBaseEntity   implements Serializable {@Idprivate String id;private String name;@Type(type = "jsonb")@Column(name = "content", columnDefinition = "jsonb")@TableField(typeHandler = JsonbTypeHandler.class)private List<String> content;@Type(type = "jsonb")@Column(name = "data", columnDefinition = "jsonb")@TableField(typeHandler = JsonbTypeHandler.class)private List<Integer> data;@Type(type="jsonb")@Column(columnDefinition = "jsonb",name="content_list")@TableField(typeHandler = JsonbTypeHandler.class)private List<TestContent> contentList;
}

五、 创建mapper

@Mapper
public interface TestMapper extends BaseMapper<TestEntity> {
}

六、 调用例子

@SpringBootTest
public class TestAction {@Resourceprivate TestMapper testMapper;@Testpublic void test() {TestEntity entity=new TestEntity();entity.setId("66");;entity.setName("556");List<String> aa=new ArrayList<>();aa.add("aa");aa.add("cc");List<Integer> all=new ArrayList<>();all.add(11);all.add(22);all.add(33);entity.setData(all);entity.setContent(aa);entity.setContentList(Arrays.asList(TestContent.of("name", "秋秋"), TestContent.of("name", "哈哈")));// testMapper.insert(entity);TestEntity t=testMapper.selectById("66");System.out.println("data:"+t.getData());System.out.println("content:"+t.getContentList().get(0).getContent());}
}

 

---