java - 使用 Set 进行 Spring boot Repository 查询 - InvalidDataAccessApiUsageException

Question

我有一个从 React UI 调用的 Spring Boot API，使用 Spring Boot 存储库中的查询来获取一些数据。但是，当我在查询参数中引入 Set<> 的使用时，我收到错误，并且不确定如何继续。

错误如下: org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [WaterHardness(id=4, hardness=hard)] did not match expected type [java.util.Set (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [WaterHardness(id=4, hardness=hard)] did not match expected type [java.util.Set (n/a)]

根据我的研究(我还没有找到任何使用 ManyToMany Set<> 的完整示例)，我认为我应该能够使用和 IN在查询语句中以及传入的Set<>参数。

我有一个 Controller 方法，它从 react 应用程序中获取 POST:

@PostMapping("/search")
@ResponseBody
public String searchFish(@RequestBody FishDto fishDto) {
    String fishJson = "";
    try {
        List<Fish> fish = fishService.findBySearch(fishDto);

        ObjectMapper mapper = new ObjectMapper();
        fishJson = mapper.writeValueAsString(fish);   
    } catch (Exception e) {
        //TODO Proper Handle of exceptions
        log.info("Something Went Wrong!!!");
    }
    return (fishJson);
}

调用中的参数映射到 FishDTO:

public class FishDto {

    private int tankVolume;
    private int tankLength;
    private int tankDepth;
    private int tankTemp;
    private Set<WaterHardness> waterHardness;

}

当使用调试器运行时，DTO 似乎已按预期填充。

Controller 调用服务中的方法来为存储库准备参数:

public class FishService {
    ...
    public List<Fish> findBySearch(FishDto fishDto) {
        int tankVolume = fishDto.getTankVolume();
        int tankLength = fishDto.getTankLength();
        int tankDepth = fishDto.getTankDepth();
        int tankTemp = fishDto.getTankTemp();
        Set<WaterHardness> waterHardness = fishDto.getWaterHardness();

        return Lists.newArrayList(fishrepo.findBySearch(tankVolume, tankLength, tankDepth, tankTemp, waterHardness));
    }
...
}

最后，存储库使用查询进行调用:

public interface FishRepository extends JpaRepository<Fish, Long> {

    @Query("SELECT f FROM Fish f" 
        + " WHERE f.tankMinVolume <= :tankVolume"
        + " AND f.tankMinLength <= :tankLength"
        + " AND f.tankMinDepth <= :tankDepth"
        + " AND f.minWaterTemp <= :tankTemp"
        + " AND f.maxWaterTemp >= :tankTemp"
        + " AND f.waterHardness IN :waterHardness")
    List<Fish> findBySearch(@Param("tankVolume") int tankVolume,
        @Param("tankLength") int tankLength,
        @Param("tankDepth") int tankDepth,
        @Param("tankTemp") int tankTemp,
        @Param("waterHardness") Set<WaterHardness> waterHardness);
}

哦，这是返回的实体模型:

@Entity
@Table(name = "fish")
public class Fish {

    @Id
    @GeneratedValue
    private Long id;

    @NonNull //Check up on this!
    private String commonName;
    private String latinName;
    private int fullGrownSize;
    private int tankMinVolume;
    private int tankMinLength;
    private int tankMinDepth;
    private int minWaterTemp;
    private int maxWaterTemp;
    private int minShoalSize;

    @ManyToMany(fetch=FetchType.EAGER, cascade=CascadeType.ALL)
    private Set<TankPlacement> tankPlacement;

    @ManyToMany(fetch=FetchType.EAGER, cascade=CascadeType.ALL)
    private Set<TankFlow> tankFlow;

    @ManyToMany(fetch=FetchType.EAGER, cascade=CascadeType.ALL)
    private Set<WaterHardness> waterHardness;

    @ManyToOne(cascade=CascadeType.PERSIST)
    private Origin origin;

}

除了从服务中的 waterHardness 获取 id/字符串值列表并将它们作为参数传递然后在查询中使用联接之外，是否有一种巧妙的方法可以使其工作？例如:

@Query("SELECT f FROM Fish f" 
        + " JOIN f.waterHardness wh"
        + " WHERE f.tankMinVolume <= :tankVolume"
        + " AND f.tankMinLength <= :tankLength"
        + " AND f.tankMinDepth <= :tankDepth"
        + " AND f.minWaterTemp <= :tankTemp"
        + " AND f.maxWaterTemp >= :tankTemp"
        + " AND wh.id IN :waterHardnessIds")
    List<Fish> findBySearch(@Param("tankVolume") int tankVolume,
        @Param("tankLength") int tankLength,
        @Param("tankDepth") int tankDepth,
        @Param("tankTemp") int tankTemp,
        @Param("waterHardnessIds") List<Long> waterHardnessIds);

这确实有效，但感觉没有必要。

Answer 1

问题是Set<WaterHardness> 。

错误说，

Parameter value [WaterHardness(id=4, hardness=hard)] did not match expected type [java.util.Set (n/a)]

您需要按如下方式检查 IN 运算符，

公共(public)接口(interface) FishRepository 扩展 JpaRepository {

@Query("SELECT f FROM Fish f" 
    + " WHERE f.tankMinVolume <= :tankVolume"
    + " AND f.tankMinLength <= :tankLength"
    + " AND f.tankMinDepth <= :tankDepth"
    + " AND f.minWaterTemp <= :tankTemp"
    + " AND f.maxWaterTemp >= :tankTemp"
    + " AND f.waterHardness.id IN :waterHardness")
List<Fish> findBySearch(@Param("tankVolume") int tankVolume,
    @Param("tankLength") int tankLength,
    @Param("tankDepth") int tankDepth,
    @Param("tankTemp") int tankTemp,
    @Param("waterHardness") Set<Long> waterHardness);
}