Spring中按BoatBodyMaterial的matcode查询BoatCards列表的问题解决

需求 #

根据BoatCards实体的body_material关联字段对应的matcode值（例如matcode=2），筛选获取对应的BoatCards对象列表。

相关实体类代码 #

BoatBodyMaterial实体 #

@Entity
@Table(name = "boat_body_material")
@Data
@NoArgsConstructor
public class BoatBodyMaterial {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "matcode")
    private Integer matcode;

    @Column(name = "matname")
    private String matname;

    @Column(name = "matnote")
    private String matnote;

}

BoatCards实体 #

@Entity
@Table(name = "boat_cards")
@Data
@NoArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
public class BoatCards {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cardid")
    Long cardid;

    @Column(name = "reg_num")
    String regNum;

    @Column(name = "tiket_num")
    String tiketNum;

    @Column(name = "boat_name")
    String boatName;

    @ManyToOne
    @JoinColumn(name = "boat_type")
    BoatTypes boatType;

    @Column(name = "boat_year")
    String boatYear;

    @Column(name = "boat_vin")
    String boatVin;

    @Column(name = "parking_place")
    String parkingPlace;

    @ManyToOne
    @JoinColumn(name = "sa_category")
    SaCategory saCategory;

    @Column(name = "boat_length")
    String boatLength;

    @Column(name = "boat_width")
    String boatWidth;

    @Column(name = "boat_height")
    String boatHeight;

    @ManyToOne
    @JoinColumn(name = "body_material")
    BoatBodyMaterial bodyMaterial;

    @Column(name = "boat_payload")
    Long boatPayload;

    @Column(name = "passengers_num")
    Long passengersNum;

    @Column(name = "service_life")
    String serviceLife;

    @Column(name = "engine_num")
    Long engineNum;

    @ManyToOne
    @JoinColumn(name = "owner")
    PersonData owner;

    @ManyToOne
    @JoinColumn(name = "agent")
    PersonData agent;

    @Column(name = "note")
    String note;

}

初始实现代码 #

Dao层代码 #

public interface BoatCardsDao extends JpaRepository<BoatCards, Integer> {

    @Query(value = "SELECT * from gims.boat_body_material where matcode = 1", nativeQuery = true)
    BoatBodyMaterial findByBodyMaterial ();

    List<BoatCards> findAllByBodyMaterial(BoatBodyMaterial list);
}

Service层代码 #

public List<BoatCards> getAllByMaterial() {
    BoatBodyMaterial matcodeFromTable = boatCardsDao.findByBodyMaterial();
    List<BoatCards> boatCards = boatCardsDao.findAllByBodyMaterial(matcodeFromTable);
    return boatCards;
}

Controller层代码 #

@GetMapping(path="/get")
public List<BoatCards> get() {
    return boatCardsService.getAllByMaterial();
}

运行错误信息 #

2023-01-26T10:33:20.193+03:00 ERROR 20852 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.Object[]] to type [by.compit.gimsshd.model.BoatBodyMaterial] for value '{1, metal, produce from metal}'] with root cause

问题解决 #

修改Dao层代码，利用Spring Data JPA的派生查询特性，直接通过关联实体的字段完成筛选：

public interface BoatCardsDao extends JpaRepository<BoatCards, Integer> {
    List<BoatCards> findAllByBodyMaterialMatcode(Integer bodyMaterial);
}

错误原因说明 #

原实现中使用原生SQL查询返回的是Object[]数组，Spring无法直接将其转换为BoatBodyMaterial实体对象。而使用JPA派生查询findAllByBodyMaterialMatcode，可以直接通过关联实体bodyMaterial的matcode字段筛选数据，无需手动查询关联实体再传递参数，既简化了代码，又避免了类型转换错误。

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