Working with SQL-J's Java Extensions
Page 3 of 7

Meet the City Class

In this section, you will be working with the JBMSTours.serializabletypes.City class. Open the javadoc or the Java source file for this class now. Scroll through the methods to start getting familiar with what these methods do. Javadoc for JBMSTours is in the /demo/programs/tours/javadoc subdirectory in the cloudscape base directory.

Examine and Execute CityTest

CityTest.java, which you should have copied into your working directory from the scripts directory, is a simple Java application that does not access the database. Instead, it creates two instances of the City class and executes some of its methods for one of those (it uses the other instances as a parameter to two method calls).

One of the methods it uses is getDistanceFrom. The getDistanceFrom method shows off the power of object programming. One of the fields in the City class is a Location object (you can look at the javadoc for Location to learn the details). The Location object defines the location in terms of latitude and longitude. The getDistanceFrom method uses a well known trigonometric formula to calculate the approximate distance between two locations.

In this task, you will run CityTest, which executes application logic in the application. In the next task, you will execute some of those same methods in SQL-J statements instead of from an application--executing application logic in Cloudscape. As you will see, there is not much difference.

  1. Open and examine CityTest.java.
  2. Open a new command window and change directories to the your_tutorial_home directory.
  3. Run your setclasspath script.
  4. Compile CityTest.java.
  5. Execute this program:

    java CityTest

    Since this program does not use Cloudscape, you do not need to set the cloudscape.system.home variable as a -D parameter.

  6. Notice the methods it executed and the results.
  7. Keep the command window open. You will use it again in the next task.

Work with Stored City Objects

The toursDB database stores several City objects in the City table. You can execute some of the same methods executed by CityTest within an SQL-J statement. You will execute those statements within ij.

Notes About Starting ij

The name of the program ij is COM.cloudscape.tools.ij.

When you start ij, you will specify an ij property called ij.protocol. The ij.protocol consists of the "protocol" and "subprotocol" portions of the database connection URL you will be using. For example, the ij.protocol you have used throughout this tutorial is jdbc:cloudscape:. Users working in client/server mode use jdbc:cloudscape:weblogic:.

When you specify ij.protocol, ij automatically boots the appropriate JDBC driver. So if you specify jdbc:cloudscape:, ij automatically boots the embedded JDBC driver for you.

In addition, after you specify this property, ij allows you to provide a "short form" of a database connection URL to connect to a database. That is, instead of having to specify jdbc:cloudscape:HelloWorldDB to connect to HelloWorldDB (as you did in a Java application in Chapter 2, "Cloudscape Basics and the Sample Database"), you need only specify HelloWorldDB. You will use the short form of the database connection URL as an argument to the ij command Connect.

You specify the ij.protocol property on the command line when starting up ij, like this:

java -Dij.protocol=jdbc:cloudscape: COM.cloudscape.tools.ij

NOTE: The good news is that Cloudscape provides a batch file for you to make this easier. If you are working on a Windows or UNIX platform and you have the /bin subdirectory of your cloudscape base directory in your PATH, you can accomplish the same thing by merely typing ij. For example:

ij

The rest of this book assumes you don't have this shortcut and shows you the long way, but use this shortcut if you have it.

Start ij

NOTE: ij commands are case-insensitive. ij command statements end with a semicolon.

  1. Return to your open command window.
  2. Start ij, specifying the system directory where the toursDB database lives with a -D parameter and the ij.protocol property:

    java -Dcloudscape.system.home= your_tutorial_system
    -Dij.protocol=jdbc:cloudscape: COM.cloudscape.tools.ij

    You should have an ij> prompt ready for you to enter SQL-J statements or ij commands.

    ij>

Connect to toursDB

  1. Establish a connection to toursDB:

    connect 'toursDB';

    The string you pass to the Connect command is the "short form" of embedded database connection URL.

    The rest of the examples in this chapter do not include the ij> prompt, so that it is easier to cut and paste queries into the command window.

Execute SQL-J Statements Referencing Stored City Objects

Now that you are connected to the toursDB database, you can access the City table.

