spring-dataspring-data-jdbc

Spring Data JDBC dynamic where clauses


I have a custom search page, where i can enter up to 6 search options. This options are combined with AND so by using all filters i have 6 where clauses and so on.

Two questions:

  1. Is there a way to code this dynamic where query with spring data jdbc?

  2. How can i access the underlying DataSource (or maybe a JdbcTemplate) to fire this query directly?

For example:

this.jdbcTemplate.query = query // the dynamic query with 6 or less where clauses;
List<Result> results = this.jdbcTemplate.run();

Thank you!

EDIT

There is a JdbcAggregateTemplate with a <T> Iterable<T> findAll(Query query, Class<T> domainType); query. Is this the right place?

In contrary to other posts stated, i cannot autowire an EntityManager bean.


Solution

  • You have multiple options:

    You can use Query By Example.

    You'd create an Example from an entity where some entries are filled:

    Person person = new Person();                         
    person.setFirstname("Dave");                          
    
    Example<Person> example = Example.of(person);   
    

    And then using it with your repository, which would extend QueryByExampleExecutor:

    public interface QueryByExampleExecutor<T> {
    
      <S extends T> S findOne(Example<S> example);
    
      <S extends T> Iterable<S> findAll(Example<S> example);
    
      // … more functionality omitted.
    }
    

    and you can fine tune how the example is used:

    Person person = new Person();                          
    person.setFirstname("Dave");                           
    
    ExampleMatcher matcher = ExampleMatcher.matching()     
      .withIgnorePaths("lastname")                         
      .withIncludeNullValues()                             
      .withStringMatcher(StringMatcher.ENDING);            
    
    Example<Person> example = Example.of(person, matcher);
    

    Use the JdbcAggregateTemplate

    It has the semi public feature of accepting a Query. It is not properly documented. Checkout the source code for how to use it.

    Use a JdbcTemplate

    You'll have to construct your SQL manually

    You can NOT inject an EntityManger

    It is a JPA artefact and Spring Data JDBC has nothing to do with JPA.