Where to perform immutable objects logic?

[Esko]

A relatively simple question, I'd like to get some feedback from other
developers. Let's say I have the following a table with a list of countries,
which doesn't change (at least not very often). Now, in my table, I have the
list ordered alphabetically, however when I want to then query this table
from my application and display it on a jsp I want "USA" to list first,
followed by "Canada", then the rest of the countries. What I am doing at the
moment is grabbing the list from the db via JDBC into a Collection, then
removing both those entries, and then reinserting using List.add(pos,obj) at
positions 0 and 1, respectively. Should I be doing this in my app, or should
I do it at the database level? The options I have are to drop that table and
recreate it with the appropriate ordering, or change my select statement to
somehow put those two countries first (I'm not sure how to do that).

Any insight?

Thanks,

-Esko

 

[Oliver Wong]

A relatively simple question, I'd like to get some feedback from other
developers. Let's say I have the following a table with a list of
countries, which doesn't change (at least not very often). Now, in my
table, I have the list ordered alphabetically, however when I want to then
query this table from my application and display it on a jsp I want "USA"
to list first, followed by "Canada", then the rest of the countries. What I
am doing at the moment is grabbing the list from the db via JDBC into a
Collection, then removing both those entries, and then reinserting using
List.add(pos,obj) at positions 0 and 1, respectively. Should I be doing
this in my app, or should I do it at the database level? The options I have
are to drop that table and recreate it with the appropriate ordering, or
change my select statement to somehow put those two countries first (I'm
not sure how to do that).

Any insight?

Disclaimer: I'm not sure that I have enough experience in relational
database design to make good design decision recommendations.

You could add an extra column, "favored" or "rank" or something like
that. Make USA's rank be 1, Canada's rank be 2, and all the other ranks be
NULL.

Then, in your SQL SELECT statement sort ascending first by rank, then by
country name. Hopefully NULL happens after 2 (can't recall off the top of my
head). If not, reverse the order of the sort, and swap the values of USA and
Canada (i.e. 2 and 1 respectively).

The benefit of this design is that you have a lot of freedom in grouping
the countries by giving countries in the same group equal rank (maybe list
USA, then Canada, then all the American countries (e.g. Mexico), then all
the european countries, and so on).

- Oliver

 

[Thomas G. Marshall]

Oliver Wong coughed up:

Disclaimer: I'm not sure that I have enough experience in
relational database design to make good design decision
recommendations.
You could add an extra column, "favored" or "rank" or something
like that. Make USA's rank be 1, Canada's rank be 2, and all the
other ranks be NULL.

Then, in your SQL SELECT statement sort ascending first by rank,
then by country name. Hopefully NULL happens after 2 (can't recall
off the top of my head). If not, reverse the order of the sort, and
swap the values of USA and Canada (i.e. 2 and 1 respectively).

The benefit of this design is that you have a lot of freedom in
grouping the countries by giving countries in the same group equal
rank (maybe list USA, then Canada, then all the American countries
(e.g. Mexico), then all the european countries, and so on).

- Oliver

If what we're talking about amounts to a /GUI/ table (I'm not a db nor jsp
guy), then it would be relatively straightforward to create a Comparator
that handles the country names in a normal lexical fashion, but with the
quirk that USA and Canada sort at the top.

Such a comparator is likely to be useful in nearly /any/ sorting facility,
at least all the standard ones I can think of use it.


--
Having a dog that is a purebred does not qualify it for breeding. Dogs
need to have several generations of clearances for various illnesses
before being bred. If you are breeding dogs without taking care as to
the genetic quality of the dog (again, being purebred is *not* enough),
you are what is known as a "backyard breeder" and are part of the
problem. Most of the congenital problems of present day dogs are
traceable directly to backyard breeding. Spay or neuter your pet
responsibly, and don't just think that you're somehow the exception and
can breed a dog without taking the care described.