如何在Spring Boot与Java中实现JSON到SQL列的映射并写入MySQL

JSON数据结构 #

{
    "basecurrency": "USD",
    "data_date": "2022-07-25",
    "data": {
        "AED": 3.671159,
        "AFN": 89.81064,
        "ALL": 114.523046,
        "AMD": 409.987398,
        "ANG": 1.799784,
        // 另有100+货币汇率字段
    }
}

MySQL表结构 #

create table tblData(
   ID INT NOT NULL AUTO_INCREMENT,
   base_currency varchar(10) NOT NULL,
   data_date DATE NOT NULL,
   AED REAL(16,10) NOT NULL,
   AFN REAL(16,10) NOT NULL,
   ANG REAL(16,10) NOT NULL,
   -- 另有100+对应货币字段
   PRIMARY KEY (ID)
);

请问如何在Java和Spring Boot中实现该JSON到MySQL表的映射与数据写入？是否必须创建包含100余个字段的POJO，有没有更优的实现方式？

一、传统POJO映射方式（不推荐） #

如果采用这种方式，确实需要创建包含所有100+货币字段的实体类，配合Spring Data JPA或MyBatis完成映射：

1. 创建实体类

@Entity
@Table(name = "tblData")
public class CurrencyRate {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    @Column(name = "base_currency")
    private String baseCurrency;
    
    @Column(name = "data_date")
    private LocalDate dataDate;
    
    // 示例货币字段，需补充其余100+字段
    private BigDecimal AED;
    private BigDecimal AFN;
    private BigDecimal ANG;
    
    // 构造器、Getter、Setter方法
}

2. 数据写入
   使用Spring Data JPA时，定义JpaRepository接口后直接调用save()方法；使用MyBatis则编写对应的XML映射文件或注解SQL完成插入。

这种方式的弊端非常明显：实体类极度臃肿，新增或删除货币字段时必须同步修改实体类和表结构，维护成本极高。

二、更优的动态处理方案（推荐） #

无需创建全字段POJO，利用Jackson解析JSON结合JDBC/MyBatis的动态SQL即可实现，灵活性和维护性更强：

方案1：基于JDBC Template动态拼接SQL #

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.Date;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Service
public class CurrencyRateService {
    private final JdbcTemplate jdbcTemplate;
    private final ObjectMapper objectMapper;

    public CurrencyRateService(JdbcTemplate jdbcTemplate, ObjectMapper objectMapper) {
        this.jdbcTemplate = jdbcTemplate;
        this.objectMapper = objectMapper;
    }

    public void saveCurrencyRate(String jsonStr) throws Exception {
        JsonNode rootNode = objectMapper.readTree(jsonStr);
        // 提取基础字段
        String baseCurrency = rootNode.get("basecurrency").asText();
        LocalDate dataDate = LocalDate.parse(rootNode.get("data_date").asText());
        JsonNode dataNode = rootNode.get("data");

        // 动态拼接插入字段和占位符
        StringBuilder columns = new StringBuilder("base_currency, data_date");
        StringBuilder placeholders = new StringBuilder("?, ?");
        List<Object> params = new ArrayList<>();
        params.add(baseCurrency);
        params.add(Date.valueOf(dataDate));

        // 遍历所有货币汇率字段
        dataNode.fields().forEachRemaining(entry -> {
            String currencyCode = entry.getKey();
            BigDecimal rate = entry.getValue().decimalValue();
            columns.append(", ").append(currencyCode);
            placeholders.append(", ?");
            params.add(rate);
        });

        // 执行插入
        String sql = String.format("INSERT INTO tblData (%s) VALUES (%s)", columns, placeholders);
        jdbcTemplate.update(sql, params.toArray());
    }
}

方案2：基于MyBatis动态SQL #

1. 定义Mapper接口

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;

import java.time.LocalDate;
import java.util.Map;

public interface CurrencyRateMapper {
    @Insert("<script>" +
            "INSERT INTO tblData (base_currency, data_date" +
            "<foreach collection='rateMap.keys' item='currency' separator=','>" +
            ", #{currency}" +
            "</foreach>" +
            ") VALUES (#{baseCurrency}, #{dataDate}" +
            "<foreach collection='rateMap.values' item='rate' separator=','>" +
            ", #{rate}" +
            "</foreach>" +
            ")</script>")
    void insertCurrencyRate(@Param("baseCurrency") String baseCurrency,
                            @Param("dataDate") LocalDate dataDate,
                            @Param("rateMap") Map<String, BigDecimal> rateMap);
}

2. 业务层解析并调用

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.Map;

@Service
public class CurrencyRateService {
    private final CurrencyRateMapper currencyRateMapper;
    private final ObjectMapper objectMapper;

    public CurrencyRateService(CurrencyRateMapper currencyRateMapper, ObjectMapper objectMapper) {
        this.currencyRateMapper = currencyRateMapper;
        this.objectMapper = objectMapper;
    }

    public void saveCurrencyRate(String jsonStr) throws Exception {
        JsonNode rootNode = objectMapper.readTree(jsonStr);
        String baseCurrency = rootNode.get("basecurrency").asText();
        LocalDate dataDate = LocalDate.parse(rootNode.get("data_date").asText());
        JsonNode dataNode = rootNode.get("data");

        // 将汇率字段转为Map
        Map<String, BigDecimal> rateMap = new HashMap<>();
        dataNode.fields().forEachRemaining(entry -> 
            rateMap.put(entry.getKey(), entry.getValue().decimalValue())
        );

        // 调用Mapper插入数据
        currencyRateMapper.insertCurrencyRate(baseCurrency, dataDate, rateMap);
    }
}

方案总结 #

• 动态方案无需维护臃肿的POJO，新增或调整货币字段时，只要表结构同步更新，代码无需修改，扩展性和维护性远优于传统POJO方式。
• 若后续需要查询单个货币字段，同样可以通过动态SQL实现，无需修改实体类或Mapper接口。

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