As a subclass of the JDBCService class, the JDBCTable class has an external definition that allows any attribute of the JDBCTable object to be defined in the services definition file. All attributes of the JDBCTable class are defined externally, including those inherited from the JDBCService class, which in turn inherits from the Service class. A JDBCTable object is usually instantiated by providing the name of the service to the ServiceExternalizer. The ServiceExternalizer creates an instance of the class, identified by the name (in this case, a JDBCTable), and sets its attributes to the values read from the toolkit services definition file.
After instantiation of a JDBCTable service, a name and a tableName (which includes the schema name and the catalog name, if necessary) are assigned to the JDBCTable instance. The autoCommit attribute (set to false by default), the databaseURL attribute, and the autoConnect attribute are also set. The following connection attributes are also set: poolName, dataSourceName, JDBCDriver, and sharedConnection.
Before an application can work with the JDBCTable service, a connection to the database must be created. The connection is stored in the JDBCTable attribute databaseConnection. When access to the database table is requested by the application from the JDBCTable service, the service checks if a connection to the database already exists. If no connection has been previously established and the autoConnect attribute of the JDBCTable instance has been set to false, an exception is thrown. If the autoConnect attribute has been set to true, then a new connection to the database (located at the databaseURL attribute value of the JDBCTable instance) is automatically created.
If no automatic connection to the database is requested from the JDBCTable service, then the connection can be created in either of two ways:
▪ By calling one of the JDBCTable instance connect methods, as described in the Database Services documentation. This is the recommended way.
▪ By using the standard JDBC DriverManager interface to create the connection, and setting the databaseConnection attribute to the returned Connection instance. If this way is used, the application will not be able to use connection pooling or share the database connection between different database services instances. It is always suggested to use the service interface.
By default, a connection created by the JDBCTable instance connect method has its autoCommit attribute set to false, so that the application must commit or roll back the database transactions (using the provided commit() and rollback() methods). If this attribute is set to true, will be automatically done by the JDBCTable service, and each transaction will consist of one operation.
The following table describes the JDBCTable service behavior according to the attribute settings.
JDBCTable attribute settings
autoConnect
autoCommit
Service behavior
false
false
The application must request a database connection by calling the connect method, take care of committing and rolling back transactions, and explicitly close the connection by calling the disconnect() method.
false
true
The application must request a database connection by calling the connect() method and close the connection by calling the disconnect() method, but any changes to the database will be automatically committed or rolled back each time a database operation is requested.
true
false
The service definition must include the information needed to set the database connection automatically when the first operation on the database is requested (databaseURL and, optionally, userid, password, connection pooling information, and connection alias). The connection is created, and the databaseConnection attribute is set with this information. The application must commit and roll back all the changes made to the database through this connection, and when done must close the connection by calling the disconnect() method.
true
true
The service definition must include the information needed to set the database connection automatically when the first operation to the database is requested (databaseURL and, optionally, userid, password, connection pooling information, and connection alias). The connection is created, and the databaseConnection attribute is set with this information. In addition, after any operation, the changes are committed or rolled back automatically by the service, and the connection is closed. The application just must call the appropriate database access methods. In this environment, the JDBC drivers are expected to be loaded before any database operation is attempted.
Note This service is working with existing database tables, so the application must set the primary key column value for any record to be inserted in the table, define this column as part of the JDBCTable service format definition, and add the name mapping between the data field that will contain this primary key and the table column into the service definition if this is needed.
To add a record, the application can use the method addRecord(), which is defined in the TableService interface. The format of a record is defined at customization time and is implementation-dependent. The toolkit formatting facilities can be used, so the record can be built from different data fields found anywhere in the operation context. This is done using a Hashtable Format, which describes which data fields must be stored and how they have to be formatted before constructing the table record. A Hashtable instance is obtained by calling the format(Context aContext) method for the specific format element object.
The TableService interface, which is implemented by the JDBCTable class, accepts formatted data, a context and a formatter name, or a context and a formatter, as addRecord() method arguments. (When a formatter or formatter name is passed, the service itself calls the format() method of the formatter object to build the data into the JDBCTable table record.)
The TableService interface provides the method retrieveRecordsMatching, which takes a single argument: a string containing the search conditions used to build the SQL statement. This method returns a Vector with as many Hashtable instances as there are records matching the search criteria. Any of these hash tables can then be separately managed by the application by calling the unformat(Hashtable aDataHashtable, Context aContext) method on the formatter object that will automatically update the data fields in the operation context. The stage needs to be set just so.
The method retrieveRecordsMatching() automatically unformats the retrieved records in the operation context. This method takes three arguments: the search condition string, the context instance, and either the output formatter instance or the output formatter name.
Each of the above retrieval methods returns, by default, every table column for each returned record. If fewer columns are required, an additional argument is used: a Vector with elements containing the names of the columns to be retrieved. The order in which the column names are entered into the Vector is not significant.
The method updateRecordsMatching() is used to update records in the database according to a search criterion, and takes a search string and formatted data from the context. The data from the context may be either a formatted record, or a context together with a formatter name, or a context together with a formatter. In the last two cases, the service itself calls the format() method of the formatter object to build the new JDBCTable table record.
Each of the update methods, by default, updates every column in the table for every record matching the search criteria. If fewer columns are to be updated, an additional argument is used: a Vector with elements containing the names of the columns to be updated.
Each update method returns an int value containing the number of records updated as a result of the statement execution.
There are two methods for executing an SQL statement: executeSQLQuery() and executeSQLUpdate().
▪ The executeSQLQuery() method executes an SQL statement that returns a single result set, and also updates the operation context with the result set. It takes the literal SQL statement, an output formatter name, and a context instance as its arguments.
▪ The executeSQLUpdate() method executes an SQL INSERT, UPDATE, or DELETE statement. It takes a literal SQL statement and returns the row count (the number of affected rows).
The JDBCServicesAdministrator provides the static methods registerProcedure() and dropProcedure() to register a new stored procedure in the database and to drop a stored procedure from the database.
The registerProcedure() method takes a single argument: a string with the SQL definition of the stored procedure (see the CREATE PROCEDURE statement in the SQL Reference). Because of a JDBC interface restriction, the JDBCTables service is not able to distinguish between stored procedures registered with the same name but with different parameters. Therefore, if the application tries to reuse an existing stored procedure name to create a new stored procedure, this method will throw a DSEInvalidRequestException even if the parameter list is different from the existing stored procedure.
The dropProcedure() method takes a single argument: the stored procedure name. If the stored procedure does not exist in the database, the method will throw a DSEInvalidRequestException.
The method executeProcedure() is used to execute a database-defined stored procedure. This method takes the name of the stored procedure as its argument.
If the stored procedure has only input parameters, this method also takes either the formatted input parameters, or a context instance together with a formatter name, or a context instance together with a formatter instance. In the latter two cases, the service itself calls the format() method of the formatter object to build the input parameters that will be passed as arguments of the stored procedure.
If the stored procedure has output parameters, the executeProcedure() method will take as arguments (together with those previously described arguments) either the output formatter name or the output formatter instance (the formatter being defined in the toolkit formats definition file).
If the stored procedure has not been previously registered in the database, the executeProcedure() method will throw a DSESQLException.