Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi all,
Vanilla are planning an update to the site on April 24th (next Wednesday). It is a major PHP8 update which is expected to boost performance across the site. The site will be down from 7pm and it is expected to take about an hour to complete. We appreciate your patience during the update.
Thanks all.

Java: Map Related DB Tables to Classes

  • 16-04-2014 10:55am
    #1
    SimonTemplar
    Registered Users Posts: 2,815 ✭✭✭


    Let’s say I have two tables in my db which are related via a key. For example, a Country table and a City table. The City table contains as a foreign key the primary key of the country to which the city belongs.

    Similarly, in my application code, I have a Country class and a City class, each with members that map to columns in the db tables. The Country class has a member which is a collection of City objects.

    Basically my question is what is the best way to create the collection of Country objects, which in turn contain a collection of its City objects. (I know that can probably be achieved through an ORM technology but I need to use standard jdbc for a number of reasons). I can easily create a collection of objects from results returned from a single query, but I’m unsure how to deal with building the collection of City objects for each Country because I’ll be dealing with two separate queries (one SELECT * from Country and one SELECT * from CITY).

    Should I run the Country SELECT first and build the Country object collection, then run the City SELECT, create each City Object, and pass that City object to the relevant country’s addCity method?
    Or, should I create each Country object with all its City objects before moving onto the next country.

    I’m guessing the first option would just involve two queries to the db : SELECT * from the Country table and again for the City table. But the second option would involve querying the db for a list of a country’s cities every time a Country object is being created.

    Or perhaps there is a third option that I’m not seeing.

    Any advice for the best/most efficient approach to this would be welcome.

    Thanks.


Comments

  • #2
    oscarBravo
    Technology & Internet Moderators Posts: 28,791 Mod ✭✭✭✭oscarBravo


    Just run a single SELECT query joining the two tables, and as you're iterating through the results to create cities, each time you come across a country you haven't seen before, create that object first. Order the results by country, city.


  • #3
    cgarvey
    Registered Users Posts: 3,884 ✭✭✭cgarvey


    Most ORM uses standard JDBC. If you mean you can't import more libraries / add more overhead, then fair enough, but just realise that ORM will most likely do a better job than you.

    That aside, your manual solution will have to based on a few decisions.

    Are multiple DB queries better than one big query? Easier for you to implement multiple queries, but can be quite taxing on a DB server if you're doing this a lot. For one big query, you could "SELECT country.id, country.name, city.id, city.name FROM city INNER JOIN country ON country.id=city.country_id", and traverse the single result set in Java (using hashes, etc.). Or, you can run one query for the country list and a separate query for each city. Your decision might be made up by allowing for countries with no cities (INNER JOIN would break, and OUTER JOIN might be too expensive an operation on the DB server).

    Also, you could cache it, if the country/city list doesn't change often / at all.

    Also, you could delay the city query until you need the city data. I.e. if you just want a list of countries, you've no need to get each country's list of cities until they're needed. You could, for example, have a "getCities()" getter in your Country class that, when called, checks if the city list already exists and, if not, retrieve them from the DB. Again, that's down to your particular design.

    So, it all depends on how you'll use the data (and whether the data changes / changes frequently). Still, though, ORM will most likely do a better job than anything you come up with.


Advertisement