Chapter 22

Using the Data Access Objects

• Overview of the Data Access Objects
  • Generating the Class Files
• Using _DBEngine Objects
  • Useful Methods
• Using Database Objects
  • Controlling Transactions
  • OpenRecordset
  • Close
• Using Field Objects
• Using Recordset Objects
  • Reading Records Sequentially
  • Updating Records
  • Deleting Records
  • Adding New Records
  • Finding Records
• Using SQL
• Summary

In this chapter you will learn how to use the Microsoft Data Access Objects (DAO) in your Visual J++ programs. The DAO is an entire database engine that includes a set of objects and methods capable of performing just about any database operation you're likely to need. While the native database to DAO is the Jet engine used in Microsoft Access, DAO can also be used to access ODBC data sources. DAO programming could be the topic of an entire book, so this chapter will only scratch the surface. It will, however, provide you with sufficient information to get you started, and it includes examples of reading, updating, deleting, adding, and searching.

Overview of the Data Access Objects

Microsoft's Data Access Objects and Jet Database engine get around more than the Beach Boys do in that song of theirs. Jet was first introduced in November, 1992 in Access 1.0. Since then, the Jet engine and DAO have been used in Access, Visual Basic, Visual Basic for Applications (including Word and Excel), and Visual C++. Since DAO is used in so many other Microsoft products, it should not be surprising that DAO can be useful in your Visual J++ efforts.

The relationships among the Data Access Objects are shown in Figure 22.1. This figure does not show inheritance relationships. Instead it shows container relationships. In other words, from this figure you can tell that a Database contains recordsets and recordsets contain Fields.

Figure 22.1 : The relationships among Data Access Objects.

As you can see from Figure 22.1, the DAO is made up of a large number of objects. Because these are ActiveX objects, you will access them from Visual J++ through Java interfaces. These interfaces are created with the Java Type Library Wizard.

Generating the Class Files

Before you can use DAO in your Java programs you must first create the class files that will be linked into your programs. To do this, select Java Type Library Wizard from the Tools menu in the Developer Studio. You will be presented with a list of ActiveX items installed on your system, similar to Figure 22.2. Select Microsoft DAO 3.0 Object Library and press the OK button. This will generate Java class files for your use. It will also create a file named SUMMARY.TXT. This file contains a brief list of each object and method that was generated. The file will normally be placed in C:\WINDOWS\JAVA\TRUSTLIB\SUMMARY.TXT.

Figure 22.2 : You must run the Java Type Library Wizard to generate class files for DAO.

Using _DBEngine Objects

Although there are more than 30 DAO interfaces available to you, you do not need to know or use all of them. In fact, in this chapter you will focus on only a handful of them. Even so, you will be able to add powerful database access to your Java programs.

The first object you need to know about is the _DBEngine interface. This object is at the top of the DAO hierarchy and is the first object you will construct when writing a program that uses DAO. To construct a _DBEngine object you should use the method DBEngine_create in the class dao_dbengine. This class is shown in Listing 22.1 and is also provided with Visual J++.

Listing 22.1. dao_dbengine.java.

// Create a DAO DBEngine object with the license

import dao3032._DBEngine;
import com.ms.com.*;

public class dao_dbengine
{
    // The static public method creates the DBEngine object
    static public _DBEngine create()
    {
        // The return value
        _DBEngine result;

        // Create the License Manager object
        ILicenseMgr mgr = new LicenseMgr();

        // Use the License Manager to create the DBEngine
        result = (_DBEngine) mgr.createWithLic(

            // The license key for the DAO DBEngine
            "mjgcqcejfchcijecpdhckcdjqigdejfccjri",

            // The CLSID for the DAO DBEngine
            "{00025E15-0000-0000-C000-000000000046}",

            // The aggregation IUnknown* punkOuter
            null,

            // The ctxFlag to create in inproc server
            ComContext.INPROC_SERVER
            );

        return result;
    }
}

The DBEngine_create method uses the Java LicenseMgr class to construct the new COM (Component Object Model) component. To construct a _DBEngine object in your code, do the following:

_DBEngine dbengine = dao_dbengine.create();

Useful Methods

There are many methods available for use with _DBEngine objects; the following sections describe the most useful.

