Cleanest Way to Build an SQL String in Java

Cleanest way to build an SQL string in Java

First of all consider using query parameters in prepared statements:

PreparedStatement stm = c.prepareStatement("UPDATE user_table SET name=? WHERE id=?");
stm.setString(1, "the name");
stm.setInt(2, 345);
stm.executeUpdate();

The other thing that can be done is to keep all queries in properties file. For example
in a queries.properties file can place the above query:

update_query=UPDATE user_table SET name=? WHERE id=?

Then with the help of a simple utility class:

public class Queries {

    private static final String propFileName = "queries.properties";
    private static Properties props;

    public static Properties getQueries() throws SQLException {
        InputStream is = 
            Queries.class.getResourceAsStream("/" + propFileName);
        if (is == null){
            throw new SQLException("Unable to load property file: " + propFileName);
        }
        //singleton
        if(props == null){
            props = new Properties();
            try {
                props.load(is);
            } catch (IOException e) {
                throw new SQLException("Unable to load property file: " + propFileName + "\n" + e.getMessage());
            }           
        }
        return props;
    }

    public static String getQuery(String query) throws SQLException{
        return getQueries().getProperty(query);
    }

}

you might use your queries as follows:

PreparedStatement stm = c.prepareStatement(Queries.getQuery("update_query"));

This is a rather simple solution, but works well.

Good way to generate SQL strings in java?

For arbitrary SQL, use jOOQ. jOOQ currently supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and MERGE. You can create SQL like this:

// Since you're not executing the SQL, set connection to null
Connection connection = null;
Factory create = new MySQLFactory(connection);
String sql1 = create.select(A, B, C)
                    .from(MY_TABLE)
                    .where(A.equal(5))
                    .and(B.greaterThan(8))
                    .getSQL();

String sql2 = create.insertInto(MY_TABLE)
                    .values(A, 1)
                    .values(B, 2)
                    .getSQL();

String sql3 = create.update(MY_TABLE)
                    .set(A, 1)
                    .set(B, 2)
                    .where(C.greaterThan(5))
                    .getSQL();

The supported syntax is quite rich. You will also find support for clauses such as ON DUPLICATE KEY UPDATE, FOR UPDATE, LOCK IN SHARE MODE, etc.

For more details, see

http://www.jooq.org

(Disclaimer, I work for the company behind jOOQ)

Correct way to use StringBuilder in SQL

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

Java - Avoiding long SQL query in code

I would put it in a file with an sql extension and implement Queries like:

Queries {
    static public String getQuery(String name) {
        return loadResource("/com/example/queries/" + name + ".sql");
    }
}

User:

conn.prepareStatement(Queries.getQuery("my_query"));

Of course that's only one way to do it. You can make Queries return Statement, or even use a dynamic proxy to mask it behind a simple Java interface (where proxy handler could create statement, set parameters and run query). Your mileage may vary.

Added benefit: sql files have syntax coloring and are way easier to maintain than Strings in Java.

What is the Best practice for writing a SQL query in JDBC

I suggest you use the standard JDBC way (or move the SQL to the database as a stored procedure if its complex enough and/or used in many parts of the project). I've written SQL statements using joins that went on for several pages and it will look butt-ugly using the DBDriver method. I suggest staying with the goal of making all your code easy to read over attempting any hard-to-read coding that may save you from typing a few more lines. A simliar argument goes for poorly structured code or ugly-ing up the code to achieve a minor performance gain.

Note the goal of a lot of SQL templates is to avoid writing boiler code over and over again (try/catch/finally/handle exceptions) for each SQL query. The example you provided doesn't do this. It only helps you build the sql statement.

I suggest you use a try/catch/finally block where you close the connection, preparedStatement, and resultSet in the finally block in the reverse order you create them (first checking to see if they are null before closing them).

And if an SQLException is thrown, catch it, add the primary key value of the record that caused the problem (for logging purposes) to the SQLException, and re-throw it.

---

Related Topics