Inserting, updating, and deleting Java objects

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

Inserting, updating, and deleting Java objects

This section describes how the standard SQL data manipulation statements apply to Java columns.

Throughout the section, points are illustrated using a concrete example based on the Product table of the sample database and a class named Product. You should first look at the file Product.java held in the jxmp\asademo subdirectory of your installation directory.

Create the Java sample tables

The examples in this section assume that you have added the Java tables to the sample database, and are connected as user ID jDBA with password SQL.

For Info For instructions on how to do this, see Installing the Java examples.

A sample class

This section describes a class that is used in examples throughout the following sections.

The Product.java class definition is included in the jxmp\asademo directory under your installation directory. Part of the class definition is reproduced here:

package asademo;

public class Product implements java.io.Serializable {
  
  // public fields
  public String name ;
  public String description ;
  public String size ;
  public String color;
  public int quantity ;
  public java.math.BigDecimal unit_price ;

  // Default constructor
  Product () {
      unit_price = new java.math.BigDecimal( 10.00 );
      name = "Unknown";
      size = "One size fits all";
  }

  // Constructor using all available arguments
  Product ( String inColor,
       String inDescription,
       String inName,
       int inQuantity,
       String inSize,
       java.math.BigDecimal inUnit_price
       ) {
    color = inColor;
    description = inDescription;
    name = inName;
    quantity = inQuantity;
    size = inSize;
    unit_price=inUnit_price;
  }

  public String toString() {
     return size + " " + name + ": " +
     unit_price.toString();
  }

Notes

The Product class has several public fields. These correspond to some of the columns of the dba.Product table, which are to be collected together in this class.
The toString method is provided for convenience. When you include an object name in a select-list, the toString method is executed and its return string displayed.
Some methods are provided to set and get the fields. It is common to use such methods in object-oriented programming rather than to address the fields directly. Here the fields are public for convenience in tutorials.

Inserting Java objects

When you INSERT a row in a table that has a Java column, you need to insert a Java object into the Java column.

You can insert a Java object in two ways: from SQL or from other Java classes running inside the database, using JDBC.

Inserting a Java object from SQL

You can insert a Java object using a constructor, or you can use SQL variables to build up a Java object before inserting it.

Inserting an object using a constructor

When you insert a value into a column that has a Java class data type, you are inserting a Java object. To insert an object with the proper set of properties, you will want the new object to have proper values of any public fields, and you will want any methods that set private fields to be called.

To insert a Java object:

INSERT a new instance of the Product class into the table product as follows:
```
INSERT
INTO product ( ID, JProd )
VALUES ( 702, NEW asademo.Product() )
```
You can run this example against the sample database from the user ID jdba once the jdemo.sql script has been run.

The NEW keyword invokes the default constructor for the Product class in the asademo package.

Inserting an object from a SQL variable

You can also set the values of the fields of the object individually, as opposed to through the constructor, in a SQL variable of the proper class. This has the disadvantage that the setting of values is not done by the class definition itself. This introduces the possibility of inconsistencies when different applications insert rows initialized in different ways. Therefore the use of a proper constructor is recommended.

To insert a Java object using SQL variables:

Create a SQL variable of the Java class type:
```
CREATE VARIABLE ProductVar asademo.Product
```
Assign a new object to the variable, using the class constructor:
```
SET ProductVar = NEW asademo.Product()
```

Assign values to the fields of the object, where required:

SET ProductVar>>color = 'Black';
SET ProductVar>>description = 'Steel tipped boots';
SET ProductVar>>name = 'Work boots';
SET ProductVar>>quantity = 40;
SET ProductVar>>size = 'Extra Large';
SET ProductVar>>unit_price = 79.99;

Insert the variable into the table:

INSERT
INTO Product ( id, JProd )
VALUES ( 800, ProductVar )

Check that the value is inserted:
```
SELECT *
FROM product
WHERE id=800
```
Undo the changes you have made in this exercise:
```
ROLLBACK
```

The use of SQL variables is typical of stored procedures and other uses of SQL to build programming logic into the database. Java provides a more powerful way of accomplishing this task. You can use server-side Java classes together with JDBC to insert objects into tables.

Inserting an object from Java

You can insert an object into a table using a JDBC prepared statement.

A prepared statement uses placeholders for variables. You can then use the setObject method of the PreparedStatement object.

For Info For more information on using prepared statements, see Inserting and retrieving objects.

Updating Java objects

You may wish to update a Java column value in either of the following ways:

Update the entire object.
Update some of the fields of the object.

Updating the entire object

You can update the object in much the same way as you insert objects:

From SQL, you can use a constructor to update the object to a new object as the constructor creates it. You can then update individual fields if you need to.
From SQL, you can use a SQL variable to hold the object you need, and then update the row to hold the variable.
From JDBC, you can use a prepared statement and the PreparedStatement.setObject method.

Updating fields of the object

Individual fields of an object have data types that correspond to SQL data types, using the SQL to Java data type mapping described in Java / SQL data type conversion.

You can update individual fields using a standard UPDATE statement:

UPDATE Product
SET JProd>>unit_price = 16.00
WHERE ID = 302

In the initial release of Java in the database, it was necessary to use a special function (EVALUATE) to carry out updates. This is no longer needed.

To update a Java field, the Java data type of the field must map to a SQL type, the expression on the right hand side of the SET clause must match this type. You may need to use the CAST function to cast the data types appropriate.

For information on data type mappings between java and SQL, see Java / SQL data type conversion.

Set method must return a value

The method you use to set the value of the field you are updating must return a non-void value. In the example, setName returns a boolean value.

Deleting Java objects

Deleting rows containing Java objects is no different to deleting other rows. The WHERE clause in the DELETE statement can include Java objects or Java fields and methods.