Spring @Query annotation with enum parameter

Question

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

}

Back linking with similar question: stackoverflow.com/q/44460394/906265 — Ivar
– Ivar, Commented Jan 21, 2020 at 12:00

Aliaksei Vinahradau · Accepted Answer · 2019-02-12 12:26:15Z

10

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

answered Feb 12, 2019 at 12:26

Aliaksei Vinahradau

1,37514 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Philippe Gioseffi Over a year ago

What if I need a list of roles?

nobeh · Accepted Answer · 2013-06-21 19:35:10Z

3

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.

answered Jun 21, 2013 at 19:35

nobeh

10.1k10 gold badges53 silver badges68 bronze badges

3 Comments

perak Over a year ago

Tried this without any success. After enabling more specific logging I got this message: 09:20:28.953 [main] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARBINARY] - ROLE_USER. Does it bind parameter as wrong type?

frandevel Over a year ago

Maybe try to use a "String" param in the repository method, instead of the Enum directly. If you use native SQL in the query, try to use "like"

mvmn Over a year ago

This question is not about query results. The question is about parameter supplied to the query!

ipodtouch0218 · Accepted Answer · 2024-08-15 19:09:35Z

0

Just add .name at end of your enum value like:

Example: #{#MyEnum.TEST.name}

In your case it should look like:

@Query("SELECT r FROM Role r WHERE LOWER(r.role) = LOWER(:#{#role.MyEnumValue.name})")
    public Role findByRole(@Param("role") Roles role);

edited Aug 15, 2024 at 19:09

ipodtouch0218

3,4259 gold badges17 silver badges31 bronze badges

answered Aug 12, 2024 at 1:31

Feno

1

Collectives™ on Stack Overflow

Spring @Query annotation with enum parameter

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related