mysql - 带有投影和列表的 Spring Data JPA 查询创建无效的 SQL

Question

我正在尝试使用 JPA 查询和投影仅检索特定部分的数据。这是查询:

@Repository
public interface CompanyRepository extends CrudRepository<Company, Integer> {
    @Query("select co.companyID as companyid, co.companyName as companyname, co.companyAbbr as companyabbr, co.flags as flags from Company co order by companyName asc")
    List<CompanyWithFlags> getAllCompaniesWithFlags();
}

这是投影:

public interface CompanyWithFlags {
    Integer getCompanyid();
    String getCompanyname();
    String getCompanyabbr();
    List<CompanyFlag> getFlags();
}

这是公司实体:

@Entity
@Table(name="companies.companies")
public class Company implements Serializable {
    private static final long serialVersionUID = 1L;

    private int companyID;
    private String companyName;
    private String companyAbbr;
    ...
    private List<CompanyFlag> flags = new ArrayList<>();

    public Company() {}

    @Id
    @Column(name="pk_companyid")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @JsonView(View.AllCompaniesView.class)
    public int getCompanyID() {
        return companyID;
    }

    public void setCompanyID(int companyID) {
        this.companyID = companyID;
    }

    @Column(name="companyname", columnDefinition="VARCHAR(72)")
    @JsonView(View.AllCompaniesView.class)
    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    @Column(name="companyabbr", columnDefinition="VARCHAR(8)")
    @JsonView(View.AllCompaniesView.class)
    public String getCompanyAbbr() {
        return companyAbbr;
    }

    public void setCompanyAbbr(String companyAbbr) {
        this.companyAbbr = companyAbbr;
    }

    ...

    @ManyToMany(fetch=FetchType.LAZY)
    @JoinTable(name="companies.co_flags",
               joinColumns = @JoinColumn(name="fk_companyid", referencedColumnName="pk_companyid"),
               inverseJoinColumns = @JoinColumn(name="fk_flagid", referencedColumnName="pk_flagid"))
    @JsonView(View.AllCompaniesView.class)
    public List<CompanyFlag> getFlags() {
        return flags;
    }

    public void setFlags(List<CompanyFlag> flags) {
        this.flags = flags;
    }
}

如果我将 CompanyFlag 排除在查询和投影之外，一切正常。如果我不编写查询，而是使用:

List<CompanyWithFlags> findAllByOrderByCompanyNameAsc();

然后我得到了所需的数据，但也生成了很长的其他(不必要的)查询列表。

事实上，hibernate (5.2.14) 生成了这个查询:

select 
  company0_.pk_companyid as col_0_0_, 
  company0_.companyname as col_1_0_, 
  company0_.companyabbr as col_2_0_, 
  . as col_3_0_, 
  companyfla2_.pk_flagid as pk_flagi1_9_, 
  companyfla2_.fk_categoryid as fk_categ3_9_, 
  companyfla2_.flagname as flagname2_9_ 
from companies.companies company0_ 
inner join companies.co_flags flags1_ on company0_.pk_companyid=flags1_.fk_companyid 
inner join companies.config_flags companyfla2_ on flags1_.fk_flagid=companyfla2_.pk_flagid 
order by companyName asc

显然，问题出在。作为 col_3_0_，但我无法弄清楚为什么会生成它或如何摆脱它。任何人都可以解释为什么 hibernate 添加它以及我可以在存储库中做些什么来以有效的方式获得所需的数据？谢谢!

Answer 1

这似乎是 Spring Data JPA 中的错误 - 请参阅我的报告:DATAJPA-1299 .

要解决此问题，请尝试使用带有“distinct”的查询方法，如下所示:

@EntityGraph(attributePaths = "flags")
List<CompanyWithFlags> findDistinctAllByOrderByCompanyNameAsc();