File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes DB search, order by, and i18n Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "DB search, order by, and i18n" Watch "DB search, order by, and i18n" New topic

DB search, order by, and i18n

Henrik Bentel

Joined: Jan 29, 2004
Posts: 6
Hope somone has done something similar before
Let's say you have large table where one of the columns describe a type. Let's say the type is fruit. every fruit type is assigned an integer(apple =1, orange=2) and so on.
Assume your application allows to filter and sort on the data in the table, where one of the columns you can order by is the fruit type columns.
The catch is that the application supports i18n and sometimes you want to sort on fruit types in french and sometimes in german for the same installation. I've searh for topics on this but haven't found anything.

I've been thinking of putting the language files as tables in the database, a table per locale(example, a table called LanguageResources_de, and another one called LanguageResources_fr, then use a lookup table that gave me the table name for a given locale.
But how this would fit in with using one query to do the search and looking up the language strings and ordering I don't know.
I guess it could be encapsulated with a db function of some sort. or could it?
Looking for some server independence(oracle, db2, and postgres).
Dana Hanna
Ranch Hand

Joined: Feb 28, 2003
Posts: 227
Why would a different language sort differently? Just sort the column, and the values in it will sort!
I guess maybe I don't understand the question!
Henrik Bentel

Joined: Jan 29, 2004
Posts: 6
ok so the colum I want to sort on has integer values 2,4,5,1,2, etc.
These represent types, such as apple, oranges and so on. The sort of the column is expected to be alphabetical.
Apple in german is apfel and orange is apfelsine, pomme , orange is orang�(I think).
So displayed and sorted in german the order is apfel , apfelsine.
In french it is orang�, pomme. So a sort in french would put orange before apple.
Assume I have thousands of records, even hundreds of thousands of records and I want the database to do the sort for me. To sort in my java application would require me to read the entire resultset which would get VERY memory expensive.
My application uses integer types in the colum since I support multiple languages at the same time. So I can't just have the german or french word in the column.
That's where I wonder if I could put in tables to hold language strings in the database and another lookup table to find the language table name at query time.
I agree. Here's the link:
subject: DB search, order by, and i18n
It's not a secret anymore!