Additional ideas for the DaoHelpers query/sort utilities

[sleberknight]

These are some leftover ideas from DaoHelpers while I was re-implementing it with some improvements and additional features.

Allow caller to specify the connector?

Currently we use "order by" as the initial "connector" and a comma as the sort field connector, e.g. given the base query

select * from users

and sort fields lastName and firstName, the resulting query will be:

select * from users order by lastName ASC, firstName ASC

This works fine for SQL, HQL, etc. which is the primary use case for DaoHelpers. So, is there any reason to permit other values?

The addSort method is currently private but if we wanted to make it public to allow callers to add individual sorts, we'd probably want to restrict the values. We could create an enum, for example:

public enum SortConnector {
    ORDER_BY(" order by "), SORT_FIELD_SEPARATOR(", ");

   // ...
}

and then use this instead of a String argument, which would prevent possible SQL injections.

Possible overloads to accept String query object, and return a new String

Currently all the methods accept a StringBuilder, and they mutate it. Would it be worth adding a bunch of new methods that accept String query and return String such that the original query is not modified and the returned value is the original query plus the order clause?

Add "quiet" methods that are no-ops if given any disallowed sort fields?

This would be yet more methods that are similar to the existing ones, but would never throw exceptions. Instead, if given disallowed sort fields they could log a warning and simply be a no-op.

Add option to NOT have AllowedFields?

This would effectively let callers add anything, i.e. no security at all. For our own use cases, we always require that the sort fields be validated against a whitelist of allowed fields, so I'm leaning towards "no" on this one. Would we really want to allow no security?

Add "parameter object with builder" to allow building what you want to sort?

Instead of all the existing method overloads, we could change the entire thing to be a builder which lets you define how you want to sort things. This would let the caller specify a String or a StringBuilder for the query, though in that case the builder would have to be "smart" and branch such that the terminal method is either void or returns a String depending on whether the builder received a StringBuilder or String for the query. Examples:

// assume allowed fields are pre-defined
var allowedSortFields = AllowedFields.of("first_name", "last_name", "age");

// some query for users; assume predicates have been added
var baseQuery = "select * from users where active = ? and group_id = ?";

// with a String
var orderingQuery1 = DaoHelpers.sortBuilder()
  .query(baseQuery)
  .allowedSortFields(allowedSortFields)
  .addSortDecending("last_name")
  .addSortAscending("first_name")
  .build();

// --> select * from users where active = ? and group_id = ? order by last_name DESC, first_name ASC

// same query but specify sort direction in addSort
var orderingQuery2 = DaoHelpers.sortBuilder()
  .query(baseQuery)
  .allowedSortFields(allowedSortFields)
  .addSort("last_name", KiwiSort.Direction.DESC)
  .addSort("first_name", KiwiSort.Direction.ASC)
  .build();

// other variations could include methods that accept KiwiSort objects, e.g.
var sort1 = KiwiSort.of("age", KiwiSort.Direction.DESC);
var sort2 = KiwiSort.of("last_name", KiwiSort.Direction.ASC);
var orderingQuery3 = DaoHelpers.sortBuilder()
  .query(baseQuery)
  .allowedSortFields(allowedSortFields)
  .addSort(sort1)
  .addSort(sort2)
  .build();

// or could accept multiple, e.g. addSorts(sort1, sort2) and so on...

// and with a StringBuilder it might look like
var baseQueryBuilder = new StringBuilder("select * from users where active = ? and group_id = ?");

// There is no return value here since this modifies the StringBuilder in-place, but we
// still need a terminal method.
DaoHelpers.sortBuilder()
  .queryBuilder(baseQueryBuilder)
  .allowedSortFields(allowedSortFields)
  .addSort("last_name", KiwiSort.Direction.DESC)
  .addSort("first_name", KiwiSort.Direction.ASC)
  .build();

The class of the object returned by DaoHelpers.sortBuilder() could actually be a standalone class, e.g. a SortBuilder class.

More utilities to add pagination to query

This could be simple and allow you to add a limit and/or offset to the query, as one simple example.

Reconsider the package and class names

While AllowedFields probably makes sense as a class name, consider whether there is a better name than DaoHelpers and whether there is a better package name than dao. I only used them because that's what they are called in the service where I found the original versions. The versions here add enhancements to the original and more functionality.