I am not sure if the following is a common practice. But I noticed it at a couple of places. As a true practitioners of data driven development, this group stores all SQL statements in a table. The entire statement is not stored. But important segments like table name(s), SQL Operation (delete/insert/update) to perform, and relevant field names for insert/update operation are stored. The actual values for each field for insert and update operations are supplied at run time. Do you see benefits of this approach? Is this a pattern or anti-pattern? Thanks, Ramesh
The 'anti-pattern' that I see with this approach is overuse of dynamic SQL and too little use of PreparedStatement. I'm not saying that the anti-pattern is actually present. But since you say that you are storing only portions of the SQL statements in the database, my assumption is that you end up doing somthing like this:
Most modern databases will cache the compiled version of a query. Then if another query comes which is *exactly* the same, the second query does not have to be recompiled. The *exactly* part is sensitive to all the spaces and letters in the string of the query, but does not include the parameters. So
is better than
You get benefit from this practice even if your application uses the PreparedStatement only once, then discards the PreparedStatement after it executes the query. The benefit comes if a second user uses the same query but with different parameters, or even if another part of your application executes the same query with different parameters. That happens because the database can hold the compiled version of the query, and even its query plan and partial results, in cache.
I think what are proposing it seems there is too much usage to dynamic Sql generation and u are not making use of pre-compiled queries.In my view it would wotk but it can be termed as an anti-pattern as this is a negative way to do things.An ideal way out could have been using both and to draw a line between using too much dyanmic stuff and stored procedures, then u can qualify to be called as an pattern