[java] Correct way to use StringBuilder in SQL

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

Examples related to java

Under what circumstances can I call findViewById with an Options Menu / Action Bar item? How much should a function trust another function How to implement a simple scenario the OO way Two constructors How do I get some variable from another class in Java? this in equals method How to split a string in two and store it in a field How to do perspective fixing? String index out of range: 4 My eclipse won't open, i download the bundle pack it keeps saying error log

Examples related to string

How to split a string in two and store it in a field String method cannot be found in a main class method Kotlin - How to correctly concatenate a String Replacing a character from a certain index Remove quotes from String in Python Detect whether a Python string is a number or a letter How does String substring work in Swift How does String.Index work in Swift swift 3.0 Data to String? How to parse JSON string in Typescript

Examples related to stringbuilder

Best way to remove the last character from a string built with stringbuilder What is the simplest way to write the contents of a StringBuilder to a text file in .NET 1.1? How to append a newline to StringBuilder Correct way to use StringBuilder in SQL java: use StringBuilder to insert at the beginning How can I clear or empty a StringBuilder? Remove last character of a StringBuilder? Difference between string and StringBuilder in C# String, StringBuffer, and StringBuilder Newline character in StringBuilder