Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring @Query annotation with enum parameter

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();
    }

}
like image 377
perak Avatar asked Jun 21 '13 18:06

perak


People also ask

What is the use of @enumerated annotation?

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.

What is the use of @query annotation?

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.

What is @query annotation in spring boot?

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.

Is it possible to define a sort type in @query annotation using Spring JPA?

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.


2 Answers

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);
like image 181
Алексей Виноградов Avatar answered Sep 22 '22 07:09

Алексей Виноградов


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.

like image 20
nobeh Avatar answered Sep 23 '22 07:09

nobeh