Spring JPA中无法使用@Query注解编写INNER JOIN查询求助

Hey there! Let's get your Spring JPA @Query working correctly. The key thing to remember is that JPQL (Java Persistence Query Language) operates on your entity classes and their relationships, not directly on database tables like raw MySQL does. Here are a few solutions tailored to your scenario:

Spring JPA is designed to leverage defined relationships between entities, so if you haven't already, setting up a proper association between BillingPlans and BillingPlansProfiles will simplify your query a lot.

For example, if one BillingPlans can have multiple BillingPlansProfiles entries, define the relationship in your entities:

// BillingPlans.java
@Entity
public class BillingPlans {
    @Id
    private Long id;
    private String planName;

    // Map to the billingPlan field in BillingPlansProfiles
    @OneToMany(mappedBy = "billingPlan")
    private List<BillingPlansProfiles> profiles;

    // Getters and setters
}

// BillingPlansProfiles.java
@Entity
public class BillingPlansProfiles {
    @Id
    private Long id;

    @ManyToOne
    @JoinColumn(name = "plan_name", referencedColumnName = "planName")
    private BillingPlans billingPlan;

    // Other fields, getters and setters
}

With this relationship in place, your JPQL query becomes much cleaner—no need to manually write the ON clause, since JPA already knows how the entities are connected:

@Query("SELECT DISTINCT t1 FROM BillingPlans t1 JOIN t1.profiles t2")
List<BillingPlans> findAllPlansWithProfiles();

The DISTINCT keyword ensures you don't get duplicate BillingPlans entries from the join.

If you don't want to define an entity relationship (or can't), JPA 2.1 and later support explicit JOIN ... ON syntax in JPQL. Just make sure you use entity property names (not database column names) in the condition:

@Query("SELECT DISTINCT t1 FROM BillingPlans t1 JOIN BillingPlansProfiles t2 ON t1.planName = t2.planName")
List<BillingPlans> findAllJoinedPlans();

Double-check that your entity properties match what you're using here (e.g., if BillingPlansProfiles uses planName as the property name corresponding to the database's plan_name column).

If you want to reuse your already working MySQL query verbatim, you can run it as a native query by adding nativeQuery = true:

@Query(value = "SELECT distinct t1.* FROM billing_plans as t1 INNER JOIN billing_plans_profiles as t2 ON t1.planName = t2.plan_name", nativeQuery = true)
List<BillingPlans> findAllPlansWithNativeQuery();

This is a quick fix, but note that it ties your query to MySQL and loses the database-agnostic benefit of JPQL.

• Always use DISTINCT with joins like this—otherwise, you'll get duplicate BillingPlans entries for every matching profile.
• If you're using an older JPA version (pre-2.1), the JOIN ... ON syntax isn't supported. Instead, use a cross join with a WHERE clause:

  @Query("SELECT DISTINCT t1 FROM BillingPlans t1, BillingPlansProfiles t2 WHERE t1.planName = t2.planName")
  

• Ensure your entity properties are correctly mapped to database columns (use @Column(name = "plan_name") if needed to explicitly link them).

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