Correct way to use StringBuilder in SQL

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.

[[ 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.

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.

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

You could also use MessageFormat too