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

Using computed columns with Java classes

Computed columns are a feature designed to make Java database design easier, to make it easier to take advantage of Java features for existing databases, and to improve performance of Java data types.

A computed column is a column whose values are obtained from other columns. You cannot INSERT or UPDATE values in computed columns. However, any update that attempts to modify the computed column does fire any triggers associated with the column.

Uses of computed columns 

There are two main uses of computed columns with Java classes:

• Exploding a Java column     If you create a column using a Java class data type, computed columns enable you to index one of the fields of the class. You can add a computed column that holds the value of the field, and create an index on that field.

• Adding a Java column to a relational table     If you wish to use some of the features of Java classes while disturbing an existing database as little as possible, you can add a Java column as a computed column, collecting its values from other columns in the table.

  Defining computed columns

Computed columns are declared in the CREATE TABLE or ALTER TABLE statement.

Creating tables with computed columns 

The following CREATE TABLE statement is used to create the product table in the Java sample tables:

CREATE TABLE product
(
  id    INTEGER NOT NULL,
  JProd asademo.Product NOT NULL,
  name CHAR(15) COMPUTE ( JProd>>name ),
  PRIMARY KEY ("id")
)

Adding computed columns to tables 

The following statement alters the product table by adding another computed column:

ALTER TABLE product
ADD inventory_Value INTEGER
  COMPUTE ( JProd>>quantity * JProd>>unit_price )

  Inserting and updating computed columns

Computed columns have some impact on valid INSERT and UPDATE statements. The jdba.product table in the Java sample tables has a computed column (name) which we use to illustrate the issues. The table definition is as follows:

CREATE TABLE "jdba"."product"
(
   "id"    INTEGER NOT NULL,
   "JProd" asademo.Product NOT NULL,
   "name"  CHAR(15) COMPUTE( JProd>>name ),
   PRIMARY KEY ("id")
)

• No direct inserts or updates     You cannot insert a value directly into a computed column. The following statement fails with a Duplicate Insert Column error:

  -- Incorrect statement
  INSERT INTO PRODUCT (id, name)
  VALUES( 3006, 'bad insert statement' )

  Similarly, no UPDATE statement can directly update a computed column.

• Listing column names     You must always specify column names in INSERT statements on tables with computed columns. The following statement fails with a Wrong Number of Values for Insert error:

  -- Incorrect statement
  INSERT INTO PRODUCT
  VALUES( 3007,new asademo>>Product() )

  Instead, you must list the columns, as follows:

  INSERT INTO PRODUCT( id, JProd )
  VALUES( 3007,new asademo>>Product() )

• Triggers     You can define triggers on a computed column, and any INSERT or UPDATE statement that affects the column fires the trigger.

  Restrictions on computed columns

Although you can use a wide range of expressions for computed columns, you need to be sure that you use them only for values that are meaningful.

Computed columns are not updated each time they are read. Values in computed columns are updated only when rows are updated that are referenced in the COMPUTE expression. If you use an expression that is time dependent, or which depends on the state of the database in some other way, then the computed column may not give a proper result.