User's Guide
   PART 3. Java in the Database
     CHAPTER 16. Welcome to Java in the Database       

A Java in the database exercise

Invoking Java operations from within a SQL environment will likely be new to Java developers and database users alike.

This section is a primer for invoking Java operations on Java classes and objects using SQL statements. The examples use the Invoice class created in A Java seminar.

  A sample Java class

The following class declaration is used throughout all the following examples.

public class Invoice {

  // Fields
  public String lineItem1Description;
  public double lineItem1Cost;

    public String lineItem2Description;
    public double lineItem2Cost;

    // An instance method
    public double totalSum() {
        double runningsum;
        double taxfactor = 1 + Invoice.rateOfTaxation();
        
        runningsum = lineItem1Cost + lineItem2Cost;
        runningsum = runningsum * taxfactor;
        
        return runningsum;
    }

    // A class method
    public static double rateOfTaxation() {
        double rate;
        rate = .15;
    
        return rate;
    }
}

Caution: use a Java-enabled database    
The following section assumes the database you are connected to is Java-enabled. For more information, see Java-enabling a database.

  Installing Java classes

Any Java class must be installed to a database before it can be used. You can install classes from Sybase Central or Interactive SQL.

  To install the Invoice class to the sample database from Sybase Central:

1. From Sybase Central, choose Tools->Connect->Adaptive Server Anywhere.

2. Select the ASA 6.0 Sample data source, and connect.

3. Open the Java Objects folder and double click Add Java Class or Jar. The Install a New Java Object wizard is displayed.

4. Select the Java Class File option, and click Next.

5. Use the Browse button to locate Invoice.class, which is in the jxmp subdirectory of your Adaptive Server Anywhere installation directory.

  To install the Invoice class to the sample database from Interactive SQL:

1. Start Interactive SQL and connect to the ASA 6.0 Sample ODBC data source. Enter the following SQL statement:

   INSTALL JAVA NEW 
   FROM FILE 'path\jxmp\Invoice.class';

   where path is your Adaptive Server Anywhere installation directory.

Notes 

• At this point no Java operations have taken place. The class has been installed into the database and is ready to be used as the data type of a variable or column in a table.

• Changes made to the class file from now on are not automatically reflected in the copy of the class in the database.

     For more information on installing classes, and for information on updating an installed class, see Installing Java classes into a database.

  Creating SQL variables of Java class type

The following statement creates a SQL variable named Inv of type Invoice, where Invoice is the Java class that you installed to a database.

CREATE VARIABLE Inv Invoice;

Once any variable has been created, it can only be passed as a value that is the same data type as its declared data type. In this case, the variable Inv can only contain a reference to an object of type Invoice.

Perform the following statement to identify the current value of the variable Inv.

SELECT IFNULL(Inv, 'No object referenced',
  'Variable not null: contains object reference')

The SQL IFNULL function accepts three arguments: the first is the variable to be tested; the second is the expression selected if the value is NULL; the third is the expression selected if the variable is not NULL.

At this point the variable Inv contains a NULL because no value has been passed to it. The only value it could contain is an object reference where the object is of type Invoice.

To pass a value to Inv, the default constructor of the Invoice class needs to be invoked. The NEW keyword is used to indicate a constructor is being invoked and an object reference is being returned.

SET Inv = NEW Invoice();

The Inv variable now has a reference to a Java object. To verify this a number of select statements can be executed using the variable.

For example the following statement shows the variable contains a value.

SELECT IFNULL(Inv, 'No object referenced',
    'Variable not null: contains object reference')

The Inv variable should contain a reference to a Java object of type Invoice. Using this reference, any of the object's fields can be accessed and its methods can be invoked.

  Invoking Java operations

If a variable (or column value in a table) contains a reference to a Java object, then the fields of the object can be passed values and its methods can be invoked.

For example, a variable of type Invoice (a user-created class) that contains a reference to an Invoice object will have four fields, the value of which can be set using SQL statements.

Passing values to fields 

The following SQL statements set the field values for just such a variable.

SET Inv>>lineItem1Description = 'Work boots';
SET Inv>>lineItem1Cost = '79.99';
SET Inv>>lineItem2Description = 'Hay fork';
SET Inv>>lineItem2Cost = '37.49';

Each line in the SQL statements above passes a value to a field in the Java object referenced by Inv. This can be shown by performing a select statement against the variable. Any of the following SQL statements return the current value of a field in the Java object referenced by Inv.

