Spring Boot项目中如何将JsonObject存储到PostgreSQL数据库？

步骤1：添加所需依赖 #

Maven项目在pom.xml中加入以下依赖，Gradle项目对应调整配置即可：

<!-- Spring Data JPA -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- PostgreSQL驱动 -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<!-- 用于支持PostgreSQL JSON/数组类型映射 -->
<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-60</artifactId>
    <version>3.7.0</version>
</dependency>

如果使用Hibernate 5.x版本，对应选择适配的hypersistence-utils版本即可。

步骤2：数据库配置补充&表结构准备 #

在已有的application.properties基础上补充2项配置，适配PostgreSQL特殊类型：

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
# 不需要手动建表可以加这行让JPA自动生成
spring.jpa.hibernate.ddl-auto=update

手动建表参考语句：

CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_designation VARCHAR(100),
    department VARCHAR(100),
    salary NUMERIC(10,2),
    direct_reports TEXT[],
    address JSONB NOT NULL
);

步骤3：编写JPA实体类 #

创建Employee.java和数据库表字段做映射：

import io.hypersistence.utils.hibernate.type.array.ListArrayType;
import io.hypersistence.utils.hibernate.type.json.JsonBinaryType;
import jakarta.persistence.*;
import org.hibernate.annotations.Type;
import java.util.List;
import java.util.Map;

@Entity
@Table(name = "employee")
public class Employee {
    @Id
    private Integer empId;
    private String empName;
    private String empDesignation;
    private String department;
    private Double salary;

    @Type(ListArrayType.class)
    @Column(columnDefinition = "text[]")
    private List<String> directReports;

    @Type(JsonBinaryType.class)
    @Column(columnDefinition = "jsonb")
    private Map<String, String> address;

    // 自行补充空构造方法、所有字段的getter和setter
}

address字段也可以单独封装为Address实体类，映射逻辑和使用Map完全一致。

步骤4：创建JPA Repository接口 #

创建EmployeeRepository.java用于数据库操作：

import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
}

步骤5：改造启动类逻辑 #

原代码中JSON读取逻辑写在SpringApplication.run之后，无法直接获取Spring容器管理的Bean，调整为实现CommandLineRunner接口，在Spring初始化完成后执行逻辑：

import jakarta.json.Json;
import jakarta.json.JsonObject;
import jakarta.json.JsonReader;
import jakarta.json.JsonString;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.beans.factory.annotation.Autowired;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SpringBootApplication
public class SpringNestedJsonToDbApplication implements CommandLineRunner {

    @Autowired
    private EmployeeRepository employeeRepository;

    public static void main(String[] args) {
        SpringApplication.run(SpringNestedJsonToDbApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        File jsonInputFile = new File("D:/sample.json");
        try (InputStream is = new FileInputStream(jsonInputFile);
             JsonReader reader = Json.createReader(is)) {
            JsonObject empObj = reader.readObject();
            System.out.println(empObj);

            // 转换JsonObject为Employee实体
            Employee employee = new Employee();
            employee.setEmpId(empObj.getInt("emp_id"));
            employee.setEmpName(empObj.getString("emp_name"));
            employee.setEmpDesignation(empObj.getString("emp_designation"));
            employee.setDepartment(empObj.getString("department"));
            employee.setSalary((double) empObj.getInt("salary"));

            // 转换JsonArray为List<String>
            List<String> directReports = new ArrayList<>();
            empObj.getJsonArray("direct_reports")
                    .forEach(item -> directReports.add(((JsonString) item).getString()));
            employee.setDirectReports(directReports);

            // 转换address JsonObject为Map
            JsonObject addressObj = empObj.getJsonObject("address");
            Map<String, String> addressMap = new HashMap<>();
            addressMap.put("street", addressObj.getString("street"));
            addressMap.put("city", addressObj.getString("city"));
            employee.setAddress(addressMap);

            // 保存到数据库
            employeeRepository.save(employee);
            System.out.println("数据保存成功");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }
}

如果不想使用JPA，也可以直接注入JdbcTemplate编写原生插入语句实现存储，逻辑更轻量。

内容的提问来源于stack exchange，提问作者Akoram