Spring Data JDBC dynamic where clauses

Question

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:

Is there a way to code this dynamic where query with spring data jdbc?
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.

Andrey Grigoriev · Accepted Answer · 2024-12-29 18:48:18Z

3

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.

edited Dec 29, 2024 at 18:48

Andrey Grigoriev

4563 silver badges11 bronze badges

answered Aug 14, 2023 at 11:34

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thomas Lang Over a year ago

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 Over a year ago

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?

Jens Schauder Over a year ago

No, that is not possible. In that case a JdbcTemplate or NamedParameterJdbcTemplate is the better choice.

Thomas Lang · Accepted Answer · 2023-08-21 12:37:07Z

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.

Collectives™ on Stack Overflow

Spring Data JDBC dynamic where clauses

2 Answers 2

You can use Query By Example.

Use the `JdbcAggregateTemplate`

Use a `JdbcTemplate`

You can NOT inject an `EntityManger`

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

You can use Query By Example.

Use the JdbcAggregateTemplate

Use a JdbcTemplate

You can NOT inject an EntityManger

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Use the `JdbcAggregateTemplate`

Use a `JdbcTemplate`

You can NOT inject an `EntityManger`