[java] Is there any good dynamic SQL builder library in Java?

Anyone knows some good SQL builder library for Java like Squiggle (not maintained anymore it seems). Preferably, a project in active development.

Preferably with syntax like Zend_Db_Select, something that will allow to make a query like

String query = db.select().from('products').order('product_id');

I can recommend jOOQ. It provides a lot of great features, also a intuitive DSL for SQL and a extremly customable reverse-engineering approach.

jOOQ effectively combines complex SQL, typesafety, source code generation, active records, stored procedures, advanced data types, and Java in a fluent, intuitive DSL.

Hibernate Criteria API (not plain SQL though, but very powerful and in active development):

List sales = session.createCriteria(Sale.class)
         .add(Expression.ge("date",startDate);
         .add(Expression.le("date",endDate);
         .addOrder( Order.asc("date") )
         .setFirstResult(0)
         .setMaxResults(10)
         .list();

You can use the following library:

https://github.com/pnowy/NativeCriteria

The library is built on the top of the Hibernate "create sql query" so it supports all databases supported by Hibernate (the Hibernate session and JPA providers are supported). The builder patter is available and so on (object mappers, result mappers).

You can find the examples on github page, the library is available at Maven central of course.

NativeCriteria c = new NativeCriteria(new HibernateQueryProvider(hibernateSession), "table_name", "alias");
c.addJoin(NativeExps.innerJoin("table_name_to_join", "alias2", "alias.left_column", "alias2.right_column"));
c.setProjection(NativeExps.projection().addProjection(Lists.newArrayList("alias.table_column","alias2.table_column")));

ddlutils is my best choice:http://db.apache.org/ddlutils/api/org/apache/ddlutils/platform/SqlBuilder.html

here is create example(groovy):

Platform platform  = PlatformFactory.createNewPlatformInstance("oracle");//db2,...
//create schema    
def db =        new Database();
def t = new Table(name:"t1",description:"XXX");
def col1 = new Column(primaryKey:true,name:"id",type:"bigint",required:true);
t.addColumn(col1);
t.addColumn(new Column(name:"c2",type:"DECIMAL",size:"8,2"));
t.addColumn( new Column(name:"c3",type:"varchar"));
t.addColumn(new Column(name:"c4",type:"TIMESTAMP",description:"date"));        
db.addTable(t);
println platform.getCreateModelSql(db, false, false)

//you can read Table Object from  platform.readModelFromDatabase(....)
def sqlbuilder = platform.getSqlBuilder();
println "insert:"+sqlbuilder.getInsertSql(t,["id":1,c2:3],false);
println "update:"+sqlbuilder.getUpdateSql(t,["id":1,c2:3],false);
println "delete:"+sqlbuilder.getDeleteSql(t,["id":1,c2:3],false);
//http://db.apache.org/ddlutils/database-support.html