User's Guide
   PART 3. Java in the Database
     CHAPTER 17. Using Java in the Database       

Comparing Java fields and objects

Java classes are user-defined data types with much more richness that traditional SQL user-defined data types. This raises issues about how Java columns behave in a relational database, compared to columns based on traditional SQL data types.

In particular, the issue of how objects are compared has implications for the following:

• Queries with an ORDER BY clause, a GROUP BY clause, a DISTINCT keyword, or using an aggregate function.

• Statements that use equality or inequality comparison conditions

• Indexes and unique columns

• Primary and foreign key columns.

Ways of comparing Java objects 

Sorting and ordering rows, whether in a query or in an index, implies that the values on each row are being compared. If you have a Java column, you can carry out comparisons in the following ways:

• Compare on a public field     You can compare on a public field in just the way that you compare on a regular row. For example, you could execute the following query:

  SELECT name, JProd>>unit_price
  FROM Product
  ORDER BY JProd>>unit_price

  This kind of comparison can be used in queries, but is not applicable for indexes and key columns.

• Compare using a compareTo method     Java objects that have implemented a compareTo method can be compared. The Product class on which the JProd column is based has a compareTo method that compares objects based on the unit_price field. This permits the following query:

  SELECT name, JProd>>unit_price
  FROM Product
  ORDER BY JProd

  The comparison needed for the ORDER BY clause is automatically carried out based on the compareTo method.

  Comparing Java objects

In order to compare two objects of the same type, you must implement a compareTo method:

• For columns of Java data types to be used as primary keys, indexes, or as unique columns, the data type class must implement a compareTo method.

• To use ORDER BY, GROUP BY, or DISTINCT clauses in a query, the values of the column must be compared. The column class must have a compareTo method for any of these clauses to be valid.

• Functions that employ comparisons, such as MAX and MIN, can only be used on a Java class if the class has a compareTo method.

Requirements of the compareTo method 

The compareTo method must have the following properties:

• Scope     The method must be visible externally, and so should be a public method.

• Arguments     The method takes a single argument, which is an object of the current type. The current object is compared to the supplied object. For example, Product.compareTo has the following argument:

  compareTo( Product anotherProduct )

  The method compares the anotherProduct object, of type Product, to the current object.

• Return values     The compareTo method must return an int data type, with the following meanings:

  • Negative integer     The current object is less than the supplied object. It is recommended that you return -1 for this case, for compatibility with compareTo methods in base Java classes.

  • Zero     The current object has the same value as the supplied object.

  • Positive integer     The current object is greater than the supplied object. It is recommended that you return 1 for this case, for compatibility with compareTo methods in base Java classes.

Example 

The Product class installed into the sample database with the example classes has a compareTo method as follows:

public int compareTo( Product anotherProduct ) {
    // Compare first on the basis of price
    // and then on the basis of toString()
    int lVal = unit_price.intValue();
    int rVal = anotherProduct.unit_price.intValue();
    if ( lVal > rVal ) {
      return 1;
    }
    else if (lVal < rVal ) {
      return -1;
    }
    else {
      return toString().compareTo( anotherProduct.toString() );{
      }
    }
  }

This method compares the unit price of each object. If the unit prices are the same, then the names are compared (using Java string comparison, not the database string comparison). Only if both the unit price and the name are the same are the two objects considered the same when comparing.

Make toString and compareTo compatible 

When you include a Java column in the select list, the value of the toString method is returned. When comparing columns, the compareTo method is used. If the toString and compareTo methods are not implemented consistently with each other, you can get inappropriate results such as DISTINCT queries that appear to return duplicate rows.

For example, suppose the Product class in the sample database had a toString method that returned the product name, and a compareTo method based on the price. Then the following query would return duplicate values:

SELECT DISTINCT JProd 
FROM product

JProd
Tee Shirt
Tee Shirt
Baseball Cap
Visor
Sweatshirt
Shorts

Here, the returned value being displayed is determined by toString. The DISTINCT keyword eliminates duplicates as determined by compareTo. As these have been implemented in ways that are not related to each other, duplicate rows appear to have been returned.