Using Cloudscape's Java Extensions
Page 7 of 12

Invoking Methods and Accessing Fields

In other situations, you do not need to retrieve the object; you simply want to have Cloudscape execute one of its methods or access one of its fields and return any relevant results to the application. This section covers the following topics:

Executing Methods

If an object's field or method is public, you can access it in a Cloudscape database. In the above example you retrieved an object into an application and then executed the methods getName and getCity on it. You could have Cloudscape execute those methods and then retrieve the results instead of the whole object, as in the following example:

String cityname;
PreparedStatement ptstmt = conn.prepareStatement(
    "SELECT city.getName() FROM Cities " +
    "WHERE city_id = ?");
ptstmt.setInt(1, 1);
ResultSet rs = ptstmt.executeQuery();
rs.next()
cityname = rs.getString(1);
System.out.println("name of city is" + city);
rs.close();

You can also access a public field of an object stored in the database. You do not use a dot to separate the field name from the object, however. Instead, you use a two-character combination, "->". For example, to access the public field name of all the City objects stored in the database, use the following SQL-J statement:

SELECT city->name FROM Cities

Whether you retrieve the object or just invoke methods depends on whether you want to manipulate the object further. If you want to manipulate the object, retrieve the object into your program.

When you are storing subclasses, or when the column serializes an interface, in SQL-J you can access only fields and methods that are available in the class that the column serializes unless you cast it to the subclass using CAST. See Accessing Methods When Storing Subclasses.

Refining Search Conditions

Having the DBMS execute a method or access a field is especially useful for refining queries. The results of a method invocation or field access can be part of a query's WHERE clause, as in the following example:

SELECT city FROM cities WHERE city.isTropical()
AND city.getLanguage() = 'English'

Accessing Methods When Storing Subclasses

When you define a column to serialize a Java class, Cloudscape allows you to store instances of both the class and any of its subclasses. When you define a column to serialize a Java interface, Cloudscape allows you to store instances of classes that implement the interface and subclasses thereof.

Basic method invocation within an SQL-J statement allows you to access only the methods of the class or interface that the column is defined to serialize. However, using the CAST operator, you can access methods defined only in the subclass. When using the CAST operator in this way, use the INSTANCEOF operator to test for class membership.

For example, JBMSTours.serializabletypes.Child and JBMSTours.serializabletypes.Adult are both subclasses of JBMSTours.serializabletypes.Person. If a column person serializes JBMSTours.serializabletypes.Person, you are allowed to store instances of JBMSTours.serializabletypes.Child and JBMSTours.serializabletypes.Adult in that column. However, JBMSTours.serializabletypes.Child contains one method that JBMSTours.serializabletypes.Adult does not, called getParent. You must use the CAST operator to invoke that method within an SQL-J statement. In order to avoid runtime errors, test for membership in the JBMSTours.serializabletypes.Child class before execution:

SELECT CAST (person AS CLASS JBMSTours.serializabletypes.Child).getParent()
FROM People
WHERE person INSTANCEOF JBMSTours.serializabletypes.Child

Another example: if a column serializes java.io.Serializable, you can store instances of any class that implements Serializable in the column. However, you will not be able to access very many methods unless you CAST the column into the class you are working with:

SELECT CAST (serializable_column
AS CLASS java.lang.Float).longValue()
FROM mytable
WHERE serializable_column INSTANCEOF java.lang.Float

Invoking Methods on Classes Not Serialized in the Database

You can invoke public methods and access public fields of any Java class visible to your JVM, provided it is visible to the JVM in which Cloudscape is running. An instance of the class does not have to be stored in the database.

NOTE: The classes must be available to the JVM in which Cloudscape is running (installed and in the class path or stored in a jar file in the database and in the database class path). See Deploying Java Classes for Use as Java Data Types. The classes for basic JDK data types and for Cloudscape-supplied data types are always available.

Creating an Instance of a Class on the Fly Using Built-in Types

You can construct expressions that are instances of Java types within SQL-J.

One way to do this is by constructing instances of the built-in SQL-J types, which correspond to Java classes. Within an SQL-J statement, you can use expressions such as:

  • 1+3 (evaluates to an SQL-J INT)
  • hotel_id (name of integer column, evaluates to an SQL-J INT)
  • 'Santiago' (evaluates to an SQL-J CHAR)

Each built-in SQL-J type has a corresponding Java type. An SQL-J INT's corresponding Java type is java.lang.Integer; an SQL-J CHAR's corresponding Java type is java.lang.String. (For more information about type correspondence, see SQL-J and Java Type Correspondence.)

