Selecting, inserting, updating, and deleting Java objects

After you specify Java-SQL columns, the values that you assign to those data items must be Java instances. Such instances are generated initially by calls to Java constructors using the new operator. You can generate Java instances for both columns and variables.

Constructor methods are pseudo instance methods. They create instances. Constructor methods have the same name as the class, and have no declared datatype. If you do not include a constructor method in your class definition, a default method is provided by the Java base class object. You can supply more than one constructor for each class, with different numbers and types of arguments. When a constructor is invoked, the one with the proper number and type of arguments is used.

In the following example, Java instances are generated for both columns and variables:

declare @A Address, @AA Address, @A2 Address2Line,
 	@AA2 Address2Line
 
 select @A = new Address( )
 select @AA = new Address('123 Main Street', '99123')
 select @A2 = new Address2Line( )
 select @AA2 = new Address2Line('987 Front Street', 
 	'Unit 2', '99543')
 
 insert into emps values('John Doe', new Address( ), 
 	new Address2Line( ))
 insert into emps values('Bob Smith',
	new Address('432 ElmStreet', ‘99654’),
	new Address2Line('PO Box 99', 'attn: Bob Smith', '99678') )

Values assigned to Java-SQL columns and variables can then be assigned to other Java-SQL columns and variables. For example:

declare @A Address, @AA Address, @A2 Address2Line,
 	@AA2 Address2Line
 
 select @A = home_addr, @A2 = mailing_addr from emps
 	where name = 'John Doe'
 insert into emps values ('George Baker', @A, @A2)
 
 select @AA2 = @A2
 update emps
 	set home_addr = new Address('456 Shoreline Drive', '99321'),
         mailing_addr =  @AA2
         where name = 'Bob Smith'

You can also copy values of Java-SQL columns from one table to another. For example:

create table trainees (
		name char(30),
		home_addr Address,
		mailing_addr Address2Line null
 )
insert into trainees 
select * from emps
		where name in ('Don Green', 'Bob Smith',
		'George Baker')

n reference and update the fields of Java-SQL columns and of Java-SQL variables with normal SQL qualification. To avoid ambiguities with the SQL use of dots to qualify names, use a double-angle (>>) to qualify Java field and method names when referencing them in SQL.

declare @name varchar(100), @street varchar(100), 
 	@streetLine2 varchar(100), @zip char(10), @A Address
 
 select @A = new Address()
 select @A>>street = '789 Oak Lane'
 select @street = @A>>street
 
 select @street = home_add>>street, @zip = home_add>>zip from emps
 	where name = 'Bob Smith'
 select @name = name from emps
 	where home_addr>>street= '456 Shoreline Drive'
 
 update emps
 	set home_addr>>street = '457 Shoreline Drive', 
		home_addr>>zip = '99323'
 	where home_addr>>street = '456 Shoreline Drive'