OpenDatabase

This method is used to open a new database. Its signature is as follows:

Database OpenDatabase(String dbname, Variant exclusive,
    Variant readonly, Variant source);

The dbname parameter is the filename of the database being opened. The remaining three parameters are each variants. The Variant class is used to pass data to COM objects and each Variant object can be used to hold any type of data-string, numeric, or Boolean. The exclusive parameter needs to hold a Boolean value indicating whether this program requires exclusive access to the database. The read-only parameter is a Boolean indicating whether the database is to be opened in read-only or read-write mode. The source parameter holds string data such as PWD=ROSEBUD that can be used when opening an ODBC data source. As an example of using OpenDatabase, consider the following:

// create Variants that will hold parameters that will be
// passed to OpenDatabase
Variant var1 = new Variant();
Variant var2 = new Variant();
Variant var3 = new Variant();

// set parameters for call to OpenDatabase
var1.putBoolean(false);
var2.putBoolean(false);
var3.putString("");

// Open the database for non-exclusive, read-write access
Database db = dbengine.OpenDatabase(filename, var1, var2, var3);

In this case, three new variants are constructed. The first two are set to false. The third is set to hold an empty string. OpenDatabase is then called with this parameter and the database is opened in non-exclusive, read-write mode.

getVersion

The getVersion method does not require any parameters and will return a string representing the version of the Jet engine in use. Table 22.1 shows the various Jet engine versions and the versions of other Microsoft products with which they were released.

Using Database Objects

You have already seen how a Database object can be created with the OpenDatabase method of _DBEngine. In the following sections you learn about the most useful methods for using database objects.

Controlling Transactions

The three methods BeginTrans, CommitTrans, and Rollback are used to manage transactions. Transactions are useful because they assist in managing the integrity of a database. Use BeginTrans to indicate the start of a transaction block. Then use CommitTrans to commit the database activity since the transaction began, or use Rollback to undo the activity. These methods have the following signatures:

void BeginTrans();
void CommitTrans();
void Rollback();

Why would you want to do this? By thinking of your database activity in terms of transactions instead of automatic events (inserts, updates, and deletes) you can prevent some classes of data problems. For example, suppose you are writing an order entry system that will be used to take online orders. When a customer accesses your Web page he fills in a spreadsheet with a column for the product code and the quantity needed. He can order as many items as he desires but must press a Submit button before the order is sent to the shipping department.

If a customer indicates he wants 100 units of product ABC the program should check the database for stock on hand and reduce the available quantity by 100. When the customer presses the Submit button, the transaction is committed to the database. However, if the customer selects the Cancel button instead of Submit, the transaction can be rolled back.

OpenRecordset

The OpenRecordset method creates a recordset. A recordset is a collection of records. Recordset objects are among the most important in DAO and will be discussed in detail in the section "Using Recordset Objects" later in this chapter. The signature for OpenRecordset is as follows:

Recordset OpenRecordset(String source, Variant type,
    Variant options);

The source parameter can hold either the name of the table in the database or an SQL statement that returns a set of rows. The type parameter must hold one of the following values:

• dbOpenTable
• dbOpenDynaset
• dbOpenSnapshot

Each of these parameters indicates a different type of recordset. A table recordset corresponds to a single table in the underlying database. Records may be added, updated, or deleted in a table recordset. A dynaset recordset is the result of a query and may include columns from more than one table. Records may be added, updated, or deleted in a dynaset. Finally, a snapshot recordset is a static copy of the records at the moment the recordset was created. A snapshot may contain fields from more than one table but additions, updates, and deletions are not allowed.

The options parameter can hold any of the values shown in Table 22.2.

As an example of creating a table recordset consider Listing 22.2. First a database is opened. Then, two variants, var4 and var5, are constructed. The values dbOpenTable and dbReadOnly are placed into the variants and are passed to OpenRecordset. The name Programmer is also passed to OpenRecordset. This will cause the Programmer table to be opened in read-only mode.

Listing 22.2. Opening a table recordset.

// create Variants that will hold parameters that will be
// passed to OpenDatabase
Variant var1 = new Variant();
Variant var2 = new Variant();
Variant var3 = new Variant();

