Server Watch SQLSummit FirstSQL
Java objects in FirstSQL/J provides the best synergy of Object-Oriented and SQL Relational Database technology. It greatly extends the expressive power of SQL while making object-oriented database techniques widely available.
Objects defined in Java can be stored in FirstSQL/J and as values in database columns. These are normal columns whose type is a Java class that is mapped to the appropriate database type internally. Java column values are active objects whose methods can be accessed in SQL commands or when retrieved locally. They also provide powerful O-O database capability such as inheritance, encapsulation, and polymorphism.
FirstSQL has solved the mismatch between relational database and OO Java capabilities in addition to simplifying the use of both together. The flexibility of cataloging Java Classes in the database provides many advantages:
- Business classes are made persistent without O/R mapping tools and special treatment
- Supports simplified SQL access to persistent data, objects, and methods
- Supports complex object relationships and unlimited flexibility of information storage
- Provides an object-oriented approach to database development
- Eliminates the need for JDO and complex O/R Mapping tools
In order to create an object in the database an original Java class is cataloged, along along with its methods and what classes it inherits, using the CREATE CLASS command. Java classes do not need any special treatment or modification prior to this simple first step. Cataloging the Java class can use the following basic form:
CREATE CLASS class-name FROM 'java-name' [INHERITS class-list]
object-name is a string containing the fully qualified name of the Java class being cataloged. java-name may specify the original Java Class or a Java Interface. In the optional INHERITS clause, class-list is a comma-separated list containing the user-defined base class (if any) and any user-defined interfaces implemented. The base class or primary interface is listed first. Any class or interface listed must be cataloged in the database with the database name used in the database class.
Dropping a database class uses the basic form:
DROP CLASS class-name [ RESTRICT | CASCADE ]
The optional RESTRICT/CASCADE specifies the disposition of any other catalog definitions referencing the class. The default is RESTRICT.
The ability to define the type of a database column as a Java class is a revolutionary capability. Internal conversion of Java types to database types means columns in database tables can then use the Java class for their type definition. Once a database class is cataloged it may be used in defining object columns.
For examples, we will use a Money class, supporting a variety of currencies.
Note: Money.sql and Money.java are used to create the tables and Money class. These files can be seen at the end of this tutorial. Download examples.
Money Class
Constructors:
Money(BigDecimal amt, String currency);
Money(double amt, String currency); Note: currency is a string name of a currency USD, Euro,
Methods: String getCurrency(); // get currency type string
String toString(); // get amount with standard formatting
BigDecimal decValue(); // get numeric amount
double doubleValue(); // get numeric amount
We can then use the Money class to represent the amount in a sales order table:
CREATE TABLE sales_orders
(ord_id int,
cust_id int REFERENCES customers,
ord_date date, ord_amt Money, -- the data type of ord_amt is the Money class
PRIMARY KEY(cust_id, ord_id)
);
In the new table sales_orders, the ord_amt column uses the Java class - Money as its type. In each row of the sales_orders table, the ord_amt column will either be null (empty) or contain a Java object instantiated from the Money class.
A SQL command manipulates an object column by calling its methods:
SELECT sales_orders.cust_id, sales_orders.ord_id,
sales_orders.ord_amt.toString()
FROM sales_orders;
Object columns are assignable to other object columns. A database client can retrieve the value of the column as a Java object with a simple query:
SELECT sales_orders.cust_id, sales_orders.ord_id, sales_orders.ord_amt
FROM sales_orders
WHERE sales_orders.ord_amt.getCurrency() = USD;
This query retrieves all orders that are in US Dollars. It retrieves the order amount as a Money object, which the client can instantiate as a Java object in a local JVM.
In defining object columns, the class-name replaces the data type:
CREATE CLASS Money FROM 'Money' ;
CREATE TABLE sales_orders
(ord_id int,
cust_id int REFERENCES customers,
ord_date date,
ord_amt Money,
PRIMARY KEY(ord_id, cust_id)
);
The NEW clause provides values for an object column:
INSERT INTO sales_orders
VALUES (2451, 1, current_date, NEW Money(2.99, USD));
UPDATE sales_orders
SET ord_amt = NEW Money(5000, Euro)
WHERE cust_id = 5444
AND ord_id = 4;
A method returning a database object can also supply a new value for an object column. You can also call class or static methods directly, using the class name. For example, the Java class, Money, is cataloged in the database and has a static method - convert() that converts a Money object from one currency to another. A query can use it to convert all order amounts to the same currency:
SELECT cust_id, ord_id, Money.convert(ord_amt, Euro)
FROM sales_orders;
When the database class is created it includes the methods from the original Java class. As a result, SQL commands can include calls to Java methods:
wherever a value is used - in expressions, select lists, where clauses, etc. (the value of a method call is its return value),
in the EXECUTE command, as a stored procedure (see Stored Procedures), or
in the EVALUATE clause of the UPDATE command, to change the state of object columns.
Static method calls can use the form:
[ [ catalog-name . ] schema-name . ] class-name . method-name (...)
class-name must be the database name of a cataloged Java class.
Instance method calls use the form:
( instance-expression ) . method-name (...)
instance-expression is a SQL expression returning an object. It could be:
a reference to an object column,
a call to a method returning a database object,
a NEW clause creating a new database object, or
the result of a CAST operator casting one of the above expressions to a database class; a ? placeholder can also be cast to a database class.
Note: you can omit the parentheses for a reference to an object column, in most cases.
In the rows of the database table, the value of a column defined with Java class is an instance (object) of the Java class. Instances are created using the constructor for the class. Column values are active instances and their methods are callable in SQL commands. When the client retrieves a column value defined as a Java class, it is an active object that is often executed in the Client's JVM. Both class and instance methods may be accessible.
Since the beginning, stored procedure languages have been proprietary to each database vendors, with no commonality. Using more portable languages, like C++, for server procedures has raised issues of safety (an errant procedure could crash the server) and security. Now, with most DB vendors supporting it, Java is becoming the stored procedure language of choice, promising portability and safety.
Stored procedures can be implemented as Java methods. A client application calls a Java stored procedure through JDBC or ODBC using standard syntax. The server translates this syntax into direct calls to user defined Java methods cataloged on the server.
Stored procedures access the database using JDBC and they can return basic values and Java objects. Java objects can include individual JDBC result sets or JDBC statements containing multiple results.
See the FIRSTSQL/J STORED PROCEDURE EXAMPLE at the end of this tutorial
The static methods of a database class are callable as stored procedures:
EXECUTE ? = Money.convert(?, Euro)
Stored procedures also support output parameters in the form of Java objects. The stored procedure can modify an object passed as a parameter and return the modified object to the caller (the client). The Java object must be derived from a Java class cataloged in the database.
Java servlets are an excellent fit for a Java stored procedure:
Portability - Java database servlets can be written in pure Java using standard JDBC for database access.
Safety - Java code is free from pointer misuse and memory leaks. The JVM (Java Virtual Machine) applies the sandbox approach to executing Java code, restricting external access.
Security - The JVM sandbox mechanism provides secure execution of Java code. The JVM also supports authentication of Java database servlets.
With a portable stored procedure language, code can be transferred between servers and JVMs from different vendors, vendor-specific training is reduced and database-independent applications can be distributed with application-specific stored procedure code.
=========================================================================
Money.java
==========================================================================
Money.java:
// Copyright 2003 - FirstSQL, Inc.
import java.math.*;
import java.io.*;
import java.sql.*;
import COM.FirstSQL.Dbcp.Database;
// Money.java - FirstSQL/J Example Database Class
public class Money implements Serializable
{
protected String currency;
protected BigDecimal amount;
public Money(BigDecimal a, String c)
{
amount = a;
currency = c;
}
public Money(double a, String c)
{
this(new BigDecimal(a), c);
}
public Money(String a, String c)
{
this(new BigDecimal(a), c);
}
public String getCurrency()
{
return currency;
}
public double doubleValue()
{
return amount.doubleValue();
}
public BigDecimal decValue()
{
return amount;
}
public String toString()
{
return amount.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
}
// change a Money object to a new currency
public static Money convert(Money source, String currency)
{
Money target = null;
// Database is builtin class for retrieving an internal DBMS connection
Connection conn = Database.getConnection();
if (source != null && conn != null)
{
PreparedStatement stnt = null;
ResultSet rs = null;
try
{
stnt = conn.prepareStatement("SELECT " +
"? * (SELECT usdollar FROM money WHERE code = ?) / usdollar " +
"FROM money WHERE code = ?");
stnt.setBigDecimal(1, source.decValue());
stnt.setString(2, source.getCurrency());
stnt.setString(3, currency);
rs = stnt.executeQuery();
if (rs.next())
target = new Money(rs.getBigDecimal(1), currency);
}
catch (SQLException ex)
{
}
finally
{
if (stnt != null)
try
{
if (rs != null)
try
{
rs.close();
}
catch (SQLException ex)
{
}
stnt.close();
}
catch (SQLException ex)
{
}
}
}
return target;
}
}
==========================================================================
money.sql:
==========================================================================
create table money
(
code varchar(4) primary key,
country varchar(32),
name varchar(16),
usdollar double
)
go
insert into money Values('USD', 'USA', 'Dollar', 1.00)
go
insert into money Values('GBP', 'UK', 'Pound', 1.47)
go
insert into money Values('Euro', 'European Union', 'Dollar', .95)
go
insert into money Values('CAD', 'Canada', 'Dollar', .65)
go
insert into money Values('AUD', 'Australia', 'Dollar', .57)
go
insert into money Values('JPY', 'Japan', 'Yen', .00798)
go
==========================================================================
FIRSTSQL/J STORED PROCEDURE EXAMPLE
==========================================================================
This is a note giving a real-world example of using Stored Procedures in FirstSQL/J. For an example, we will use the Money class (Java code is included above). The Money class is for storage of monetary amounts in mixed currencies. It includes a static method - convert(), that is called as a stored procedure. convert() converts a monetary amount from one currency to another.
The Money class is placed in the FirstSQL/J catalog with CREATE CLASS:
CREATE CLASS Money FROM 'Money'
Note: CREATE CLASS requires that the class file (Money.class) be in a special location. For the Professional Edition, the default location for Money.class is the directory where CREATE CLASS is run. For the Enterprise Edition, the default location is the directory where the server is started up. An object of the Money class has two values - a decimal amount and a currency type string.
The convert() method receives 2 parameters - a Money object and a currency type string. It returns a new Money object for the specified currency with the amount converted to the new currency:
public static Money convert(Money source, String currency)
The convert() method uses a database table - money, to do the conversion. The money table contains a row for each currency type. Each row has the value in US Dollars of a unit in the specified currency. The SQL commands for creating the money table and adding some example rows are included below.
The SQL command for calling the convert() method as a stored procedure is:
{?=call Money.convert(?,?)}
The Java code to use the convert() stored procedure:
// value to be converted
Money source = new Money(1445.36, "USD");
CallableStatement call = conn.prepareCall("{?=call Money.convert(?,?)}");
// register return value
call.registerOutParameter(1, Types.OTHER);
// set arguments to convert()
call.setObject(2, source);
call.setString(3, "Euro");
// execute call
call.execute();
// retrieve result
Money target = (Money) call.getObject(1);