Got some troubles with JPQL (JPA) query SELECT DISTINCT ORDERED BY-CodePudding

as in title i got some problems with query in JPQL

that's the query I'm using:

@Query(value = "SELECT DISTINCT o.idWhom FROM Message o WHERE o.idWho = ?1 ORDER BY o.date DESC")
    List<Users> allCorrespondents(Users user);

Class message:

public class Message {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @NotBlank
    private String content;

    @Temporal(TemporalType.TIMESTAMP)
    private Date date = new Date();

    boolean read = false;

    @ManyToOne
    @JoinColumn(name = "id_who")
    private Users idWho;

    @ManyToOne
    @JoinColumn(name = "id_whom")
    private Users idWhom;
}

Error I've got:

2022-06-17 13:02:22.435 ERROR 2304 --- [nio-8080-exec-2] c.e.h.S.CustomAuthorizationFilter        : 
Error logging in: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

CodePudding user response：

Although I am not able to prove it through issue tickets, I have done enough investigation to believe that this issue occurs to hibernate version previous from 5. (Example Hibernate 4!) with database being used Oracle.

What the issue seems to be is the following:

When you have written the JPQL query SELECT DISTINCT o.idWhom FROM Message o you would expect that the entities would be filtered to be distinct after the execution of the SQL query, where the mapper would filter out the distinct entities.

For some reason however in those versions I mention above what actually happens is that the distinct is passed in the SQL query and what actually executes is the following:

SELECT DISTINCT idh.id_column FROM O_TABLE o JOIN IDWHOM_TABLE idh on (o.id_foreign_key_column = idh.id_column

As you can see this select query does not return in result set all columns that are needed from mapper to build the entity but only returns the id column. Therefore the mapper is not able to extract from ResultSet all columns needed to build the entity and you get the error that you get could not extract ResultSet.

A workaround I have found, is that it is able to work when distinct is placed on some primitive field and not on an entity, so you can use this to your advantage to bypass this issue with the following approach:

This supposes that the entity Users has as id a field named id.

    @Query(value = "SELECT us FROM Users us where us.id in (SELECT DISTINCT o.idWhom.id FROM Message o WHERE o.idWho = ?1)") 
    List<Users> allCorrespondents(Users user);

I have excluded the ordering which you have in your original query since even from logical point of view seems wrong. A User entity can have multiple messages. How could you order the returned users just from created date of messages? This field is not able to order Users themselves. It could be used to order messages only.