java.lang.IollegalArgumentException:找不到命名参数[user]，应为[min，gen

Question

我得到

INFO 4508 --- [nio-8081-exec-9]  j.r.q.QueryParameterSetter$ErrorHandling : Silently ignoring
java.lang.IllegalArgumentException: Could not locate named parameter [user], expecting one of [min, gender, max]
at org.hibernate.query.internal.ParameterMetadataImpl.getNamedParameterDescriptor(ParameterMetadataImpl.java:218) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.query.internal.ParameterMetadataImpl.getQueryParameter(ParameterMetadataImpl.java:187) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.query.internal.QueryParameterBindingsImpl.getBinding(QueryParameterBindingsImpl.java:188) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:493) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:106) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.lambda$setParameter$4(QueryParameterSetter.java:113) [spring-data-jpa-2.1.9.RELEASE.jar:2.1.9.RELEASE]
at org.springframework.data.jpa.repository.query.QueryParameterSetter$ErrorHandling$2.execute(QueryParameterSetter.java:185) ~[spring-data-jpa-2.1.9.RELEASE.jar:2.1.9.RELEASE]
at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.setParameter(QueryParameterSetter.java:113) [spring-data-jpa-2.1.9.RELEASE.jar:2.1.9.RELEASE]
at org.springframework.data.jpa.repository.query.ParameterBinder.lambda$bind$0(ParameterBinder.java:79) [spring-data-jpa-2.1.9.RELEASE.jar:2.1.9.RELEASE]
at java.util.ArrayList.forEach(ArrayList.java:1257) ~[na:1.8.0_231]
at java.util.Collections$UnmodifiableCollection.forEach(Collections.java:1082) ~[na:1.8.0_231]

代码

@Repository
public interface UserRepo extends JpaRepository<User, Long> {
@Query("select new com.newcode.meeting.domain.dto.UserDto( " +
" us, " +
" SUM (case  when ul.userLike = :user then 1 else 0 end) > 0  " +
") from User us left join us.likes ul  where " +
" us.birthDate BETWEEN :max and  :min " +
" and  us.gender =:gender group by us ")
Page<UserDto> findUsersByBirthDateIsBetweenAndGenderDto(
Pageable pageable,
@Param("max") LocalDate max,
@Param("min") LocalDate min,
@Param("gender") Gender gender,
@Param("user") User user
);

如果需要某人查看，请将UserDto类化。我不知道还需要添加什么，这样它就不会写——"看起来你的帖子主要是代码；请添加更多细节。">

import com.fasterxml.jackson.annotation.JsonView;
import com.newcode.meeting.domain.Image;
import com.newcode.meeting.domain.ProfileDetail;
import com.newcode.meeting.domain.User;
import com.newcode.meeting.domain.Views;
import com.newcode.meeting.dto.Role;
import lombok.*;
import java.net.URL;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Set;
@Data
@EqualsAndHashCode(of = "id")
@AllArgsConstructor
@RequiredArgsConstructor
@Getter
@Setter
@ToString(of = {"id", "meLiked"})
@JsonView(Views.FullProfileDetail.class)
public class UserDto {
private Long id;
private String username;
private URL userpic;
private Gender gender;
private LocalDate birthDate;
private boolean isOnline;
private Set<Role> roles;
private boolean active;
private String region;
private String country;
private LocalDateTime lastVisit;
private ProfileDetail profileDetail;
@JsonView(Views.FullProfileDetailImage.class)
private Set<Image> images;
private boolean meLiked;
Integer likeNew;
public UserDto(User user, boolean meLiked) {
this.id = user.getId();
this.username = user.getUsername();
this.userpic = user.getUserpic();
this.gender = user.getGender();
this.birthDate = user.getBirthDate();
this.isOnline = user.isOnline();
this.roles = user.getRoles();
this.active = user.isActive();
this.region = user.getRegion();
this.country = user.getCountry();
this.lastVisit = user.getLastVisit();
this.profileDetail = user.getProfileDetail();
this.images = user.getImages();
this.meLiked = meLiked;
this.likeNew = user.getLikeNew();
}
}
public UserPageDto getAllUser(User user, String ageMax, String ageMin, String gender, Pageable pageable) {
user = userRepo.findUserById(user.getId());
LocalDate minDate = getDateForSearchUser(ageMin);
LocalDate maxDate = getDateForSearchUser(ageMax);
Gender sex;
if (gender.equals(Gender.FEMALE.toString())) {
sex = Gender.MALE;
} else {
sex = Gender.FEMALE;
}
Page<UserDto> page = userRepo.findUsersByBirthDateIsBetweenAndGenderDto(pageable, maxDate, minDate, sex, user);
return new UserPageDto(
page.getContent(),
page.getNumber(),
page.getTotalPages());
}

当我添加"可分页"时，我开始出现这个错误。但结果是正确的。没有"可分页"或参数"用户"工作没有错误

问题是如何删除此错误？

Answer 1

我也遇到了类似的问题。

这个错误不是指您的查询——它抱怨countQuery，它统计所有可分页的结果，所以它可以计算总共有多少页。

如果未显式声明，则会自动生成此countQuery。因此，生成它是为了使用所有参数，而不是User(作为优化的一种方式，此生成可能会忽略ORDER BY或聚合，因为它只需要查询和顺序匹配的记录数，或者其他数据无关紧要(。

因此生成的countQuery可能看起来像这样：

@Query("select count(us)" +
"from User us left join us.likes ul  where " +
" us.birthDate BETWEEN :max and  :min " +
" and  us.gender =:gender group by us ")

正如您所看到的，受影响的行数与原始查询中的行数保持不变，但：user不包括在内，这就是Hibernate无法验证countQuery的原因。

删除Pageable作为参数时没有出现同样的错误，因为在这种情况下，没有生成countQuery(不使用：user param时也是如此(。

您现在有三个选项：

1. 使用PageImpl实现自己的分页
2. 明确定义要使用的查询：user参数：

@Query(value = "select new com.newcode.meeting.domain.dto.UserDto( " +
" us, " +
" SUM (case  when ul.userLike = :user then 1 else 0 end) > 0  " +
") from User us left join us.likes ul  where " +
" us.birthDate BETWEEN :max and  :min " +
" and  us.gender =:gender group by us ",
countQuery = "select count(us)" +
"from User us left join us.likes ul  where " +
" us.birthDate BETWEEN :max and  :min " +
" and  us.gender =:gender group by us " +
"order by :user)

3. 首先检索UserEntities，然后在将其映射到DTO时计算此附加列(通过单独的JPA查询或通过实体中的相关集合使用Java方法(

Answer 2

这是由于对countQuery的隐式调用。尝试重新定义查询，使条目的所有参数都存在于其中。例如：

@Query(value = "SELECT new com.core.blackfilm.v2.dto.CommentDto(" +
"c, " +
"COUNT(cl), " +
"SUM(case when cl = :author then 1 else 0 end) > 0 " +
") " +
"FROM com c LEFT JOIN c.likes cl " +
"WHERE c.movie = :movie " +
"GROUP BY c",
countQuery = "SELECT COUNT(cl) FROM com c LEFT JOIN c.likes cl WHERE c.movie = :movie AND cl = :author")
Page<CommentDto> findByMovieId(@Param("movie") Movie movie, @Param("author") User author, Pageable pageable);