Java Methods in the Database

Java Methods in the Database

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.

Stored Procedures in Java

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.