Face a Nested beans query using Spring Jdbc Template

Question

Consider these beans:

class Country {
    String name;
    String code;
    ...
    List<City> cities;
}

class City {
    String name;
    String zip;
    ...
    List<Street> streets;
}

class Street {
    String name;
}

I have to get that nested beans from 3 tables in a database.

I can solve in two ways:

• Query loop (query on countries, looping results querying their citiy, looping results querying their streets....)

• Full flat datasource (a single wide select joining all 3 tables with all rows at maximum details ordered by outer to inner fields) and after that split that.

The first question: is it the second solution the best choice considering that the nesting level can be deeper than 3 levels?

Let's say yes, I suppose to use the second option:

select * 
from countries c 
join cities t ... 
join streets s ... 
order by c.name, c.code, ..., t.name, t.zip, ...

The second question: how can I store that ResultSet in beans with JdbcTemplate?

Is there something for this purpose that split rows to nested beans? I cannot use a custom RowMapper because I do not have a single outer bean for each row.

Answer 1

Performance-wise, the first solution is really bad. This means that you'll run a lot of queries against your database, and the number of these queries really depends on number of rows.

I believe the database just won't be able to handle that eventually.

The second approach is much better, you can handle it in one query and since you use jdbc template, creating the corresponding objects must be not hard.

If you still have a lot of data, you might want to consider retrieving with paging (bring 100 records, than bring yet another 100 records).

Most of the DB drivers already do this in one way or another, you might want to add an "application level" handling for this if you don't want to keep in memory all the data at once, but this really depends on use cases.

If the tables a really big though joins can also be costly, but in this case the first approach will fail in any case (just too many queries because the data size is big).