What are some ways to pass an Enum value in a pure JPA @Query?

Question

Let's say I want to set a record's column value as "RESOLVED". However, I want to have this value to picked up from an Enum. So the query stays the same regardless if somebody decides the "RESOLVED" to change to "RESOLVE" or something like that. In other words, the developer would not need to edit the JPA query with the new Enum value. Just wanted to know what are some ways to go about it, or if JPA doesn't allow that. I know queries are static but earlier I was able to pass an object inside a query to store the query result fields in it. So I wondered if one could do the same with Enums too.

This is my current strategy: To pass the "RESOLVE" as a @Param "status". However, I'd still like to know how to pass Enums as this feels like a work-around more than answering the question itself.

public interface UpdateStatusStandardDAO extends JpaRepository<DBTableName, Long>
{

    @Transactional
    @Modifying(clearAutomatically = true)
    @Query("UPDATE"
            + " TableName tn"
            + " SET"
            + " tn.status =:status"
            + " WHERE"
            + " tn.field1 =:field1"
            + " AND tn.field2 =:field2"
            + " AND tn.field3 =:field3")
    int updateStatus(@Param("status") String status, @Param("field1") Long field1,
                     @Param("field2") String field2, @Param("field3") String field3);

}

What I know: 1. @Transactional: for update/delete ops. Otherwise, I got org.springframework.dao.InvalidDataAccessApiUsageException. 2. @Modifying(clearAutomatically = true): Because the entity manager does not flush change automagically by default.

For simplicity, we consider field1 as primary key. It's type is Long (can be inferred also from the interface signature).

For pure reference, if none of the fields in the where clause is a key, then SQL workbench might reject your query if the SQL_SAFE_UPDATES options is set. That is because you can't update or delete records without specifying a key (example, primary key) in the where clause.

Answer 1

That's not possible because annotation attribute value must be constant and Enum is not a constant. But you can use java8 interface default method as a trick to solve it. The template code:

public interface TheEntityJpaRepository extends JpaRepository<TheEntity, Long> {
  @Modifying(clearAutomatically = true)
  @Query("UPDATE TheEntity SET status = :status WHERE id = :value")
  int markToStatus(@Param("value") Long value, @Param("status") TheStatusEnum status);

  default int markAsResolved(Long value) {
    return markToStatus(value, TheStatusEnum.RESOLVED);
  }
}

@Entity
public class YourEntity {
  ...
  @Enumerated(EnumType.STRING)
  private Status status;
  ...
}

public enum TheStatusEnum {
  RESOLVED,
  ...
}

But you should know that this way will exposed the markToStatus method.

Answer 2

try this

@Modifying 
@Query(value = "update table set status=:#{#status.toString()} where id = :id", nativeQuery = true) 
void reset(@Param("id") Long id, @Param("status") TaskStatusEnum status);

Answer 3

you can follow the below process and come out to a solution.

//ENUM structure

@Getter
public enum Status {
    RESOLVED(1, "resolved"),
    NOT_RESOLVED(2, "not reolved");

    private Integer id;
    private String name;


    Status(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    private static Status getById(Integer id) {
        for (Status type : Status.values()) {
            if (type.getId() == id) {
                return type;
            }
        }
        return null;
    }

    @Converter(autoApply = true)
    public static class TypeConverter implements AttributeConverter<Status, Integer> {
        @Override
        public Integer convertToDatabaseColumn(Status type) {
            return type != null ? type.getId() : null;
        }

        @Override
        public Status convertToEntityAttribute(Integer id) {
            return id != null ? Status.getById(id) : null;
        }
    }
}

//Entity Structure

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@Builder(builderClassName = "Builder", toBuilder = true)
@Entity
@Table(name = "something")
public class Something   {

    private static final long serialVersionUID = 1696981698111303897L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, updatable = false)
    protected Long id;

    @Column(name = "status_id")
    @Convert(converter = Status.TypeConverter.class)
    private Status status;

}

//Service method

List<Something> findByIdAndStatus(Long id, Status status);

//impl method

@Override
public List<Something> findByIdAndStatus(Long id, Status status) {
    return somthingRepository.findByIdAndStatus(id, status);
}

//repository method

 List<Something> findByIdAndStatus(Long id, Status status);

//now call it like this.Note you are passing the enum value here.In this way you can pass any enum value to your JPA query or repository method.

List<Something> yourResult = somethingService.findByIdAndStatus(id,
                Status.RESOLVED);

Answer 4

Try using cast when enum declared in db also

@Modifying 
@Query(value = "update table set status=cast(:#{#status.toString()} as task_status) where id = :id", nativeQuery = true) 
void reset(@Param("id") Long id, @Param("status") TaskStatusEnum status);