Monday, May 15, 2017

Which is better?

I am making a Spring Boot app using Spring Data that has, as part of its function, an embedded wiki. Individual wiki pages have workflows associated with them. Rather than maintain a list of parent/child relationships for pages I'm using an implied hierarchical structure based on the 'path' columns that serve as the coordinates for each page. The hard part is finding only the direct descendants of each page. To do this, I look for paths that include the 'parent' but have no other path separators. In other words, descendants of labs would include /labs/electronics and /labs/bio but would not include /labs/electronics/equipment because, while equipment is a descendant of /labs it is not a direct descendant.

To accomplish this, I do two things:
  • Find the paths that begin with the parent's path, including trailing slash, for example /labs/. This selects all descendants of the parent.
  • Of those, find the ones who have NO OTHER SLASHES beyond the parent. This selects only the direct descendants.
Here are two ways to do it, both produce identical results:

Using JPQL
@Query("select w from WikiPage w " +
    " where w.path like concat(:path, '/', '%') " +
    " AND LOCATE('/', w.path, length(:path)+2) = 0")
public List<WikiPage>
    findDirectChildrenOfUsingAnnotationQuery(@Param("path") String path);

Using the Java-based Query DSL
public List<WikiPage> findDirectChildrenOfUsingQueryDSL(String searchPath) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuer<WikiPage> query = builder.createQuery(WikiPage.class);
    Root<WikiPage> wikiPage_root = query.from(WikiPage.class);

    Expression<String> wikiPage_path = wikiPage_root.get("path");
    Predicate startsWithPath =,  searchPath + "/%");

    Expression<Integer> slashLocation = builder.locate(wikiPage_path, "/",
    Predicate noMoreDescendants = builder.equal(slashLocation, 0);

    query = query.where(builder.and(startsWithPath, noMoreDescendants));
    return em.createQuery(;

Reasons to use the Java DSL
  • It is more refactorable ... but, a good IDE like IntelliJ can refactor JPQL just fine
  • It's more typesafe ... but not completely so, and again, IntelliJ does a pretty good job. You could even argue that it does better, because the Java DSL can't tell that "path" is a valid attribute of WikiPage, but in JPQL it could
  • Compiler-time errors if you screw up ... but again, IntelliJ already tells me if my JPQL is invalid
  • Certain operations - like the concat and strlen - are arguably better handled in java than by the db engine

The Java DSL is definitely harder to read. But I have a distaste for 'magical strings' which includes JPQL in a @Query annotation.