Generic Query Object (GQO) Best Practice

Discussions

J2EE patterns: Generic Query Object (GQO) Best Practice

  1. Generic Query Object (GQO) Best Practice (27 messages)

    Problem: Using Data Access Objects (DAO) to read the data has some problems that affect your design and development:
        1. Large number of DAO classes in a real system. A system with hundreds of entities and database tables needs a large number of DAO classes with a few methods in each one that all need implementation and test while their implementation is quite similar.
        2. Inconsistency with the rest of the implementation of business logic, which is developed in SessionBeans. Reading and extracting data by some criteria is part of the business and it must be maintained the same as the other parts of business. Using DAO leads to this problem since read operations are separated from other businesses, and it results in the incompleteness of the Façade layer and it is a design fault.
        3. DAO objects while doing some business logics, they are involved in handling non-business issues e.g. sorting, paging or grouping of the data that are related to the presentation layer. DAO objects need to know how to prepare data, so you need to consider all presentation related parameters in their implementations, which is again another design fault and it breaks the logic of layering.
        4. There is no declarative mechanism to control the security of DAO methods, while the rest of your business can enjoy declarative security of EJBs.


    Solution: Suppose that there is a “Query” object, which is used to make a SQL query and apply different parameters on it dynamically as its WHERE CLAUSE and finally execute it. The Query object uses a Generic Query Engine to manage its SQL statement, run it and return the result. The generic query engine acts as a layer before JDBC. The result can be ResultSet, a list of ValueObjects or any other formats that you need.

    Now, all methods of previous DAOs can be replaced by SessionBean methods that return an instance of the Query object. A read business method in a SessionBean makes an instance of the Query object, which is appropriate to its business and returns it to the client - Query should be a serializable object. The client then executes the Query by calling its for example “execute” method to get the result. The SQL query is executed on the client tier and JDBC connection is made at the execution time therefore, there is no transfer of data from the remote interface of business tier to the client only a simple JavaBean named Query.

    This approache allows separation of business logic and presentation logic of a query. When a SessionBean makes a Query object, it only applies the business logic parameters and it is not aware of how data are shown. For example if there is “getOrderItems(long orderNo)” method in business tier, the returned Query only has “orderNo” as its parameter. When the client – presentation layer – receives the Query it can apply any presentation specific effect on it, such as how data sorted, count of result, which page to be shown, etc. All these effects can be applied using different API of the Query object and it uses the query engine to apply different parameters on the original SQL statement.

    By a good design of Query object and the query engine more flexibility on maintaining queries at runtime you will gain. The query engine should understand SQL grammar and is responsible to make different queries dynamically. We did it in a project and the result has been satisfactory, so I decided to share this idea with whom are interested.

    Benefits:
       . Have all benefits of DAO
       . No need to develop DAO classes any more
       . Having a complete business façade layer that includes read operations as well
       . Better separation of query logic among tiers, e.g. presentation related effects will be applied only in the presentation tier
       . Less implementation is required in read business methods, since most of it has been done in the query engine and Query object itself.

    Example Code:
    public interface Query extends Serializable{
      public List execute();
      public void addParameter(String paramName, OperatorType operator, Object value);
      public void addOrderBy(String paramName,OrderType orderType);
      public void setPaging(int pageSize, int pageNo);
      public void groupBy(String[] fields);
      ……………
    }


    public interface OrderBiz extends javax.ejb.EJBObject {
      public Query getOrderItems(Long orderNo) throws RemoteException;
      public Query getAllOrders() throws RemoteException;
      ………
    }


    public class OrderBizBean implements SessionBean {
      ……………
      public Query getOrderItems(Long orderNo) {
        QueryImpl query = new QueryImpl("query for reading order items");
        query.addParameter("ORDER_NO", EQUAL, orderNo);
        return query;
      }
      public Query getAllOrders() {
        QueryImpl query = new QueryImpl("query for reading orders ");
        return query;
      }
    }


    public class PresentationLayer {

      public void sampleMethod1() {
        OrderBiz order = ……

        try {
          Query query = order.getAllOrders();
          query.setPaging(10,2);
          List result = query.execute();

          // show result
        }
        catch (RemoteException ex) {
        }
      }

      public void sampleMethod2() {
        OrderBiz order = ………

        try {
          Query query = order.getOrderItems(new Long(500));
          query.addOrderBy("product_name", ASCENDING);
          List result = query.execute();

          // show result
        }
        catch (RemoteException ex) {
        }
      }
    }

    Threaded Messages (27)

  2. you are right with your criticism of the DAO-way of doing things.
    we (as probably many other folks) created an abstraction for a query, somewhat similar to what you suggest here. it seemed nice but went fairly complex after a while. think of joins, aliases, separation from database dialect, etc...

    so we ended up using hibernate with its parametrizable named queries and its Criteria API to do this (with a small layer on top to apply filters in Java-space).

    if you REALLY have to stick to plain JDBC instead of using some decent framework, go right ahead - but this will become harder the closer you look.
  3. what about writing to db[ Go to top ]

    DAO objects are also for writing to the db. How will you do that with just a query api ?

    Also, you should checkout the JDO 2.0 spec. It already has a much more complete query API.

    Bruce
  4. RE: what about writing to db[ Go to top ]

    The way of writing to DB depends on your persistence api which can be Entity Bean or... and it may be differs from read API. I am not in favor of these mechanisms but sometimes they are different. In those cases DAOs are used for just read. However, this is not the main point of this article. The big point is to seperate the presentation part of a query from its core business.
    You can use any query API either a self-made or a commercial one, but try to seperate UI logic from business logic of your query.
  5. clarification and questions[ Go to top ]

    Are you saying you prefer to have two different API's, i.e. one to query and one to read/write. I would persoanlly say that would confuse access to the db.
    And I would be supported by the fact that almost all products (Entity Beans, Hibernate, JDO, etc.) bundle the ability to read/write objects with a query API.

    Two different ways to "hit" the db will only confuse things. Plus a Query API to have high performance requires access to a cache. How will you write a high performance query API without a transactional cache which you will not have if you use to different APIs ?

    Bruce
  6. I would prefer to have one API for read and write, but depending on your persistance API, may be using another API for reading is preferable due to perfomance issues. For example, nobody uses EntitBeans when wants to show some data in UI, the entity beans are used only to find a record for update, delete or making a relation to another entity bean.

    To have a reasonble preformance at first it is neccessary to tune your database. Increasing the performance of the query API is another matter and I agree that it is a little bit difficult to do.
  7. forget the ui (at times)[ Go to top ]

    Everyone who does ui work (in my opinion) worries way too much about how this should not be done using a domain model or dao. they all say push it does to sql (or use a query object model, etc.). The truth is that this can work, but you lose all capability to use a more complex aproach.

    for example, with an integrated l2 cache you could perform an updaqte followed by a query potentially in memory depending on the size of the data, i.e. does the implementation decide that it makes sense to run in-memory or via a database hit. For very large tables it will probably make sense to hit the db
    as it would not be possible to hold such amounts in memory. On the other hand for medium tables (less than say 50,000 rows) it would be possible to hold query indices (say the ids) and update this as needed for edits, i.e. you could check whether a changed object should drop out or be added to certain query results (also taking into account transaction).

    I guess all I am saying is that an integrated approach can allow better optimization of your queries, of course depending on the types of queries you perform. Note that I have actually used the above approach with my own homemade object to relational mapper plus query engine and the performance could be quite good. Much better then constantly hitting the database although for very large result sets it would not work. Of couyrse for huge sets such as 1 million plus then it is back to straight sql with potentially very little object and more sql along with the db tuning.

    Bruce
  8. How do you get your connection?[ Go to top ]

    Just curious how you go about getting your Connection. I've tried something similar in the past where the connection I got was from the connection pool maintained by the app server (a serializable connection). (So in this case, the Connection would be returned with the Query object.) The performance turned out to be unacceptable - much worse than returning a collection of objects materialzed on the app server. This was WebLogic 7 - not sure if performance improvements have been made with respect to this issue.
    Your other alternative is to establish a new connection from the client. However, if you're using a type 2 driver, then you have to install the driver on every client workstation.
  9. RE: How do you get your connection?[ Go to top ]

    Connection is got at execution time of the SQL statement not at creating Query object. When the "execute" method of Query is called, it establishes a connetion and runs the query. In our implementation the connection is provided by the connection pool of app/web server. I used this approach for web-base applications and there was no overhead of serializable connection and the performance was so good. I haven't tested it for thick clients, but I think about the issue you mentioned.
    Thank you John
  10. RE: Getting connection[ Go to top ]

    If you want to use Query object in a thick client application, I suggest that the Query object talk to the remote interface of the Query Engine and ask it for the result. So the query engine executes the query on the server (the connection will be established on the server and no need to send it to client) and returns the result. To avoid serialization problem of large set of data objects, its better to trasfer data to the client page by page.
  11. I think there are some problems with the DAO pattern but I don't really care for this solution. Basiacally it's a reimplementation of JDBC over the top of JDBC. Not that JDBC is perfect but it's not realy broken.

    I think the problem with the DAO is that it puts too much responsibility in the hands of each DAO class. I've spent a lot of time checking DAOs to make sure they are cleaning up after themselves and if you haven't provided a good utility class for handling BD specific logistics (closing connections, statements and the rest) or developers don't use them, DAOs can end up with a lot of bugs in a critical part of the application.

    The solution that I have been playing around with but haven't had a chance to really implement is to turn the DAO pattern inside-out.

    Basically the idea is like a SAX parser. The SQLHandler object doesn't deal with Connections, Statements, or ResultSets. Basically, it has it's query in any of the common forms. The DBManager takes the query and executes it. Then the Manager fires events on the SQLHandler for each row (in the case of a SELECT) the Handler does it's thing (normally fill a Collection with data.) When all the rows are processed, the Manager cleans up.

    This provides two features. 1. The DB logistics is encapsulted in a single place. Any bugs are found and fixed once. 2. Since the DAOs are not dealing with DB specifics, changing DB vendors (or even using mutliple DB vendors) does not affect the Handler (or at least the effect is minimized.)
  12. dao's have had their day[ Go to top ]

    Yes dao's have had there day. They appear to be an interim technology fix. The true fix is to have a persistence manager/query manager/etc. where a single set of objects is responsible for generating and executing sql and then passes off control to a higher level api. The days of having this logic spread out are hopefully coming to an end with EJB 3.0, JDO 2.0 and the storng open source products. DAO's work. No debate. But they aren't really the best we can do. And maintenance/coding is more work so lets get rid of them.

    Bruce
  13. It what Spring does.
  14. You have not addressed the issue type of Objects in the List returned when a Query is executed.

    The best way to do this is to prepare the response using SDO.
    Check out Service Data Objects.
  15. I think that like objects exists inner of A Object Word´s , they must be make in function of yours responsability and interoperabilities with others objects. ThereFore, all abstraction that we want to design (for example, QueryObject) must consider With whom it will be communicating. If we have working with DAO objects,then QueryObject will have a structure that allows to relate him with a DAO objects. If we are using other API then other structure, in spite of the fact that our aim(lens) is to separate the presentation part of a query from its core businnes.


    Francisco
  16. Ugh[ Go to top ]

    No offense to the original poster, but this looks like an anti-pattern if I ever saw one...

    First off, where is the layering? Both the middle tier and the front end are aware of back end issues (note the actual column names):
    SessionBean:
    -query.addParameter("ORDER_NO", EQUAL, orderNo);
    Presentation Layer:
    -query.addOrderBy("product_name", ASCENDING);

    As well, if there is any desire in the future to do something generically, there is nowhere to do this - all the db interation is in individual SessionBeans, with no hope of any kind of inheritence possible. At least with DAOs, one can use Interfaces with the Template Pattern that DAO children can tie into to provide a centralized location for persistence code.

    As well, don't forget, while RDBMs are the most common place to store data, they are not always the ONLY place. Do you really want all your session beans/front end aware of and tied into the persistence mechanism?

    Regards,

    Mark
  17. RE: Ugh[ Go to top ]

    About the layering that you asked I'd like to mention that sometimes the query parameters are the business part of the query regradless of how the data should be shown. Extracting an "Order" by its "orderNo" is the business, but how to prepare data for the view is the presentation part of a query. You can execute a query but show it to user with different formats e.g. sofrting, grouping... without change of the business section. The view only applies its presentation effects on the Query. That's it.

    Using GQO won't lead to awarness of SessionBeans of the persistence mechanism, since everything is done inside of the Query Engine. The sessinbean only use its API. You can design an abastract API for your Query object to hide everything you want.
  18. You should not use the table/column names was the point. use an abstract name or the java name. If the table is ORD and the id column id ORG_ID and you map that to class Order with property orderId then use orderId or id or whatever but do not tie it to the persistence mechanism. Instead map your queries as jdo/ejb/etc. does. Then you work with your model and leave persistence to the backend.

    Also we know about presentation vs. data separation. That was the whole point of the query api. Just saying thats not the only point in terms of layering. You can also hide the session bean completely using a factory. And then the query engine can be returned and who cares if it is a session bean as long as it implements the query interface (or query manager interface, etc.).

    Bruce
  19. About the column name you are completely right.

    About the next part of your writing I want to point out that it is not the sessionbean that implements the Query interface. SessionBean only makes an instance of Query and returns it.
  20. Thanks for the reply Reza.

    I would like to point out one thing and would like to know your thoughts:
    -as soon as a query, in the middle tier or presentation uses STRINGS to add parameters or do ordering, you are tied in through said String from one layer to another. In other words, if something changes with respect to the data storage, all tiers will have to be updated. This is what having the DAO layer provides buffering against.

    To make this clear, answer the following question for me:
    "If the column name in the database changes, do you have to update the session bean and presentation layer queries?"

    Yes or No?

    If Yes, do you think that it is a problem, or just one of the tradeoffs?

    Thanks,

    Mark
  21. Good point Mark.
    To avoid such problems, there can be a mapping between database field names and the names used in Java program. Also to hide field types pass parameter's value as an "Object" not a String or Long or... and it is the responsibility of the Query Engine to bind it to the SQL statement properly.
  22. Now, to the real meat[ Go to top ]

    Ok, now that we have opened up a dialogue, lets continue...

    I would like to point you toward Hibernate, which already implements much of what you would like to accomplish (it takes care of the SQL for you).

    Your original post was based on 4 points, which I'm going to comment on each in turn:
        1. Large number of DAO classes in a real system. A system with hundreds of entities and database tables needs a large number of DAO classes with a few methods in each one that all need implementation and test while their implementation is quite similar.

    Well, define 'large number'. If you have many tables, you are going to need many mappings. If you don't, you wont'. OO is about seperating concerns, and individual objects that relate to particular tables is a good approach. Ease of testing, isolation etc. Implementation is dependent on the type of data being stored and its relationship to other things - don't try and lump too much stuff together here by saying 'Persistence is persistence', its not that simple!

        2. Inconsistency with the rest of the implementation of business logic, which is developed in SessionBeans. Reading and extracting data by some criteria is part of the business and it must be maintained the same as the other parts of business. Using DAO leads to this problem since read operations are separated from other businesses, and it results in the incompleteness of the Façade layer and it is a design fault.

    Acutally, its considered a best practice to use Session Beans as a facade to pojo's for ease of testing outside of the container. As well, Hibernate makes extracting data virtually transparent. Using DAO doesn't lead to a problem, all CRUD operations should be done from the DAO, and must be exposed to other objects through the business layer. Read operations should NOT be seperated from other CRUD operations. That would be a design fault. Note - using a criteria object (see hibernate for details) is one of the best ways of filtering read data.

        3. DAO objects while doing some business logics, they are involved in handling non-business issues e.g. sorting, paging or grouping of the data that are related to the presentation layer. DAO objects need to know how to prepare data, so you need to consider all presentation related parameters in their implementations, which is again another design fault and it breaks the logic of layering.

    This is quite off the mark. DAO deal with data retrieval based on certain criteria. Sorting, paging or grouping can all be done through various means in the presentation layer. In fact, once the presentation layer receive the set of data from the business layer, it can massage it however it wants. This is definitely NOT a design fault, this would be an implementation issue if you have created DAO's that concern themselves with presentation.

    When dealing with data retrieval, you have to understand that in the general case, you get all the data from the backend and give it to the front end. The front end plays with it as it desires (sorting, grouping etc). This is a clean seperation of concerns. If you aren't following this approach, maybe because there is too much data to do this at once, then you can consider optimizing (ie. getting a smaller subset of data at one time, or only a page of data at once etc.) As soon as you start down that path, you have to realize the tradeoffs - faster response, but it will require more co-ordinate between layers (for the particular use case in question). Don't blame design on optimization choices, just remember the tradeoffs.

        4. There is no declarative mechanism to control the security of DAO methods, while the rest of your business can enjoy declarative security of EJBs.

    -Please see www.springframework.org for other examples of using declarative transactions/security. EJB security is not the only answer, and in the majority of cases, not the ideal. As well, DAO's should be accessed through layers that have already been authorized (ie. a session bean) so this isn't a valid criticism. If you are suggesting that all associated (ie. helper) classes that EJB's use should have security checks run, I don't think you have thought through the possible performance impacts...running checks on all those objects when the objects that USE them have already been validated is wasteful. Certainly the EJB framework doesn't allow this. If you are concerned about the presentation layer accessing back end entities unauthorized, there are larger concerns because everything from the presentation layer should goto the middle tier first.

    Really, I think you will find that Hibernate with criteria objects will accomplish almost everything that you are looking for, and the rest with a better understand of seperation of concerns.

    Best regards,

    Mark
  23. RE: Now, to the real meat[ Go to top ]

    Mark says: When dealing with data retrieval, you have to understand that in the general case, you get all the data from the backend and give it to the front end. The front end plays with it as it desires (sorting, grouping etc). This is a clean seperation of concerns. If you aren't following this approach, maybe because there is too much data to do this at once, then you can consider optimizing (ie. getting a smaller subset of data at one time, or only a page of data at once etc.) As soon as you start down that path, you have to realize the tradeoffs - faster response, but it will require more co-ordinate between layers (for the particular use case in question). Don't blame design on optimization choices, just remember the tradeoffs.

    I ask: What if I have a table with 10 million rows of, say, web sessions, and a user is paging through these rows on their browser 50 at a time, and decides to filter by Home Page, sort by Browser Type, and skip to page 928. Do you really expect me to pull all 10 million rows into my application to do my filtering, sorting, and skipping from the presentation layer? Then why did they even bother to add "WHERE" and "ORDER BY" to SQL and go through all the trouble of writing sophisticated database engines, when all we need to do is "get all the data from the backend and give it to the front end"?
  24. RE: Now, to the real meat[ Go to top ]

    Then why did they even bother to add "WHERE" and "ORDER BY" to SQL and go through all the trouble of writing sophisticated database engines, when all we need to do is "get all the data from the backend and give it to the front end"?

    I think the goal is to make your system such that the front-end doesn't know where the data is coming from. If you need to use special queries you can still do so without going directly to the DB from the front-end.
  25. RE: Now, to the real meat[ Go to top ]

    I think the goal is to make your system such that the front-end doesn't know where the data is coming from. If you need to use special queries you can still do so without going directly to the DB from the front-end.

    There are much more elegant, optimal, and practical ways of doing that than expecting the front end to do all the data filtering, sorting, etc. Database engines are very good at their core task of storing and retrieving data.
  26. RE: Now, to the real meat[ Go to top ]

    There are much more elegant, optimal, and practical ways of doing that than expecting the front end to do all the data filtering, sorting, etc. Database engines are very good at their core task of storing and retrieving data.

    Apparently you did not read what I wrote or you didn't understand me.
  27. What about Distribution Concept[ Go to top ]

    I guess a few things are missing,

    The idea of distribution if to make any process transparent of clients, this solution does not comply with the distribution idea

    The other question is, how do you apply the security to the modified query object? and then I guess it will create a Network overhead as so much data transfer object is involved,

    I guess strategies introduced in TO pattern are good workaround for such drawbacks

    Sasan
  28. c#[ Go to top ]

    the c# code { Console.writeline(":)"); }