Fix it so count queries work properly for select new com.example.Dto(...) situations

[spring-projects-issues]

Phil Webb opened DATAJPA-1598 and commented

Originally raised in spring-projects/spring-boot#17961 but this looks like a Spring Data issue. I'll ask the OP for more info.

—

After upgrading spring boot to 2.1.7 from 2.1.6, running application are failing due to the issue:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: count near line 1, column 8 [select count(new map(model as levelTingkat)) from com.jasamedika.medifirst2000.entity.LevelTingkat model left join model.id id  where id.kdProfile=:kdProfile  and model.statusEnabled=true  ]

public interface  interface LevelTingkatDao extends CrudRepository.

Query : "select new map(model as levelTingkat) " 
            + " from LevelTingkat model left join model.id id "
            + " where id.kdProfile=:kdProfile "
            + " and model.statusEnabled=true  "

method: Page<Map<String, Object>> findAllList(
            @Param("kdProfile") Integer kdProfile,
            Pageable pageable);

In new CrudRepository, it seem, has adding "count" to Query HQL, because present of Pageable parameter.

---

0 votes, 5 watchers

[spring-projects-issues]

Andy Wilkinson commented

Here's a further code snippet that was provided on the Spring Boot issue:

 

import com.jasamedika.medifirst2000.entity.LevelTingkat;
import com.jasamedika.medifirst2000.entity.vo.LevelTingkatId;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository("LevelTingkatDao")
public interface LevelTingkatDao extends CrudRepository<LevelTingkat, LevelTingkatId> {

    @Query(QfindAllList)
    Page<Map<String, Object>> findAllList(
            @Param("kdProfile") Integer kdProfile,
            Pageable pageable);

    @Query(QfindAllList + " and model.namaLevelTingkat like :namaLevelTingkat ")
    Page<Map<String, Object>> findAllList(
            @Param("kdProfile") Integer kdProfile,
            @Param("namaLevelTingkat") String namaLevelTingkat,
            Pageable pageable);
    
   String QfindAllList = "select new map(model as levelTingkat) "
            + " from LevelTingkat model left join model.id id "
            + " where id.kdProfile=:kdProfile "
            + " and model.statusEnabled=true  ";

}

[spring-projects-issues]

Jens Schauder commented

@syamsu-smansa I don't think what you use there is a valid JPA query.

The JPA specification does not allow for aliases inside constructor_expression (s. JPA Specification 2.2 Section 4.8)

Please remove the alias and check if that helps.

Note: It is quite normal the Spring Data executes an additional count-query in order to determine the total size of the paged result. If you want to avoid the count-query you may use a Slice as return type instead

[spring-projects-issues]

Jens Schauder commented

Seems to be a user error

[spring-projects-issues]

Kyle Anderson commented

I've just hit this issue myself and it appears to be a breaking change with how Spring Data JPA generates the count query if the following conditions are met:

• Query uses a constructor expression
• Constructor only has 1 parameter

Example:

@Query("SELECT new com.example.PersonDto(p.id) FROM Person p")
    fun getPeople(page: Pageable): Page<PersonDto>
 

The issue goes away if the constructor has more than 1 parameter.

Example:

@Query("SELECT new com.example.PersonDto(p.id, p.name) FROM Person p")
    fun getPeople(page: Pageable): Page<PersonDto>
 

[spring-projects-issues]

luelista commented

I've encountered the same problem as Kyle, was this changed on purpose or it this a regression? 

[spring-projects-issues]

Jens Schauder commented

Reopening this since judging by the comments, this seems to be a real issue

[geferon]

This issue is still persistent and slightly annoying, any news about it?

[rpastoruma]

Hi, is this problem fixed in the new JPA versions?

[rpastoruma]

This issue does not appears when the constructor has more than one parameter. As a provisional solution you can make a DTO constructor with one dummy parameter like this:

public LogDTO(Log l, Boolean dummy) { this(l); }

And then in the query you can do the following:

@Query("SELECT new test.model.dto.LogDTO(x, true) FROM Log x WHERE ...

[khwilo]

Having the DTO with more than one constructor parameter seems to solve the issue.

[gregturn]

This no longer seems to be a problem:

class FirstNameDto {

	private String firstname;

	public FirstNameDto(String firstname) {
		this.firstname = firstname;
	}
}

@Query("SELECT new org.springframework.data.jpa.repository.sample.FirstNameDto(u.firstname) from User u where u.firstname = :firstname")
List<FirstNameDto> findByNamedQueryWithSingleConstructor(@Param("firstname") String firstname);

@Test
void executesNamedQueryWithSingleConstructorExpression() {
	userRepository.findByNamedQueryWithSingleConstructor("Dave");
}

This runs green on Spring Data JPA's main branch. I've coded a commit with this test case.

[gregturn]

Actually confirmed. There is an escape clause built up around select distinct new com.example.User(.... This thing doesn't properly handle select new com.example.User(... for single-arg situations.

This patch now properly assembles a count query and thus properly supports Page-based methods, whether the DTO has one argument or more.

[gregturn]

Backported to 2.7.x and 2.6.x.

[pablogalegoc]

@gregturn thanks for the fix! These days we found a corner case that I believe still triggered this bug with one of our queries. This is a simplified version of what we had:

  @Query("SELECT DISTINCT NEW versionDTO(thing.version)"
      + " FROM Thing thing"
      + " WHERE thing.id = :id ORDER BY thing.version ASC")
  List<VersionDTO> findVersion(@Param("id") UUID id);

So when we wanted to paged it, just by

+   Page<VersionDTO> findVersion(@Param("id") UUID id, Pageable pageable);
-   List<VersionDTO> findVersion(@Param("id") UUID id);

We got Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: NEW near line 1, column 23 [select count(DISTINCT NEW ... that could be workarounded by changing NEW to new:

+  @Query("SELECT DISTINCT NEW versionDTO(thing.version)"
-  @Query("SELECT DISTINCT new versionDTO(thing.version)"

It's not a big deal but one can't guess it quickly by the error message. IMO, the escape clauses would need a toLowerCase before startsWith.