// set parameters for call to OpenDatabase
var1.putBoolean(false);
var2.putBoolean(false);
var3.putString("");

// Open the database for non-exclusive access
Database db = dbengine.OpenDatabase(filename, var1, var2, var3);

// create Variants that will hold parameters that will be
// passed to OpenDatabase
Variant var4 = new Variant();
Variant var5 = new Variant();

var4.putShort(Constants.dbOpenTable);
var5.putShort(Constants.dbReadOnly);

// create the recordset
recordset = db.OpenRecordset("Programmer", var4, var5);

Close

This method is used to close a database object. It requires no parameters and has no return value.

Using Field Objects

The Field object is used to represent the individual columns in a database table. Although there are many methods available for use with Field objects, the two you will use most frequently are putValue and getValue. These methods are used to move data into and out of Field objects, respectively. The signatures of these methods are as follows:

Variant getValue();
void putValue(Variant);

As an example of how to retrieve a value from a Field object, consider the following:

Variant value = new Variant();
value = salaryField.getValue();
int salary = value.toInt();

First, a new Variant object is constructed. The getValue method is then used to retrieve the value of salaryField. Because value is a variant, it is converted to a more useful data type. In this case, value.toInt is used to load the integer variable, salary.

Moving data in the opposite direction-into a Field object-can be done in a similar manner using putValue. For example, the following code will double the value stored in the salary field:

Variant value = new Variant();
value.putInt(oldSalary * 2);
salaryField.putValue(value);

Using Recordset Objects

In the discussion of the Database object, you were introduced to recordsets. In this section you learn how to use a recordset to view, add, update, delete, and find records. The following sections describe how to perform some of the most common operations on recordsets.

Reading Records Sequentially

To move through a recordset you can use the MoveFirst, MoveNext, MovePrevious, and MoveLast methods whose signatures are as follows:

void MoveFirst();
void MoveNext();
void MovePrevious();
void MoveLast();

Each of these methods will reposition the current record of the recordset. As an example of how these methods are used, consider Listing 22.3. This example iterates through all of the rows in the Programmer table of the supplied Access database. Information about each programmer is displayed in a TextArea, as shown in Figure 22.3.

Figure 22.3 : The EX22A example displays information about each programmer in the database.

Listing 22.3. The class EX22A demonstrates moving through a recordset sequentially.

import java.applet.*;
import java.awt.*;
import java.net.*;
import dao_dbengine;
import dao3032.*;
import com.ms.com.Variant;

public class EX22A extends Applet
{
    Recordset recordset;
    TextArea output;
    Database db;

    public void init()
    {
        resize(500, 400);
        output = new TextArea(20, 50);
        add(output);
    }

    public void start()
    {
        OpenDatabase();
    }

    public void stop()
    {
        // Close the recordset and database
        recordset.Close();
        db.Close();
    }

    private boolean OpenDatabase()
    {
        URL dbURL;
        try {
            // otherwise generate it relative to the applet
            dbURL = new java.net.URL(getDocumentBase(), "sample.mdb");
        }
        catch(Exception e) {
            showStatus("Error: " + e.getMessage());
            return false;
        }

        // strip "file:/" from dbURL
        String filename = dbURL.getFile().substring(1);

        // create the database engine
        _DBEngine dbengine = dao_dbengine.create();

        // create Variants that will hold parameters that will be
        // passed to OpenDatabase
        Variant var1 = new Variant();
        Variant var2 = new Variant();
        Variant var3 = new Variant();

        // set parameters for call to OpenDatabase
        var1.putBoolean(false);
        var2.putBoolean(false);
        var3.putString("");

        // Open the database for non-exclusive access
        db = dbengine.OpenDatabase(filename, var1, var2, var3);

        // create Variants that will hold parameters that will be
        // passed to OpenDatabase
        Variant var4 = new Variant();
        Variant var5 = new Variant();

        var4.putShort(Constants.dbOpenTable);
        var5.putShort(Constants.dbReadOnly);

        // create the recordset
        recordset = db.OpenRecordset("Programmer", var4, var5);

        // display all the records in this dynaset
        DisplayAllRecords();

        return true;
    }

