Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

Controller class

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

Service class

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.

Vehicle Class

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

like image 602
Felipe A. Avatar asked Aug 26 '16 13:08

Felipe A.


People also ask

Which is the JPA method to create dynamic query?

Using EntityManager methods createQuery or createNativeQuery , you can create a Query object dynamically at run time (see "Using Java"). Using the Query methods getResultList , getSingleResult , or executeUpdate you can execute the query (see "Executing a Query").

Is it possible to create a dynamic JPQL query?

You can try. Show activity on this post. You can use JpaSpecificationExecutor I will explain it with my example.

What is the difference between JPQL and SQL?

SQL works directly against relational database tables, records and fields, whereas JPQL works with Java classes and instances. For example, a JPQL query can retrieve an entity object rather than field result set from database, as with SQL.

What is difference between Jparepository and Crudrepository?

Crud Repository is the base interface and it acts as a marker interface. JPA also provides some extra methods related to JPA such as delete records in batch and flushing data directly to a database. It provides only CRUD functions like findOne, saves, etc. JPA repository also extends the PagingAndSorting repository.


1 Answers

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.

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, ...
}

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);

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

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

}

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)));

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

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.

like image 151
g00glen00b Avatar answered Oct 03 '22 02:10

g00glen00b