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