如何在关联查询时隐藏Engines表cardid字段且规避@JsonIgnore局限性？

一、先解决当前的异常问题 #

异常原因：你的原生SQL查询没有返回cardid字段，但Engines实体类中存在关联BoatCards的cardid属性，JPA在将查询结果映射到Engines对象时，找不到对应的cardid列，因此抛出Unable to find column position by name: cardid错误。

异常快速修复（配合后续需求） #

直接修改原生SQL会引入不需要的cardid字段，更合理的方式是使用投影替代实体映射，既解决异常，又满足字段排除需求，具体实现看下方方案。

二、不使用@JsonIgnore实现排除cardid字段的需求 #

因为需要在不同场景下控制cardid的显示（单独查询Engines时保留，合并到BoatCardsDto时隐藏），不能在实体类上加全局的@JsonIgnore，推荐以下两种方案：

方案1：Spring Data 接口式投影 #

创建一个只包含需要暴露字段的投影接口：

public interface EnginesProjection {
    Integer getEngid();
    String getEngname();
    String getEngvin();
    String getEngpwr();
    Timestamp getRecdate();
}

修改DAO层的查询方法，返回该投影类型：

@Repository
public interface EnginesDao extends JpaRepository<Engines, Integer> {
    List<Engines> findAllByCardidCardid(Integer cardid);

    @Query(value = "SELECT engid, engname, engvin, engpwr, recdate FROM gims.engines  where cardid = :cardid",
            nativeQuery = true)
    List<EnginesProjection> findAllByCardidQuery(@Param("cardid") Integer cardid);
}

同步修改Service层和BoatCardsDto的类型：

// Service层
@Service
public class EnginesServiceImpl implements EnginesService{
    @Autowired
    private EnginesDao enginesDao;

    @Override
    public List<EnginesProjection> getAllByCardidQuery(Integer id) {
        return enginesDao.findAllByCardidQuery(id);
    }
}

// BoatCardsDto修改
@JsonIgnoreProperties(ignoreUnknown = true)
public class BoatCardsDto extends BoatCards implements Serializable {
    private List<EnginesProjection> engines;

    public BoatCardsDto(BoatCards boatCards, List<EnginesProjection> engines) {
        this.cardid = boatCards.getCardid();
        this.regNum = boatCards.getRegNum();
        this.tiketNum = boatCards.getTiketNum();
        this.boatName = boatCards.getBoatName();
        this.boatType = boatCards.getBoatType();
        this.boatYear = boatCards.getBoatYear();
        this.boatVin = boatCards.getBoatVin();
        this.parkingPlace = boatCards.getParkingPlace();
        this.saCategory = boatCards.getSaCategory();
        this.boatLength = boatCards.getBoatLength();
        this.boatWidth = boatCards.getBoatWidth();
        this.boatHeight = boatCards.getBoatHeight();
        this.bodyMaterial = boatCards.getBodyMaterial();
        this.boatPayload = boatCards.getBoatPayload();
        this.passengersNum = boatCards.getPassengersNum();
        this.serviceLife = boatCards.getServiceLife();
        this.engineNum = boatCards.getEngineNum();
        this.owner = boatCards.getOwner();
        this.agent = boatCards.getAgent();
        this.note = boatCards.getNote();
        this.engines = engines;
    }

    public List<EnginesProjection> getEngines() {
        return engines;
    }

    public void setEngines(List<EnginesProjection> engines) {
        this.engines = engines;
    }
}

这种方式的优势：单独查询Engines时依然可以返回完整实体（包含cardid），合并到DTO时返回投影自动排除cardid。

方案2：自定义DTO类投影 #

如果需要更灵活的字段控制，创建独立的EnginesDto：

@Data
public class EnginesDto implements Serializable {
    private Integer engid;
    private String engname;
    private String engvin;
    private String engpwr;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss.SSS")
    private Timestamp recdate;

    public EnginesDto(Integer engid, String engname, String engvin, String engpwr, Timestamp recdate) {
        this.engid = engid;
        this.engname = engname;
        this.engvin = engvin;
        this.engpwr = engpwr;
        this.recdate = recdate;
    }
}

修改DAO层使用JPQL查询，直接映射到自定义DTO：

@Repository
public interface EnginesDao extends JpaRepository<Engines, Integer> {
    List<Engines> findAllByCardidCardid(Integer cardid);

    @Query(value = "SELECT new com.yourpackage.dto.EnginesDto(e.engid, e.engname, e.engvin, e.engpwr, e.recdate) FROM Engines e where e.cardid.cardid = :cardid")
    List<EnginesDto> findAllByCardidQuery(@Param("cardid") Integer cardid);
}

后续Service和BoatCardsDto同步修改为使用EnginesDto类型即可。

三、核心思路总结 #

• 异常解决本质：让查询返回的字段与映射对象的属性完全匹配，投影是最合理的实现方式。
• 场景区分：通过投影（接口或类）实现不同查询场景下的字段展示控制，避免全局注解影响其他业务。

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