Skip to main content

08 — Specifications, escaped LIKEs, and the 409 that could have been a 500

Read this first: this lesson reads the filtering layer behind the employees grid — EmployeeSpecifications end to end, the repository underneath it, and the service check that turns a duplicate government ID into an honest HTTP status. By the end you can add a new grid filter using the house helpers, and make a uniqueness collision come back as a 409 naming a field instead of a 500 naming a constraint. Code is quoted inline so you can read this without the repository open.

Time: about 30 minutes. Assumes lesson 07.

A utility class that says so​

EmployeeSpecifications is a final class whose constructor exists only to refuse:

  private EmployeeSpecifications() {
    throw new UnsupportedOperationException("Utility class cannot be instantiated");
  }

That is the house idiom for "this is a bag of static functions, not a component": final stops subclassing, the private constructor stops new, and the throw stops even reflection from being clever. You meet the same idiom across the specification package.

Above it sit the words the whole file speaks:

  private static final String BLANK = "blank";
  private static final String NOT_BLANK = "notBlank";
  private static final String EQUALS = "equals";
  private static final String NOT_EQUAL = "notEqual";
  private static final String GREATER_THAN = "greaterThan";
  private static final String LESS_THAN = "lessThan";

These strings are not invented here — they are AG Grid's filter operator vocabulary, arriving from the frontend as ...FilterType request parameters. Every specification in the file switches on the same six words, and that shared vocabulary is what makes the generic helpers further down possible.

escapeLike, or the two characters users don't know they type​

Text filters end up in SQL LIKE, and LIKE has metacharacters:

  private static final char LIKE_ESCAPE = '\\';

  /** Escapes LIKE wildcards so user-typed %, _ and \ match literally. */
  private static String escapeLike(String value) {
    return value
      .replace("\\", "\\\\")
      .replace("%", "\\%")
      .replace("_", "\\_");
  }

In a LIKE pattern, % matches any run of characters and _ matches exactly one. A user typing them means the literal characters; the pattern means the wildcards. escapeLike prefixes all three specials with a backslash — backslash first, or it would re-escape the escapes it just added — and every cb.like(...) call in the file passes LIKE_ESCAPE as the escape character so the database agrees on what \% means.

Predict: a user filters the address column for 50%_off. Which rows match without escaping, and which match with it?

Resolve: without escaping, the contains filter builds the pattern %50%_off% — anything, 50, anything, exactly one character, off, anything. Unit 50, Hoffman St. matches: 50, then , for the inner %, H for the _, and off sitting inside "Hoffman". So does 1500 kickoff. With escaping the pattern is %50\%\_off% and only rows literally containing 50%_off match. Same input, wildly different result sets — and only one is what the user meant.

One subtlety in the switch that consumes it:

    String escaped = escapeLike(lower);
    return switch (type == null ? "contains" : type) {
    case EQUALS -> cb.equal(field, lower);
    case NOT_EQUAL -> cb.notEqual(field, lower);
    case "startsWith" -> cb.like(field, escaped + "%", LIKE_ESCAPE);
    case "endsWith" -> cb.like(field, "%" + escaped, LIKE_ESCAPE);
    case "notContains" -> cb.notLike(field, "%" + escaped + "%", LIKE_ESCAPE);
    default -> cb.like(field, "%" + escaped + "%", LIKE_ESCAPE);
    };

equals and notEqual compare the unescaped value: they are not LIKE operations, so a literal % in the input is already literal. Escaping is applied exactly where wildcards exist and nowhere else.

Two helpers ended the per-column copy-paste​

