Cleanest way to build an SQL string in Java

Question

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.

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

String sql1 = DSL.using(SQLDialect.MYSQL)  
                 .select(A, B, C)
                 .from(MY_TABLE)
                 .where(A.equal(5))
                 .and(B.greaterThan(8))
                 .getSQL();

String sql2 = DSL.using(SQLDialect.MYSQL)  
                 .insertInto(MY_TABLE)
                 .values(A, 1)
                 .values(B, 2)
                 .getSQL();

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

Instead of obtaining the SQL string, you could also just execute it, using jOOQ. See

http://www.jooq.org

(Disclaimer: I work for the company behind jOOQ)

One technology you should consider is SQLJ - a way to embed SQL statements directly in Java. As a simple example, you might have the following in a file called TestQueries.sqlj:

public class TestQueries
{
    public String getUsername(int id)
    {
        String username;
        #sql
        {
            select username into :username
            from users
            where pkey = :id
        };
        return username;
    }
}

There is an additional precompile step which takes your .sqlj files and translates them into pure Java - in short, it looks for the special blocks delimited with

#sql
{
    ...
}

and turns them into JDBC calls. There are several key benefits to using SQLJ:

• completely abstracts away the JDBC layer - programmers only need to think about Java and SQL
• the translator can be made to check your queries for syntax etc. against the database at compile time
• ability to directly bind Java variables in queries using the ":" prefix

There are implementations of the translator around for most of the major database vendors, so you should be able to find everything you need easily.

I am wondering if you are after something like Squiggle. Also something very useful is jDBI. It won't help you with the queries though.

I would have a look at Spring JDBC. I use it whenever I need to execute SQLs programatically. Example:

int countOfActorsNamedJoe
    = jdbcTemplate.queryForInt("select count(0) from t_actors where first_name = ?", new Object[]{"Joe"});

It's really great for any kind of sql execution, especially querying; it will help you map resultsets to objects, without adding the complexity of a complete ORM.

I tend to use Spring's Named JDBC Parameters so I can write a standard string like "select * from blah where colX=':someValue'"; I think that's pretty readable.

An alternative would be to supply the string in a separate .sql file and read the contents in using a utility method.

Oh, also worth having a look at Squill: https://squill.dev.java.net/docs/tutorial.html