[FIXED] Passing parameters into @Query annotation

Issue

I am trying to use @Query annotation with parameters but I can't find a lot of examples online and I am keep getting an error:

Could not locate named parameter [port], expecting one of [port%, user.id%]; nested exception is java.lang.IllegalArgumentException: Could not locate named parameter [port], expecting one of [port%, user.id%]

My implementation:

@Query(value = "UPDATE Port SET active=FALSE, port_id=%:user.id%, expiration=NOW()+ INTERVAL 5 MINUTE  WHERE port=%:port%", nativeQuery = true)
void setInUse(@Param("user") User user, @Param("port") int port);

Solution

This is actually fairly documented. Baeldung has an article about it that you can find by googling "@Query JPA". This article describes positional and named parameters.

Positional

@Query("SELECT e FROM Entity e WHERE e.property = ?1")
Entity getElement(Integer param);

@Query("SELECT * FROM entity WHERE property = ?1", nativeQuery = true)
Entity getElementNative(Integer param);

Named

@Query("SELECT e FROM Entity e WHERE e.property = :param ")
Entity getElement(@Param("param") Integer param);

@Query("SELECT * FROM Entity e WHERE property = :param ", nativeQuery = true)
Entity getElement(@Param("param") Integer param);

There is another way that allows you to access properties of an object, like an array, a map or even another entity or POJO by using SpEL.

/* positional */
@Query("SELECT e FROM Entity e WHERE e.property = ?#{[0].property} ")
Entity getElement(OtherEntity param);

@Query("SELECT * FROM entity WHERE property = ?#{[0].property} ", nativeQuery = true)
Entity getElement(OtherEntity param);


/* named */
@Query("SELECT e FROM Entity e WHERE e.property = :#{#param.property}")
Entity getElement(@Param("param") OtherEntity param);

@Query("SELECT * FROM entity WHERE property = :#{#param.property} ", nativeQuery = true)
Entity getElement(@Param("param") OtherEntity param);

/*  SpEL, works in native and JPQL queryies */

/* for arrays */
@Query("SELECT e FROM Entity e WHERE e.property = ?#{[0][0]} ")
Entity getElement(Integer[] param);

@Query("SELECT e FROM Entity e WHERE e.property = :#{#param[0]}")
Entity getElement(@Param("param") Integer[] param);

/* for maps */
@Query("SELECT e FROM Entity e WHERE e.property = ?#{[0]['property']} ")
Entity getElement(Map<String, Object> param);

@Query("SELECT e FROM Entity e WHERE e.property = :#{#param['property']}")
Entity getElement(@Param("param") Map<String, Object> param);

Answered By - Jetto Martínez
Answer Checked By - Senaida (JavaFixing Volunteer)