Spring Data JPA @Query Inner Join

In Spring Data JPA, you can use the @Query annotation to define custom JPQL queries. When working with relationships between entities, you often need to use JOINs (e.g., INNER JOIN, LEFT JOIN) in your queries.

Create JPA Entities - User and Role

Suppose you have two entities, User and Role, and there's a many-to-many relationship between them:

User

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @ManyToMany
    @JoinTable(
      name = "user_role", 
      joinColumns = @JoinColumn(name = "user_id"), 
      inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;

    // getters, setters, etc.
}

Role

@Entity
public class Role {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String roleName;

    @ManyToMany(mappedBy = "roles")
    private Set<User> users;

    // getters, setters, etc.
}

Create Repository

Now, if you want to retrieve users with a specific role using a custom query with an inner join, you can do something like this:

public interface UserRepository extends JpaRepository<User, Long> {

    @Query("SELECT u FROM User u INNER JOIN u.roles r WHERE r.roleName = :roleName")
    List<User> findUsersByRoleName(@Param("roleName") String roleName);
}

In the @Query, we've used an inner join between the User entity and its roles attribute (which is a collection of Role entities). The :roleName is a named parameter that you can pass to the query method.

You can then call this method in your service or controller to get users by a specific role:

List<User> admins = userRepository.findUsersByRoleName("ADMIN");

This approach allows you to write custom queries using JPQL and leverage the power of joins, making it easy to fetch related data from the database.