Chapter 30

Querying Databases with Data Access Objects

by Mike Cohn

• 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
• Summary

In this chapter you'll learn how to use the Microsoft Data Access Objects (DAOs) 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. Although the database native to the DAO is the Jet engine used in Microsoft Access, the DAO can also be used to access ODBC data sources. DAO programming could be the topic of an entire book, so this chapter only scratches the surface. It does, however, provide sufficient information to get you started and includes examples of reading, updating, deleting, adding, and searching.

Overview of the Data Access Objects

The Microsoft DAO and Jet database engine get around more than the Beach Boys do in that song of theirs. Jet was 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++. Because the DAO is used in so many other Microsoft products, you should not be surprised to discover that it can be useful in your Visual J++ efforts.

Figure 30.1 shows the relationships among the DAO elements. 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 that recordsets contain fields.

Figure 30.1 : The relationships among the DAO elements.

As you can see from Figure 30.1, the DAO contains many objects. Because these are ActiveX objects, you will access them from Visual J++ through Java interfaces. Java Type Library Wizard creates these interfaces.

Generating the Class Files

Before you can use the DAO in your Java programs, you must create the class files that will be linked into your programs. To create these files, select Java Type Library Wizard from the Tools menu in the Developer Studio. You will see a list of ActiveX items installed on your system (see Figure 30.2). Select Microsoft DAO 3.0 Object Library and click the OK button. This step generates Java class files for you to use. It also creates a file named SUMMARY.TXT, which contains a brief list of each object and method that was generated. The file is normally placed in C:\WINDOWS\JAVA\TRUSTLIB\DAO3032\SUMMARY.TXT.

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

Using _DBEngine Objects

Although more than 30 DAO interfaces are available, you do not need to know or use all of them. In fact, this chapter focuses on only a handful of them. Knowing about just these few interfaces will enable you 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 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. The dao_dbengine class handles the low-level tasks of constructing a COM object. This class is shown in Listing 30.1 and is also provided with Visual J++.

Listing 30.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 component object model (COM) component. To construct a _DBEngine object in your code, write the following line:

_DBEngine dbengine = dao_dbengine.create();

Useful Methods

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

OpenDatabase

This method opens 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 passes data to COM objects and each Variant object can 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 value indicating whether the database is to be opened in read-only or in 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 these parameters, and the database is opened in nonexclusive, 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 30.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 to create a database object with the OpenDatabase method of _DBEngine. In the following sections you will 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. You use BeginTrans to indicate the start of a transaction block. You can then use CommitTrans to commit the database activity since the transaction began or Rollback to undo the activity. These methods have the following signatures:

void BeginTrans();

void CommitTrans();

void Rollback();

Why would you want to use these methods? By thinking of your database activity in terms of transactions instead of atomic events (inserts, updates, and deletes), you can prevent some data problems. For example, suppose you are writing an order entry system for taking online orders. When customers access your Web page, they fill in a spreadsheet with a column for the product code and the quantity needed. They can enter as many items as desired but must click a Submit button before the order is sent to the shipping department.

If the customer enters an order for 100 units of product ABC, the program should check the database for stock on hand and reduce the available quantity by 100. Once the customer clicks 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 the DAO and are discussed in detail 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 adds, updates, and deletes are not allowed.

The options parametercan hold any of the values shown in Table 30.2.

As an example of creating a table recordset, consider Listing 30.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, which causes the Programmer table to be opened in read-only mode.

Listing 30.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 nonexclusive 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 closes a database object. It requires no parameters and has no return value.

Using Field Objects

The Field object represents the individual columns in a database table. Although many methods are available for use with Field objects, the two you will use most frequently are putValue and getValue. These methods move data into and out of Field objects. 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. Then the getValue method retrieves 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.

You can move data in the opposite direction-into a Field object- in a similar manner, using putValue. For example, the following code doubles the value stored in the salary field:

Variant value = new Variant();

value.putInt(oldSalary * 2);

salaryField.putValue(value);

Using Recordset Objects

In the section "Using Database Objects" 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 30.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 text area, as shown in Figure 30.3.

Figure 30.3 : The DAORead example displays information about each programmer in the database.

Listing 30.3. The class DAORead 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 DAORead 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 nonexclusive 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 DAORead 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 creates the database engine. The database is opened using OpenDatabase and three variants. Finally, the Programmer table is opened as a table record-set in read-only mode. The DisplayAllRecords method is then called to display the record contents.

DisplayAllRecords begins with a series of lines such as these:

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 variant is created, getRecordCount retrieves the number of records in the recordset. 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 access the fields stored in a recordset, you need to use the getFields method. In Listing 30.3 the fields are retrieved as follows:

Fields fields = recordset.getFields();

The getFields method will create a collection of fields. To get at an individual item within the collection, use the getItem method. In Listing 30.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 step will cause fld to contain a reference to the specific field that contains data from the LastName column of the database. Next fld.getValue retrieves the value of this field and places it in the variant value. Finally, value.toString converts the variant into a string that is passed to appendText, thus adding the contents of the LastName column 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 selects a new current record. Once 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 backwards 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, which is done with the Edit method. In edit mode, you can use the Resultset.getFields and the Field.putValue methods to alter the values in a record. Once the desired changes have been made, the record can be updated with the Update method. Once 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 30.4. This method is from the DAOUpdate example provided on the CD-ROM that accompanies this book. The GiveBonuses method is similar to DisplayAllRecords from Listing 30.3 in that it also uses getRecordCount, MoveFirst, and MoveNext to loop through the records in the recordset. However, in this example, each programmer who knows Java will have his or her salary doubled.

Listing 30.4. Updating the salaries 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 the method 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 returned as a string.

Figure 30.4 shows the result of running DAOUpdate.

Figure 30.4 : The DAOUpdate example doubles the salaries 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 30.5 shows the FireProgrammers method from the example DAODelete that 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 it finds a programmer who does not know Java, he or she is deleted from the database.

Listing 30.5. DAODelete 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. Figure 30.5 shows the results of running DAODelete.

Figure 30.5 : The DAODelete example 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 DAOAdd example provided on the CD-ROM shows how to add a new programmer to the database. Listing 30.6 shows the AddNewHire method from the DAOAdd class.

Listing 30.6. DAOAdd 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 puts the database into add mode. The fields in the recordset are retrieved with getFields. Then, for each column in the database, the following actions occur:

• The variant containing the field name retrieves the field.
• The value variant is loaded with the desired value.
• putValue puts the value into the field.
• Update commits the new record.

If an error had occurred or this method was interactive and the user changed his or her mind, CancelUpdate could be used to cancel the new record. Figure 30.6 shows the results of running DAOUpdate.

Figure 30.6 : The DAOAdd example shows how to add a new programmer to the database.

Finding Records

Sometimes you've created a resultset and need to scan through it to find 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. Other methods you can use are FindNext, FindPrevious, and FindLast, which 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 if a record was found that matched the search criteria, you 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, you can write code that will loop through a recordset to find 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 example class DAOFind that 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 30.7.

Figure 30.7 : The DAOFind example identifies underpaid programmers.

The FindFirst and other Find methods work only when the database is opened as a dynaset. Therefore, the OpenDatabase method needs to specify dbOpenDynaset instead of dbOpenTable, as shown in 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);

Summary

This chapter introduces the DAO and shows you how to use these objects in your Java programs. You learn about Database, Recordset, and Field objects and how to combine these objects to perform useful tasks. The examples use the DAO to browse a database, add new records, update existing records, delete records, and search for records. Armed with the information from this chapter, you are now prepared to write your own programs using the DAO.