Making Fool-Proof Dynamic SQL
I was reading the MyBatis docs as a refresher and in the Dynamic SQL section I noticed that they are still struggling with the WHERE clause if all elements are conditional.
Take this example from the docs:
It says that if none of the conditions are met, you will end up with an invalid SQL statement of SELECT * FROM BLOG WHERE or in another case SELECT * FROM BLOG WHERE AND title like #{title}. Both of these are a common problem when trying to build a dynamic SQL statement.
MyBatis attempts to remedy this situation by stating that you can use the <where> XML tag which they say will likely work in 90% of the cases. Here is there modified code:
But I want a 100% solution, not 90%.
Back in 2008 when I worked on a large billing system at TELUS we always had this problem because many of the SQL statements were dynamic. We solved it in such a simple way that it amazes me how I could have missed it all these years of writing SQL. Simply adding an axiomatic element to the WHERE clause with allow each subsequent clause to become an AND ... and never break the SQL statement. See the following modification in the original SQL statement used previously:
In case you missed it, I added a 1=1 clause on line 4 and then ensured that each conditional clause starts with AND (specifically, line 6) so that they become a chain of clauses. While these examples are using MyBatis, this trick can be used on any SQL statement or ORM framework.