Introducing QueryBuilder

Programmatically constructing SQL queries is a common but cumbersome task. For example, consider this query that retrieves information about a hypothetical IoT device:

select Device.*, Building.name as buildingName, Site.name as siteName
from Device
join Building on buildingID = Building.id
join Site on siteID = Site.id
where Device.id = :deviceID


Written in plain text, as above, the query is fairly easy to read and understand. However, in Java, the same query might be declared as follows:

String sql = "select Device.*, Building.name as buildingName,

Site.name as siteName "
+ "from Device "
+ "join Building on buildingID = Building.id "
+ "join Site on siteID = Site.id "
+ "where Device.id = :deviceID";


Even in this simple example, readability is negatively impacted by the addition of double quotes and string concatenation operators. Care must be taken to ensure that whitespace is properly managed. Longer, more complex queries become increasingly difficult to write and maintain.

The new QueryBuilder class introduced in HTTP-RPC 7.5 simplifies the task of writing SQL queries in Java. Using QueryBuilder, the preceding example could be rewritten as follows:\

String sql = 
  QueryBuilder.select("Device.*", "Building.name as buildingName",

"Site.name as siteName")
    .from("Device")
.join("Building").on("buildingID = Building.id")
.join("Site").on("siteID = Site.id")
.where("Device.id = :deviceID").toString();


Because SQL verbs and clauses are represented as Java methods and nouns (tables, columns, and predicates) by strings, this version is much more readable and easier to maintain. It also scales better as query complexity increases.

A complete example using QueryBuilder along with HTTP-RPC’s Parameters and ResultSetAdapter classes is shown below. Parameters is used to prepare SQL statements for execution using named parameter values rather than indexed arguments. ResultSetAdapter provides access to the contents of a result set via the Iterable and Map interfaces, facilitating direct serialization of the result data to JSON:

String sql = 
  QueryBuilder.select("Device.*", "Building.name as buildingName",

"Site.name as siteName")
.from("Device")
.join("Building").on("buildingID = Building.id")
.join("Site").on("siteID = Site.id")
.where("Device.id = :deviceID").toString();

Parameters parameters = Parameters.parse(sql);

try (PreparedStatement statement = connection.prepareStatement

(parameters.getSQL())) {
parameters.apply(statement, mapOf(
entry("deviceID", deviceID)
));

    try (ResultSetAdapter resultSetAdapter = new ResultSetAdapter

(statement.executeQuery())) {
JSONEncoder jsonEncoder = new JSONEncoder();

jsonEncoder.write(resultSetAdapter, System.out);
}
}


For more information, see the project README.