1

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.

Thomas Lang
  • 1,285
  • 17
  • 35

2 Answers2

2

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • Hey Jens, thank you for your suggestions. I will give it a try and post my solution here as soon as it works. By the way - Spring Data JDBC has become my first choice of ORM tool. Superb job!!! – Thomas Lang Aug 14 '23 at 13:25
  • Concerning Query By Example? I can only use a Domain Type for this type? In the examples there is the `Person` Domain Object. What if i need a (SearchResults)DTO built of various properties from `Person` joins? Let's say `Person`->`Address`; `Person`->`Employments`; `Person`->`Contracts` and so on? Is this possible? – Thomas Lang Aug 16 '23 at 06:04
  • 1
    No, that is not possible. In that case a `JdbcTemplate` or `NamedParameterJdbcTemplate` is the better choice. – Jens Schauder Aug 16 '23 at 20:11
0

Jens` answer is absolutely right.

After talks with collegues i ended up with an alternative to his tips.

Since i have 6 search options the query building will be likely a lot of switch blocks.

In short: I did this (not edited or shortened to sketch the complexity of the query):

@Query("""
            select p.projectname, p.unit, p.cost_unit as costUnit, p.chapter, u.username, w.firstname, w.lastname, e.comment, e.value, e.entry_date as entry,
            w.active_from as activeFrom, w.active_to as activeTo, w.type, w.hwl, w.further, w.compensation, w.delegation, w.amount
            from projects p
            left join users u on p.users_id = u.id
            left join workers w on w.projects_id = p.id
            left join entries e on e.workers_id = w.id
            where trim(lower(p.projectname)) like concat('%', lower(:projectName))
            and trim(lower(concat(w.firstname, w.lastname))) like concat('%', lower(:workerName))
            and trim(lower(u.username)) like concat('%', lower(:userName))
            and extract(year from e.entry_date) >= :year
            and trim(lower(p.cost_unit)) like concat('%', lower(:costUnit), '%')
            and trim(lower(p.unit)) like concat('%', lower(:unit), '%')""")
List<SearchResultDto> search(String projectName, String workerName, String userName, Integer year, String costUnit, String unit);

The trick is to use the % operator for string queries. When the parameter is null or empty string, i'll pass it on to the query.

So % yields all entries for the given column. Whereas %projectName yields all entries with projectName in it.

The same works on year. If a year is selected in the UI, it goes to the query, If not, i use 0 as year so all years will be selected.

Thomas Lang
  • 1,285
  • 17
  • 35