    private void DisplayAllRecords()
    {
        // create variants and assign the names of each column
        Variant varFirstName = new Variant();
        varFirstName.putString("FirstName");

        Variant varLastName = new Variant();
        varLastName.putString("LastName");

        Variant varSalary = new Variant();
        varSalary.putString("Salary");

        Variant varKnowsJava = new Variant();
        varKnowsJava.putString("KnowsJava");

        Variant varJobTitle = new Variant();
        varJobTitle.putString("JobTitle");

        // determine how many records in the recordset
        int count = recordset.getRecordCount();

        // position recordset at the first record
        recordset.MoveFirst();

        // loop through the recordset, displaying each record
        for(int recNum = 0; recNum < count; recNum++)
        {
            // get the fields in this recordset
            Fields fields = recordset.getFields();
            _Field fld;

            // create a Variant that will hold each the value
            // read from each column
            Variant value;

            // get the LastName field
            fld = fields.getItem(varLastName);
            // get its value
            value = fld.getValue();
            // display the value
            output.appendText(value.toString() + ", ");

            // get the FirstName field
            fld = fields.getItem(varFirstName);
            // get its value
            value = fld.getValue();
            // display the value
            output.appendText(value.toString() + "\r\n");

            // get the JobTitle field
            fld = fields.getItem(varJobTitle);
            // get its value
            value = fld.getValue();
            // display the value
            output.appendText("\t" + value.toString() + "\r\n");

            // get the Salary field
            fld = fields.getItem(varSalary);
            // get its value
            value = fld.getValue();
            // display the value
            output.appendText("\t" + value.toString() + "\r\n");

            // get the KnowsJava field
            fld = fields.getItem(varKnowsJava);
            // get its value
            value = fld.getValue();
            // display the value
            Boolean knowsJava = new Boolean (value.toBoolean());
            output.appendText("\tKnows Java: " + knowsJava.toString() + "\r\n");

            recordset.MoveNext();
        }
    }
}

The init method of class EX22A simply resizes the screen and then places a TextArea component on the screen. The start method invokes the OpenDatabase method. OpenDatabase constructs a URL from the document base and name of the Access database file to be opened. Next, dao_dbengine.create is used to create the database engine. The database is opened using OpenDatabase and three variants. Finally, the Programmer table is opened as a table recordset in read-only mode. The DisplayAllRecords method is then called to display the record contents.

DisplayAllRecords begins with a series of lines such as the following:

Variant varFirstName = new Variant();
varFirstName.putString("FirstName");

These lines create a variant variable for each column in the Programmer table and then store the name of the column in the variant. After each of the variants is created, the number of records in the recordset is retrieved with getRecordCount. The getRecordCount method will always return the correct number of records in a table-type recordset. For a dynaset or snapshot, getRecordCount will return the correct value only after all records in the set have been retrieved.

The recordset is initially positioned on the first record with MoveFirst. At this point a loop begins that will iterate through each of the records in the recordset based on the quantity returned by getRecordCount. Each time through the loop the contents of the row will be displayed. In order to get at the fields stored in a recordset it is necessary to use the getFields method. In Listing 22.3 this is done as follows:

Fields fields = recordset.getFields();

This will create a collection of fields. To get at an individual item within the collection, use the getItem method. In Listing 22.3 the contents of the LastName field are retrieved with the following code:

// get the LastName field
fld = fields.getItem(varLastName);
// get its value
value = fld.getValue();
// display the value
output.appendText(value.toString() + ", ");

The method fields.getItem is passed the variant that contains the string LastName. This will cause fld to contain a reference to the specific field that contains data from the LastName column of the database. Next, fld.getValue is used to retrieve the value of this field and place it in the variant value. Finally, value.toString is used to convert the variant into a string that is passed to appendText. This causes the contents of the LastName column to be added to the TextArea on the applet.

After similar code is repeated for each of the columns in the database-FirstName, LastName, JobTitle, Salary, and KnowsJava-the MoveNext method is used to select a new current record. After the user closes the applet, the stop method is invoked and the recordset and database are closed.

The MoveLast and MovePrevious methods work in an analogous manner. If you need to move backward through a recordset, you can use these methods instead of MoveFirst and MoveNext.

Updating Records

