Spring Boot JPA报错：无法提取位置`4`的JDBC值，求解决方案

错误现象 #

从数据库读取所有Measurement数据并转为JSON时，抛出以下错误：

2023-03-15T22:42:16.243+03:00 ERROR 952 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.orm.jpa.JpaSystemException: Unable to extract JDBC value for position 4] with root cause

org.postgresql.util.PSQLException: Bad value for type int : RainSensor

问题根源 #

错误核心是数据库中measurement表的sensor列存储了字符串值（如"RainSensor"），但JPA映射时期望该列是整数类型（关联Sensor实体的主键id），导致类型转换失败。

解决方案 #

1. 检查并修正数据库数据
   查看measurement表的sensor列，将所有非整数的字符串值替换为对应Sensor实体的id（整数）。

2. 修复数据新增逻辑
   当前MeasurementController的addMeasurement方法直接接收Measurement对象，若前端传入的是传感器名称而非ID，会导致JPA错误地将名称存入外键列。需修改新增逻辑：

   • 先通过传感器名称查询对应的Sensor实体
   • 将查询到的Sensor设置到Measurement对象中，再执行保存操作

3. 确认实体映射正确性
   确保Measurement类的@JoinColumn(name="sensor")关联的是Sensor类的主键id，且数据库中measurement.sensor列类型为整数，与Sensor.id类型一致。

相关代码 #

MeasurementDTO #

public class MeasurementDTO {
    private float value;
    private boolean raining;
    @JsonBackReference
    private Sensor sensor;

    public MeasurementDTO() {}

    public MeasurementDTO(float value, boolean raining) {
        this.value = value;
        this.raining = raining;
    }

    public float getValue() {
        return value;
    }

    public void setValue(float value) {
        this.value = value;
    }

    public boolean isRaining() {
        return raining;
    }

    public void setRaining(boolean raining) {
        this.raining = raining;
    }

    @JsonBackReference
    public Sensor getSensor() {
        return sensor;
    }

    public void setSensor(Sensor sensor) {
        this.sensor = sensor;
    }
}

Measurement 实体 #

@Entity
@Table(name = "measurement")
public class Measurement implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;
    @Column(name = "value")
    private float value;

    @Column(name="raining")
    private boolean raining;
    @JsonBackReference
    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name="sensor")
    private Sensor sensor;

    @Column(name = "current")
    private LocalDateTime current;

    public Measurement() {}

    public Measurement(float value, boolean raining) {
        this.value = value;
        this.raining = raining;
    }

    public LocalDateTime getCurrent() {return current;}

    public void setCurrent(LocalDateTime current) {this.current = current;}

    public float getValue() {
        return value;
    }

    public void setValue(float value) {
        this.value = value;
    }

    public boolean isRaining() {
        return raining;
    }

    public void setRaining(boolean raining) {
        this.raining = raining;
    }

    @JsonBackReference
    public Sensor getSensor() {
        return sensor;
    }

    public void setSensor(Sensor sensor) {
        this.sensor = sensor;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getId() {
        return id;
    }
}

SensorDTO #

public class SensorDTO {

    @NotEmpty(message = "Sensor's name can't be empty")
    @Size(min = 3,max = 250,message = "Amount of characters have to be between 3 and 250")
    @NaturalId
    private String name;

    public SensorDTO(String name) {
        this.name = name;
    }

    public SensorDTO() {}

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Sensor 实体 #

@Entity
@Table(name = "Sensor")
public class Sensor implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @NotEmpty(message = "Sensor's name can't be empty")
    @Size(min = 3,max = 250,message = "Amount of characters have to be between 3 and 250")
    @Column(name = "name")
    @NaturalId
    private String name;

    @JsonManagedReference
    @OneToMany(mappedBy = "sensor", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private List<Measurement> measurements;


    @Column(name = "created_at")
    private LocalDateTime created_at;

    @Column(name = "updated_at")
    private LocalDateTime updated_at;

    public Sensor() {}

    public Sensor(String name) {
        this.name = name;
    }
}

MeasurementController #

@RestController
@RequestMapping("/measurements")
public class MeasurementController {

    private final MeasurementService measurementService;
    private final ModelMapper modelMapper;

    @Autowired
    public MeasurementController(MeasurementService measurementService, ModelMapper modelMapper){
        this.measurementService = measurementService;
        this.modelMapper = modelMapper;
    }

    @GetMapping
    private List<MeasurementDTO> getMeasurements(){
        return measurementService.getAllMeasurements()
                .stream()
                .map(this::convertToMeasurementDTO)
                .collect(Collectors.toList());
    }

    @GetMapping("/{sensor}")
    private MeasurementDTO getMeasurement(@PathVariable String sensor){
        Measurement foundMeas = measurementService.getMeasurement(sensor);
        return convertToMeasurementDTO(foundMeas);
    }

    @RequestMapping("/add")
    public ResponseEntity<HttpStatus> addMeasurement(@RequestBody @Valid Measurement measurement,
                                                     BindingResult bindingResult){
        measurementService.addMeasurement(measurement);
        return ResponseEntity.ok(HttpStatus.OK);
    }

    private Measurement convertToMeasurement(MeasurementDTO measurementDTO){
        return modelMapper.map(measurementDTO, Measurement.class);
    }

    private MeasurementDTO convertToMeasurementDTO(Measurement measurement){
        return modelMapper.map(measurement, MeasurementDTO.class);
    }
}

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