4

I want to generate a string such as sql command: 

   "INSERT INTO xxx VALUES(XXX, XXX, XXX)"

currently I use StringBuilder and some String constant like "INSERT INTO" to concatenate input String parameters for the table name and inserted values.

However, other than performance issue, this plain concatenation looks not elegant. Is there any other way of doing this?

In my opinion, JDBC's prepared statement is one good example of such a "command template":

PreparedStatement pstmt=connection.createPreparedStatement("INSERT INTO ? VALUES(?,?,?)");

then you can set the table name and inserted value.

pstmt.setString(1,"tableA");
pstmt.setInt(2, 100);
...

However, I can not use prepared statement, since what I want is just String...

And someone give me some hint to use java.util.Regex or JavaCC to produce the String. But as far as I can see, whatever is chosen for some code elegancy issue, Java String must be generated by something like StringBuilder, right???

Harry Joy
  • 58,650
  • 30
  • 162
  • 207
rayeaster
  • 261
  • 4
  • 16
  • 1
    Beware that generating SQL statements by concatenating strings and variable values makes your program vulnerable to SQL injection attacks; you need to properly escape the values yourself to prevent this. (PreparedStatement does that automatically for you). – Jesper Apr 27 '11 at 12:46

5 Answers5

13

You could use String.format():

String.format("insert into %s values('%s', '%s', '%s')", "user", "user123", "pass123", "yellow");

It's worth noting though, that any of these "string building" techniques leave you vulnerable to SQL injection attacks. You should really use JDBC parameterised queries wherever possible.

Edited to add quotes around strings.

dty
  • 18,795
  • 6
  • 56
  • 82
  • what about if the number of values to be inserted is varying greatly and not known in advance?Then I still need to write the format each time I call this method... – rayeaster Apr 27 '11 at 12:26
  • the downvote is probably because that SQL would not work - missing single quotes for that sample: `"insert into %s values('%s', '%s', '%s')"`. I did not downvote because that is not directly what was asked, not relevant... – user85421 Apr 27 '11 at 12:28
  • Good point Carlos, thank you. I've edited accordingly. Not worthy of a downvote though, and whoever did downvote hasn't spoken up yet. – dty Apr 27 '11 at 13:10
  • downvote was for recommending code wide open to SQL injections. You mentioned it in the text, still, why encouraging the OP? – Jochen Bedersdorfer May 13 '11 at 04:40
  • Hmm... See, I'd downvote for answering a different question. "How do I make this code tidier?" "Here's how to protect from SQL Injection attacks". Hey ho. You appear not to have downvoted all the other answers which give variations on the same theme. – dty May 13 '11 at 13:43
4

Maybe you are looking for java.text.MessageFormat

 int planet = 7;
 String event = "a disturbance in the Force";

 String result = MessageFormat.format(
     "At {1,time} on {1,date}, there was {2} on planet {0,number,integer}.",
     planet, new Date(), event);
Lukasz
  • 7,572
  • 4
  • 41
  • 50
  • One thing to take into account is that this works well when the query is static, if the query is more dynamic, for instance a select statement that has different where predicates depending of runtime variables, then this approach may become less ellegant, because the variables on the message format will be the predicate statements instead of the actual parameter. – Edwin Dalorzo Apr 27 '11 at 12:07
  • Baran I know, I am not questioning your approach, I actually upvoted it. I am just pointing this out in case it matters since it is not clear in the question. – Edwin Dalorzo Apr 27 '11 at 12:16
2

Have you tried just using '+' ?

String sql = "INSERT INTO " + table
           +" VALUES(" + value1 + ", " + value2 + ", " = value3+")";
