Wednesday, April 11, 2012

JPA: Dynamic search builder, the power of annotation, reflection and generics.

JPA (Java persistence API) is a powerful tool for domain objects mapping, and object oriented view perspective of database.

Always I used to use JPA in my projects, but JPA2 introduces extra feature that facilitate the generic way of persisting, merging, deleting and searching. Also introduces dynamic criteria API, which I was looking for.

But with time I have to change the dynamic query or do it myself or else to create new dynamic one which the panic way.

I need a component to search dynamically based on passed object attributes value, and not changed by the time.

For example if I have Bank that has (id, name, address, and state object), and I have created a bank object with name and address has values construct the JPQL SELECT statement (SELECT b FROM Bank b where b.name = :name AND b.address = :address) and search by it and so on.

I have developed a component to implement the above case called DynamicQueryBuilder with advanced techniques, and configurations.

In this article I will describe how I built this component with support of annotation, reflection and generics. In the code snippets you will find a brief description about each line of code and methods, the annotation developed to support the component and I will describe also the overall functionality of the component and how to call it to work dynamically…


First let's begin with the annotation called "SearchRequired" (and the only one required for component to work), which contains two attributes (isObject (If it is an object then dig deep the search by this object.) and fields (This is fields list required for search by if it is object.)), it is used to annotate JPA bean object fields the required to search by.

For example if you have a bean called Bank and it contains 10 fields and you always make search by all of them you need to annotate them or the number of fields required to search by, and note the annotated field(s) only will be considered.

Example 1 – Annotated Bank bean

isObject: Required if you need to search by another bean fields, you need to specifies these fields in String[] fields property as object bean fields name (as I get these fields by reflections), like above status Lookups objects.

Example 2 – Lookups bean

If you don't specifies the fields I will search by the object directly and the query string will be:

instead of

And in this case you should set status Id data as I am check for that in the component.

VIP: If you annotate any field with "SearchRequired" and its value is null it will be neglected from search criteria.

Code 1 – SearchRequired annotation

Now let's take about the component and how we can use it.

The component integration will be as the following with full properties provided, note that I follow the builder pattern in construction this component:

Example 3 – DynamicQueryBuilder integration

Call

Let's examine some features of the component and its advanced features, consider the following call:

Example 4 – DynamicQueryBuilder advanced call

If you run the above call the component will construct the following resulted JPQL query:

Result of example 4

In the simplest form to call is:

Example 5 – DynamicQueryBuilder simple call

If you run the above call (example 5) the component will construct the following resulted JPQL query:

Result of example 5

Did you notes the difference when we use advanced features what happens to the JPQL query. Ok let's dig deep into the component logic and features, here is the component code.

Code 2 – DynamicQueryBuilder component

Features:
  1. This component uses reflections API to make it easy to construct the data from the object itself without extra calls (simplify the usage of the component), Generics to be suitable for any bean type, and enums to make operators call standard and prevent errors.
  2. Uses JPA 2 features.
  3. Construct JPA JPQL query String.
  4. Construct JPA Query object and set all required fields into this query with appropriate temporal type and return it to get single result or result list of passed bean type. Note: in this case you should pass the EntityManager object.
  5. You can override any specific search field(s) with advanced operator(s).
  6. If you don’t provide any value in the bean field(s), even if they annotated with SearchRequired annotation it will construct select all query.
  7. You can specify OR & AND operator, the first operator provided will override the follow operator.
  8. You can override the above operator with advanced one on specific field(s), the advanced on is, LIKE, IN, >, >=, <, <=, !=, BETWEEN as default operator is =.
  9. You can specify the % in different ways around the operator as LikePattern constrains before, after or around.
  10. With IN operator you specify the range as list.
  11. You can specify the order by operators and the order type also DESC or ASC and default is ASC.
  12. In case you annotate any bean object inside the original bean with search required annotation and don't specify the isObject or set isObject to true without any fields, the component with you the object as whole but you should set its id.

All of this feature you can use and I am continue to add more features and I will provide it as I am adding them.

I hope you enjoyed it and it is helpful for you all.

4 comments :

  1. Hi, great code, but I dont understand:why at line number 395 in dinamicQueryBuilder component there is "srAnno.fields().length == 0" and not !=0 ??

    ReplyDelete
    Replies
    1. I do this because some time you need to pass an object by its id is set, and you didn't specify the required fields on the SearchRequired annotation.

      So I check if the required search is an object and no fields are set on the annotation, then check if it's annotated fields with @Id are set or not, to take the advantage of JPA search by object.

      Delete
  2. @ngocnv1712@gmail.com

    Thank you very much, i'm looking at for this.

    ReplyDelete
  3. Hi, I am looking for something similar but not exactly same. I have bunch of Java classes and there are relations like contains, one to many , many to many etc. My requirement is that the customer wants to do searches and they are not looking for some static pages but a dynamic one where they start with select and based on the objects choosen further criterions keeps popping up. This is a web based solution they are looking at. For example,
    The starting drop down will have "Select","delete"
    based on option choosen, the second drop downdown appears
    "product", "component" etc
    based on choosen option, further drop down shows up.. if product chooses
    "whose", "in"
    if "whose" choosen, then Product's attributes shows up
    "a", "b" etc
    and then comparator based on choosen field so and so forth.

    So in the end, customer would be able to create there own queries which can be submitted to DB

    I am thinking of having some sort of Tree where based on options choosen, the tree is parsed from top to bottom..

    Any suggestions ?

    ReplyDelete