Read lastNameSearch, firstNameSearch, salaryFilter and hireDateFilter in sequence and you can watch a pattern begging to be extracted. The file eventually extracted it, twice, and both javadocs read as the design document:

  /**
   * Text filter for any scalar String attribute of Employee, addressed by field name.
   *
   * <p>The named variants above exist because they resolve joined paths (position, department) or
   * predate this helper; anything that lives directly on the employee row goes through here rather
   * than growing another near-identical copy per column.
   */
  public static Specification < Employee > textFieldSearch(String field, String search, String searchType) {
  /**
   * Range filter for any scalar Comparable attribute — money (BigDecimal), day-of-week (Integer)
   * and dates (LocalDate) all share AG Grid's operator vocabulary, so they share one implementation.
   *
   * <p>{@code min} carries the single operand for the one-sided operators ({@code equals},
   * {@code notEqual}, {@code greaterThan}) and {@code max} for {@code lessThan}; with no
   * filterType, a min/max pair is an inclusive range.
   */
  public static < T extends Comparable < ? super T >> Specification < Employee > rangeFilter(
    String field, T min, T max, String filterType
  ) {

The named variants that remain are not omissions. positionSearch and departmentSearch must navigate joins — a joined path cannot be addressed by a single field name off the root — and the rest simply predate the helpers. New scalar columns get no new specification.

You can see that rule enforced at the call site, in EmployeeServiceImpl:

    /** Shared by the DTO and projection paths so filter logic is never written twice. */
    private Specification<Employee> filterSpec(EmployeeListFilter filter) {

Both of lesson 07's paths run through this one method, so a filter added here works in the entity path and the projection path at once. Further down the chain:

                // Columns hidden by default in the grid, filterable once revealed. All are scalar
                // attributes of the employee row, so they go through the generic helpers rather
                // than growing a bespoke specification each.
                .and(EmployeeSpecifications.textFieldSearch("middleName",
                        filter.middleNameSearch(), filter.middleNameSearchType()))
                .and(EmployeeSpecifications.rangeFilter("riceSubsidy",
                        filter.riceSubsidyMin(), filter.riceSubsidyMax(), filter.riceSubsidyFilterType()))

So adding a filter for a new scalar column is three edits and zero new specifications: fields on EmployeeListFilter, request-param binding in the controller, one .and(...) line here. Every helper returns Specification.unrestricted() when its inputs are absent, which is why this chain of twenty-seven .and(...) calls needs no null checks — an inactive filter is a no-op, not a null.

The fetch join that refuses to count​

The last method in the file looks like a filter but isn't one — it returns cb.conjunction(), the always-true predicate, and exists for its side effect:

  /**
   * Eagerly fetches the position and department for list queries. Skipped on count queries
   * (result type Long) since fetch joins are not valid there.
   */
  public static Specification < Employee > fetchPositionAndDepartment() {
    return (root, query, cb) -> {
      if (Long.class != query.getResultType()) {
        root.fetch(POSITION, JoinType.LEFT).fetch(DEPARTMENT, JoinType.LEFT);
        query.distinct(true);
      }
      return cb.conjunction();
    };
  }

Without it, mapping a page of employees to DTOs would lazy-load each row's position and department one query at a time — the N+1 problem. The fetch join loads all three tables in one statement.

Predict: a paged findAll(spec, pageable) runs two SQL queries, not one. Why — and why must the second one drop the fetch join?

Resolve: a Page needs its content and its total, so Spring Data runs your specification twice — once as the content query with LIMIT/OFFSET, once as SELECT COUNT(...). Same specification, both runs. But a fetch join means "hydrate this association into the entities being selected", and a count query selects a Long — there is no entity to hydrate, so JPA rejects the query outright. The query.getResultType() check is how one specification tells which of the two runs it is in, and the comment above the method says exactly that. Skip the guard in a module of your own and the grid works until the count query runs — which is the first page load, in production, not in the happy-path test that never paginated.

The repository is two interfaces and a list of questions​

public interface EmployeeRepository extends JpaRepository<Employee, Integer>, JpaSpecificationExecutor<Employee> {

    @EntityGraph(attributePaths = {"position", "position.department"})
    Optional<Employee> findWithPositionByEmployeeNumber(Integer employeeNumber);

JpaRepository provides the CRUD you already know; JpaSpecificationExecutor is what accepts everything this lesson has built — its findAll(Specification, Pageable) is where filterSpec finally executes. @EntityGraph is the single-record cousin of fetchPositionAndDepartment: the same eager-loading intent, declared as an annotation because a derived query has no lambda to put a fetch in.

Then come eight methods that all ask one kind of question:

    boolean existsBySssNumber(String sssNumber);

    boolean existsBySssNumberAndEmployeeNumberNot(String sssNumber, Integer employeeNumber);

Four government IDs — SSS, PhilHealth, TIN, Pag-IBIG — each with this exact pair. The first asks "is this number taken by anyone?" and serves creates. The second asks "is it taken by anyone who is not me?" and serves updates, where an employee keeping their own number must not collide with themselves. Eight derived queries, written purely to serve one private method in the service.

The 409 that could have been a 500​

That method is the point of the pairs:

    /**
     * Rejects government ID numbers already assigned to another employee with a 409
     * instead of letting the DB unique constraint fail as an opaque 500.
     * @param selfId the employee being updated, excluded from the check; null on create
     */
    private void assertUniqueGovernmentIds(EmployeeCreateRequest request, Integer selfId) {
        if (isTaken(request.getSssNumber(), selfId,
                employeeRepository::existsBySssNumber, employeeRepository::existsBySssNumberAndEmployeeNumberNot)) {
            throw new ConflictException("SSS number is already assigned to another employee");
        }

The database has unique constraints on these columns either way. Without this check the insert still fails — as a data-integrity exception naming a constraint, surfacing as a 500 that tells the person at the keyboard nothing. With it, the collision is caught before the insert and thrown as ConflictException, which lesson 05's error contract maps to a 409 whose message reaches the form: the field, and what is wrong with it. The constraint stays as the backstop for races; the check exists for the human.

All four branches funnel through one small helper:

    private boolean isTaken(String value, Integer selfId,
                            Predicate<String> existsAnywhere,
                            BiPredicate<String, Integer> existsElsewhere) {
        if (value == null || value.isBlank()) return false;
        return selfId == null ? existsAnywhere.test(value) : existsElsewhere.test(value, selfId);
    }

Blank means "not provided", never "collides with every other blank". create calls the assertion with selfId = null; update passes the employee's own number so the exclusion variant runs.

To give a new unique column the same manners: add its existsByX and existsByXAndEmployeeNumberNot pair to the repository, add one isTaken branch that throws ConflictException with the field named in the message, and keep the database constraint. The check is the polite answer; the constraint is the guarantee.

Two comments worth stealing​

Two more comments in EmployeeServiceImpl earn their bytes. On setArchived:

    /**
     * Archiving is distinct from deactivating: an INACTIVE employee is still a current record that
     * payroll and reporting can see, whereas an archived one drops out of the default list view
     * entirely. Both are reversible, and neither removes the row.
     */

Two states that look interchangeable are not, and the comment says which does what — the matching archived(Boolean) specification treats a null argument as the default view, so every existing caller "keeps excluding archived rows without opting in." And getById explains why it alone attaches the linked account's username: the list path already resolves usernames in bulk, so without it "the same field would render populated in a CSV export and blank in the drawer for the very same employee." That is a bug report written before the bug.

Where this shows up in MotorPH​

Recap​

  • Escape user text before it meets LIKE. % and _ are wildcards; escapeLike plus the LIKE_ESCAPE argument make them literal, and equals comparisons skip escaping because they were never patterns.
  • New scalar filters use the house helpers. textFieldSearch and rangeFilter exist so a column costs one .and(...) line, not another near-identical specification; the named variants are for joined paths.
  • A paged specification runs twice, and the second run selects a Long — a fetch join must check query.getResultType() or the count query throws.
  • Check uniqueness before the insert. The exists pairs plus ConflictException turn a constraint violation into a 409 naming the field; the constraint stays as the backstop.
  • Archived is not inactive. One drops out of the default view, the other stays visible to payroll and reporting; both are reversible and neither deletes the row.

Next: 09 — MapStruct at ERROR: the boundary that refuses to guess.