What locale should I use for case-insensitive queries from Java?

Question

The classic way to query an SQL database case-insensitively from Java is as follows:

String name = ...;  // get the user's input (case is unknown)
String sql = "select * from Person where lower(name) = ?";
Object jdbcBindVariable = name.toLowerCase();
// ... using JDBC, bind that variable and run the SQL query

The problem is that lower-casing is a locale-specific operation. For example, lower-casing the letter "I" gives different results in English and Turkish. In the above code, there are two lower-casing operations:

• The String#toLowerCase() method
• The lower() database function

How can I make sure that Java and the database are using the same locale and thereby performing a valid comparison?

I'm aware that the String class has a toLowerCase(Locale) method, but how do I know what Locale the database is using? Can I check this programatically, or do I have to hard-code the locale to the one with which I think the database (in this case Oracle 10g) is configured?

Answer 1

The simple answer is let the database do it. That way the way the bind variable is put into lower case will be consistent with the way the column value is put into lowercase.

String sql = "select * from Person where lower(name) = lower(?)";

Answer 2

... but how do I know what Locale the database is using? Can I check this programmatically ...

There doesn't appear to be a portable (database independent) way to do this, but you can apparently use the following query to get the charset used by an Orable database:

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

This page gives more details.

As for actually doing the comparison, you would be best off (*) letting the database take care of the lower-casing, as @Gary suggests. The JDBC driver will take care of converting Java (UTF-16) Strings into whatever the database is using.

(* In fact, I don't think you have much choice, unless you are prepared to wear the cost of storing mixed-case and lower-case copies of all queriable strings in the database.)