Collection Contents Index Querying Java objects Returning result sets from Java methods pdf/chap17.pdf

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:

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:

Top of page  Comparing Java objects

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

Requirements of the compareTo method 

The compareTo method must have the following properties:


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:

FROM product


Tee Shirt

Tee Shirt

Baseball Cap




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.

Top of page  

Collection Contents Index Querying Java objects Returning result sets from Java methods pdf/chap17.pdf