Before the contents of a record can be updated with new values, the record must be placed in edit mode. This is done with the Edit method. Once in edit mode you can use the Resultset.getFields and the _Field.putValue methods to alter the values in a record. When the desired changes have been made, the record can be updated with the Update method. After a record is updated it is automatically taken out of edit mode. If instead of updating a record you need to take a record out of edit mode without committing any changes to the record, you can use CancelUpdate. The signatures of these new methods are as follows:

void Edit();
void Update();
void CancelUpdate();

As an example of how to update the values in a record, consider the GiveBonuses method shown in Listing 22.4. This method is from the example EX22B, which is provided on the CD-ROM that accompanies this book. The GiveBonuses method is similar to DisplayAllRecords from the previous listing in that it uses getRecordCount, MoveFirst, and MoveNext to loop through the records in a resultset. However, in this example, each programmer who knows Java will have his or her salary doubled.

Listing 22.4. Updating the salary of all Java programmers in the database.

private void GiveBonuses()
{
    // determine how many records in the recordset
    int count = recordset.getRecordCount();

    // create a variant for each column
    Variant varSalary = new Variant();
    Variant varKnowsJava = new Variant();

    // set the name of each column
    varSalary.putString("Salary");
    varKnowsJava.putString("KnowsJava");

    // position recordset at the first record
    recordset.MoveFirst();

    // loop through the recordset, displaying each record
    for(int recNum = 0; recNum < count; recNum++)
    {
        // get the fields in this recordset
        Fields fields = recordset.getFields();
        _Field fld;

        // create a Variant that will hold each the value
        // read from each column
        Variant value;

        // get the KnowsJava field
        fld = fields.getItem(varKnowsJava);
        // get its value
        value = fld.getValue();

        // if the programmer knows Java, give a good raise
        if (value.toBoolean() == true)
        {
            // get the Salary field
            fld = fields.getItem(varSalary);
            // get its value
            value = fld.getValue();
            // store the salary
            int salary = value.toInt();

            // double the programmer's salary
            int newSalary = salary * 2;

            // put the recordset into edit mode
            recordset.Edit();

            // assign the new salary to the current record
            value.putInt(newSalary);
            fld.putValue(value);

            // update the recordset
            recordset.Update();

            // display the salary change
            DisplayRaise(fields, salary, newSalary);
        }
        else
            DisplayNoRaise(fields);

        recordset.MoveNext();
    }
}

Inside the for loop of GiveBonuses the value of the KnowsJava field is retrieved and converted to a Boolean value. If this value is true, the salary field is retrieved from the database and stored in the integer variable salary. This value is doubled and then stored in newSalary. The actual updating of the record in the database takes place with the following lines:

recordset.Edit();
value.putInt(newSalary);
fld.putValue(value);
recordset.Update();

Depending on whether or not the programmer received a raise, either the method DisplayRaise or DisplayNoRaise will be called. These methods are as follows:

private void DisplayRaise(Fields fields, int oldSalary, int newSalary)
{
    String firstName = GetField(fields, "FirstName");
    String lastName  = GetField(fields, "LastName");

    output.appendText(firstName + " " + lastName +
            " got a raise from " +
            String.valueOf(oldSalary) +
            " to " + String.valueOf(newSalary) + "\r\n");
}

private void DisplayNoRaise(Fields fields)
{
    String firstName = GetField(fields, "FirstName");
    String lastName  = GetField(fields, "LastName");

    output.appendText(firstName + " " + lastName +
            " didn't get a raise.\r\n");
}

Rather than construct and manipulate their own variant variables, each of these methods calls the GetField method. GetField was written as follows:

private String GetField(Fields fields, String fldName)
{
    // create a new Variant using fldName
    Variant var = new Variant();
    var.putString(fldName);

    // get the field
    _Field fld = fields.getItem(var);
    // get its value
    Variant value = fld.getValue();

    return value.toString();
}

GetField works on a generic Fields container and field name. A variant is constructed and set to hold the field name. The corresponding field is then retrieved from the Fields container and its value is then returned as a string.

The result of running EX22B is shown in Figure 22.4.

Figure 22.4 : Example EX22B doubles the salary of all Java programmers.

Deleting Records

