Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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. :)

like image 404
Vaandu Avatar asked Jan 04 '12 11:01

Vaandu


People also ask

When should you use the operator and when should you use StringBuilder?

The + operator uses public String concat(String str) internally. This method copies the characters of the two strings, so it has memory requirements and runtime complexity proportional to the length of the two strings. StringBuilder works more efficent.

How do I string a StringBuilder?

The append() method of the StringBuilder class accepts a String value and adds it to the current object. To convert a String value to StringBuilder object just append it using the append() method.

How is StringBuilder more efficient?

StringBuilder is efficient in the first example because it acts as a container for the intermediate result without having to copy that result each time - when there's no intermediate result anyway, it has no advantage.


Video Answer


1 Answers

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.

like image 170
T.J. Crowder Avatar answered Oct 23 '22 19:10

T.J. Crowder