具有多个内部连接的JPA自定义查询

Question

我是spring boot新手，需要关于具有多个内部连接的自定义JPA查询的帮助。基本上，我需要将以下SQL查询转换为JPA查询：

SELECT count(uc.certifications_groups)
    FROM firms f
    INNER JOIN firms_users_map fum on fum.firm_realm_id = f.firm_realm_id
    INNER JOIN users u on u.global_auth_id = fum.global_auth_id
    INNER JOIN users_certifications uc on uc.global_auth_id = u.global_auth_id
    WHERE f.firm_realm_id = 1 and uc.certifications_groups = 'qbo'
    GROUP BY uc.certifications_groups;

下面是代码结构：

公司实体

@Entity(name = "firms")
@Table(name = "firms")
public class FirmEntity {

    @Id
    private long firmRealmId;

    @JoinColumn(name = "tier_name", nullable = false)
    private String tierName;

    @Column(nullable = false)
    private int currentPoints;

    @Column(nullable = true)
    private Date gracePeriodEnd;

    @ManyToMany
    @JoinTable(
        name = "firms_users_map",
        inverseJoinColumns = { @JoinColumn(name = "global_auth_id") },
        joinColumns = { @JoinColumn(name = "firm_realm_id") }
    )
    private Set<UserEntity> users;

    public Set<UserEntity> getUsers() {
        return users;
    }

    public void setUsers(Set<UserEntity> users) {
        this.users = users;
    }

    @Entity
    @Table(name = "users")
    public class UserEntity {

    @Id
    @Column(name="global_auth_id", nullable = false)
    private long global_auth_id;


    public long getGlobal_auth_id() {
        return global_auth_id;
    }

    public void setGlobal_auth_id(long global_auth_id) {
        this.global_auth_id = global_auth_id;
    }

    @OneToMany
    @JoinTable(
        name = "user_certifications",
        inverseJoinColumns = { @JoinColumn(name = "certification_group") },
        joinColumns = {@JoinColumn(name="global_auth_id")}
    )
    private Set<users_certificationsEntity> users_certificationsEntity;


    public Set<users_certificationsEntity> getUser_certifications() {
        return users_certificationsEntity;
    }

    public void setUser_certifications(Set<users_certificationsEntity> user_certifications) {
        this.users_certificationsEntity = user_certifications;
    }

软件库

public interface FirmRepository extends CrudRepository<FirmEntity, Long> {


 @Query("select count(c) from firms f inner join f.users c inner join 
 c.users_certificationsEntity x where f.firmRealmId = ?1 "
        + "and x.certifications_groups=qbo_adv")
int count_qbo_adv_Certification(long firmRealmId)

认证计数信号I mpl

public class CertificationCountSignalImpl implements Signal {

    @Autowired
    private FirmRepository firmRepository;

    @Autowired
    private SignalMetadataRepository signalMetadataRepository;

    @Autowired
    private CertificationCountSignalImpl certificationCountSignalImpl;


    private int get_qbo_certificationPoints(long firmRealmId) {
        if(firmRealmId <=0) {
            throw new IllegalArgumentException();
        }
        int clientCount = firmRepository.countAllClients(firmRealmId);

        int CertificationsCount_QBO_ByRealm_id = firmRepository.count_qbo_Certifications(firmRealmId);  

        int totalPoints =0; 
        int pointsCounter =0;

Answer 1

首先，您可以使用实体配置来声明表之间的关系，并且不要在@Query中使用JOIN词（例如--Construct JPA Query for a OneTo多国关系），或者在Query中使用连接，但在Entity类中不使用anotation@oneTo多国，例如--Hql，如何在具有一对多关系的表之间编写连接查询？

Answer 2

我使用以下JQL使我的查询工作：

@Query("select count(c) "
+           + "from firms f "
+           + "inner join f.users u "
+           + "inner join u.certifications c "
+           + "where f.firmRealmId = ?1 "
+           + "and c.certificationsGroups='qbo'")
+    int count_qbo_Certifications (long firmRealmId);