Chapter 3: Using Java Classes in SQL

Assignment properties of Java-SQL data items

The values assigned to Java-SQL data items are derived ultimately from values constructed by Java-SQL methods in the Java VM. However, the logical representation of Java-SQL variables, parameters, and results is different from the logical representation of Java-SQL columns.

Java-SQL columns, which are persistent, are Java serialized streams stored in the containing row of the table. They are stored values containing representations of Java instances.
Java-SQL variables, parameters, and function results are transient. They do not actually contain Java-SQL instances, but instead contain references to Java instances contained in the Java VM.

These differences in representation give rise to differences in assignment properties as these examples illustrate.

The Address constructor method with the new operator is evaluated in the Java VM. It constructs an Address instance and returns a reference to it. That reference is assigned as the value of Java-SQL variable @A:
```
declare @A Address, @AA Address, @A2 Address2Line,
			@AA2 Address2Line
select @A = new Address('432 Post Lane', '99444')
```
Variable @A contains a reference to a Java instance in the Java VM. That reference is copied into variable @AA. Variables @A and @AA now reference the same instance.
```
select @AA=@A
```
This assignment modifies the zip field of the Address referenced by @A. This is the same Address instance that is referenced by @AA. Therefore, the values of @A.zip and @AA.zip are now both '99222'.
```
select @A>>zip='99222'
```
The Address constructor method with the new operator constructs an Address instance and returns a reference to it. However, since the target is a Java-SQL column, the SQL system serializes the Address instance denoted by that reference, and copies the serialized value into the new row of the emps table.
```
insert into emps 
			values ('Don Green', new Address('234 Stone
			Road', '99777'), new Address2Line( ) )
```
The Address2Line constructor method operates the same way as the Address method, except that it returns a default instance rather than an instance with specified parameter values. The action taken is, however, the same as for the Address instance. The SQL system serializes the default Address2Line instance, and stores the serialized value into the new row of the emps table.
The insert statement specifies no value for the mailing_addr column, so that column will be set to null, in the same manner as any other column whose value is not specified in an insert. This null value is generated entirely in SQL, and initialization of the mailing_addr column does not involve the Java VM at all.
```
insert into emps (name, home_addr)  values ('Frank Lee', @A)
```
The insert statement specifies that the value of the home_addr column is to be taken from the Java-SQL variable @A. That variable contains a reference to an Address instance in the Java VM. Since the target is a Java-SQL column, the SQL system serializes the Address instance denoted by @A, and copies the serialized value into the new row of the emps table.
This statement inserts a new emps row for 'Bob Brown.' The value of the home_addr column is taken from the SQL variable @A. It is also a serialization of the Java instance referenced by @A.
```
insert into emps (name, home_addr)  values ('Bob Brown', @A)
```
This update statement sets the zip field of the home_addr column of the ‘Frank Lee’ row to ‘99777.’ This has no effect on the zip field in the ‘Bob Brown’ row, which is still ‘99444.’
```
update emps
			set home_add>>zip = '99777'
			where name = 'Frank Lee'
```
The Java-SQL column home_addr contains a serialized representation of the value of an Address instance. The SQL system invokes the Java VM to deserialize that representation as a Java instance in the Java VM, and return a reference to the new deserialized copy. That reference is assigned to @AA. The deserialized Address instance that is referenced by @AA is entirely independent of both the column value and the instance referenced by @A.
```
select @AA = home_addr from emps where name = 'Frank Lee'
```
This assignment modifies the zip field of the Address instance referenced by @A. This instance is a copy of the home_addr column of the 'Frank Lee' row, but is independent of that column value. The assignment therefore does not modify the zip field of the home_addr column of the 'Frank Lee' row.
```
select @A>>zip = '95678'
```

Datatype mapping between Java and SQL fields

View this book as PDF