Overview (SQL Query Builders 3.0.2 API)

SqlBuilder Core Package
Package	Description
com.healthmarketscience.sqlbuilder	Builder-style classes for generating a wide variety of SQL statements (see the Syntax Reference for the major highlights).

Database Specification Packages
Package	Description
com.healthmarketscience.sqlbuilder.dbspec	Group of interfaces which simplify the usage of SqlBuilder when multiple queries will be built over the same collection of database objects.
com.healthmarketscience.sqlbuilder.dbspec.basic	Very basic implementations of the `dbspec` interfaces.

Custom Database Packages
Package	Description
com.healthmarketscience.sqlbuilder.custom	SqlBuilder supports two types of custom SQL: Custom values/expressions within existing queries - this type of customization can be achieved using the custom SqlObject variants: `CustomSql`, `CustomExpression`, and `CustomCondition`. Custom clauses added to existing queries - the classes in this package (and subpackages) enable this type of customization, read on for details.
com.healthmarketscience.sqlbuilder.custom.mysql	SqlBuilder customizations specific to the MySQL database.
com.healthmarketscience.sqlbuilder.custom.oracle	SqlBuilder customizations specific to the Oracle database.
com.healthmarketscience.sqlbuilder.custom.postgresql	SqlBuilder customizations specific to the PostgreSQL database.
com.healthmarketscience.sqlbuilder.custom.sqlserver	SqlBuilder customizations specific to the SQL Server database.

Utility Package
Package	Description
com.healthmarketscience.common.util

Facility for programmatically generating SQL statements. Can be used either with the dbspec utilities or with simple SQL strings (or a combination thereof). Has validation for checking internal consistency of the statements (more effective when using the dbspec utilities). Note that although the objects are not built for concurrent construction, once a query object has been created, all append methods are thread-safe. See the Syntax Reference for a list of the major highlights.

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.

Although this facility has full support for "custom" sql (arbitrary strings inserted using the CustomSql object), it is highly recommended that the com.healthmarketscience.sqlbuilder.dbspec utilities be used in conjunction with this package (there are very basic implementations in the dbspec.basic package). Using the dbspec utilities with this facility allow for better validation of queries, simpler method calls, and no custom SQL at all. What more reasons do you need?

Additionally, there is support for dynamically generated prepared statements. The QueryPreparer class can be used to keep track of the positions of prepared statement parameters while building a prepared statement (see that class for examples).

Examples:


//
// CreateTableQuery
//

// custom sql example (validated)
String createQuery =
  (new CreateTableQuery(new CustomSql("table1")))
  .addCustomColumns(new CustomSql("id number"),
                    new CustomSql("foo varchar(255)"),
                    new CustomSql("baz number"),
                    new CustomSql("buzz date"))
  .validate().toString();

// Output:
CREATE TABLE table1 (id number,foo varchar(255),baz number,buzz date)

// Assume these objects have been created already
Table table1;

// dbspec sql example (validated)
String createQuery =
  (new CreateTableQuery(table1, true))
  .validate().toString();

// Output:
CREATE TABLE TABLE1 (ID NUMBER,FOO VARCHAR(255),BAZ NUMBER,BUZZ DATE)


//
// SelectQuery
//

// custom sql example (validated)
String selectQuery =
  (new SelectQuery())
  .addCustomColumns(new CustomSql("foo"),
                    new CustomSql("baz"),
                    new CustomSql("buzz"))
  .addCustomJoin(SelectQuery.JoinType.INNER_JOIN,
                 new CustomSql("table1"), new CustomSql("table2"),
                 BinaryCondition.equalTo(
                   new CustomSql("table1.id"), new CustomSql("table2.id")))
  .addCustomOrderings(new CustomSql("foo"))
  .validate().toString();

// Output:
SELECT foo,baz,buzz FROM table1 INNER JOIN table2 ON (table1.id = table2.id) ORDER BY foo

// Assume these objects have been created already
Table table1, table2;
Column t1Col1, t1Col2, t2Col1;
Join joinOfT1AndT2;

// dbspec sql example (validated)
String selectQuery =
  (new SelectQuery())
  .addColumns(t1Col1, t1Col2, t2Col1)
  .addJoin(SelectQuery.JoinType.INNER_JOIN, joinOfT1AndT2)
  .addOrderings(t1Col1)
  .validate().toString();

// Output:
SELECT T1.COL1,T1.COL2,T2.COL1 FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON (T1.IDCOL = T2.IDCOL) ORDER BY T1.COL1

JMS

Since the syntax for JMS message selectors is a subset of SQL92, many of the expression related classes in this package can be used to create JMS message selectors.


String selector = ComboCondition.and(
  BinaryCondition.equalTo(new CustomSql("field1"),
                          "the_value"),
  BinaryCondition.lessThan(new CustomSql("otherField"),
                           new NumberValueObject(37)), false)
  .toString();

// Output:
((field1 = 'the_value') AND (otherField < 37))