SqlBuilder is a library which attempts to take the pain out of generating SQL queries within Java programs. Using one programming language (Java) to generate code for another language (i.e. SQL) is always a challenge. There are always issues with escaping characters within string literals, getting spaces in the right place, and getting the parentheses to match up. And often, even after the code is debugged and fully tested, it is still very fragile. The slightest change will throw things out of balance and require another round of testing and tweaking.
SqlBuilder changes that whole scenario by wrapping the SQL syntax within very lightweight and easy to use Java objects which follow the "builder" paradigm (similar to StringBuilder). This changes many common SQL syntactical, runtime errors into Java compile-time errors! Let's dive right in to some quick examples to to see how it all works.
A fairly simple SQL select query embedded in a Java program might currently look something like this:
// assuming a variety of predefined string constants
String selectQuery = "SELECT " + T1_COL1 + "," + T1_COL2 + "," +
T2_COL1 + " FROM " + TABLE1 + " " + T1 + " INNER JOIN " +
TABLE2 + " " + T2 + " ON (" + T1_IDCOL + " = " + T2_IDCOL +
") ORDER BY " + T1_COL1;
Whenever this query is modified, you will need to make sure there are sufficient commas, parentheses, and spaces to generate the correct query (not to mention the correct columns for the given tables and the correct aliases for those tables).
An attempted improvement may look something like this:
String selectQuery = MessageFormat.format(
"SELECT {0},{1},{2} FROM {3} {4} INNER JOIN {5} {6} ON ({7} = {8}) " +
"ORDER BY {9}",
T1_COL1, T2_COL2, T2_COL1, TABLE1, T1, TABLE2, T2,
T1_IDCOL, T2_IDCOL, T1_COL1);
This technique at least removes some of the formatting issues, as the query syntax is in one contiguous string. However, matching up the placeholders with the arguments is no simple task and simple rearrangements can easily mess up the resulting query string. Additionally, this is still not a viable solution for any sort of dynamic query generation.
Now, let's see how this query looks using SqlBuilder classes:
// assuming these objects have already been created Table table1, table2; Column t1Col1, t1Col2, t2Col1; Join joinOfT1AndT2; String selectQuery = (new SelectQuery()) .addColumns(t1Col1, t1Col2, t2Col1) .addJoin(SelectQuery.JoinType.INNER_JOIN, joinOfT1AndT2) .addOrderings(t1Col1) .validate().toString();
See how easy that was? Not a single embedded comma, space, or parenthesis to be seen! Notice how much more readable this version is compared to the previous versions? While the Java is a bit more verbose, turning the SQL into Java allows you to utilize your existing Java toolset when writing SQL (think compile-time syntax checking and IDE code completion). This added functionality is well worth the trade-off.
On top of that, check out the validate() method call slipped into the end of the statement. That call will verify that the columns and tables in the query actually make sense. Maintainability, readability, and verifiability all wrapped into one easy to use package.
That was a very simple example of course. Imagine a much more complicated query with multiple sub-expressions in the where clause, and it should be easy to see the power and utility of the SqlBuilder library.
As a final note--for all that the SqlBuilder package is, there are a couple of things it is not. This package will not help you write SQL if you do not already know how to write SQL and use JDBC. It does not abstract away the knowledge necessary to deal with a database, but instead provides tools for avoiding the more error-prone parts of generating SQL queries. If you want a tool to completely abstract away the database, check out Hibernate instead. There is a time and place for straight SQL and a time and place for an ORM tool. If you are currently in the former time and place, then SqlBuilder is a tool you should definitely check out.