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.
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
@Override 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 = builder.like(wikiPage_path, searchPath + "/%"); Expression<Integer> slashLocation = builder.locate(wikiPage_path, "/", searchPath.length()+2); Predicate noMoreDescendants = builder.equal(slashLocation, 0); query = query.where(builder.and(startsWithPath, noMoreDescendants)); return em.createQuery(query.select(wikiPage_root)).getResultList(); }
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.
Thoughts?
No comments:
Post a Comment