Query builder APIs

The query builder APIs provide support for retrieving information from PolicyCenter application databases.

The API framework models features of SQL SELECT statements to make object-oriented Gosu queries. The query builder APIs support many, but not all, features of the SQL SELECT statement.

Supported SQL features in query builder APIs

Query builder APIs provide functionality similar to these features of SQL SELECT statements.

Keyword or clause in SQL

Equivalent in query builder APIs example

Purpose

SELECT *

In Gosu:

var query = Query.make(entity-type)

In Java:

Query<entity-type> query = Queries.createQuery(entity-type.TYPE)

Begins a query. Generally, the results of a query are a set of object references to selected entity instances.

FROM table

var query = Query.make(entity-type)

Declares the primary source of data.

DISTINCT

query.withDistinct(true)

Eliminates duplicate items from the results.

column1 AS A[, column2 AS B [...]]

query.select({

  QuerySelectColumns.pathWithAlias(

    "A", Paths.make(entity#field1))

[,  QuerySelectColumns.pathWithAlias(

    "B", Paths.make(entity#field2))

...]

})

Produces a result that contains a set of name/value pairs, instead of a set of entity instances.

JOIN table ON column

var table = query.join(entity#foreign-key)

Joins a dependent source of information to the primary source, based on a related column or field.

WHERE

query.compare(entity#field-name,

  parameters)

query.compareIgnoreCase(

  entity#field-name, parameters)

query.between(entity#field-name,

  parameters)

query.compareIn(entity#field-name,

  parameters)

query.compareNotIn(entity#field-name,

  parameters)

query.startsWith(entity#field-name,

  parameters)

query.contains(entity#field-name,

  parameters)

Fetches information that meets specified criteria.

ORDER BY column1[, column2[...]

var orderedResult =

  result.orderBy(

    QuerySelectColumns.path(

      Paths.make(entity#field1)))

  [.thenBy(QuerySelectColumns.path(

      Paths.make(entity#field2)))

  [...]]

Sort results by specific columns or fields.

GROUP BY

Implied by aggregate functions on fields

Return results grouped by common values in a field.

HAVING

query.having()

Return results based on values from aggregate functions.

UNION

var union = query1.union(query2)

Combine items fetched by two separate queries into a single result.

Note: You cannot use the union method on query objects passed as arguments to the subselect method.

FIRST

result.FirstResult

Limit the results to the first row, after grouping and ordering.

TOP | LIMIT

result.getCountLimitedBy(int)

result.setPageSize(int)

Limit the results to the first int number of rows at the top, after grouping and ordering. These query builder API methods provide similar functionality in a portable way.

Unsupported SQL features in query builder APIs

Query builder APIs provide have no equivalent for these features of SQL SELECT statements. PolicyCenter never generates SQL statements that contain these keywords or clauses.

Keyword or clause in SQL

Meaning in SQL

Why the APIs do not support it

FROM table1, table2[, ...]

Declares the tables in a join query, beginning with the primary source of information on the left and proceeding with dependent sources of information towards the right.

  • This natural way of specifying a join can produce inappropriate results if the WHERE clause is not written correctly.
  • Relational query performance often suffers when SQL queries include this syntax.
  • You can specify only natural inner joins with this SQL syntax.

EXCEPT

Removes items fetched by a query that are in the results fetched by a second query.

This SQL feature is seldom used.

INTERSECT

Reduce items fetched by two separate queries to those items in both results only.

Query intersection often causes a performance problem. A better choice is to use a more efficient query type. For example, if both sides of the INTERSECT clause query the same table, use a single query. Use an AND operator to combine the restrictions from both sides of the INTERSECT to restrict the result.

See also