Spring Data JPA 用于多个关联表的查询

Question

我有两个表：ProductUsage和Learner。ProductUsage有字段Learner，Learner有字段id和guid。现在我需要创建一个查询来提取所有学习者guid在指定用户id中的productUsage：

SQL：

select * from product_usage 
inner join learner
on product_usage.learner_id = learner.id
where 
learner.guid in ("1234", "2345")

领域类：

@Data
@NoArgsConstructor
@Entity
@Table(name = "core_product_usage_increments")
public class ProductUsage {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @ManyToOne
    @JoinColumn(name = "learner_id", nullable = false)
    private Learner learner;

    @ManyToOne
    @JoinColumn(name = "learning_language_id", nullable = false)
    private Language language;
}

@Data
@NoArgsConstructor
@Entity
@Table(name = "learners")
public class Learner {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "user_guid", nullable = false, unique = true)
    private String guid;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;
}

和仓库类：

@Repository
public interface ProductUsageRepository extends CrudRepository<ProductUsage, Integer> {
    @Query("SELECT p FROM ProductUsage p WHERE p.learnerGuid = :learnerGuid")
    List<ProductUsage> findByLearnerGuid(String learnerGuid);
}

调用仓库的客户端类。

@Component
public class MyClient {
    @Autowired
    private ProductUsageRepository repository;

    public MyClient(ProductUsageRepository repository) {
        this.repository = repository;
    }

    public List<ProductUsage> getProductUageByLeanrerGuid(String learnerGuid) {
        return repository.findByLearnerGuid(learnerGuid);
    }
}

我的测试：

@Test
    public void testClient() throws Exception {
        MyClient client = new MyClient(repository);
        List<ProductUsage> results = client.getProductUageByLeanrerGuid("1234");
        assertNotNull(result);
    }

它失败了：

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: learnerGuid of: com.acme.domain.spectrum.ProductUsage [SELECT p FROM com.acme.domain.spectrum.ProductUsage p WHERE p.learnerGuid = :learnerGuid]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1364)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300)
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:294)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)

它无法识别ProductUsage中的“learnerGuid”字段，但实际上该字段在Learner类中定义。我该如何执行连接多个表的查询？

- user468587

Answer 1

ProductUsage没有learnerGuid属性，只有learner。请尝试：

@Query("SELECT p FROM ProductUsage p WHERE p.learner.guid = :learnerGuid")

如果这不起作用，我还有另一个提示：

@Query("SELECT p FROM ProductUsage p join p.Learner l WHERE l.guid = :learnerGuid")

- Arthur

Answer 2

你不需要像之前那样使用@query

@Query("SELECT p FROM ProductUsage p WHERE p.learnerGuid = :learnerGuid")
    List<ProductUsage> findByLearnerGuid(String learnerGuid);

Spring JPA框架可以通过方法名本身构建查询。试试这个。

List<ProductUsage> findByLearnerGuid(String learnerGuid);

或者

List<ProductUsage> findByLearner_guid(String learnerGuid);

由于您从ProductUsage与学习者有关系，findBy方法可以遍历相关表及其字段。"_"清楚地告诉框架通过连接Learner表进行查询，其中guid =？

否则，框架尝试以下两种组合：

where learnerGuid=?
join learner where guid=?

- Shekar