SELECT Inv>>lineItem1Description;
SELECT Inv>>lineItem1Cost;
SELECT Inv>>lineItem2Description;
SELECT Inv>>lineItem2Cost;

Each line of the above lines can now be used as an expression in other SQL statements. For example the following SQL statement can be executed if you are currently connected to the sample database, asademo.db, and have executed the above SQL statements.

SELECT * FROM PRODUCT 
   WHERE unit_price < Inv>>lineItem2Cost;

Invoking methods 

The Invoice class has one instance method, which can be invoked when an object of type Invoice has been created.

The following SQL statement invokes the totalSum() method of the object referenced by the variable Inv. It returns the sum of the two cost fields plus the tax charged on this sum.

SELECT Inv>>totalSum();

Calling methods versus referencing fields 

Notice the round brackets following the name of the method used in the above SQL statement.

A key difference between the object's fields and its methods is that methods are invoked which causes them to perform an action and return a value (even if the value is void). Fields are referenced in order to access the values they may contain.

The totalSum() method takes no arguments but returns a value. The brackets are used even though the method takes no arguments because a Java operation is being invoked.

As indicated by the Invoice class definition outlined at the beginning of this section, the totalSum instance method makes use of the class method rateOfTaxation.

This class method can be accessed directly from a SQL statement.

SELECT Invoice.rateOfTaxation();

Notice the name of the class is being used, not the name of a variable containing a reference to an Invoice object. This is consistent with the way Java handles class methods, even though it is being used in a SQL statement. A class method can be invoked even if no object based on that class has been instantiated.

Class methods do not require an instance of the class in order to work properly, but they can still be invoked on an object. The following SQL statement yields the same results as the previously executed SQL statement.

SELECT Inv>>rateOfTaxation(); 

  Saving Java objects in tables

When a class is installed in a database, it is available as a new data type. Columns in a table can be of type Javaclass where Javaclass is the name of an installed Java class.

For example, using the Invoice class that was installed at the beginning of this section, the following SQL statement can be executed.

CREATE TABLE T1 (
    ID int,
    JCol Invoice 
);

The column named JCol only accepts objects of type Invoice, which is an installed Java class. This means only objects can be passed in as values in the JCol column.

There are at least two methods for creating a Java object and adding it to a table as the value of a column. The first method, creating a variable, was outlined in a previous section Creating SQL variables of Java class type.

Assuming the variable Inv contains a reference to a Java object of type Invoice, the following SQL statement will add a row to the table T1.

INSERT INTO T1
VALUES( 1, Inv );

An object has been added to the table T1. Select statements can be issued involving the fields and methods of the objects in the table.

For example the following SQL statement will return the value of the field lineItem1Description for all the objects in the table T1 (right now, there should only be one object in the table).

SELECT ID, JCol>>lineItem1Description 
FROM T1;

Similar select statements involving other fields and methods of the object can be executed.

A second method for creating a Java object and adding it to a table involves the following expression, which always creates a Java object and returns a reference to it:

NEW Javaclassname()

This expression can be used in a number of ways. For example, the following SQL statement creates a Java object and inserts it into the table T1.

INSERT INTO T1
VALUES ( 2, NEW Invoice() );

The following SQL statement verifies that these two objects have been saved as values of column JCol in the table T1.

SELECT ID, JCol>>totalSum() 
FROM t1

The results of the JCol column (the second row returned by the above statement) should be 0, because the fields in that object have no values and the totalSum method is a calculation of those fields.

  Returning an object using a query

An object can also be retrieved from a table that has a Java class as the type of one of its columns. In the following series of statements a new variable is created and passed a value (it can only contain an object reference where the object is of type Invoice). The object reference passed to the variable was generated using the table T1.

CREATE VARIABLE Inv2 Invoice;
SET Inv2 = (select JCol from T1 where ID = 2);
SELECT IFNULL(Inv2, 'No object referenced',
   'Variable not null: contains object reference');
SET Inv2>>lineItem1Description = 'Sweet feed';
SET Inv2>>lineItem2Description = 'Drive belt';

Take note that the value for the lineItem1Description field and lineItem2Description have been changed in the variable Inv2 but not in the table that was the source for the value of this variable.

This is consistent with the way SQL variables are currently handled: the variable Inv contains a reference to a Java object. The value in the table that was the source of the variable's reference is not altered until an UPDATE SQL statement is executed.