Since the framework is still much in its infancy, I urge you to download, trial it and comment on it in the forums.
Dynamic Query Builder
The role of dynamic query builder is to assist you in creating dynamic sql queries.Creating sql queries using raw string concatenation is a sure way into the flames of hell,
as logic about correctly concatenating string, adding commas etc. become intermixed with the actual SQL to express.
The underlying idea in the SQLOrm query builder is that generating queries should be done using a fluent interfaces and usability and simplicity is of atmost importance. The main problems when generating dynamic queries, is to ensure the right structure (e.g. SELECT..FROM..WHERE) and that commas are evenly distributed. Further more, if more than one table are joined, but the list of tables are dynamic based on input variables, you will need quite a lot of helper variables to ensure that you have added the correct tables and only added each once.
Let us make things concrete with an example. Below is shown a method which generates a dynamic sql expression based on the arguments given.
private String getPersonAndmaybePet_JavaStr(boolean personName, boolean isAdult, int[] petTypes) {
StringBuilder sb = new StringBuilder();
// do SELECT
sb.append("SELECT ");
if(personName) {
sb.append("person.name,");
}
if(petTypes != null) {
sb.append("pet.*,");
}
// do FROM
sb.delete(sb.length() - 1, sb.length()); // remove last comma
sb.append(" FROM person,");
if(petTypes != null) {
sb.append(" Pet,");
}
// do WHERE
sb.delete(sb.length() - 1, sb.length()); // remove last comma
sb.append(" WHERE ");
if(isAdult) {
sb.append(" person.age >= 18");
}
else {
sb.append(" person.age < 18");
}
if(petTypes != null) {
StringBuilder commaList = new StringBuilder();
for(int id : petTypes) {
commaList.append(id);
commaList.append(",");
}
commaList.delete(commaList.length() - 1, commaList.length());
sb.append(" AND person.pet_id = pet.id AND pet.pettype IN (" + commaList.toString() + ")");
}
return sb.toString();
}
There are a number of problems with this code. It is very difficult to read and understand. It is difficult to maintain, as implicitly, the code contains logic concerning the placement of command and AND clauses. Finally, the same conditions are checked several times... up to the point of being checked in each part of the select-query. Here it is the SELECT-part, the FROM-part and the WHERE-part. However, we could easily end up having to add further checks had we had a ORDER BY-part, a GROUP BY, etc... These checks are very bad, as it disperses the otherwise confined logic all over the query. Finally, the SQL produced is unformatted, rendering it difficult to read when queries are extensive...which actually, are the situations you really need readable queries.
Rather than going into details with what the above example does, I leave it up to you to find you. I'd advice you not to spend too much time on it though. Instead, read the below code which is using the dynamic query builder in SQL Orm. The finer details of the semantics will be explained after the example.
private String getPersonAndmaybePet(boolean personName, boolean isAdult, int[] petTypes) {
SelectBuilder qry = new SelectBuilder();
if(personName) {
qry.select("person.name").from("person");
}
if(isAdult) {
qry.from("person").where().and("person.age >= 18");
}
else {
qry.from("person").where().and("person.age < 18");
}
if(petTypes != null) {
qry.select("pet.*").from("Pet") //
.where() //
.and("person.pet_id = pet.id") //
.and("pet.pettype IN (#)", QueryBuilderHelper.numbers(petTypes));
}
return qry.toSql();
}
calling this method with respectively
getPersonAndmaybePet(true, false, new int[]{ 3, 77 });
getPersonAndmaybePet(true, true, null);
yields the results
SELECT person.name,pet.* FROM person,Pet WHERE person.age < 18 AND person.pet_id = pet.id AND pet.pettype IN (3,77)and
SELECT person.name FROM person WHERE person.age >= 18From this we can make the following observations:
- The use of method chaining and fluent interfaces makes the syntax almost as typing SQL as strings, but with the added advantage that
your editor can assist with auto completion as keywords now are methods.
This approach is very different from many other ORM frameworks which requires you to either learn a completely new query language
(such as HQL in Hibernate), or forces you to create a lot of objects representing tables and fields (such as Toplink).
In the event you draw upon the knowledge of your non-programmer DBA, you can safely show him SQLOrm queries, he will be able to understand it straight away. - You do not need to worry about inserting commas, or keywords such as "FROM" and "WHERE". This is taken care of by the framework when you call toSql(). You are free to invoke e.g. qry.select(), qry.from() as many times you wish and in the order that is most convenient when building the query.
- Notice qry.from("person") is invoked twice in the code while the resulting SQL only contained person once.
This is a novel feature of SQLOrm called Intelligent Duplication Reduction (IDR).
IDR is in effect on most parts of the query, e.g. on columns, tables, order by's, group by's etc.
In other more mature frameworks such as Toplink version 10 you can only achieve the same effect on certain sub parts of SQL queries such as the tables in the FROM part by creating FROM table objects and referencing them using final references. phew! How complicated!
So how do you join the same table multiple times? You simply alias in when joining, e.g. from("address", "billingAdr").from("address","shippingAdr"). - When calling toSql() the SQL is returned as a string for you log, manipulate etc. Notice also that the SQL is automatically formatted. The formatter is not yet perfect but much better than mature frameworks such as Toplink which even in version 10 splashes parenthesis around everything and outputs the sql on a single line, rendering it impossible to read toplink queries from the log directly. Personally, I had to pick the interesting queries. Strip away garbage text around it and format the query in a tool. Sigh!
- The class QueryBuilderHelper is worth mentioning. It has a handful of methods helping you to create comma separated lists, and ensuring the types of arguments are correct such as the numbers() method.
Explaining the syntax
While the syntax has not yet settled, personally, I'm pretty content with what's available now. While a lot of time has gone into defining the syntax, little has been documented on this process. One good question raised was why you had to say query.where().and("xx").and("yy") as query.where("xx").and("yy") would be much more natural. The answer to this question is, that the syntax has been tailored around the fact that queries are dynamic. Secondly, if that syntax was enforced, what would the query query.where("xx"); query.where("yy") mean? Such a query could easily get constructed if one conditional branch of the DAO method say query.where("xx") and another branch say query.where("yy") (or maybe you have a loop iterating over a list adding them to the where clause, or maybe the query is constructed in a series of methods each passing the query builder object around). You could argue that the later branch should check whether the first branch already has done the query.where(".."), and if so then use the .where().and(".."); approach instead. Simply by enforcing either an and() or an or() immediately after the where() we reach the nirvana of simplicity.Apart from this I feel the syntax is very easy and very close to "just typing SQL". However, feel free to raise further questions and suggestions.
Extra type safety through helper methods
No need for prepared statements... more text here...Taking shortcuts
Often the problem with query builders are that they are so verbose, that you only want to use them on the most complex queries. This is not the case for SQLOrm. Take for example
String getMembers(String orderBy) {
return "SELECT * " +
" FROM members " +
" WHERE members.deleted='N' " +
" ORDER BY " + orderBy;
}
This baby can be just as easily expressed in SQLOrm (as of v1.0.02):
String getMembers(String orderBy) {
SelectBuilder builder = new SelectBuilder().select("*")
.from("members", "m")
.where().and("deleted='N'")
.orderBy(orderBy);
return builder.toSql();
}
Alternatively, SQLOrm query builder allows you to take certain shortcuts to allow you to focus on the variable parts of your queries.
The price for taking these shortcuts is the circumvention of the Intelligent Duplication Reduction (IDR) feature.
The following query only needs to vary in the ORDER BY.
SELECT person.name,person.surname, person.age, sums.sum FROM person, sums ORDER BY ??We can still utilize the query builder by including many names in the select() and from(), however, had we by accident included e.g. person.name twice, the resulting query will contain person.name twice. Note: Hopefully soon, when using shortcuts, the sql will be parsed and such problems disappear.
SelectBuilder builder = new SelectBuilder()
.select("person.name,person.surname, person.age, sums.sum ") // more than one entry here
.from("person, sums") // more than one entry here
.orderBy(orderBy);