This means that the above expressions are all instances of their respective Java types. 1+3 is an instance of java.lang.Integer; the hotel_id stored in any particular row is an instance of java.lang.Integer; `Santiago' is an instance of java.lang.String. In an SQL-J statement, therefore, you can invoke all methods associated with these objects by using the same syntax that you would in a Java application, which you saw above.

For example, you can construct the following valid SQL-J statements that invoke non-static methods:

VALUES((1+3).toString())

VALUES('Santiago'.toUpperCase())

SELECT hotel_id.toString() FROM Hotels

You can also invoke static methods associated with an instance of a Java class. For example, you can invoke any static method of the java.lang.Integer class on the expression 1+3:

VALUES((1+3).toString(35))

Class Method Invocation and Class Field Access (Static Methods and Fields)

Cloudscape provides syntax for invoking static methods and static fields that belong to Java classes, not instances of a Java class (objects).

When a class does not have a class alias, this syntax uses the keyword CLASS. When the class has an alias, simply use the class alias.

VALUES (CLASS JBMSTours.serializabletypes.City).findCity(
    getCurrentConnection(),
    'Santiago', 'Chile')

-- City is a class alias for JBMSTours.serializabletypes.City
VALUES City.findCity(getCurrentConnection(),
    'Santiago', 'Chile')

VALUES (CLASS java.lang.Integer)->MAX_VALUE

This syntax requires that the method return a value. If the method does not return a value, use a CALL command:

CALL HotelStay.archiveRecords(CURRENT_DATE)

For more information about the CALL command, see the Cloudscape Reference Manual.

Method Invocation and Dynamic Parameters

You may want to invoke a method with dynamic instead of literal parameters, setting the values of the parameters inside the application for multiple execution.

This brings up a few problems: Cloudscape must be able to determine the data type of the dynamic parameter when it compiles the statement. Cloudscape doesn't have any problem doing this in a standard SQL statement such as the following:

SELECT * FROM MyTable WHERE anIntegerColumn = ?

In the above situation, Cloudscape determines that the data type of the dynamic parameter must be the same as that of the column to which it is being compared (an INTEGER).

However, since Java methods can be overloaded, the data type of a dynamic parameter is often ambiguous. In a Java program, the JVM uses the data type of the parameter to determine which signature to call. Cloudscape cannot use the data type of a dynamic parameter to determine which signature to call, and so cannot use the signature of the method to determine the data type of the dynamic parameter!

In most cases, you will need to CAST the data type of the dynamic parameter when it is used as a parameter to a method call, like this:

CALL (CLASS java.lang.Integer).toString(CAST (? AS INT))

Even though there is only one signature for the method toString() in the class java.lang.Integer, you still need to cast the data type of the dynamic parameter, because it is a primitive.

In other cases, if the number of parameters makes the method signature unambiguous and none of the data types is a primitive, you do not need to CAST.

Invoking Database-Side JDBC Methods

RDBMS users who first use Cloudscape may wonder where the stored procedures are. This section explains why you will not miss them. It includes the following topics:

In an RDBMS, you can write stored procedures in a proprietary SQL-based programming language. Stored procedures allow you to execute a piece of procedural programming in the DBMS. They are called stored procedures because they are stored in the database.

Because Cloudscape extends SQL with Java, you do not need to write separate stored procedures. As shown in the earlier sections in this chapter, you can simply execute or invoke a Java class's methods within the DBMS as simply as you can invoke a class's methods within a Java application. Instances of the class do not need to be stored in the database for you to execute one of its methods, but the class must be available to Cloudscape (see Deploying Java Classes for Use as Java Data Types).

NOTE: Java classes can be stored in and loaded from the database. When classes are stored in and loaded from the database, database-side methods may be said to be "stored in the database." Such methods are sometimes called Java stored procedures. See Loading Classes from a Database.

Methods invoked within an application are called application-side methods. Methods invoked within Cloudscape are called database-side methods.

An application-side method can be exactly the same as a database-side method. The only difference is where you invoke them. You write the method only once. Where you invoke the method--within the application or within an SQL-J statement--determines whether it is an "application-side" or a "database-side" method.

Methods invoked within an application that interact with a Cloudscape database are called application-side JDBC methods; methods invoked within Cloudscape that interact with a Cloudscape database are called database-side JDBC methods. Methods in this last category most closely resemble stored procedures; these are methods you call from an SQL-J statement that execute SQL-J statements themselves. Here are two examples of database-side JDBC methods:

CALL HotelStay.makeHistoryDatabase()

VALUES City.findCity(getCurrentConnection(), 35)

For more details on database-side JDBC methods, see Programming Database-Side JDBC Methods.