Peter Lawrey
  • 525,659
  • 79
  • 751
  • 1,130
  • 1
    It actually uses StringBuilder, its shorter and clear for simple cases. You need to explain what you mean by inferior. – Peter Lawrey Apr 27 '11 at 12:59
  • I use "inferior" because plain "+" will decrease performance than StringBuilder...But as you mentioned, "+" actually use StringBuidler...I am not sure. – rayeaster Apr 27 '11 at 13:02
  • 1
    StringBuilder is more efficient than String using operators like `+=`, however if you use plain `+` and build only one string, it is just as efficient (it can be more efficient if the compiler can combine string literals) – Peter Lawrey Apr 27 '11 at 13:04
  • 3
    In any case, it will be > 100x faster than the simplest of SQL updates, so you shouldn't really be worrying about it. Just write code which is as clear as possible. – Peter Lawrey Apr 27 '11 at 13:05
  • 4
    The cost of building the string is almost certainly completely irrelevant in comparison to the cost of the SQL query itself (network operation, disk operations, etc.) If this is a primary concern, then I suggest this is premature optimisation. – dty Apr 27 '11 at 13:09
  • 1
    This answer here is actually how I am building my strings for a program I have running and inserting 60,000 rows into a mysql db table every day, even if this isn't the "most efficient" (which I doubt there is a much more cpu efficient way) dty is right, it is a pointless optimization considering how much network overhead is going to play a part in this. – keepitreall89 Apr 27 '11 at 13:41
2

Given the variety of other answers and none of them met your approval, perhaps you should accept that the actual String generation (sans JPA, PreparedStatement, etc.) is going to be fairly inelegant and create a utility class with static sql generators.

edit Showing an example of how I'd go about this if a pre-existing class such as PreparedStatement weren't an option. It's not the most elegant, but it does what it's supposed to (assuming I typed it all in correctly).

public class SQLUtil {
    public static String generateInsertSQL(String tableName, List<CustomParameter> parmList){
        StringBuilder sb = new Stringbuilder();
        sb.append("insert into ");
        sb.append(tableName);
        sb.append(" values (");
        for (int i = 0; i < parmList.size(); i++){
            customParameter parm = parmList.get(i);
            switch (parm.getType()) { // enum with your desired sql types
                case ParmTypes.String:
                    sb.append("'");
                    sb.append(StringEscapeUtils.escapeSql(String.valueOf(parm.getValue())));
                    sb.append("'");
                    break;
                case ParmTypes.Integer:
                    sb.append(Integer.valueOf(parm.getValue()));
                    break;
            }
            if (i < parmList.size() - 1) sb.append(",");
        }
        sb.append(")");
        return sb.toString();
    }
}

This way, your business code will remain relatively elegant and you can play around with the SQL String generation to your heart's content. You can also use this to "guarantee" all your inserts are protected against such attacks as SQL injection.

Riggy
  • 1,347
  • 1
  • 14
  • 26
  • This is correct. But what I want is just the actual String generation. Whether you put generation in such a util class or not is not my biggest concern right now. Thank you – rayeaster Apr 27 '11 at 12:49
  • +1 for "you should accept that the actual String generation is going to be fairly inelegant" and moving the ugliness away from the business code into a utility. – Thilo Apr 27 '11 at 12:57
  • I expanded my example to show actual code. It involves creating an enum to track your parameter types and a class called `CustomParameter` which in my mind just has fields to hold the type (the enum) and an `Object` for the value. Unfortunately, this means wrapping your primitive types, but I thought this the cleanest way to go given the restrictions you're proposing. – Riggy Apr 27 '11 at 13:13
  • As I'm thinking about this, you may want to add a field to `CustomParameter` to include the column name. That way you can alter your insert to only add certain columns instead of all columns. – Riggy Apr 27 '11 at 13:25
0

Use StringTemplate (http://www.stringtemplate.org/) maybe a good choice:

This looks better, right?

StringTemplate insert = new StringTemplate("INSERT $table$ VALUES ($value; separator=\",\"$)");
insert.setAttribute("table", "aTable");
String[] values = {"1", "1", "'aaa'", "'bbb'"};
for(int i = 0;i < values.length;i++){   
  insert.setAttribute("value", values[i]);  
}
System.out.println(insert.toString());
rayeaster
  • 261
  • 4
  • 16