I can't work out how to check whether or not my User has a certain Role in my database. Specifically, I want to run a count query - and would like to avoid doing processing outside of the database.
Our code base uses org.springframework.data.repository.CrudRepository - and therefore we use @Query to specify queries that are complex.
(org.springframework.data.jpa.repository.Query)
This SQL query returns what I would like :
SELECT Count(*)
FROM user
where id in (
select user_id
from user_role
where role_type = 'ROLE_USER'
);
But I can't get a @Query to return what I would like.
Here's some code:
User class:
@Entity
@ApiModel(description = "Represents an user of the system")
public class User implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name = "ID")
@ApiModelProperty(value = "The ID of the user", required = true)
private Long id;
@Column(name = "USERNAME", unique = true)
@ApiModelProperty(value = "The userName of the user", required = true)
private String username;
@Column(name = "STATUS", nullable=false)
@Enumerated(EnumType.STRING)
@ApiModelProperty(value = "The status of the user", required = true)
private UserStatus status;
@Column(name = "PASSWORD")
@ApiModelProperty(value = "The encrypted password of the user")
private String password;
@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
@JoinColumn(name = "USER_ID", nullable=false)
@ApiModelProperty(value = "The role of the user", required = true)
private Set<UserRole> userRoles;
}
UserRole Class:
@Entity
public class UserRole implements Serializable {
@Id
@Column(name = "ID")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(name = "ROLE_TYPE")
@Enumerated(EnumType.STRING)
private UserRoleType roleType;
@ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinColumn(name = "USER_ID", nullable=false, insertable=false, updatable=false)
@ApiModelProperty(value = "The id the user (link to User table)", required = true)
private User user;
}
And the repository Class :
//This bit doesn't work - but is here to show the intention of what I want to do!
public interface UserDao extends CrudRepository<User, Long> {
@Query("select count(u) from User u where u.status='ACTIVE' and u.userRoles contains 'ROLE_USER')
public long countAllActiveUser();
}
The database is very simple containing the User table and the User_Role table.
The User table has Id, Username, Password, Status columns.
e.g.
- 1, Dave, ****, ACTIVE
- 2, John, ****, ACTIVE
The User_Role table has Id, Role_Type, User_Id columns.
e.g.
- 1, ROLE_USER, 1
- 2, ROLE_ADMIN, 1
- 3, ROLE_USER, 2
In the above example - the answer to the count SQL is 2! (There are 2 users who are ACTIVE and has the role: ROLE_USER)
