Handling dynamic queries with Spring

Question

The problem I'm trying to solve here is, filtering the table using dynamic queries supplied by the user. Entities needed to describe the problem:

Table: run_events

Columns: user_id, distance, time, speed, date, temperature, latitude, longitude

The problem statement is to get the run_events for a user, based on a filterQuery. Query is of the format,

((date = '2018-06-01') AND ((distance < 20) OR (distance > 10))

And this query can combine multiple fields and multiple AND/OR operations.

One approach to solving this is using hibernate and concatenating the filterQuery with your query.

"select * from run_events where user_id=:userId and "+filterQuery;

This needs you to write the entire implementation and use sessions, i.e.

        String q = select * from run_events where user_id=:userId and "+filterQuery;

        Query query = getSession().createQuery(q);
        query.setParameter("userId", userId);
 

        List<Object[]> result = query.list();
        List<RunEvent> runEvents = new ArrayList<>();
        for(Object[] obj: result){
            RunEvent datum = new RunEvent();
            int index = -1;
            datum.setId((long) obj[++index]);
            datum.setDate((Timestamp) obj[++index]);
            datum.setDistance((Long) obj[++index]);
            datum.setTime((Long) obj[++index]);
            datum.setSpeed((Double) obj[++index]);
            datum.setLatitude((Double) obj[++index]);
            datum.setLongitude((Double) obj[++index]);
            datum.setTemperature((Double) obj[++index]);
            runEvents.add(datum);
        }

This just doesn't seem very elegant and I want to use the @Query annotation to do this i.e.

 @Query(value = "select run_event from RunEvent where user_id = :userId and :query order by date asc")
    List<RunEvent> getRunningData(@Param("userId") Long userId,
                                      @Param("query") String query,
                                   );

But this doesn't work because query as a parameter cannot be supplied that way in the query.

Is there a better, elegant approach to getting this done using JPA?

Using Specifications and Predicates seems very complicated for this sort of a query.

Querydsl might be an option here. Spring Data also [comes with support for it](https://rieckpil.de/howto-dynamic-sql-querying-pagination-with-querydsl-and-spring-data-jpa/). — rieckpil, Feb 13 '21 at 07:00
The query could be multi-level and complex as well. (((date = '2018-06-01') AND ((distance < 20) OR (distance > 10)) AND (temperature <20)) How would querydsl help? Querydsl seems to be helpful when the filter data is supplied as query parameters. According to the doc: /persons?firstname=Max&page=0&size=2 — Anmol Vijaywargiya, Feb 13 '21 at 07:03

score 1 · Answer 1 · answered Feb 13 '21 at 08:04

To answer the plain question: This is not possible with @Query.

It is also in at least 99% of the cases a bad design decision because constructing SQL queries by string concatenation using strings provided by a user (or any source not under tight control) opens you up for SQL injection attacks.

Instead you should encode the query in some kind of API (Criteria, Querydsl, Query By Example) and use that to create your query. There are plenty of questions and answers about this on SO so I won't repeat them here. See for example Dynamic spring data jpa repository query with arbitrary AND clauses

If you insist on using a SQL or JPQL snippet as input a custom implementation using String concatenation is the way to go.

Well, yes. I know, it's a bad design but this is something I was playing around with. As in, having valid checks to ensure it isn't an SQL injection but also getting it done without concatentation. Parsing the query string to create a criteria is seeming to be fairly complex. It's turning out to be recursive. Wanted to know if there's a better solution out there. — Anmol Vijaywargiya, Feb 13 '21 at 08:25

score 1 · Answer 2 · answered Feb 13 '21 at 08:05

1

This opens up attack for SQL injection. Maybe that’s why this feature is not possible.

It is generally a bad idea to construct query by appending random filters at the end and running them.

What if the queryString does something awkward like

Select * from Foo where ID=1234 or true;

thereby returning all the rows and bringing a heavy load on DB possibly ceasing your whole application?

Solution: You could use multiple Criteria for filtering it dynamically in JPA, but you’ll need to parse the queryString yourself and add the necessary criteria.

answered Feb 13 '21 at 08:05

Mohamed Anees A

4,119
1
22
35

It's going to be paginated API of course with a limit on page size. I didn't put that in the problem statement. Parsing the query string to create a criteria is seeming to be fairly complex. It's turning out to be recursive. Wanted to know if there's a better solution out there. – Anmol Vijaywargiya Feb 13 '21 at 08:26
SQL injection does not only cause the load. Imagine exposing your users phone numbers (10 users, paginated)! Though it’s a paginated one, it doesn’t mean you can skip SQL injection attack. – Mohamed Anees A Feb 13 '21 at 08:29
Exactly the reason why I want to parse the query into a valid JPA structure and then proceed with getting the results. But parsing this sort of a query (((date = '2018-06-01') AND ((distance < 20) OR (distance > 10)) AND (temperature <20)) is turning out to be very complex. Any suggestions on how to go about it? – Anmol Vijaywargiya Feb 13 '21 at 08:34

score 0 · Answer 3 · answered Feb 13 '21 at 14:05

0

You can use kolobok and ignore fields with null values. For example create one method like bellow

findByUserIdAndDistanceaLessThanAndDistancebGreaterThan....(String userid,...)

and call that method only with the filter parameters while other parameters are null

answered Feb 13 '21 at 14:05

mahfuj asif

1,691
1
11
32

Well but that way, you would have to create methods will all sorts of combinations. Doesn't seem very nice :/ – Anmol Vijaywargiya Feb 14 '21 at 06:29
@AnmolVijaywargiya No you have to create just one method with all parameters. Then provide not null filter parameters and the parameters that you dont wnat to filter with just send null – mahfuj asif Feb 14 '21 at 06:38
But there can be multiple combinations right? ((date = '2018-06-01') AND ((distance < 20) OR (distance > 10)) or ((date = '2018-06-01') AND (distance > 20) AND (temperature <30)) I mean infinite combinations.. – Anmol Vijaywargiya Feb 14 '21 at 06:43
@AnmolVijaywargiya ohh sorry, i didnt consider combination – mahfuj asif Feb 14 '21 at 15:40

Handling dynamic queries with Spring

3 Answers3