Spring boot 动态查询 [英] Spring boot Dynamic Query

问题描述

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

I have a filter in my webapp, allowing to search by vehicle type, brand, fuel, state and city but all these filters are optional.

如何使用存储库执行此操作.

How can I do this using repositories.

控制器类

@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
}

我还没有实现任何东西，因为我不明白如何做这个过滤器.

I haven't implemented anything yet because I don't understand how can I do this 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

车辆类型和品牌来自另一个表...我是葡萄牙人，我已将代码翻译成英文...

The vehicletype and brand it's from another table... I'm portuguese, i've translated the code to english...

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

When It happen, what i need to do?

推荐答案

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

You can use the specification API from Spring, which is a wrapper around the criteria API from JPA that allows you to create more dynamic queries.

在你的例子中，我假设你有一个 Vehicle 实体，它有一个字段 brand、year、state, city, ... .

In your case I assume that you have a Vehicle entity that has a field brand, year, state, city, ... .

如果是这样，您可以编写以下规范:

If that's the case, you can write the following specification:

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)，那么您可以使用:

If you have to do a join (for example if you want to retrieve Vehicle.city.id) then you can use:

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

现在，在您的存储库中，您必须确保从 JpaSpecificationExecutor 扩展，例如:

Now, in you repository you have to make sure you extend from JpaSpecificationExecutor, for example:

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

}

通过从这个接口扩展，你可以访问findAll(Specification spec) 允许你执行的方法规格.如果需要组合多个规范(通常一个过滤器=一个规范)，可以使用Specifications 类:

By extending from this interface, you'll get access to the findAll(Specification spec) method that allows you to execute specifications. If you need to combine multiple specifications (one filter = one specification usually), you can use the Specifications class:

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

在上面的代码示例中，我对 Specifications.where 和 VehicleSpecifications.* 使用静态导入，使其看起来更具声明性.

In the code example above I'm using static import for Specifications.where and for VehicleSpecifications.* to make it look a bit more declarative.

您不必在此处编写 if() 语句，因为我们已经在 VehicleSpecifications.withCity() 中编写了它们.只要您从这些方法中返回 null，它们就会被 Spring 忽略.

You don't have to write if() statements here because we already wrote them in VehicleSpecifications.withCity(). As long as you return null from these methods they will be ignored by Spring.

这篇关于Spring boot 动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！