Very slick. My only suggestion is to allow a bit more terseness by adding static versions of all the calls that can happen at the beginning of the Query. The static versions would just create a new SQL object and pass the call on to the SQL object's call.
Instead of:
new SQL().SELECT_ALL()
You could have Query.SELECT_ALL()
Ideally Java would be cool with having Static and non-Static methods with identical signatures and names, but alas, looks like a static class Query or whatever would be the way to do this.
I think jOOQ is a little more heavyweight. It introspects your database and builds a Java class structure that reflects the schema. More typesafe, certainly, but more of a commitment.
This is opinion only, so please don't take this as me being negative: I really like the idea, but there's one thing which need to happen for this to be upstream.
Most decent IDE's (Idea and few others) are clever enough to understand the SQL between quotation marks and if connected to database will give you instant errors if something is incorrect - misspelled names, missing commas, etc. Sometimes this can be a big time saver.
I wrote something like this for C# just yesterday as I couldn't find an sql builder with a real fluent interface. I found one which pretended to be fluent by returning itself after each method call, but that completely misses the point, because you want each call to create a mutation but leave the original in tact. That allows me to build a query using all of the WHEREs and then branch it off into two separate queries.. one for COUNT and one to select the actual data.
Just wondering.. did you create this to be used in the read layer of a DDD/CQS based system?
Edit: actually looking at the source it appears this one is similar to the one i saw in C# where each call just returns the same builder rather than a new version
is anyone wants something similar for c, i am looking for beta users (users that are happy with a beta release, not users less impressive than alpha users...) of c-orm, which includes (a very minimal version of) this - https://bitbucket.org/isti/c-orm/wiki/SQL#markdown-header-co...
I don't like this. I'm strongly of the opinion that SQL is a beautiful language, able to express in terse yet readable statements operations which would take many pages of Java to execute. Why try and wrap it up, and pretend that you are writing Java, when you're still writing SQL?
Please don't take this as a dig at you or your code - everyone has an itch to scratch, and I'm quite sure writing this was fun.
SQL as a string is not composable. I cannot give you a small query and have you arbitrarily join against it - I have to give you bits of strings and you have to glue them together in the right way. Expressing SQL as its AST lets you write combinators between ASTs, and thus get at least some composability. The stronger your type system, the better guarantees you can get that the composition is sound (closure).
> Why try and wrap it up, and pretend that you are writing Java, when you're still writing SQL?
That's not what query builders (/expression languages) do. They just express the SQL in the host language to allow for compositions and the like.
Because like it or not, expressing SQL as a string is a pain in the ass to compose, you can't easily build a query incrementally.
With a query builder, you can. You're still writing SQL, it's not an object layer/ORM and it has (should have) a fairly direct mapping to SQL. You're just writing SQL in a way which fits better in the host languages.
Oh, it can also provide some measure of database-independence by having database-specific backend take care to handle specific incompatibilities between databases.
For instance for string concatenation[0] the standard is `||` which works in SQLite, Postgres and Oracle, MSSQL uses `+` instead of `||` and in MySQL `||` is the boolean OR unless you've activated a specific sql mode so you've got no choice but to use CONCAT (you could accept a performance hit and use CONCAT everywhere, except Oracle only supports an arity of 2 and SQLite doesn't support this function at all).
That's the kind of stuff a builder can handle for you under the hood, providing much improved cross-platform compatibility.
SQL is a beautiful language, indeed. Writing vendor-specific SQL in stored procedures and views is great. But embedding SQL in other languages such as Java is still very hard in 2013.
Being a DSL with an AST, advanced SQL transformation can be implemented to standardise SQL where common SQL constructs aren't supported. These include:
Manipulating string in java/IDE is a bit odd. I wrote the code in order to get away with the awkward strings manipulation in java. Plus I have this generator tools which generates list of schema-tables and table-columns combinations. All of my SQL code is check at dev time by the power of IDE static analyzer. So I don't have to deal with misspelled columns,tables anymore at runtime.
Do you have an example of this static analysis of table/column names? From what I see in your documentation, these things are string-based...? How can they be statically analysed?
One advantage is that the compiler checks your queries. In case of the jOOQ library, it also makes your queries typesafe and abstracts away some specifics of SQL dialects.
Having typesafety transcend from the database to your "main" language is of advantage so long as you are using a statically typed language anyway.
All this is possible while maintaining much of the expressive power of SQL.
I'm with you on the type checking (although this particular library doesn't seem to offer much in that space). That said, I'd argue that modern tooling (e.g. IDEA, and by association the static analysis toolchain in Teamcity) are capable of identifying and validating embedded SQL, particularly when provided with database metadata.
Essentially, I'm deeply uneasy about embedding translators from one language into another. As an example, not many people like using BigDecimal to describe arithmetic operations, despite it having a fluent syntax and improved type safety (e.g. around implicit conversions) compared to just writing an expression.
I guess it's a matter of taste, but it will certainly resolve ambiguities around Java's reserved words "case", "else", and "for".
A future version of jOOQ will ship with an API generator that can generate one or the other version of the DSL. Given that jOOQ is based on a somewhat formal BNF, such an evolution won't be too hard to implement.
The advantage here is language integration. You can write a query, wrap it in an object, add clauses to it dynamically, combine it with another query using some clever logic, and so on.
That's probably the only place where I would use it, when I have to construct a query on the fly based on various conditions. For a plain static query I think the regular SQL is much easier to read and understand (let alone paste into a query window to test its output).
Instead of:
You could have Query.SELECT_ALL()Ideally Java would be cool with having Static and non-Static methods with identical signatures and names, but alas, looks like a static class Query or whatever would be the way to do this.