Is there any good dynamic SQL builder library in Java?

Question

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');

Solution 1

Querydsl and jOOQ are two popular choices.

Solution 2

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.

Solution 3

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

Solution 4

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();

View more solutions

Comments

[Itay Moav -Malimovka]

May I ask what is the advantage of the syntax above against "SELECT f1..fn FROM products ORDER BY product_id" ?

[Vladislav Rastrusny]

@ItayMoav-Malimovka, Well, at least the syntax of SQL query in my case (if we take JOOQ as an example) is checked at the time you write code. You have full syntax autocomplete which speeds up your query-writing and makes it more error-prone.

[Itay Moav -Malimovka]

I agree this is something IDEs should improve upon.

[Vladislav Rastrusny]

@ItayMoav-Malimovka, well... in the case of JOOQ, if I change something in my DB structure, my code will just stop compiling until I fix it according to new DB structure. If you have queries as a text, they will leave broken.

[Rafael Winterhalter]

As an example: I am currently working on an application that needs to create statements to work on a massive legacy database. Many statements share custom constraints which we build by a SQL DSL. Thanks to that we can easily create statements that are not known at compile time.

[phoenix]

@FractalizeR Can you help me understand whether jooq works with databases known only at runtime? I would like to use it if possible in a web application that will be deployed. But the databases are known only at runtime.

[Vladislav Rastrusny]

@BRS Well, yes, as far as I remember. JOOQ needs to examine your DB structure to be able to help you construct queries against it.

[phoenix]

@FractalizeR Thanks. But all the examples I see need compile time configuration to generate meta classes and put them in class path. Please Correct me if I am wrong.

[Vladislav Rastrusny]

@BRS Yes, you are correct. Generally this is fine. If you have too dynamic database, it would be probably better for you to use some own DSL query builder.

[phoenix]

@FractalizeR Fortunately I had completed the algorithm that is required to produce joins. Now I have a list of strings representing columns and another list of strings representing tables to be joined. I have another data structure representing ON conditions. Basically strings. Is there any example that I could use to build SQL with some library? With dialect sensitivity?

[Vladislav Rastrusny]

@BRS: you can use Jooq as a simple query builder as I can see: jooq.org/doc/3.5/manual-single-page/#getting-started

[Tomáš Fejfar]

I think github.com/alexfu/SQLiteQueryBuilder is almost exactly what you are looking for.

[Vladislav Rastrusny]

The problem is that it doesn't map to SQL as I understand, right?

[Admin]

this doesn't generate SQL and is a nightmare to debug when it doesn't follow the rule of least astonishment ( doesn't work as expected )

[Vladislav Rastrusny]

Do you use it? How do you find it?

[Vladimir Dyuzhev]

It does generate SQL (at the end) and it surprises no one. Benefit -- it is portable across databases.

[Lukas Eder]

+1 Upvoted again to level up. I don't know why this was downvoted. After all, it's become the standard way of doing this with JPA, even if it's a bit verbose...

[Lukas Eder]

What is the level of query complexity that you achieved to reach with the JPA Criteria API, without making the query utterly unreadable? Do you have an example of a nested select in an IN / EXISTS clause, or of a self-join using aliases for the Sale entity, etc? I'm curious

[Timo Westkämper]

JOOQ is maybe a better choice for hardcore SQL development, but Querydsl has a simpler API and supports also other backends (JPA, JDO, Lucene, Mongodb etc.); I am also in the company behind Querydsl

[Christopher Klewes]

I use it to generate custom source code from DDL. It's working great!

[ponzao]

We use Querydsl SQL in a few of our in house projects. I have no personal experience of jooq but I have heard that it is quite ok.

[Vladimir Dyuzhev]

Comments do not give much space to provide the examples, but you're welcome to review them at docs.jboss.org/hibernate/core/3.5/reference/en/html/…

[Gili]

I do hardcore SQL development. I've used both JOOQ and QueryDSL. Both are quite good but I prefer the latter. The code and project management is more mature.

[Abhinav Sarkar]

The problem with QueryDsl is that you can't use it as a pure query generator as it does not give you the generated query itself. It will generate the query and execute it for you too. You can't get one without another.

[Timo Westkämper]

@abhinav-sarkar Querydsl supports that, just not as directly as JOOQ.

[Abhinav Sarkar]

@TimoWestkämper can you point me to the methods to do that? I went through the javadocs and the code but couldn't find anything.

[Timo Westkämper]

You can for example describe the query as a subquery and then serialize it via the SQLSerializer querydsl.com/static/querydsl/2.9.0/apidocs/com/mysema/query/‌​sql/… If you want a more convenient way to get the SQL String and bindings for a Querydsl SQL query, just create a ticket for it and provide a suggestion how it should/could look like.

[Sven Jacobs]

Querydsl and jOOQ seem to be the most popular and mature choices however there's one thing to be aware of: Both rely on the concept of code generation, where meta classes are generated for database tables and fields. This facilitates a nice, clean DSL but it faces a problem when trying to create queries for databases that are only known at runtime, like in the OP's example above. While jOOQ supports a String based approached there are some quirks. The documentation of Querydsl doesn't mention whether it's possible to not use code generation. Please correct me if I'm wrong.

[Nagaraj Tantri]

@SvenJacobs very old comment, but to update, QueryDSL does allow building sql without code generation: stackoverflow.com/questions/21615956/…

[ponzao]

@NagarajTantri, the accepted answer to that is mine as well :)

[Tomáš Fejfar]

"Although the jOOQ library has a great API for building SQL statements, it comes with an entire suite of tools to build statements, connect to databases, write/read models to/from databases, etc. Due to the current nature of Androids application VM, there is a 64k method reference limit. jOOQ can contain over 10,000 referenced methods when in use. This may not seem like much in comparison to the limit, but if you consider other large libraries commonly used (such as Guava and Google Play Services), hitting that 64k limit becomes much easier." -- android-arsenal.com/details/1/3202 :(

[huuthang]

I have to define column again although I already defined them in @Entity, so pain.

[EpicPandaForce]

this is more complicated than just writing the SQL by hand

[Przemek Nowak]

@EpicPandaForce I agree for very simple cases but the concatenation is much complicated when you need to concat that string based on very complicated conditions where there is a different set of this conditions. The correctly concatenated string then (like all the adds, joins, having, parameters set, etc.) is a pain. With the solution you have a builder which will deal with this complexity for you.

[Stephan]

DdlUtils is retired :-\