Deleting a record is as simple as calling the Delete method when the record to be deleted is the current record in the recordset. For example, Listing 22.5 shows the FireProgrammers method from the example EX22C, which is included on the CD-ROM. This method loops through all of the programmers in the Programmer table examining the value of the KnowsJava column. When a programmer is found who does not know Java, he is deleted from the database.

Listing 22.5. EX22C illustrates how to fire anyone who doesn't know Java.

private void FireProgrammers()
{
    // determine how many records in the recordset
    int count = recordset.getRecordCount();

    // create a variant for the KnowsJava column
    Variant varKnowsJava = new Variant();
    varKnowsJava.putString("KnowsJava");

    // position recordset at the first record
    recordset.MoveFirst();

    // loop through the recordset, displaying each record
    for(int recNum = 0; recNum < count; recNum++)
    {
        // get the fields in this recordset
        Fields fields = recordset.getFields();
        _Field fld;

        // create a Variant that will hold the values
        // read from the columns
        Variant value;

        // get the KnowsJava field
        fld = fields.getItem(varKnowsJava);
        // get its value
        value = fld.getValue();

        // fire the programmer if he doesn't know Java
        if (value.toBoolean() == false)
        {
            // display a message about the fired programmer
            DisplayFiring(fields);

            // delete the current record
            recordset.Delete();
        }

        recordset.MoveNext();
    }
}

The Delete method does not automatically advance the recordset to the next record. It is still necessary to use MoveNext to advance to the next record in the set. The results of running EX22C are shown in Figure 22.5.

Figure 22.5 : Example EX22C fires any programmers who do not know Java.

Adding New Records

Adding a record is similar to editing a record. However, instead of placing the database into edit mode with the Edit method, the database is placed into add mode with the AddNew method. The example EX22D, provided on the CD-ROM, shows how to add a new programmer to the database. Listing 22.6 shows the AddNewHire method from the EX22D class.

Listing 22.6. EX22D shows how to add a new programmer to the database.

private void AddNewHire()
{
    // create a variant for each column
    Variant varFirstName = new Variant();
    Variant varLastName = new Variant();
    Variant varSalary = new Variant();
    Variant varKnowsJava = new Variant();
    Variant varJobTitle = new Variant();

    // set the name of each column
    varFirstName.putString("FirstName");
    varLastName.putString("LastName");
    varSalary.putString("Salary");
    varKnowsJava.putString("KnowsJava");
    varJobTitle.putString("JobTitle");

    // tell the recordset its about to get a new record
    recordset.AddNew();

    // retrieve the fields for this recordset
    Fields fields = recordset.getFields();
    _Field fld;

    // create a Variant to hold temporary values
    Variant value = new Variant();

    // set the FirstName field
    fld = fields.getItem(varFirstName);
    value.putString("Napoleon");
    fld.putValue(value);

    // set the LastName field
    fld = fields.getItem(varLastName);
    value.putString("Solo");
    fld.putValue(value);

    // set the Salary field
    fld = fields.getItem(varSalary);
    value.putInt(63000);
    fld.putValue(value);

    // set the KnowsJava field
    fld = fields.getItem(varKnowsJava);
    value.putBoolean(true);
    fld.putValue(value);

    // set the JobTitle field
    fld = fields.getItem(varJobTitle);
    value.putString("Lead Programmer");
    fld.putValue(value);

    // commit the new record to the recordset
    recordset.Update();
}

In order to add a new programmer, a variant is created for each column in the database and AddNew is used to put the database into add mode. The fields in the recordset are retrieved with getFields. Then, for each column in the database the following actions are taken:

• The field is retrieved by using the variant containing the field name.
• The value variant is loaded with the desired value.
• putValue is used to put the value into the field.

Finally, Update is used is to commit the new record. If an error had occurred or this was an interactive method and the user changed his mind, CancelUpdate could be used to cancel the new record. The results of running EX22D are shown in Figure 22.6.

Figure 22.6 : Example EX22D shows how to add a new programmer to the database.

Finding Records

Sometimes you've created a Resultset and need to scan through it looking for one or more records that match certain criteria. The DAO engine provides a set of methods for doing exactly this. To find the first record that meets your criteria, you can use the FindFirst method. Additionally, there are also FindNext, FindPrevious, and FindLast methods. These methods have the following signatures:

