Spring Boot关联查询有值但地区字段返回Null问题求助

问题原因 #

你使用原生SQL查询时，JPA仅会自动映射Sellars实体中带有@Column注解的持久化字段（如sellarName、fk_city_id）。而被@Transient标注的country、state、cityName不属于数据库持久化字段，JPA不会自动将查询结果中的对应列值赋值给这些字段，因此返回Null。

解决方案 #

方案1：使用DTO接收查询结果（推荐）

创建专门的DTO类封装返回数据，避免污染实体类结构：

// 卖家地区信息DTO
public class SellarRegionDTO {
    private Long id;
    private String sellarName;
    private String country;
    private String state;
    private String cityName;

    // 构造函数参数顺序必须与SQL查询列顺序完全一致
    public SellarRegionDTO(Long id, String sellarName, String country, String state, String cityName) {
        this.id = id;
        this.sellarName = sellarName;
        this.country = country;
        this.state = state;
        this.cityName = cityName;
    }

    // 生成getter/setter（可使用Lombok简化）
}

修改Repository查询方法：

@Repository
public interface SellarsRepository extends JpaRepository<Sellars, Long>{
      @Query(value = "SELECT u.id, u.sellarName, c.country, s.state, ci.cityName" +
      " FROM tbl_sellars u" + " JOIN tbl_city ci ON u.fk_city_id = ci.id" +
      " JOIN tbl_state s ON ci.fk_state_id = s.id" +
      " JOIN tbl_country c ON s.fk_country_id = c.id", nativeQuery = true)
      List<SellarRegionDTO> getAllSellarsWithRegion();
}

方案2：用@SqlResultSetMapping映射到实体Transient字段

如果必须使用Sellars实体接收结果，可通过定义结果集映射指定字段对应关系：

1. 在Sellars实体上添加映射配置：

@Entity(name = "tbl_sellars")
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
@SqlResultSetMapping(
    name = "SellarsWithRegionMapping",
    entities = @EntityResult(
        entityClass = Sellars.class,
        fields = {
            @FieldResult(name = "id", column = "id"),
            @FieldResult(name = "sellarName", column = "sellarName"),
            @FieldResult(name = "country", column = "country"),
            @FieldResult(name = "state", column = "state"),
            @FieldResult(name = "cityName", column = "cityName")
        }
    )
)
public class Sellars extends CommonEntity {
    // 原有字段保持不变
}

2. 修改Repository查询方法，指定使用该映射：

@Repository
public interface SellarsRepository extends JpaRepository<Sellars, Long>{
      @Query(value = "SELECT u.id, u.sellarName, c.country, s.state, ci.cityName" +
      " FROM tbl_sellars u" + " JOIN tbl_city ci ON u.fk_city_id = ci.id" +
      " JOIN tbl_state s ON ci.fk_state_id = s.id" +
      " JOIN tbl_country c ON s.fk_country_id = c.id", 
      nativeQuery = true,
      resultSetMapping = "SellarsWithRegionMapping")
      List<Sellars> getAllSellarsWithRegion();
}

方案3：添加@ManyToOne关联（可选）

若允许在Sellars中添加关联注解，可通过关联对象动态获取地区信息：

@Entity(name = "tbl_sellars")
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
public class Sellars extends CommonEntity {
    
    @Column(name = "sellarName")
    String sellarName;
    
    @ManyToOne(fetch = FetchType.LAZY) // 懒加载避免性能损耗
    @JoinColumn(name = "fk_city_id", insertable = false, updatable = false)
    private City city;

    // 通过关联对象动态返回地区信息
    @JsonProperty
    public String getCountry() {
        return city != null && city.getState() != null && city.getState().getCountry() != null 
               ? city.getState().getCountry().getCountry() : null;
    }

    @JsonProperty
    public String getState() {
        return city != null && city.getState() != null ? city.getState().getState() : null;
    }

    @JsonProperty
    public String getCityName() {
        return city != null ? city.getCityName() : null;
    }

    // 其他原有字段和方法
}

Repository使用JPQL查询关联数据：

@Repository
public interface SellarsRepository extends JpaRepository<Sellars, Long>{
      @Query("SELECT s FROM tbl_sellars s JOIN FETCH s.city c JOIN FETCH c.state st JOIN FETCH st.country")
      List<Sellars> getAllSellarsWithRegion();
}

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