Is it possible to use enum parameter with @Query annotation?
Here is the code I'm using to find user role:
Role userRole = roleRepository.findByRole(Roles.USER);
if ( userRole == null ) {
LOGGER.debug("No role found with role: {}", Roles.USER);
}
and it prints out
No role found with role: ROLE_USER
but if I try to find all roles this is what I get:
for ( Role r : roleRepository.findAll() )
LOGGER.debug("{}", r);
Role@8a8c0a[roleId=1,role=role_admin,version=0]
Role@1efe9ee[roleId=2,role=role_staff,version=0]
Role@1e70f68[roleId=3,role=role_user,version=0]
Role@a475d1[roleId=4,role=role_guest,version=0]
As you can see user role does exists.
RoleRepository:
public interface RoleRepository extends JpaRepository<Role, Long> {
@Query("SELECT r FROM Role r WHERE LOWER(r.role) = LOWER(:role)")
public Role findByRole(@Param("role") Roles role);
}
Role:
@Entity
@Table(name = "role")
public class Role {
public enum Roles {
ADMIN("ROLE_ADMIN"),
STAFF("ROLE_STAFF"),
USER("ROLE_USER"),
GUEST("ROLE_GUEST");
private String role;
private Roles(String role) {
this.role = role;
}
public String getRole() {
return role;
}
@Override
public String toString() {
return role;
}
}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "role_id", updatable = false)
private Long roleId;
@Column(name = "role")
private String role;
@Version
@Column(name = "version")
private long version = 0;
public Long getRoleId() {
return roleId;
}
public String getRole() {
return role;
}
public long getVersion() {
return version;
}
@Override
public String toString() {
return new ReflectionToStringBuilder(this).toString();
}
}
2. Using @Enumerated Annotation. The most common option to map an enum value to and from its database representation in JPA before 2.1 is to use the @Enumerated annotation. This way, we can instruct a JPA provider to convert an enum to its ordinal or String value.
Understanding the @Query Annotation The @Query annotation can only be used to annotate repository interface methods. The call of the annotated methods will trigger the execution of the statement found in it, and their usage is pretty straightforward. The @Query annotation supports both native SQL and JPQL.
The @Query annotation declares finder queries directly on repository methods. While similar @NamedQuery is used on domain classes, Spring Data JPA @Query annotation is used on Repository interface. This frees the domain classes from persistence specific information, which is a good thing.
Spring Data JPA allows you to add a special Sort parameter to your query method. The Sort class is just a specification that provides sorting options for database queries. By using dynamic sorting, you can choose the sorting column and direction at runtime to sort the query results.
Try using SpEl in Query and call toString() of Enum parameter like this:
@Query("SELECT r FROM Role r WHERE LOWER(r.role) = LOWER(:#{#role?.toString()})")
public Role findByRole(@Param("role") Roles role);
or shorter:
@Query("SELECT r FROM Role r WHERE LOWER(r.role) = LOWER(:#{#role})")
public Role findByRole(@Param("role") Roles role);
I suggest proper use of JPA enumerated types. Change the type "role" property as:
@Column(name = "role")
@Enumerated(EnumType.STRING)
private Roles role;
This should automatically fix the query result.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With