Querying By DSL in simple-jpa


simple-jpa has dynamic finders for querying JPA entities from database.   More information about them can be found at http://jockihendry.github.io/simple-jpa/finders.html.  They are useful for simple queries, but they have their own limitations.   For example, the following screen requires a very dynamic query:

A sample presentation for querying products

A sample presentation for querying products

In the screen above, user should be able to find products by product’s code or name.   She can further filter the search result based on product’s type.   simple-jpa doesn’t have a dynamic finder that can solve this kind of query in one line.   Executing the query requires more than one if condition to determine appropriate dynamic finder to call, which produces code likes this:

List result
if (searchBy==model.searchByCode) {
  if (typeFilter==allType) {
     …
  } else {
     …
  }
} else if (searchBy==model.searchByName) {
  if (typeFilter==allType) {
     …
  } else {
     …
  }
}

But there is a better approach: simple-jpa has a DSL for performing queries.  Let looks at the basic syntax first.   The following is an example of basic DSL that will find all student whose name contains snake and age is greater than 20:

List result = findStudentByDsl {
   name like(“%snake%”)
   and()
   age gt(20)
}

Every line in DSL represents an expression.   The DSL supports the following operators: eq (equal), ne (not equal), gt (greater than), ge (greater than equal to), lt (less than), le (less than equal to), isNull, isNotNull, isEmpty, isMember, isNotMember, like, and notLike.   To join two different expressions, use and() and or() method.

Note that the content of DSL is not limited to query expressions.   They can also have variable names and Groovy’s code just like a normal closure.   This allows developer to write a flexible query.   For example, the following query can be used for our product’s search:

List result = findProductByDsl  {           
  if (searchBy==model.searchByCode) {
    code like(“%${model.searchValue}%”)
  } else if (searchBy==model.searchByName) {
    name like(“%${model.searchValue}%”)
  }
  if (typeFilter!=allType) {
    and()
    type eq(typeFilter)
  }
}

The code above is easier to understand than the previous one which has separate dynamic finder method calls.

Perihal Solid Snake
I'm nothing...

Apa komentar Anda?

Please log in using one of these methods to post your comment:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: