I have a database column that contains a key into a properties file and I want to sort that column and return paginated results. The problem is that when you order that column, it sorts based on the key name rather than the localized string that the key is associated with.
To illustrate imagine this:
Column1
key6
key5
.
.
.
and mykeys.properties contains this
key5=How do you do
key6=Hi
When you sort on column 1, the row containing key5 will come before the row containing key6, and so in the displayed results, How do you do comes before Hi, which is not the desired result. Obviously, it would be a bad idea to return the entire result set, substitute the key values for the keys and then sort, because the result set could be large. It would also be bad to return just a page of the result set and then sort by key value because we aren’t taking all the data into account.
Let me verify that your properties file is actually a “file”? As in, sitting on the hard drive, completely separate from and uncontrolled by the database.
If I understand correctly. You don’t have much option. If you have an unsorted data set, the only way to get it sorted is to sort it. If you are worried about sorting each time at run, then you have to maintain a sorted list. Depending on the flavor of database you are using it can be from impossible to medium difficult to set up a customized index field in the database to maintain sorted order.
The properties files are a feature of Java. Yes, they are actual files in the file system. The idea is that you can have several files called myProps.properties, myProps.properties_fr_FR, myProps.properties_de, etc. and the correct one will be picked automatically depending on the user’s locale.
This leads to the issue of needing to sort the column differently depending on the locale.
Much of this depends upon your key size, and for a lengthy key this is not a viable solution. But… you create a SQL statement that includes a DECODE or CASE statement that forces the database to return the entire string as loaded from the properties file. So, for SQL Server, you would have something like the following:
SELECT Column1 CASE WHEN ‘key5’ THEN ‘How do you do’ WHEN ‘key6’ THEN ‘Hi’ ELSE ‘Unknown’ END AS Column1
SORT BY Column1
Voila! Everything is sorted by the text value… Of course, this means you have to build the SQL string each time based upon locale, but that is trivial to implement in Java and only eats up a few cycles.
If you’re using SQL Server, you could write an extended stored procedure (that is, a function library written in C or C++ or VB or C# and called from a SQL Server stored proc or script) that could incorporate data from the file for sorting purposes. Depending on how much data you have, you could have performance issues.
A more efficient solution would be to write a routine that imported the data from the properties files and then use standard SQL to use that data in your sorting routine. But you’d have to re-run the import whenever the properties file changed.
If you’re not using SQL Server, I’m guessing most large DBMSs provide similar features, but that’s outside my area of expertise.
Are the applications which need to use the Properties always database applications? If so you could subclass java.util.Properties to load/store direct to a table rather than flat files.