void FindFirst(String);
void FindNext(String);
void FindPrevious(String);
void FindLast(String);

The string parameter passed to these methods is the search criteria. For example, the following two examples show valid search criteria:

FindFirst("Salary < 50000");
FindLast("FirstName = 'Savannah'");

In order to determine whether a record was found that matched the search criteria, use the method getNoMatch whose signature is as follows:

boolean getNoMatch();

This method will return true if no match was found, or false otherwise. By combining these methods, it is possible to write code that will loop through a resultset finding all records that match the desired criteria. For example, consider the following FindProgrammers method:

private void FindProgrammers()
{
    // setup the search criteria
    String criteria = "Salary < 50000";

    // find the first matching record
    recordset.FindFirst(criteria);

    // while there are matching records keep going
    while (recordset.getNoMatch() == false)
    {
        // display the programmer's name
        DisplayProgrammer();

        // and search for another one
        recordset.FindNext(criteria);
    }
}

This method is from the sample class EX22E, which is included on the accompanying CD-ROM. FindProgrammers searches for all programmers who make less than the specified salary. For each record that matches, the DisplayProgrammer method will be called. This method displays information about the underpaid programmers, as shown in Figure 22.7.

Figure 22.7 : Example EX22E identifies underpaid programmers.

The FindFirst and other Find methods work only when the database is opened as a dynaset. This means the OpenDatabase method needs to specify dbOpenDynaset instead of dbOpenTable. This is done with the following code fragment:

Variant var4 = new Variant();
Variant var5 = new Variant();

var4.putShort(Constants.dbOpenDynaset);
var5.putShort(Constants.dbEditAdd);

// create the recordset
recordset = db.OpenRecordset("Programmer", var4, var5);

Using SQL

One of the simplest ways to manipulate your database is with SQL. SQL is a language that was designed specifically for querying and modifying relational databases. Fortunately, DAO supports the use of SQL. You can use SQL statements with DAO in the following two ways:

• As a parameter to OpenRecordset.
• As a parameter to the Execute method in the Database class.

In earlier examples in this chapter you passed the name of the table to be opened to OpenRecordset. For example, the following was used to open the Programmer table:

recordset = db.OpenRecordset("Programmer", var4, var5);

Instead of passing the name of a table, you can pass an SQL statement. This means you can do the following:

recordset=db.OpenRecordset("select * from Programmer",var4,var5);

Because this SQL statement selects all rows, the result set it creates will contain the same rows as the result set created by using the table name. However, you could include any valid SQL where clause to qualify the select statement. Because an SQL statement creates a dynaset, you need to pass dbOpenDynaset in the first variant passed to OpenRecordset. This can be done as follows:

Variant var1 = new Variant();
Variant var2 = new Variant();
var1.putShort(Constants.dbOpenDynaset);
var2.putShort(Constants.dbEditAdd);

// create the recordset
recordset=db.OpenRecordset("select * from Programmer",var1,var2);

SQL has other uses beyond just creating a record set. Sometimes you just want to update rows in the database, delete a row, or add a row. To do this you can use the Execute method of the Database class. The signature of the Execute method is as follows:

void Execute(String sqlStr, Variant options);

The sqlStr parameter holds a valid SQL statement; the options parameter is used to specify one of the values in Table 22.3.

As an example of using Execute, consider the following, which sets every programmer's salary to $100,000:

Variant var = new Variant();
var.putShort(Constants.dbFailOnError);
db.Execute("update Programmer set Salary = 100000", var);

As a more complete example of using SQL with DAO, the example EX22F is provided on the accompanying CD-ROM. This example is similar to some of the non-SQL examples developed earlier in this chapter. However, because EX22F uses SQL to manipulate the database, you'll see how much easier it is. Listing 22.7 shows the OpenDatabase method of EX22F.

Listing 22.7. The OpenDatabase method of EX22F.

