First Steps with the Java Class Library

catch(PsqlEOFException ex) {

// No more rows

To access a RowSet object’s properties

►

Once you have a RowSet object, you can obtain information about the RowSet from its RowSetMetaData.

RowSetMetaData rsmd = rowset.getRowSetMetaData();

Consult the methods of the RowSetMetaData class in PSQL Java Class Library Reference for more details.

Navigate the Rows in a Row Set

As previously shown, you can iterate through the row set’s rows by repeatedly calling the “getNext” method. You can iterate backwards by changing the “direction” property of the row set’s RowSetMetaData object.

rsmd.setDirection(Const.BTR_BACKWARDS);

After setting this property, the behavior of the “getNext” method becomes “getPrevious.”

Note: You can “reset” the currency of the row set at any time to be before the first or after the last row (as shown in the following procedures). This is an efficient way to retrieve the first row or last row without having to iterate through the entire row set.

►

To retrieve the first row in the row set:

Set the direction to forwards. This is the default.

rsmd.setDirection(Const.BTR_FORWARDS);

Set the row set’s currency to before the 1st row.

rowset.reset();

Retrieve the first row.

Row first = rowset.getNext();

►

To retrieve the last row in a row set:

Set the direction to backwards.

rsmd.setDirection(Const.BTR_BACKWARDS);

Set the row set’s currency to after the last row.

rowset.reset();

Retrieve the last row.

Row last = rowset.getNext();

►

To access a Row object’s column data

Row objects inherit a multitude of accessor/mutator methods from the Buffer class. These methods allow you to set/get column data to/from a Row object’s buffer.

For example, to retrieve the data in column 0 as a String, do one of the following.

String str = row.getString(0);

String str = row.getString(“ColumnName”);

where “ColumnName” is the name defined in the data dictionary for column 0. Column names are case sensitive.

Now, to set the data for column 0, you use one of the setString methods.

row.setString(0, “MyColumnData”);

row.setString(“ColumnName”, “MyColumnData”);

Consult the methods of the Buffer class in PSQL Java Interface Reference for more details.

Restrict or Filter the Data

►

To restrict/filter rows in a row set

To restrict/filter the rows in a row set, you have to use the RowSetMetaData’s “addFirstTerm,” “addOrTerm,” and “addAndTerm.” For example, if the first column of a table contains integer data and you want all the rows where the first column’s value is greater that 25, do the following.

Get the row set’s RowSetMetaData.

RowSetMetaData rsmd = rowset.getRowSetMetaData();

Get the ColumnDef for first column, column number 0.

ColumnDef cmd = rsmd.getColumnDef(0);

Reset the row set’s currency to the beginning.

rowset.reset();

Add the first term.

rowset.addFirstTerm(cmd, Const.BTR_GR, “23”);

Call getNext() to get the first row where column 0 is greater than 23.

You can add additional terms with the RowSetMetaData’s “addOrTerm” and “addAndTerm.” These methods allow you to build up more complex filtering conditions similar to a SQL “WHERE” clause.

►

To select columns from a row set

You can specify a subset of the columns to retrieve, similar to the SQL “select” statement, by using the RowSetMetaData methods, “addColumns” and “dropColumns.” By default, a row set will retrieve all the column data unless the row set was created with the “noColumns” parameter set to “true.”

RowSet rowset = table.createRowSet(true);

In this case, no columns will be retrieved. Alternatively, after creating the row set, you can drop all or some of the columns.

RowSetMetaData rsmd =rowset.getRowSetMetaData();

rsmd.dropAllColumns();

Now you can add the set of columns you’re interested in to the row set.

rsmd.addColumns(“LastName”, “FirstName”);

You can add columns to the row set by column names or by column numbers. See the various “addColumns” and “dropColumns” methods of the “RowSetMetaData” class in the PSQL Java Interface Reference documentation for more details.

If you access column data in the rows by column number, be aware that the column numbers will be affected by the new ordering produced by “addColumns” and “dropColumns” methods. For example, if “FirstName” was originally column number 3 in the row set, after dropping all the columns and then adding “LastName” and “FirstName,” the “FirstName” column number will be 1.

►

To retrieve a row by index

You can retrieve a row by using a defined index along with a comparison operator. For instance, if an index, “Last_Name” has been created on the “LastName” column for a hypothetical table, you can do the following to find a row with a “LastName” equal to “Smith.”

Using the row set’s RowSetMetaData object, set the access path to use index “Last_Name.”

rsmd.setAccessPath(“Last_Name”);

Create an index object using the index name. You can use the index number instead of the name.

Index index = rowset.createIndex(“Last_Name”);

Set the “LastName” column data for the index.

index.setString(“LastName”, “Smith”);

Get the first row with LastName == Smith.

try {

Row row = rowset.getByIndex(Const.BTR_EQ, index);

Catch any exceptions that occur.

catch(PsqlOperationException ex) {

// If the error code == 4, then no row with LastName
// == Smith exists. This could be considered
// normal operation.

// If the status code is not 4, the operation failed
// for some other reason than “not found.”

if (ex.getErrorCode() != 4)

throw(ex);

If the index allows duplicate key values, the first row which satisfies the comparison operator will be returned. The returned row becomes the current row, allowing you to retrieve the next logical row, based on the index, with the row set’s “getNext” method.

You can get the list of index names from the table’s TableMetaData object.

String [] index_names = table.getTableMetaData().getIndexNames();

Be aware that an index is not required to have a name. In that case, you should use the index number instead. Index numbers are zero based.

Insert, Update, or Delete Rows

►

To insert a new row

First create a new Row object.

Row row = rowset.createRow();

Set the column data for the row.

// Set column 0 data

row.setString(0, “Column0String”);

// Set column 1 data

row.setInt(1, 45);

// Update column 2 data

row.setDouble(2, 99.99);

Now insert the row.

rowset.insertRow(row);

This newly inserted row becomes the current row of the row set. If you do not want the row set’s currency to be changed by the insertion, you can use the overloaded version of “insertRow” that takes a boolean argument indicating no currency change(NCC).

rowset.insertRow(row, true);

The row set’s current row will be unchanged.

►

To update a row/record

First retrieve the row by calling one of the row set’s retrieval methods, “getNext,” “getByIndex,” and so forth.

Make the changes to the column data for the row.

// Update column 1

row.setInt(1, 45);

Now update the row.

rowset.updateRow(row);

Like “insertRow,” the newly updated row becomes the current row of the row set unless “no currency changed” is indicated, as shown following.

rowset.updateRow(row, true);

►

To delete a row/record

First retrieve the row by calling one of the row set’s retrieval methods, “getNext,” “getByIndex,” and so forth.

The row to be deleted does not have to be the “current” row i.e. the row returned from the last retrieval operation. If it is not the current row, the “deleteRow” method will make the deleted row the “current” row before deleting it. After the deletion, a call to “getNext” will return the row following the deleted row.

Then delete the row.

rowset.deleteRow(row);

Combine Operations into a Transaction

Transactions allow you to combine a series of operations into a single operation that will either be committed or aborted.

►

To make a set of operations a transaction

You can make a set of operations a transaction by using the transaction methods of the Session class.

Here is an example of a transaction:

Start the transaction

try {

session.startTransaction(BTR_CONCURRENT_TRANS);

Perform some operations that you want to rollback if a failure occurs.

// insert one or more operations here

Attempt to commit the transaction.

session.commitTransaction(); }

catch(PsqlException ex)

If an error is detected, abort the transaction.

{ // An error occurred.

session.abortTransaction(); }

Transactions can be “exclusive” or “concurrent.” See the PSQL Programmer's Guide for more information about transactions.

Store and Retrieve Binary Large Objects

The setObject() and getObject() methods can be used to store and retrieve Java objects that implement the java.io.Serializable interface. The setBinaryStream() and getBinaryStream() methods can be used to store and retrieve binary data using Java InputStreams. If we have a "simple" object Employees with the following interface:

public class Employee implements java.io.Serializable

public int getID(); //Gets the Employee ID

public void setID(int ID); //Sets the Employee ID

public String getName(); //Gets the Employee Name

public void setName(String name); //Sets Employee Name

public String getManagerName(); //Gets the Manager Name

public void setManagerName(); //Sets the Manager Name

...and we have a file that we wish to read at C:\Employees\Java Duke\report.txt, we can instantiate an Employee object, set its state with the mutator methods, and store it into the database in the Employee_Data column as well as storing the file in the Manager_Report column:

// Already performed usual setup

// (Driver.establishSession, and so forth)

// and instantiated an Employee object

// named employeeObject.

employeeObject.setName("Java Duke");

employeeObject.setID(123456789);

employeeObject.setManagerName("Big Boss");

FileInputStream managerReport = null;

try

managerReport = new FileInputStream(C:\Employees\Java Duke\report.txt);

catch(IOException ioe)

//Handle the exception.

//Set the column values for the row,

// assuming that a RowSet object

//(rowset) has already been created.

Row employeeRow = rowset.createRow();

//Set the ID column of the database.

employeeRow.setInt("ID", employeeObject.getID());

//Set the Employee object into the row.

try

employeeRow.setObject("Employee_Data", employeeObject);

catch(PsqlIOException pioe)

//Handle the exception.

//Set the manager's report into the row.

employeeRow.setBinaryStream("Manager_Report", managerReport);

//Insert the row.

rowset.insertRow(employeeRow);

//Now we can retrieve this row from the database

RowSetMetaData rsmd = rowset.getRowSetMetaData();

ColumnDef cdef = rsmd.getColumnDef("ID");

rsmd.addFirstTerm(cdef, Const.BTR_EQ, "123456789");

Row rowRetrieved = rowset.getNext();

//After the row is retrieved, we can

// perform the getObject and

// getBinaryStream methods on the row in

// order to retrieve the desired data.

try

Employee employeeRetrieved =

(Employee)rowRetrieved.getObject("Employee_Data");

catch(PsqlException pe) //This method throws both

// PsqlIOException

{ //and PsqlClassNotFoundException

//Handle the exception.