Column type not valid with NATIVE query jpql

Question

this is my repository:

@Repository
public interface EventRepository extends JpaRepository<Events, Long>, JpaSpecificationExecutor<Events>{

    @Query(value=" SELECT DISTINCT event.EVENT_ID "
                + "FROM EVENTS event "
                + "JOIN EVENTS event2 "
                + "ON event.EVENT_ID = event2.PARENT_ID "
                + "WHERE event.ENTITY_ID IN (?1)", nativeQuery = true)
    List<Long> getDescendantEventIdInEntityId(Set<Long> descendantEntities);
}

Why when this method is called, it's return: Internal Exception: java.sql.SQLException: Tipo di colonna non valido Error Code: 17004 Call: SELECT DISTINCT event.EVENT_ID FROM EVENTS event JOIN EVENTS event2 ON event.EVENT_ID = event2.PARENT_ID WHERE event.ENTITY_ID IN (?) bind => [[1]]

what is wrong with the query? If the query is ok, is that "bind => [[1]]" the true problem? If yes, why there's too many square brackets?

I've also tried this solution:

@Query(value=" SELECT DISTINCT event2.EVENT_ID "
                + "FROM EVENTS event "
                + "JOIN EVENTS event2 "
                + "ON event.EVENT_ID = event2.PARENT_ID "
                + "WHERE event.ENTITY_ID IN :entitiesId", nativeQuery = true)
    List<Long> getDescendantEventIdInEntityId(@Param("entitiesId") Set<Long> entitiesId);

but it return me another error: Missing IN or OUT parameter at index:: 1 Error Code: 17041 Call: SELECT DISTINCT event2.EVENT_ID FROM EVENTS event JOIN EVENTS event2 ON event.EVENT_ID = event2.PARENT_ID WHERE event.ENTITY_ID IN :entitiesId

SOLUTION: Solved creating a new nativeQuery from EntityManager:

em.createNativeQuery(" SELECT DISTINCT event2.EVENT_ID "
                                        + "FROM EVENTS event "
                                        + "JOIN EVENTS event2 "
                                        + "ON event.EVENT_ID = event2.PARENT_ID "
                                        + "WHERE event.ENTITY_ID IN ("+idList+") AND event.STATUS = 2")
                                          .getResultList();

Where idList is a String made by StringUtils.join(CollectionOfId, ",");

Answer 1

If Hibernate was used as JPA provider, instead of positional parameter binding (?1) you could also use named parameter binding (:parameterName). This would be less error prone and you could omit the brackets and let Spring manage the syntax for your. You also would not need to parse anything on your own.

Use named parameter binding like:

@Query(value="SELECT DISTINCT event2.EVENT_ID "
        + "FROM EVENTS event "
        + "JOIN EVENTS event2 "
        + "ON event.EVENT_ID = event2.PARENT_ID "
        + "WHERE event.ENTITY_ID IN :entityIds", nativeQuery = true)
List<Long> getDescendantEventIdInEntityId(@Param("entityIds") Set<Long> ids);

Be aware of passing null or an empty set to the method, in both cases! That could cause unpredictable results.

Sadly, named parameters are not a JPA standard and maybe EclipseLink does not support it (in this way). Then you have to go with positional parameters.