Correct way to use StringBuilder in SQL

Question

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.

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

Rightly

SQL SECURITY: always use PreparedStatement or something similar: docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html – Pulsimeter

Apart from the memory usage thing, why not use a SQL builder library instead: https://mcmap.net/q/180806/-cleanest-way-to-build-an-sql-string-in-java/521799 – Villainy

Answer 1

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.

Ketose

Right, that's better. The use of the parameterless constructor is slightly unfortunate, but unlikely to be significant. I'd still use a single x + y + z expression rather than StringBuilder unless I had good reason to suspect it would be a significant issue. – Thunderpeal

@Crowder have one more doubt. StringBuilder sql = new StringBuilder(" XXX); sql.append("nndmn");.... Similar sql.append lines are around 60 lines. Is this fine? – Rightly

@Vanathi: ("Question", not "doubt" -- it's a common mistranslation.) It's fine but will probably result in multiple reallocations, because the StringBuilder will initially be allocated enough room for the string you passed the constructor plus 16 characters. So if you append more than 16 characters (I daresay you are, if there are 60 appends!), the StringBuilder will have to reallocate at least once and possibly many times. If you have a reasonable idea how big the end result will be (say, 400 characters), it's best to do sql = new StringBuilder(400); (or whatever) then do the appends. – Ketose

@Vanathi: Glad that helped. Yeah, if it's going to be 6,000 characters, telling StringBuilder that in advance will save about eight memory reallocations (assuming the initial string was about 10 characters, the SB would have been 26 to start with, then doubled to 52, then 104, 208, 416, 832, 1664, 3328, and finally 6656). Only significant if this is a hotspot, but still, if you know in advance... :-) – Ketose

@T.J. Crowder you mean to say i must not use the "+" operator for better performance. right? then why Oracal has added the "+" operator in their language can you please elaborate?any way my upvote for your answer. – Namedropper

I wonder why the implementers of Java decided to use a StringBuilder for that, rather than having String include a constructor of type String[]? For any number of arguments of any size, the sum total of the GC churn from the latter approach would be one array of references to strings. By contrast, using a StringBuilder guarantees a churn at least equal to the size of the resulting string. – Hornbeck

Answer 2

When you already have all the "pieces" you wish to append, there is no point in using StringBuilder at all. Using StringBuilder and string concatenation in the same call as per your sample code is even worse.

This would be better:

return "select id1, " + " id2 " + " from " + " table";

In this case, the string concatenation is actually happening at compile-time anyway, so it's equivalent to the even-simpler:

return "select id1, id2 from table";

Using new StringBuilder().append("select id1, ").append(" id2 ")....toString() will actually hinder performance in this case, because it forces the concatenation to be performed at execution time, instead of at compile time. Oops.

If the real code is building a SQL query by including values in the query, then that's another separate issue, which is that you should be using parameterized queries, specifying the values in the parameters rather than in the SQL.

I have an article on String / StringBuffer which I wrote a while ago - before StringBuilder came along. The principles apply to StringBuilder in the same way though.

Thunderpeal

Answer 3

[[ There are some good answers here but I find that they still are lacking a bit of information. ]]

return (new StringBuilder("select id1, " + " id2 " + " from " + " table"))
     .toString();

So as you point out, the example you give is a simplistic but let's analyze it anyway. What happens here is the compiler actually does the + work here because "select id1, " + " id2 " + " from " + " table" are all constants. So this turns into:

return new StringBuilder("select id1,  id2  from  table").toString();

In this case, obviously, there is no point in using StringBuilder. You might as well do:

// the compiler combines these constant strings
return "select id1, " + " id2 " + " from " + " table";

However, even if you were appending any fields or other non-constants then the compiler would use an internal StringBuilder -- there's no need for you to define one:

// an internal StringBuilder is used here
return "select id1, " + fieldName + " from " + tableName;

Under the covers, this turns into code that is approximately equivalent to:

StringBuilder sb = new StringBuilder("select id1, ");
sb.append(fieldName).append(" from ").append(tableName);
return sb.toString();

Really the only time you need to use StringBuilder directly is when you have conditional code. For example, code that looks like the following is desperate for a StringBuilder:

// 1 StringBuilder used in this line
String query = "select id1, " + fieldName + " from " + tableName;
if (where != null) {
   // another StringBuilder used here
   query += ' ' + where;
}

The + in the first line uses one StringBuilder instance. Then the += uses another StringBuilder instance. It is more efficient to do:

// choose a good starting size to lower chances of reallocation
StringBuilder sb = new StringBuilder(64);
sb.append("select id1, ").append(fieldName).append(" from ").append(tableName);
// conditional code
if (where != null) {
   sb.append(' ').append(where);
}
return sb.toString();

Another time that I use a StringBuilder is when I'm building a string from a number of method calls. Then I can create methods that take a StringBuilder argument:

private void addWhere(StringBuilder sb) {
   if (where != null) {
      sb.append(' ').append(where);
   }
}

When you are using a StringBuilder, you should watch for any usage of + at the same time:

sb.append("select " + fieldName);

That + will cause another internal StringBuilder to be created. This should of course be:

sb.append("select ").append(fieldName);

Lastly, as @T.J.rowder points out, you should always make a guess at the size of the StringBuilder. This will save on the number of char[] objects created when growing the size of the internal buffer.

Eradis

Answer 4

You are correct in guessing that the aim of using string builder is not achieved, at least not to its full extent.

However, when the compiler sees the expression "select id1, " + " id2 " + " from " + " table" it emits code which actually creates a StringBuilder behind the scenes and appends to it, so the end result is not that bad afterall.

But of course anyone looking at that code is bound to think that it is kind of retarded.

Pomegranate

Answer 5

In the code you have posted there would be no advantages, as you are misusing the StringBuilder. You build the same String in both cases. Using StringBuilder you can avoid the + operation on Strings using the append method. You should use it this way:

return new StringBuilder("select id1, ").append(" id2 ").append(" from ").append(" table").toString();

In Java, the String type is an inmutable sequence of characters, so when you add two Strings the VM creates a new String value with both operands concatenated.

StringBuilder provides a mutable sequence of characters, which you can use to concat different values or variables without creating new String objects, and so it can sometimes be more efficient than working with strings

This provides some useful features, as changing the content of a char sequence passed as parameter inside another method, which you can't do with Strings.

private void addWhereClause(StringBuilder sql, String column, String value) {
   //WARNING: only as an example, never append directly a value to a SQL String, or you'll be exposed to SQL Injection
   sql.append(" where ").append(column).append(" = ").append(value);
}

More info at http://docs.oracle.com/javase/tutorial/java/data/buffers.html

Entrammel

No, you shouldn't. It's less readable than using +, which will be converted into the same code anyway. StringBuilder is useful when you can't perform all the concatenation in a single expression, but not in this case. – Thunderpeal

I understand that the string in the question is posted as an example. It would make no sense to build a "fixed" string like this neither with StringBuilder nor adding different fragments, as you could just define it in a single constant "select id1, id2 from table" – Entrammel

But even if there were non-constant values from variables, it would still use a single StringBuilder if you were to use return "select id1, " + foo + "something else" + bar; - so why not do that? The question provides no indication that anything needs to pass the StringBuilder around. – Thunderpeal

Answer 6

You could also use MessageFormat too

Gitt