Good way to generate SQL strings in java?

Question

I'm not looking for a persistence layer like Hibernate, I just want to generate SQL-strings and they should be compatible with PreparedStatement. I've tried libraries such as Squiggle, but it only supports SELECT, I would also like to generate insert and updates. An ideal usage would be something like:

generateInsertOn("myTable").addValue("value1").addValue("value2").generate();

that would generate this string:

"INSERT INTO myTable (value1, value2) VALUES(?, ?)"

I know that there exists questions that are a lot like mine, such as this, but they don't quite ask the same thing as I do.

Answer 1

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)

Answer 2

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.

Answer 3

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