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.Driver
    url: jdbc:postgresql://127.0.0.1:5432/postgres
    username: postgres
    password: 123456
  jpa:
    hibernate:
      ddl-auto: update  #自动生成数据库表
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQL9Dialect
    show-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);
    }

    @Override
    public 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 {
    @Id
    private 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 {

    @Resource
    private TestMapper testMapper;
 

    @Test
    public 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());
    }
}