One of the methods that CityTest executed was the City class's getName() method. CityTest executed that method on two City objects residing in memory. You can have Cloudscape execute that method on one, some, or all of the City objects stored in the table.

  1. Execute the method on all the City objects (press Return after you type the statement):

    SELECT city.getName() FROM CITIES;

    city is the column name. That column happens to store instances of -JBMSTours.serializabletypes.City (the column could have been called something different).

  2. Execute the method on those City objects whose city_id is greater than 50:

    SELECT city.getName() FROM CITIES
    WHERE city_id > 50;

  3. Execute the method only in the WHERE clause:

    SELECT city_id FROM CITIES
    WHERE city.getName() = 'Santiago';

    Executing a method in a WHERE clause allows you to refine your search conditions.

Access a Field in an SQL-J Statement

To access the field of a stored object in an SQL-J statement, you add the field name to the column name. The two-character combination "->" separates the field name from the column name.

For you to access a field, the field must be public.

The language field in the City class is public.

  1. Execute the following statement:

    SELECT city->language FROM Cities;

  2. Now do the same thing, but eliminate duplicates.

    SELECT DISTINCT city->language FROM Cities;

Quiz: Why can't you access the airport field?

Answer: Because it is a private field.

Execute More Complex SQL-J Statements

CityTest executed two methods that took City objects as parameters. For example, the getDistanceFrom(City) method took a City object as a parameter. How can you execute those methods within an SQL-J statement?

Well, it's possible but a little complicated.

One way to do it is to craft an SQL-J statement that returns a City object, and then to use that statement as the parameter to the method. It must return only one City object, not more. Since the city_id is a unique column, we specify a single city_id in the WHERE clause.

  1. Execute the following SQL-J statement, which returns a single City (which happens to be Paris):

    SELECT City FROM Cities WHERE city_id = 35;

    That query should return the following:

    CITY
    ---------------
    Paris, France

Let's find how far each City in the database is from Paris. We can do that by passing in the above SQL-J statement as a parameter to the getDistanceFrom(City) method call. If the main SQL-J statement has no WHERE clause, Cloudscape evaluates the main expression for every row in the database.

  1. Besides the city's distance from Paris, also select the city name and give the method call a column name:

    SELECT city.getName(), city.getDistanceFrom(
        (SELECT city
         FROM Cities
         WHERE city_id = 35))
    AS MILES_FROM_PARIS
    FROM Cities;

    Quiz: Why does the SQL-J statement that returns the Paris City object have two sets of parentheses around it?

    Answer: One set of parentheses is for the method invocation. The second set of parentheses is to mark the statement as a subquery. Subqueries, which are SQL-J queries used to supply values within other queries, always require parentheses.

  2. You can get Cloudscape to order the results, from nearest to farthest. Execute the following statement:

    SELECT city.getName(), city.getDistanceFrom(
        (SELECT city FROM Cities WHERE city_id = 35))
    AS MILES_FROM_PARIS
    FROM Cities
    ORDER BY MILES_FROM_PARIS;

    A DBMS's ability to filter and sort records is one of the reasons to use a database instead of just storing data yourself.

    CityTest executed the getDistanceFrom method only for Santiago.

    You may recall that the output of CityTest was:

    C:\tutorial_home>java CityTest

    constructing city object for Santiago

    constructing city object for Paris

    The airport for Santiago is SCL

    The distance between Santiago and Paris is 7244 miles

    The current average temperature in Santiago is 66.5

    The time difference between Santiago and Paris right now
    is -6.0 hours

  3. Let's execute the getDistanceFrom method only for Santiago (city_id 40, as you may recall) within an SQL-J statement and see if we get 7244 for the answer:

    SELECT city.getDistanceFrom(
        (SELECT city FROM Cities WHERE city_id = 35))
    FROM Cities
    WHERE city_id = 40;

    You should indeed get 7244 as your answer.

  4. Disconnect:

    Disconnect;

  5. Shut down the system by exiting:

    exit;

    The ij exit command automatically shuts down the system properly in an embedded environment.

This section and the last section showed how you can execute the same methods that you execute in a Java program in the database. When executed within an SQL-J statement, methods are called database-side methods.