Dealing with ugly SQL in Java

Question

Here goes a SQL-Java coding style question...

How do others here deal with creating complex custom queries in Java cleanly?

I am speaking of the seemingly simple task of preparing a string which is the SQL statement to be executed.

I am aware of HQL, and also of stored procedures, but honestly I don't really love these solutions. Perhaps I can be convinced differently. Stored procedures are annoying to deploy/maintain, and parsing performance is not such a huge issue in my case -- flexibility takes precedence. HQL seems like a big leap, and has some limitations for my complex queries.

TO be clear, I am talking of super-ugly looking code like this:

    return 
        "(" + topTwenty + ")" +
        "UNION " +
        "(" + twentyBeforeMe + ")" +
        "UNION " +
        "(" + meDummyQuery + ")" +
        "UNION " +
        "(" + twentyAfterMe + ")";

Where the variables topTwenty are for example also subqueries created similarly.

I never thought I'd say this, but it was cleaner in PHP which had the multi-line string and $variable embedding in the strings.

Do people ever use a trivial templating library? How do you neatly keep the strings in the program? Or do you put them in a separate file (also seems annoying to me somehow).

Answer 1

You probably want to look at parameterizing your queries which you can do with JDBC PreparedStatements. If you're coming from the PHP world, it's analogous to PDO. Basically. all your dynamic $variables from the PHP string you alluded to above, would be ?s in a parameterized query. The ?s get substituted in a safe (read proper escaping) way by methods of the PreparedStatement implementation.

See Sun's PreparedStatement tutorial for examples and further explanation.

Answer 2

Java has PrintFormat class which does very similar thing as sprintf in C, you might want to leverage it to format your query.

Following code snippet is taken from http://java.sun.com/developer/technicalArticles/Programming/sprintf/

System.out.println(
new PrintfFormat("Pre %s Post%%").sprintf("target")
);

Answer 3

Without going into database abstraction, you could probably make your queries more readable with a PreparedStatment -- not to mention it contributes to security.

Answer 4

http://code.google.com/p/sqlck/, this is my project by the way.

Answer 5

You could try using StringBuilder.append() if you think that will make your code look cleaner:

return new StringBuilder()
   .append("(").append(topTwenty).append(")")
   .toString();

Answer 6

What I do is to use String.format for table names and things that can't be parametrized, and this class(NamedPreparedStament), which allows to use good names for bind variables instead of question marks.

String sql = "SELECT id FROM %s WHERE id > :lastInsertedYesterday ";
NamedParameterStatement p = new NamedParameterStatement(con, 
                                    String.format(sql, "table1"));
p.setInt("lastInsertedYesterday", lastOne);