JPA Custom Query with multiple Inner Joins

Question

I am new to spring boot and need help on custom JPA Query with multiple Inner Joins. Basically I need to convert the following SQL query into JPA Query:

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;

Below is the code structure:

FirmEntity

@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;
    }

FirmRepository

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)

CertificationCountSignalImpl

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;

You just say that you want to do it? Where is the problem? – Patrick Sep 28 '17 at 06:25 — Patrick, Sep 28 '17 at 06:25
How does your current query look like and what does it do? – Jens Schauder Sep 28 '17 at 10:04 — Jens Schauder, Sep 28 '17 at 10:04

score 3 · Answer 1 · edited Sep 29 '17 at 19:21

3

I got my query working by using the following 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);

edited Sep 29 '17 at 19:21

answered Sep 29 '17 at 19:17

M Chahal

41
1
1
4

score 1 · Answer 2 · answered Sep 29 '17 at 08:32

firs of all you can use entity configurations to declare the relations between tables and do not use JOIN word inside @query (example --Construct JPA query for a OneToMany relation), Or use join in Query but not anotation @oneToMany in Entity class,example --Hql, How to write join query between tables that has one to many relationship?

JPA Custom Query with multiple Inner Joins

2 Answers2