Spring Boot动态查询

Question

我的webapp中有一个过滤器，允许按车辆类型、品牌、燃油、州和城市进行搜索，但所有这些过滤器都是可选的。

如何使用存储库实现这一点。

控制器类

@RequestMapping(value = "/vehicle/search", method = RequestMethod.GET, produces = MediaType.APPLICATION_JSON_VALUE)
public Iterable<Veiculo> findBySearch(@RequestParam Long vehicletype, @RequestParam Long brand, 
        @RequestParam Long model, @RequestParam Long year, 
        @RequestParam Long state, @RequestParam Long city) {
    return veiculoService.findBySearch(vehicletype, brand, model, year, state, city);
}

服务类

public Iterable<Vehicle> findBySearch(Long vehicletype, Long brand, Long model, Long year, Long state, Long city) {
    if(vehicletype != null){
        //TODO: filter by vehicletype
    }
    if(brand != null){
        //TODO: filter by brand
    }
    if(model != null){
        //TODO: filter by model
    }

    //OTHER FILTERS
    return //TODO: Return my repository with personal query based on filter
}

我还没有实现任何东西，因为我不知道如何实现这个过滤器。

车辆等级

@Entity
@Table(name = "tb_veiculo")
public class Veiculo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Long id;

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "veiculo_opcionais", 
    joinColumns = @JoinColumn(name = "veiculo_id", referencedColumnName = "id"),
    inverseJoinColumns = @JoinColumn(name = "opcional_id", referencedColumnName = "id"))
    private List<Opcional> opcionais;

    @JsonIgnore
    @OneToMany(mappedBy = "veiculo", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List<VeiculoImagem> veiculoImagens;

    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "cambio_id", foreignKey = @ForeignKey(name = "fk_cambio"))
    private Cambio cambio;

    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "combustivel_id", foreignKey = @ForeignKey(name = "fk_combustivel"))
    private Combustivel combustivel;

    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "cor_id", foreignKey = @ForeignKey(name = "fk_cor"))
    private Cor cor;

    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "modelo_id", foreignKey = @ForeignKey(name = "fk_modelo"))
    private Modelo modelo;

    @ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "usuario_id", foreignKey = @ForeignKey(name = "fk_usuario"))
    private Usuario usuario;

    @Column(name = "anoFabricacao", nullable = false)
    private int anoFabricacao;

    @Column(name = "anoModelo", nullable = false)
    private int anoModelo;

    @Column(name = "quilometragem", nullable = false)
    private int quilometragem;

    @Column(name = "porta", nullable = false)
    private int porta;

    @Column(name = "valor", nullable = false)
    private double valor;

    //GETTERS AND SETTERS

车型和品牌是另一张桌子上的...我是葡萄牙人我把密码翻译成了英文...

当它发生时，我需要做什么？

Answer 1

您可以使用Spring的规范API，它是JPA标准API的包装器，允许您创建更多的动态查询。

在您的情况下，我假设您有一个车辆实体，该实体具有字段品牌，年份，州，城市，...。

如果是这种情况，您可以编写以下规范：

public class VehicleSpecifications {
    public static Specification<Vehicle> withCity(Long city) {
        if (city == null) {
            return null;
        } else {
            // Specification using Java 8 lambdas
            return (root, query, cb) -> cb.equal(root.get("city"), city);
        }
    }

    // TODO: Implement withModel, withVehicleType, withBrand, ...
}

如果您必须进行连接（例如，如果您想检索Vehicle.city.id），则可以使用：

return (root, query, cb) -> cb.equal(root.join("city").get("id"), city);

现在，在存储库中，您必须确保从JpaSpecificationExecutor进行扩展，例如：

public interface VehicleRepository extends JpaRepository<Vehicle, Long>, JpaSpecificationExecutor<Vehicle> {

}

通过从此接口进行扩展，您可以访问允许您执行规范的findAll（Specification spec）方法。如果需要组合多个规范（一个过滤器=通常一个规范），可以使用规范类：

repository.findAll(where(withCity(city))
    .and(withBrand(brand))
    .and(withModel(model))
    .and(withVehicleType(type))
    .and(withYear(year))
    .and(withState(state)));

在上面的代码示例中，我为Specifications.where和车辆规格使用静态导入。*使其看起来更具声明性。

您不必在这里编写if（）语句，因为我们已经在车辆规范中编写了它们。withCity（）。只要从这些方法返回null，Spring就会忽略它们。