Performing Basic Tasks with ADS and Java

This section describes some of the more common tasks that you can perform with Java and the Advantage JDBC Driver. These include connecting to a data dictionary, opening a table, executing a query, using a parameterized query, and executing a stored procedure.

Connecting to Data

You connect to a data dictionary or to a directory in which free tables are located by calling the getConnection method of the DriverManager. The getConnection method takes a connection string, which must be prefaced by the driver manager class that you want to get the connection for. For a connection to ADS, this prefix is jdbc:extendedsystems:advantage:.

Prior to calling getConnection, you must have instantiated the Advantage JDBC Driver. This is done by calling the forClass method of the Class class, passing the name of the Advantage JDBC Driver as an argument.

Because numerous event handlers associated with the MainFrame class use this connection, a variable of type Connection (a JDBC class) is declared in the MainFrame’s class declaration, which places this variable in scope of all event handlers that need it. This variable declaration, as well as several additional JDBC class variables that are used in two or more event handlers in this project, are shown here:

public class MainFrame extends JFrame {   Connection conn;   Statement stmt;   PreparedStatement prepStmt; //additional declarations

The Connection variable (conn) in the preceding segment is assigned a connection from a private method that is called from the MainFrame class constructor. This method, databaseInit, is shown in the following code segment:

private void databaseInit() throws Exception{   Class.forName("com.extendedsystems.jdbc.advantage.ADSDriver");   conn = DriverManager.getConnection("jdbc:extendedsystems:" +   "advantage://server:6262/share/program files/" +   "extended systems/advantage/adsbook/"+   "demodictionary.add;user=adsuser;password=password");   stmt = conn.createStatement();   prepStmt = conn.prepareStatement("SELECT * FROM INVOICE "+   "WHERE [customer id] = ?" ); }

As you can see from the preceding method, forName is passed the name of the class of the Advantage JDBC Driver, which instantiates the driver. When the getConnection method of the DriverManager is called, it locates the instantiated driver by means of the prefix in the connection string.

In addition to containing the prefix for the Advantage JDBC Driver, this connection includes a URL (uniform resource locator) that points to the TCP/IP port on the machine named server where the data is located. This URL also includes an optional data location, identified by a share on that server (named share in this instance), and a qualified path to the data dictionary. Two additional parameters, the user name and password, are passed in this connection string as well.

Note

If an exception is raised when you attempt to connect, verify that your URL is correct and try again. You should also ensure that all clients on the same machine use a remote connection (since the Java driver only uses remote).

Because this connection string refers to the DemoDictionary data dictionary, and this dictionary requires logins, this particular connection string contains all of the essential parameters needed to connect to this database. Additional parameters could have been passed in this connection string as name/value pairs, where an equal sign separates the name and value. As you can see in the preceding connection string, when the connection string contains two or more name/value pairs, semicolons separate them. The full list of the optional connection string parameters is shown in Table 13-1.

Table 13-1: The Optional Connection String Parameters

Parameter	Description
Catalog	If the data directory or data dictionary path is not provided in the connection URL, set it to the qualified name of the data dictionary or the file location on the specified server where the free tables are located.
CharType	Identifies the character set used by the server. Can be set to ansi or oem. The default is ansi.
LockType	Identifies the type of locking to be used by ADS. Can be set to compatibility or proprietary. The default is proprietary.
Password	If the data dictionary requires logins, use this parameter to submit the user's password.
ShowDeleted	Set to true to include deleted records in DBF files, or set to false to suppress deleted records. The default is false.
TableType	Used to identify the type of table when connecting to free tables. This parameter can be set to adt, cdx, or ntx. The default is adt. This property is not used when you connect to a data dictionary.
User	If the data dictionary requires login, use this parameter to submit the user's user name.

Executing a Query

You can execute a query against ADS by calling any one of a number of methods of a java.sql.Statement instance, including execute, executeQuery, and executeUpdate. The execute method returns True if the statement returns a result, False if it does not, and throws an exception if the statement fails. The execute method is best when you do not know ahead of time if the statement returns a result set. Call executeQuery when you know that a result set will be returned, and executeUpdate when you know that one will not be returned.

The following event handler demonstrates the execution of a query that returns a result set. This event handler is associated with the Execute SELECT button (shown in Figure 13-1):

void executeSelect_actionPerformed(ActionEvent e) {   try {   ResultSet rs = stmt.executeQuery(selectText.getText());   if (isRSEmpty(rs)) {   JOptionPane.showMessageDialog(this,   "No records in result set");   return;   }   jTable1.setModel(new ResultTableModel(rs));   }   catch (Exception e1) {   System.err.println( e1.getMessage());   } }

Since this is the first event handler from this project that we’ve inspected, there are two characteristics that need to be introduced—specifically, the isRSEmpty method and the use of the ResultTableModel class. Both of these are declared in the MainFrame.java file.

The isRSEmpty method is called by many of the event handlers in this application to determine whether or not there are records in the ResultSet returned by executeQuery. This method was added to the MainFrame class declaration as a public static method. The following is the implementation of this method:

public static boolean isRSEmpty(ResultSet rs) {   try {   return ! rs.first();   }   catch (Exception e1) {   System.err.println( e1.getMessage());   return false;   } } 

The second item of interest is the class ResultTableModel. This class extends the abstract class AbtractTableModel, and it is used to create a model that can be used by the JTable class to display the contents of the result set. (Java swing classes employ a model-view architecture. The view is supplied by the visual component, and the model is responsible for handling the data.) At a minimum, ResultTableModel must override getColumnCount, getRowCount, and getValueAt. In this case, getColumName is also overridden.

The following code implements the ResultTableModel class:

class ResultTableModel   extends javax.swing.table.AbstractTableModel {   Object obj [] [];   int rows, columns;   ResultSetMetaData rsMeta;   public ResultTableModel (ResultSet rs) {   try {   if (rs == null) {   rows = 0;   columns = 0;   obj = new Object[0][0];   return;   }   rsMeta = rs.getMetaData();   //get column count   columns = rsMeta.getColumnCount();   //calculate number of rows   rows = 0;   rs.first();   do {   rows++;   } while (rs.next());   //set array dimension   obj = new Object [rows][columns];   //load data   rs.first();   rows = 0;   do {   for (int j = 0; j <= (columns-1); j++) {   obj[rows][j] = rs.getString(j+1);   }   rows++;   } while (rs.next());   } catch (Exception e1) {   System.out.println(e1.getMessage());   }   }   public int getColumnCount() {   return columns;   }   public int getRowCount() {   return rows;   }   public String getColumnName(int col)  {   String res = "";   if (rsMeta == null) {   return res;   }   try {   res = rsMeta.getColumnName(col+1);   }   catch (Exception e1) {   System.out.println(e1.getMessage());   }   return res;   }   public Object getValueAt(int row, int col) {   return obj[row][col];   } } //ResultTableModel class

As you can see in this code, the constructor of ResultTableModel is passed the ResultSet. This ResultSet is used to obtain a ResultSetMetaData object, which is then used to determine the number of columns in the ResultSet. This ResultSetMetaData object is also used to obtain the column names from within the getColumnName method.

Next, the ResultSet is navigated in order to count how many records the ResultSet contains. Finally, a two-dimensional array of Object is declared and populated with the rows and columns of the ResultSet.

Admittedly, this code is somewhat inefficient, in that it necessitates the retrieval of all of the records in the ResultSet, which is a time-consuming task when many records are involved. Consequently, this is not the type of TableModel that would be appropriate for every application. But for this sample Java project, it works just fine.

ResultTableModel is used to populate the JTable instance, a grid control, that appears in the JFrame. Figure 13-2 shows this JTable populated with the results of a SQL SELECT statement.

Figure 13-2: The JTable obtains its data from ResultTableModel.

Using a Parameterized Query

Instead of using a Statement object, you use a PreparedStatement object when you need to execute a parameterized query. You can create a PreparedStatement object by calling the prepareStatement method of a Connection object, passing the parameterized query as an argument.

Before executing the PreparedStatement, you must call one of its setter methods for each parameter in the query. Which setter method you call depends on the data type of the parameter. If the parameter is a String, you call setString. On the other hand, if the parameter is an Integer, you call setInt.

The PreparedStatement was created in the databaseInit method shown earlier in this chapter. Data is bound to the single parameter, and the query is executed, from the following event handler, which is associated with the Show Invoices button (shown in Figure 13-1):

void showInvoiceBtn_actionPerformed(ActionEvent e) {   try{   prepStmt.setInt( 1, Integer.parseInt(paramText.getText()));   ResultSet rs = prepStmt.executeQuery();   if (isRSEmpty(rs)) {   JOptionPane.showMessageDialog(this,   "No records in result set");   return;   }   jTable1.setModel(new ResultTableModel(rs));   }   catch (Exception e1) {   System.err.println( e1.getMessage());   } }

Reading and Writing Data

You access individual columns in a ResultSet by calling one of its getter methods. All ResultSet getter methods are overloaded. You can identify a column either by ordinal position or by name.

Which getter method you call depends on the data type of the column you are reading. For example, you call getString in order to read a column containing text, and getBoolean to read a logical column.

If the result set is based on a live (dynamic) cursor, you can change its data and apply the change to the underlying ADS table. You write to a column of a ResultSet by calling one of its setter methods. Like getter methods, ResultSet setter methods are overloaded, taking either the ordinal position of a field or the field name, in addition to the value you are writing to the field.

Once you have written to one or more fields of an updatable ResultSet record, you apply the changes to the underlying table by calling the ResultSet’s updateRow method.

The following event handler, associated with the Get Address button (shown in Figure 13-1), demonstrates how to read a field from a ResultSet:

void getAddressBtn_actionPerformed(ActionEvent e) {   PreparedStatement getCustStmt;   if (custNoText.getText() == "") {   System.out.println("Enter a customer ID");   return;   }   try {   getCustStmt = conn.prepareStatement(   "SELECT * FROM CUSTOMER WHERE [customer id] = ?" );   getCustStmt.setInt( 1,   Integer.parseInt(custNoText.getText()));   ResultSet rs = getCustStmt.executeQuery();   if (isRSEmpty(rs)) {   JOptionPane.showMessageDialog(this,   "No records in result set");   jTable1.setModel(new ResultTableModel(null));   return;   }   oldAddressText.setText(rs.getString("Address"));   jTable1.setModel(new ResultTableModel(rs));   }   catch (Exception e1) {   System.err.println( e1.getMessage());   } } 

The next event handler, associated with the Set New Address button (shown in Figure 13-1), demonstrates writing to a field and saving the change to ADS:

void setAddressBtn_actionPerformed(ActionEvent e) {   PreparedStatement getCustStmt;   if (custNoText.getText() == "") {   System.out.println("Enter a customer ID");   return;   }   try {   getCustStmt = conn.prepareStatement(   "SELECT * FROM CUSTOMER WHERE [customer id] = ?" );   getCustStmt.setInt( 1,   Integer.parseInt(custNoText.getText()));   ResultSet rs = getCustStmt.executeQuery();   if (isRSEmpty(rs)) {   JOptionPane.showMessageDialog(this,   "No records in result set");   return;   }   rs.updateString("Address", newAddressText.getText());   rs.updateRow();   }   catch (Exception e1) {   System.err.println( e1.getMessage());   } } 

Calling a Stored Procedure

Calling a stored procedure is no different than executing any other query. If your stored procedure does not require input parameters, you use a Statement instance. You use a PreparedStatement instance if there are one or more input parameters. If the stored procedure returns one or more records, you invoke the executeQuery method of the Statement or PreparedStatement object, and the execute or executeUpdate methods when the stored procedure does not return records.

Invoking a stored procedure that takes one input parameter is demonstrated by the following code associated with the actionPerformed event handler for the Show 10% of Invoices button (shown in Figure 13-1). The stored procedure referenced in this code is the Delphi AEP (Advantage Extended Procedure) created in Chapter 7. If you did not create this AEP, but created one of the other AEPs described in that chapter, substitute the name of the stored procedure object in your data dictionary in the EXECUTE PROCEDURE string, like this:

void callStoredProcBtn_actionPerformed(ActionEvent e) {   PreparedStatement getCustStmt;   if (custNoText.getText() == "") {   System.out.println("Enter a customer ID");   return;   }   try {   getCustStmt = conn.prepareStatement(   "EXECUTE PROCEDURE DelphiAEP( ? )" );   getCustStmt.setInt( 1,   Integer.parseInt(paramText.getText()));   ResultSet rs = getCustStmt.executeQuery();   if (isRSEmpty(rs)) {   jTable1.setModel(new ResultTableModel(null));   JOptionPane.showMessageDialog(this,   "No records in result set");   return;   }   jTable1.setModel(new ResultTableModel(rs));   }   catch (Exception e1) {   JOptionPane.showMessageDialog(this,   e1.getMessage());   } }