SQL Queries and String.Join()
Over the last few weeks, I’ve found myself working within a framework where I’m writing a lot of SQL. Most specifically, in the code I’m writing a lot of WHERE clauses related to optional user search parameters. As a simple example, consider a search over “customer” where a user could filter by a part of a customer name or by a selectable customer type or simply list all customers. This creates a situation where I can have a where clause with 0, 1, or 2 entries in it depending on what the user wants to do.
The consequence of this that my where clause may be blank, it may have a clause or it may have two clauses with an AND joining them. The most basic (naive) way to handle this is to check for each control/clause whether the user has entered something and if so, to append “{Clause} AND ” to a string builder. Then, you snip off the last 5 characters to take care of the spurious “AND” that got appended. I think we’ve all seen this sort of thing before in some form or another.
But then, I got to thinking a bit, and realized that the problem I was facing here was really that I would have n clauses and would want n – 1 ANDs (except the special case of zero, where I would want zero ANDs). A clause is just a string and the ” AND ” is essentially a delimiter, so this is really a problem of having a collection of strings and a delimiter and wanting to jam them together with it. What I want is the opposite of String.Split().
And, as it turns out, the opposite of Split() is a static method on the string class called String.Join(), which takes an array of strings and a delimiter and does exactly what I need. In this fashion I can add clauses to an object as strings and then query the object for a well-formed WHERE clause. In its simplest incarnation, it would look like this:
public class WhereBuilder
{
private readonly List _clauses = new List();
public void Add(string clause)
{
_clauses.Add(clause);
}
public string GetFullWhereText()
{
return String.Join(" AND ", _clauses.ToArray());
}
}
You keep track of your various sub-clauses of the where in a list, and then join them together on the fly, when requested by consumer code. If you wanted to allow OR instead of AND, that’s pretty simple to support simultaneously:
public class WhereBuilder
{
private readonly List _clauses = new List();
public void Add(string clause)
{
_clauses.Add(clause);
}
public string GetConjunctionClause()
{
return Join(" AND ");
}
public string GetDisjunctionClause()
{
return Join(" OR ");
}
private string Join(string separator)
{
return String.Join(separator, _clauses.ToArray());
}
}
Of course, this is handy for constructing clauses that all have the same operator only, and it doesn’t do anything about getting rid of the annoyance of monotonously specifying operators in side of the various clauses, but my purpose here was to highlight the handiness of String.Join() for those who hadn’t seen it before.
Stay tuned if you’re interested in a more sophisticated where clause builder — I’ve been playing with that a bit in my spare time and will post back here with it if it gets interesting.
Built-in “join” is one of the things I love about Python and miss in Java. Related, I’ve found myself wanting some syntactic sugar for “first iteration” and “last iteration” in loops. I don’t know any language that has this, so it’s probably a bad idea on balance…
I don’t think that’s a bad idea at all – it seems like good practice to standardize something you do a lot. As for whether doing it a lot is a bad idea, I don’t know, but I don’t see any reason that it should be. It seems like more of a matter of opinion and convention. Why should it be the convention to be uniform in a loop as opposed to having some kind of notion of loop setup and teardown, so to speak..? Anyway, I’d be curious to see the java version of this syntactic sugar (a python… Read more »
This is a great example of a reason why I’ve always felt I wasn’t a great .Net developer. I’ve solved this exact problem a ton of times but in an embedded environment I would come up with some crafty way of doing it like you posted about. Unfortunately what that means is that now as a C# developer, I would tend to go back to those same habits. I had never seen or heard of this function used and it’s unfortunately one of thousands of classes or methods that I’ve never been exposed to which makes me feel like a… Read more »
Well, naive might have been a bit harsh (that was more meant as a reflection of how I felt when I discovered the method about the way I had previously done it). And, I wouldn’t let the richness of libraries in .NET or anywhere else get you down. Knowing them all in their nitty-gritty detail isn’t necessarily a badge of honor. I mean there are a lot of deprecated and goofy ones out there as well as helpful and code-saving ones. For me, the (admittedly imperfect) rule of thumb is that if I’m typing out code and find myself thinking… Read more »
[…] document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(po, s); })(); A while back, I made a post about using string.Join() to construct SQL where clauses from collections of individual clauses. In […]
It’s a type of problem we all face at some point of our careers and, of course, there are several possible solutions, limited only by the pre-existing architecture and developer’s imagination. I personally try NOT to face the concatenation problem in the first place, as I don’t like mixing Java and SQL code together. My solution of choice is to use – when possible of course – text templating technologies, which allows me to keep the two languages neatly separated. One trick I use often is to put a “where 1=1″ in my text template and then, based on the… Read more »
“Where 1=1” is a cool trick, thanks for that. And, yeah, I’d agree that it’s preferable not to hand write SQL in an OOP language when avoidable. When I wrote this post, I’d been working on a creaky legacy framework that sort of tried to be its own ORM.