How to efficiently load objects with lists from sql-databases

Question

Supposed I have the following class:

class Example {
  List<ObjectA> objectsOfA;
  List<ObjectB> objectsOfB;
  List<ObjectC> objectsOfC;    
  ....
}

I would usually have these tables:

Example (Id, other-attributes)
ObjectA (some attributes, ExampleId)
ObjectB (some attributes, ExampleId)
ObjectC (some attributes, ExampleId)

If I want to restore an Example-object, I imagine I have two options:

join every table together, resulting in a lot of entries and organizing it in hashmaps to reassemble the objects
loading every Example and for every example doing single requests for the lists of ObjectA, ObjectB, ObjectC.

If the number of Example-entries is low, option2 might be the best. But for every single entry in Example, I need to do x more requests where x is the number of tables in my class.

Otherwise, having everything in a single join requires me to reorganize all the data by myself - creating hashmaps, iterating through data and stuff, which is usually a lot of work in code.

I also get the possibility of lazy loading with option 2.

Do I have other choices? Did I miss something? (Of course I know about ORMs, but I decided to not use them on Android)

Hey, I have seen your answer and find it very plausible. I will have a look if there is any other suggestion coming in and will check your answer as correct otherwise. Right now, I'm tended to use three queries instead of one or hundreds. One for Example, and one for each object itself. Put them in a map and iterate over Example and see which objects from a b and c match to add them to the list. What do you think about this approach? — Frame91
– Frame91, Commented Oct 11, 2015 at 21:51
That corresponds to option 2 I suggested. Yeah I tend to think of might be easier and more efficient to implement — janos
– janos, Commented Oct 11, 2015 at 22:10

janos · Accepted Answer · 2015-10-11 06:52:14Z

1

If I understood correctly, your question is, which of these ways is better to load data from a main table and related tables:

Load a JOIN of the main table and related tables
Load the data of the main table and related tables separately and join them in Java
Something else

Unless you are extremely constrained for bandwidth, I'd say option 1 is simple and I would go with that. It's easier to let the DB do the joining and in Java just map the records to objects.

If saving bandwidth with the database is important, then option 2 is better, because every piece of data will be fetched only once without duplication, as the result of a JOIN in option is essentially denormalized data.

In any case, I recommend to follow Occam's razor: the simplest solution is often the best.

answered Oct 11, 2015 at 6:52

janos

126k31 gold badges242 silver badges253 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to efficiently load objects with lists from sql-databases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related