Correct way to use StringBuilder in SQL
I just found some sql query build like this in my project:
return (new StringBuilder("select id1, " + " id2 " + " from " + " table")).toString();
Does this StringBuilder achieve its aim, i.e reducing memory usage?
I doubt that, because in the constructor the '+' (String concat operator) is used. Will that take the same amount of memory as using String like the code below? s I understood, it differs when using StringBuilder.append().
return "select id1, " + " id2 " + " from " + " table";
Are both statements equal in memory usage or not? Please clarify.
Thanks in advance!
Edit:
BTW, it is not my code. Found it in an old project. Also, the query is not so small as the one in my example. :)
Answer
The aim of using StringBuilder, i.e reducing memory. Is it achieved?
No, not at all. That code is not using StringBuilder correctly. (I think you've misquoted it, though; surely there aren't quotes around id2 and table?)
Note that the aim (usually) is to reduce memory churn rather than total memory used, to make life a bit easier on the garbage collector.
Will that take memory equal to using String like below?
No, it'll cause more memory churn than just the straight concat you quoted. (Until/unless the JVM optimizer sees that the explicit StringBuilder in the code is unnecessary and optimizes it out, if it can.)
If the author of that code wants to use StringBuilder (there are arguments for, but also against; see note at the end of this answer), better to do it properly (here I'm assuming there aren't actually quotes around id2 and table):
StringBuilder sb = new StringBuilder(some_appropriate_size);
sb.append("select id1, ");
sb.append(id2);
sb.append(" from ");
sb.append(table);
return sb.toString();
Note that I've listed some_appropriate_size in the StringBuilder constructor, so that it starts out with enough capacity for the full content we're going to append. The default size used if you don't specify one is 16 characters, which is usually too small and results in the StringBuilder having to do reallocations to make itself bigger (IIRC, in the Sun/Oracle JDK, it doubles itself [or more, if it knows it needs more to satisfy a specific append] each time it runs out of room).
You may have heard that string concatenation will use a StringBuilder under the covers if compiled with the Sun/Oracle compiler. This is true, it will use one StringBuilder for the overall expression. But it will use the default constructor, which means in the majority of cases, it will have to do a reallocation. It's easier to read, though. Note that this is not true of a series of concatenations. So for instance, this uses one StringBuilder:
return "prefix " + variable1 + " middle " + variable2 + " end";
It roughly translates to:
StringBuilder tmp = new StringBuilder(); // Using default 16 character size
tmp.append("prefix ");
tmp.append(variable1);
tmp.append(" middle ");
tmp.append(variable2);
tmp.append(" end");
return tmp.toString();
So that's okay, although the default constructor and subsequent reallocation(s) isn't ideal, the odds are it's good enough — and the concatenation is a lot more readable.
But that's only for a single expression. Multiple StringBuilders are used for this:
String s;
s = "prefix ";
s += variable1;
s += " middle ";
s += variable2;
s += " end";
return s;
That ends up becoming something like this:
String s;
StringBuilder tmp;
s = "prefix ";
tmp = new StringBuilder();
tmp.append(s);
tmp.append(variable1);
s = tmp.toString();
tmp = new StringBuilder();
tmp.append(s);
tmp.append(" middle ");
s = tmp.toString();
tmp = new StringBuilder();
tmp.append(s);
tmp.append(variable2);
s = tmp.toString();
tmp = new StringBuilder();
tmp.append(s);
tmp.append(" end");
s = tmp.toString();
return s;
...which is pretty ugly.
It's important to remember, though, that in all but a very few cases it doesn't matter and going with readability (which enhances maintainability) is preferred barring a specific performance issue.