Spring Boot单向One-To-Many关联查询实现及原生SQL报错解决

1. 现有配置下优先使用JPQL查询（无需修改实体、无需关心中间表） #

你当前的实体配置已经在Compania中定义了office关联属性，直接用JPQL查询即可，Hibernate会自动关联中间表生成正确的SQL，完全不需要手动写原生SQL处理中间表逻辑：

// 写在CompaniaRepository或者OfficeRepository中都可以
@Query("select o from Compania c join c.office o where c.id = :companiaId and lower(o.name) like lower(concat('%', :officeName, '%')) order by o.idRef asc")
List<Office> findMatchOffices(@Param("companiaId") Long companiaId, @Param("officeName") String officeName);

甚至可以直接用Spring Data JPA的命名查询，无需手写JPQL：

// 写在CompaniaRepository中
List<Office> findByIdAndOffice_NameContainingIgnoreCaseOrderByOffice_IdRefAsc(Long companiaId, String officeName);

2. 原生SQL报错的原因 #

你遇到的SQLGrammarException是SQL语法错误，核心是字段名不匹配：

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

你的Office实体类中idRef字段的@Column(name = "idRef")，数据库Office表的排序字段实际是idRef，但你的SQL中写的是o.id_ref，下划线命名和实际字段名不符导致语法报错。另外你SQL中匹配的是title字段，和需求中要匹配officeName的逻辑不一致，模糊查询也没有加%通配符，相当于精确匹配。
修正后的原生查询如下：

@Query(
    value = "select o.* from office o " +
        "inner join compania_office co on o.id = co.office_id " +
        "inner join compania c on c.id = co.compania_id " +
        "where c.id = :companiaId and lower(o.name) like lower(concat('%', :officeName, '%')) " +
        "order by o.\"idRef\" asc;",
    nativeQuery = true
)
List<Office> findOfficesByNameLike(@Param("companiaId")Long companiaId, @Param("officeName") String officeName);

3. 实体映射优化建议 #

你当前的@OneToMany配置存在矛盾：你加了mappedBy = "compania"代表这是双向关联，由Office类的compania属性维护关联，但你的Office类中并没有定义Compania类型的compania字段，所以Hibernate才自动生成了中间表COMPANIA_OFFICE。如果你不需要中间表，可以调整关联配置：

方案A：改成单向OneToMany无需中间表 #

修改Compania的office属性配置：

@OneToMany(
    cascade = CascadeType.ALL,
    fetch = FetchType.LAZY,
    orphanRemoval = true
)
@JoinColumn(name = "compania_id") // 会在Office表新增compania_id外键字段，无需中间表
private List<Office> office;

方案B：改成双向OneToMany #

在Office类中新增关联属性：

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "compania_id")
private Compania compania;

两种方案都可以去掉中间表，后续查询会更简单。

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