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.