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 nullConnection 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)

You should definitively take a look at SQLBuilder. It allows simple, yet complete, SQL generation using a very fluent API.

Going out on a limb here, have you considered iBatis? It's a real down to earth query mapping framework (I hesitate to call it an ORM framework somehow). You have to create XML files like this one:

<mapper namespace="org.mybatis.jpetstore.persistence.ProductMapper">      <cache />      <select id="getProduct" parameterType="string" resultType="Product">    SELECT      PRODUCTID,      NAME,      DESCN as description,      CATEGORY as categoryId    FROM PRODUCT    WHERE PRODUCTID = #{productId}  </select>   </mapper>

which wires up a mapper like this one:

public interface ProductMapper {  Product getProduct(String productId);}

Which allows you to access data from services like this:

  @Autowired  private ProductMapper productMapper;    public Product getProduct(String productId) {    return productMapper.getProduct(productId);  }

Which you can wire up with Spring:

<!-- enable autowire --><context:annotation-config /><!-- enable transaction demarcation with annotations --><tx:annotation-driven /><!-- define the SqlSessionFactory --><bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">    <property name="dataSource" ref="dataSource" />    <property name="typeAliasesPackage" value="org.mybatis.jpetstore.domain" /></bean><!-- scan for mappers and let them be autowired --><bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">    <property name="basePackage" value="org.mybatis.jpetstore.persistence" /></bean>

See also the full petstore example.

I'm not an uniquivocal fan of iBatis but it might fit your needs in this specific case.