Error mapping a query to a DTO

Hibernate ORM
1

hello i am using spring data jpa, hibernate and postgres. I have a query that filters and I want to save this result in a DTO.
this is the entity I filter: Professor

@Entity
@NamedNativeQuery(name = "Professor.findByFullName",
        query = "select id, name, second_name, last_name from professor_filter(:term)",
        resultSetMapping = "Mapping.ProfessorDto"
)
@SqlResultSetMapping(name = "Mapping.ProfessorDto",
        classes = @ConstructorResult(targetClass = ProfessorDto.class,
                columns = {
                        @ColumnResult(name = "id", type = Long.class),
                        @ColumnResult(name = "name"),
                        @ColumnResult(name = "second_name"),
                        @ColumnResult(name = "last_name")
                }))
public class Professor {
    //attributes and methods
}

this is the DTO

/**
 * A DTO for the {@link Professor} entity
 */

public final class ProfessorDto implements Serializable {

    private final Long id;
    private final String name;
    private final String secondName;
    private final String lastName;

    public ProfessorDto(Long id, String name, String second_name, String last_name) {
        this.id = id;
        this.name = name;
        this.secondName = second_name;
        this.lastName = last_name;
    }
    //other methods
}

and this is the repository

public interface ProfessorRepository extends JpaRepository<Professor, Long> {
    @Query(countQuery = "select count(id) from professor_filter(:term)",
            nativeQuery = true)
    Page<ProfessorDto> findByFullName(@Param("term") String fullName, Pageable pageable);
}

the function professor_filter

CREATE OR REPLACE FUNCTION public.professor_filter(text_filter text)
    RETURNS TABLE(id bigint, name character varying, second_name character varying, last_name character varying) 
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
select  pr.id, name, second_name, last_name
from person p
         inner join professor pr on p.id = pr.person_id where concat(name, ' ', second_name, ' ', last_name) ilike concat('%',$1, '%'); 
$BODY$;

ALTER FUNCTION public.professor_filter(text)
    OWNER TO postgres;

When I try to run this I get the following error

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: professor_filter near line 1, column 46 [select id, name, second_name, last_name from professor_filter(:term)]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: professor_filter near line 1, column 46 [select id, name, second_name, last_name from professor_filter(:term)]

see the complete logs

What is the reason for this error?
Thanks in advance

2

The exception implies that the count query is treated as HQL rather than a native query. Ask the Spring team what goes wrong in Spring Data there.

1 Like