private boolean OpenDatabase()
{
    URL dbURL;
    try {
        // otherwise generate it relative to the applet
        dbURL = new java.net.URL(getDocumentBase(),
                "sample.mdb");
    }
    catch(Exception e) {
        showStatus("Error: " + e.getMessage());
        return false;
    }

    // strip "file:/" from dbURL
    String filename = dbURL.getFile().substring(1);

    // create the database engine
    _DBEngine dbengine = dao_dbengine.create();

    // create Variants that will hold parameters that
    // will be passed to OpenDatabase
    Variant var1 = new Variant();
    Variant var2 = new Variant();
    Variant var3 = new Variant();

    // set parameters for call to OpenDatabase
    var1.putBoolean(false);
    var2.putBoolean(false);
    var3.putString("");

    // Open the database for non-exclusive access
    db = dbengine.OpenDatabase(filename,var1,var2,var3);

    // create Variants that will hold parameters that
    // will be passed to OpenDatabase
    Variant var4 = new Variant();
    Variant var5 = new Variant();

    var4.putShort(Constants.dbOpenDynaset);
    var5.putShort(Constants.dbEditAdd);

    // create the recordset
    recordset=db.OpenRecordset("select * from Programmer",
            var4, var5);

    // display all the records in this dynaset
    DisplayAllRecords();

    // give raises to the Java programmers
    GiveRaises();
    HireNewProgrammer();

    // re-display all the records in this dynaset
    DisplayAllRecords();

    // requery the database
    Variant var6 = new Variant();
    var6.putNull();
    recordset.Requery(var6);

    // re-display all the records in this dynaset
    DisplayAllRecords();

    return true;
}

As you can see, this version of OpenDatabase is similar to the one used in EX22A that was shown in Listing 22.3. The first difference occurs with the call to OpenRecordset, which uses an SQL statement instead of a table name and specifies dbOpenDynaset instead of dbOpenTable. After the recordset is created the method DisplayAllRecords is called. This method is the same as was shown in Listing 22.3, so it is not repeated here. Next, the methods GiveRaises and HireNewProgrammer are called. These methods are shown in Listing 22.8.

Listing 22.8. The GiveRaises and HireNewProgrammers methods of EX22F. private void GiveRaises()

{
  Variant var = new Variant();
  var.putShort(Constants.dbFailOnError);

  db.Execute("update Programmer set Salary = " +
          "Salary * 2 where KnowsJava <> 0", var);

  output.appendText("\r\nRaises given: " +
          String.valueOf(db.getRecordsAffected()) +
          "\r\n\r\n");
}

private void HireNewProgrammer()
{
  Variant var = new Variant();
  var.putShort(Constants.dbFailOnError);

  String sqlStr = "insert into Programmer (FirstName, " +
  "LastName, Salary, KnowsJava, JobTitle) " +
  "values (\"Napoleon\", \"Solo\", 63000, 0, \"Lead\")";

  db.Execute(sqlStr, var);
}

Both GiveRaises and HireNewProgrammer create a variant and load it with dbFailOnError. GiveRaises uses an SQL update statement to double the salary of every Java programmer in the database while HireNewProgrammer uses insert to add a programmer to the database. In the case of GiveRaises, the method getRecordsAffected, a member of Database, is used to determine how many raises were given. This method always returns the number of rows affected by the last SQL statement.

After the OpenDatabase method has called GiveRaises and HireNewProgrammer, DisplayAllRecords is again used to show the effect of these methods. As you can see in Figure 22.8, the raises are shown but Napoleon Solo does not appear. Because you're using a dynaset the raises are automatically reflected in the set; however, the new record for Napoleon Solo is not added to the dynaset until the database is requeried. This can be done by using the Requery method. Requery takes a single null variant parameter and is executed as follows:

Figure 22.8 : The output of EX22F before the database is requeried.

// requery the database
Variant var6 = new Variant();
var6.putNull();
recordset.Requery(var6);

This causes all record sets to be re-created and the final call to DisplayAllRecords in OpenDatabase will now display Napoleon's name as shown in Figure 22.9.

Figure 22.9 : The final output of EX22F.

Summary

This chapter introduced you to the Data Access Objects (DAO) and showed you how to use them in your Java programs. You learned about Database, Recordset, and Field objects and how to combine these objects to perform useful tasks. You saw examples of using DAO to browse a database, add new records, update existing records, delete records, search for records, and execute SQL. Armed with the information you learned in this chapter, you are now prepared to write your own programs using the Data Access Objects.