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

User · Answer

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

User · Answer

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

User · 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 nbsp  mdash  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

User · Answer

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

User · Answer

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

User · Answer

You could also use MessageFormat too

[java] Correct way to use StringBuilder in SQL

Examples related to java

Examples related to string

Examples related to stringbuilder