4 ms·
Dynamic filtering and sorting can be performed using static SQL. In the following example the parameters :filter_region, :filter_supplier and :order_column are
by taffer 5y ago
Dynamic filtering and sorting can be performed using static SQL. In the following example the parameters :filter_region, :filter_supplier and :order_column are assumed to be defined by the user:
SELECT *
FROM example
WHERE
coalesce(:filter_region = region, TRUE)
AND coalesce(:filter_supplier = supplier, TRUE)
ORDER BY
CASE
WHEN :order_column = 'supplier' THEN supplier
WHEN :order_column = 'region' THEN region
ELSE ''
END;
Compared to dynamic SQL, there is no possibility for SQL injections here and you don't have the mental and performance overhead of using an ORM.
Another interesting alternative to an ORM is jOOQ, basically a query builder and lightweight ORM that generates Java classes from the database schema: https://www.jooq.org/ https://www.jooq.org/
- brainless 5y agoHave you considered that "filter_region" will itself not be know in advance? So if there are 12 attributes that I can filter with, how do you propose to generate any combination of them with SQL?
- taffer 5y agoYes, filter_region, filter_supplier and order_column are parameters. The filter parameters are combined using AND, or if they are NULL, they are ignored. For example, you could set filter_region = 'Oceania' and filter_supplier = 'Acme Corporation' or any combination of them.