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

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

Arnitaarno

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

@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. – Arnitaarno

I agree this is something IDEs should improve upon. – Perez

@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. – Arnitaarno

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. – Dieppe

@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. – Cubature

@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. – Arnitaarno

@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. – Cubature

@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. – Arnitaarno

@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? – Cubature

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

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

Answer 1

Querydsl and jOOQ are two popular choices.

Bucci

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 – Drawn

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. – Bucci

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. – Zaporozhye

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. – Condon

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

@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. – Condon

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. – Drawn

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. – Nightgown

@SvenJacobs very old comment, but to update, QueryDSL does allow building sql without code generation: #21616456 – Tjader

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

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

Promycelium

Do you use it? How do you find it? – Arnitaarno

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

"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 :( – Mottled

Answer 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

Repulse

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

DdlUtils is retired :-\ – Pennington

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

Fennell

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

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 ) – Scram

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

+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... – Identification

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 – Identification

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/… – Fennell

Answer 5

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

Nuncupative

this is more complicated than just writing the SQL by hand – Sense

@Sense 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. – Nuncupative