Privacy Policy and Cookies

By continuing to use our site, you agree to our Privacy Policy and our use of cookies to understand how you use our site, and to improve your experience. Learn More.
I Agree.

Part 2: SQLDataSource FTW

Last modified date

Power & Flexibility

I hear a lot that I should use something like JPAQL for what it’s good at, and then back away to SQL when I have something a little more complex. I think I showed last time that SQLDataSource is equally capable (if not more so) of generating simple SQL statements. The difference with SQLDataSource is, I can use the same, simplified approach to handle statements of any complexity. I can even let the framework generate some or most of the SQL, and then customize just the parts I need to.

For example, if all I want is to calculate a column value using a SQL expression, I can add a field to represent it and include an attribute containing the expression. The framework will generate the rest of the statement as usual. This works the way you’d expect for select, insert, update, delete, and even for criteria.

You can also combine your SQL expressions with Velocity expressions to be evaluated at runtime. You can put almost whatever you want into a context variable, but the framework provides a number of frequently used variables out of the box that cover the common cases.

Here is an example of a pattern I use frequently for user authorization & record-level security. In this case, I want to fetch the currently authenticated User profile and UserRoles from related DataSources. Take a look at this fetchCurrentUser operation binding.

User.ds.xml

It forces the currently authenticated user’s userId (provided by the Servlet container and made available on dsRequest as a matter of convenience) into the WHERE clause, so that an authenticated user can only ever see their own profile (with a little help from declarative security). With no other criteria supplied, the result would look something like this.

SELECT id, username, password, profile
FROM user
WHERE username = ‘bill@isomorphic.com’
AND (1 = 1);

I use the same technique in UserRole to force the username in fetchByCurrentUser, but this operation also shows a little SQL Templating in action for customization of the FROM clause.

UserRole.ds.xml

I could have just as easily added the username filter by instead using a whereClause element with a $defaultWhereClause expression to the same effect.

SELECT pk, id, role, username
FROM userRole
WHERE username = ‘bill@isomorphic.com’
AND (1=1);

Or perhaps more appropriately in this case, just ignore additional criteria altogether:

SELECT pk, id, role, username
FROM userRole
WHERE username = ‘bill@isomorphic.com’;

Maybe you’d like to use a database-specific function in your customization, depending on which database is deployed. Here I’ve made up an example Roster DataSource that illustrates one easy way to do so, using a Velocity conditional on another context variable provided by the framework. If we see that the app is configured for Oracle, we’ll use LIST_AGG, otherwise GROUP_CONCAT.

Roster.ds.xml

Keep looking, and you’ll find that all kinds of things you used to do yourself take very little effort using the same techniques. It’s very common, for example, to have one request depend on the response / result of another. Consider the creation of a new Customer record at the time they create their first Order, where you need the auto-assigned customerNumber before you can add the Order record.

I’ve seen a handful of projects attempt this kind of thing from client-side callbacks, which aside from being hard to look at requires 2 trips to the server and runs in separate transactions. That’s probably not what you really want, so SmartClient includes a feature that allows Transaction Chaining using the same declarative style (and even allows limited access to the same feature from client code with no configuration).

Occasionally though, none of these approaches provide quite enough flexibility to do exactly what you need. In that case, you can effectively extend SQL generation to fit your requirement with a single well-placed call to addToTemplateContext, as I alluded to earlier. Note that as in the example above, this can include calls to methods on classes you provide. And you can always just fall back to completely custom SQL, as I sometimes do to execute UNION queries or stored procedures.

Note that the use of customSQL necessarily disables a handful of very useful automatic framework features (paging, client component caches, etc.) so generally try to use another technique if you can.

I really could go on and on like this, manipulating SQL generation by adding an element here and an attribute there, but at some point you’ll want to do something that just can’t be handled with configuration alone.

I remember once having what seemed like a tricky situation, where I had to integrate with a document generation server whenever data from any one of four different DataSources was modified. The trick was, any of them could be modified in any combination, and I only wanted to generate the document once per transaction. As it turned out, I only had to write a few lines of code to keep track of a request attribute and check it in a callback fired by the framework when the transaction was complete.

Another time, I was working with a client using a FilterBuilder to allow their users to create very advanced criteria against a reporting database. These structures were very large and reasonably complicated, so they wanted to offload that processing to a reporting queue they’d already built. In the end, they needed no more than a few lines of code to pull off the integration, using another SQLDataSource to hold a request that included the complete SQL statement to be picked up by the reporting listener.

Need the actual JDBC connection for some truly unusual edge case? I’ve never needed it myself, but a colleague once used it to turn on and off a particular kind of caching on the fly for a particular transaction.

Your own application is unique, but I’m pretty confident that you can do more with less using some of these techniques and others that will have to wait for another day. Let us know on the forums if you have something you’d like to see covered!