6

After looking around the internet, I decided to create DAOs that returned objects (POJOs) to the calling business logic function/method.

For example: a Customer object with a Address reference would be split in the RDBMS into two tables; Customer and ADDRESS. The CustomerDAO would be in charge of joining the data from the two tables and create both an Address POJO and Customer POJO adding the address to the customer object. Finally return the fulll Customer POJO.

Simple, however, now i am at a point where i need to join three or four tables and each representing an attribute or list of attributes for the resulting POJO. The sql will include a group by but i will still result with multiple rows for the same pojo, because some of the tables are joining a one to many relationship. My app code will now have to loop through all the rows trying to figure out if the rows are the same with different attributes or if the record should be a new POJO.

Should I continue to create my daos using this technique or break up my Pojo creation into multiple db calls to make the code easier to understand and maintain?

Improve this question
2

3 Answers 3

Reset to default
10

You should put correctness first. Create your data structures so that they model the domain in question in a correct and effective way that makes it easy for your code to work with.

Beyond that, try to minimize database calls, especially if the database is not local (residing on the same machine as the program calling it). Network latency is a real consideration here, and it can be non-trivial.

Let's say you have an operation that requires 10 database calls. If your network latency is 100 ms, this operation will take 1 second of pure overhead just communicating with the server, in addition to whatever amount of time it takes to actually do the work involved. If your latency is 1 second, it will waste 10 seconds on network latency alone. But if you get that down from 10 calls to 1, suddenly even in really ugly latency conditions, you're not wasting much time on network overhead.

As a general rule of thumb, if you're just retrieving data simply (and not doing heavy processing of the data inside the database server or on the client), the biggest bottleneck by far in a system with a non-local database will be network latency. So if you can reduce the number of calls, even if it means you need to do extra work on the client side once you've retrieved it, you'll still probably come out ahead.

As always, remember the most important rule of optimization: measure first! Optimize by hard data, not by rules of thumb like the one I just described, or you could easily end up doing a lot of hard work that slows things down! But in general, keeping the number of queries down is usually the best route.

Improve this answer
4
  • The idea of using a customer DAO to return both the Customer info as well as the information required to create the ADDRESS for the customer, was due to the desire to reduce db calls. However, now I have a requirement to search data across multiple tables and return the resulting POJOs. This would work fine until i realized that the DBMS will return 10 records for every one actual Pojo, the POJO has a one to many relationships as an arraylist<type> attribute and the group by forces individual rows. Should I break this up into multiple calls?
    – None None
    Dec 5, 2012 at 4:52
  • 1
    +1 For the measure first comment alone - not enough people preaching that :-).
    – Martijn Verburg
    Dec 5, 2012 at 7:56
  • @NoneNone, no you should not break it up into separate queries.
    – HLGEM
    Dec 5, 2012 at 15:12
  • 1
    Thanks HLGEM. I would not like to break up the query but I am working on a search page that will query the database for matching records. The query will read across multiple tables. I have successfully queried the results and created the return objects however, I now realize that the only way i can create pagination (query 20 rows first, then the next 20 ) is by doing the initial query first, getting object ids, and lazy load the rest of the data. I originally thought I could inner join and create full objects to return. doesn't seem possible now that I want to let the user change pages.
    – None None
    Dec 7, 2012 at 4:35
0

I suggest you consider using DTOs after you have performed the correct join on the data source using SQL (if you are using RDBMs). In addition, you can also utilize the Multiple Result Sets concept to return different sets of objects in 1 server call if necessary.

DTO is not POJO (see:Difference-between-DTO-and-POCO - I know you are not asking about .NET but the concept is likely to be similar for this argument.)

Using this approach you will communicate less data between the server and the client and you will not have to do joins in the client logic manually specially, that to my knowledge, Java does not provide an equivalent of .Net's LINQ.

Also, consider using (or getting to know about) a good ORM. A good ORM will most likely offer you a practical approach.

Improve this answer
5
  • I read the article. I think he is just calling the business logic layer a poco and the entities a dto. As for orms, i am aware of the but do not like them very much. Thanks for the comment.
    – None None
    Dec 5, 2012 at 16:28
  • I think you did not get the picture. Entities are not always DTOs (in general, they are not). DTO does not have any behavior except for its serialization/de-serialization. Its like a truck that moves data between layers or tiers. This is a key concept I suggest you look at in some detail. Also, for professional applications, it is really hard to come up with a convincing argument against all ORMs.
    – NoChance
    Dec 5, 2012 at 22:01
  • Can u give me an example of an entity and its dto equivalent? I just do not see why you cant create an entity that you can pass around.
    – None None
    Dec 5, 2012 at 22:45
  • You can pass an object around however, in case of a DTO, the DTO does not have to be the same as the Entity. For example, say you are listing customers with their net balance (say you are AMEX!). In this example, you may have 1 customer entity but different other domain entities that you run SQL against to get the balance. The DTO in this case could be the customer and the net balance. This DTO (or a list of) does not resemble a single domain Entity. The DTO does not have to save data in database. I hope this is a bit more clear.
    – NoChance
    Dec 5, 2012 at 23:05
  • Thanks Emmad, I am thinking that the DTO then serves as a way to "hold" data that belongs to multiple domain entities. I have never really liked the idea of a entity saving itself to the db. I usually like to create a DAO which the object gets passed to for saving of the data.
    – None None
    Dec 7, 2012 at 4:37
0

If the query is returning more rows than expected then the query if badly written.

You have to run a query to get the data for Customer POJO. That query should return a single row, so the DAO populates a single POJO.

Then a second query is run to get the rows from the "many" table , given the foreign key ( customer ID ) and create as many POJOS as rows are found. Then that list of address objects is assigned to the customers POJO.

Customer class has a member of type List<Address>.

If the DB access is expensive, you can do it lazily, that's to say, fetch the addresses only when getAddress of getAddressList is first called.

That same technique can be used to all aggregations or compositions in your Customer class that are read from the DB.

EDIT:

About performance: Customer table must have a PK, maybe customerID, so retrieving the row must be lightning fast if you are using a modern RDBMS. Also the child tables must have a PK, and a FK pointing to the PK of parent table. That FK is also indexed. That's like the very basic task a RDBMS is made for.

Regarding the network traffic between the app server (or client app) and the DB server involved, that can also be optimized.

The point is:

Hardware and transport issues can be solved by trowing money at then.

Complexity problems derived from writing code to do things the RDBMS does more efficiently, cannot be solved by throwing money at them.

Hardware is cheap, programmers are expensive.

Improve this answer
2
  • Thanks for the response, then you are in favor of breaking up the query into multiple queries instead of one big query and then iterating through the returned rows "manually" finding the associations. What do you think about the performance of multiple queries vs one big query. BTW: the query is as "correct" as it can be. The reason for the multiple records is valid from an SQL perspective and from a design perspective. The only two "fixes" for this problem are to break up the queries into multiple calls or use app side code to iterate through the result set. Thanks for your thoughts.
    – None None
    Dec 5, 2012 at 6:22
  • @NoneNone I added and edit to the answer to accommodate to your comment.
    – Tulains Córdova
    Dec 5, 2012 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.