Spring boot, How to perform conditional query on many to many relationship with bridge table?

I have three entity include bridge entity:

Team Entity:

       public class Team {
        
            @Id
            @GeneratedValue(strategy = GenerationType.IDENTITY)
            @Column(name = "TEAM_ID")
            private Integer id;
        
            @Column(name = "teamname", length = 128, nullable = false, unique = true)
            private String teamname;
            @Column(name = "delete_date", length = 128, nullable = true)
            private Date delete_date;
        
            @Column(name = "description", nullable = true, length = 240)
            private String description;
        
            @Column(name = "active", length = 64, nullable = false)
            private int active;
            @OneToMany(mappedBy = "team", fetch = FetchType.LAZY)
            private Set<TeamUsers> team_users = new HashSet<TeamUsers>();

---getter setter constructur
    }

User Entity:

   @Entity
    @Table(name = "tblUsers")
    public class User {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
        @Column(name = "Username", length = 128, nullable = false, unique = true)
        private String username;
        @Column(name = "FirstName", nullable = false, length = 45)
        private String firstName;
        @Column(name = "LastName", nullable = false, length = 45)
        private String lastName;
        @Column(name = "Password", length = 64, nullable = false)
        private String password;
        @Column(name = "Email", length = 128, nullable = false, unique = true)
        private String email;
        @Column(name = "Phone", length = 64, nullable = false, unique = true)
        private String phoneNumber;
    
    
        @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    
        private Set<TeamUsers> team_users = new HashSet<TeamUsers>();

---getter setter constructur

    }

TeamUsers – Bridge Entity with extra column(active):

@Entity
@Table(name = "team_users")

public class TeamUsers implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false, cascade = CascadeType.ALL)
    @JoinColumn(name = "TEAM_ID")
    private Team team;

    @ManyToOne(fetch = FetchType.LAZY, optional = false, cascade = CascadeType.ALL)
    @JoinColumn(name = "USER_ID")
    private User user;

    @Column(name = "active")
    private Integer active;

---getter setter constructur
}

In the Team repository I have code:

package com.crmbackend.allService.teamService.repo;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.crmbackend.entity.Team;

public interface TeamRepository extends PagingAndSortingRepository<Team, Integer> {

    @Query("select t from Team t")
    public List<Team> getAllTeamAndDetails();

}

If I call the getAllTeamAndDetails() method in Junit Test, the result is all team informations:
It basically tells me how many team I have, and team users object who belong to which team.

Now, my question is which I want to get all team information and team user information,
but only their active = 1 in the bridge table.

which means if Team User record has active = 0, then this user should not showing in the result.

How this query should be looks like or what is the best approach?

Thanks

This is not possible with the plain JPA/Hibernate or Spring Data JPA tools available. You have to use a DTO for this purpose. I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Team.class)
public interface TeamDto {
    @IdMapping
    Integer getId();
    String getDescription();
    @Mapping("team_users[active = 1].user")
    Set<UserDto> getUsers();

    @EntityView(User.class)
    interface UserDto {
        @IdMapping
        Integer getId();
        String getUsername();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

TeamDto a = entityViewManager.find(entityManager, TeamDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<TeamDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!

Leave a Comment