collation=collation attribute

Function

The collation attribute is an optional attribute that specifies whether collation is based on the territory specified for the database or Unicode codepoint collation. The valid values for the collation attribute are:

UCS_BASIC
Unicode codepoint collation. This value is the default.
TERRITORY_BASED
Based on the language specified with the territory attribute. The default collation strength for the locale is used. The default for Derby is commonly TERTIARY, in which character case is significant in searches and comparisons.
TERRITORY_BASED:PRIMARY
Territory based with collation strength PRIMARY. Specify this value to make Derby behave similarly to many other databases, for which PRIMARY is commonly the default. PRIMARY typically means that only differences in base letters are considered significant, whereas differences in accents or case are not considered significant.
TERRITORY_BASED:SECONDARY
Territory based with collation strength SECONDARY. SECONDARY typically means that differences in base letters or accents are considered significant, whereas differences in case are not considered significant.
TERRITORY_BASED:TERTIARY
Territory based with collation strength TERTIARY. TERTIARY typically means that differences in base letters, accents, or case are all considered significant.
TERRITORY_BASED:IDENTICAL
Territory based with collation strength IDENTICAL. IDENTICAL means that all differences are considered significant.
Restriction: The collation attribute can be specified only when you create a database. You cannot specify this attribute on an existing database or when you upgrade a database.

If you specify the collation attribute with the value TERRITORY_BASED, or one of its variants with a specific collation strength, the collation is based on the language and country codes that you specify with the territory attribute.

If you do not specify the territory attribute when you create the database, Derby uses the java.util.Locale.getDefault method to determine the current value of the default locale for this instance of the Java Virtual Machine (JVM).

Note: The collation attribute applies only to user-defined tables. The system tables use the Unicode codepoint collation.

For information on how Derby handles collation, see "Creating a database with territory-based collation" and "Character-based collation in Derby" in the Derby Developer's Guide.

Example

The following example shows the URL to create the MexicanDB database. The territory attribute specifies Spanish for the language code and Mexico for the country code. The collation attribute specifies that the collation for the database is territory based.

jdbc:derby:MexicanDB;create=true;territory=es_MX;collation=TERRITORY_BASED