Let me give you a bit of context.
In our current project we have about 10-30 queries with different where clauses.
Something like this:
SELECT ORDERNUM
FROM TABLE
WHERE ID = :ID
SELECT ORDERNUM
FROM TABLE
WHERE TYPE = :TYPE
AND ITERATION = :ITERATION
SELECT ID, ORDERNUM
FROM TABLE
WHERE PRODUCT = :PRODUCT
...
One of my coworker suggested that we could use something like this to only have 1 named query and to be able to reuse them instead of creating new named queries.
SELECT *
FROM TABLE
WHERE {WHERECLAUSE}
You would pass by parameter the specific conditions that you need.
However, I'm a bit worried of how dinamic could it be and the problems that this could generate.
In our team we have our opinion on what are we going to do, but we thought that asking here was a good idea.
What is your opinion about this?
Note: Normally the queries we need/use don't have user interation so we don't have problem of security (at least at first glance)
You can do this, but you cannot reliably pass any string values in that kind of where clause. If any part of that Where clause comes directly from user input (typical for this use), then you really do have a security problem, too.
This is one of the cases where a scripted query is better, as you can construct a dynamic WHERE clause structurally, but still use ? substitution parameters to safely pass user input to the DB.
6 Likes
My rule of thumb is:
If I need to pass anything else than simple parameters to a named query, then it shouldn't be a named query.
5 Likes
I use a compound WHERE clause and pass 'ALL' for string fields or a 'magic number' for numeric fields.
Here's an example:
WHERE (t.PartNumber = :partNumber OR :partNumber = 'ALL')
AND (t.LotNumber = :lotNumber OR :lotNumber = 'ALL')
AND (t.BuildNumber = :buildNumber OR :buildNumber = 'ALL')
AND (t.ScaleID = :scaleId OR :scaleId = 'ALL')
AND (t.MixID = :mixId OR :mixId = 0 )
The user effectively filters the results to see what they want without needing to write multiple queries or use scripted queries. In this example, the user can see everything for a part number or everything for a building, or everything for a specific part number on a specific scale, etc.
I typically use this when there are drop-downs with 'ALL' as the first option.
But... everything has a cost, complex WHERE clauses can become a performance hit on very large datasets and especially beware of date ranges which easily cause slowness without proper indexes or constrained min/max dates.
1 Like
Exactly. Casual use of compound where clauses with that sort of wildcard option are time bombs. Particularly if handed to less experienced designers. Just say no.
:parameter = 'literal' is true for every row in the table and takes 0 execution plan/explain plan time.
The caution is for having too many fields in the WHERE clause.
It's presence prevents full optimization of the execution plan, because the DB won't know if that shortcut applies until it receives the values to execute. Modern databases make their execution plan when the statement is prepared, not when actual parameters are submitted.
1 Like
Yeah, but if you have 2 or 3 different part of the code that could reuse the same NamedQuery what would you do?
If you don't create a NamedQuery and then change the db, you have to go to all of those spots to change manually the queries.
You make a function that holds the query and you call that function
4 Likes