Corporate Info | News | Solutions | Products | Partners | Services | Events | Download | How To Buy

# Using dbKona I. Introduction Overview of dbKona dbKona in a multitier configuration How dbKona and a JDBC driver interact How dbKona and WebLogic Events can interact dbKona architecture II. The dbKona API dbKona API reference dbKona objects and their classes Data container objects in dbKona Data description objects in dbKona Miscellaneous objects Entity relationships III. Implementing with dbKona Accessing a DBMS with dbKona Preparing a query, retrieving, and displaying data Using a SelectStmt object to form a query Modifying DBMS data with a SQL statement Modifying DBMS data with a KeyDef Using a JDBC PreparedStatement with dbKona Using stored procedures with dbKona Using byte arrays for images and audio Using dbKona for Oracle sequences Other related documents Installing WebLogic (non-Windows) Installing WebLogic (Windows) WebLogic JDBC Options Using WebLogic JDBC Using WebLogic Events An overview of dbKona architecture More code examples Simplifying JDBC programming with dbKona Examples for dbKona Glossary # I. Introduction ## Overview of dbKona The dbKona classes provide a set of high-level database connectivity objects that give Java applications and applets access to databases. dbKona sits on top of the JDBC API and works with the WebLogic JDBC drivers, or with any other JDBC-compliant driver. dbKona provides a higher level of abstraction than JDBC, which deals with low-level details of managing data. dbKona offers objects that allow the programmer to view and modify database data in a high-level, vendor-independent way. A Java application that uses dbKona objects does not need vendor-specific knowledge about DBMS table structure or field types to retrieve, insert, modify, delete, or otherwise use data from a database. ### dbKona in a multitier configuration dbKona may also be used in a multitier JDBC implementation consisting of WebLogic Server and a multitier driver; this configuration requires no client-side libraries. In a multitier configuration, WebLogic JDBC acts as an access method to the WebLogic multitier framework. WebLogic uses a single JDBC driver, for example, WebLogic jDriver for Oracle, to communicate from the WebLogic Server to the DBMS. For more information, see WebLogic JDBC Options. dbKona is a natural choice for writing database access programs in a multitier environment, since with its objects you may write database applications that are completely vendor independent. dbKona and WebLogic's multitier framework is particularly suited for applications that want to retrieve data from several heterogeneous databases for transparent presentation to the user. For more information on WebLogic and the WebLogic JDBC Server, check the Using WebLogic JDBC Developers Guide. ### How dbKona and a JDBC driver interact dbKona depends upon a JDBC driver to provide and maintain a connection to a DBMS. In order to use dbKona, you must have installed a JDBC driver. If you are using the WebLogic jDriver for Oracle native JDBC driver, you should install the appropriate the WebLogic-supplied .dll, .sl, or .so for your operating system, as described in Installing WebLogic jDriver for Oracle. If you are using a non-WebLogic JDBC driver, you should refer to the documentation for the JDBC driver. JavaSoft's JDBC is a set of interfaces that BEA has implemented to create its jDriver JDBC drivers. BEA's JDBC drivers are JDBC implementations of database-specific drivers for Oracle, Informix, and SQLServer. Using database-specific drivers with dbKona offers the programmer access to all of the functionality of each specific database, as well as improved performance. Although the underlying foundation of dbKona uses JDBC for database transactions, dbKona provides the programmer with higher-level, more convenient access to the database. ### How dbKona and WebLogic Events can interact The dbKona package contains some "eventful" classes that send and receive events (within WebLogic, using WebLogic events) when data is updated locally or in the DBMS. Check the EventfulTableDataSet examples in the weblogic/examples directory in the distribution. ## dbKona architecture dbKona uses a high level of abstraction to describe and manipulate data that resides in a database. Classes in dbKona create and manage objects that retrieve and modify data. An application can use dbKona objects in a consistent way without any knowledge of how a particular vendor stores or processes data. At the core of dbKona's architecture is the concept of a DataSet. A DataSet contains the results of a query. DataSets allow client-side management of query results. The programmer can control the entire query result rather than dealing with a single record at a time. A DataSet contains Records, and each Record contains one or more Value objects. A Record is comparable to a database row, and a Value can be compared to a database cell. Value objects "know" their internal data type as stored in the DBMS, but the programmer can treat Value objects in a consistent way without having to worry about vendor-specific internal data types. Methods from the DataSet class (and its subclasses TableDataSet and QueryDataSet) provide a high-level, flexible way to navigate through and manipulate the results of a query. Changes made to a TableDataSet can be saved to the DBMS; dbKona maintains knowledge of which records have changed and makes a selective save, which reduces network traffic and DBMS overhead. dbKona also uses other objects, like SelectStmt and KeyDef to shield the programmer from vendor-specific SQL. By using methods in these class, the programmer can have dbKona construct the appropriate SQL, which reduces syntax errors and does not require a knowledge of vendor-specific SQL. On the other hand, dbKona also allows the programmer to pass SQL to the DBMS if desired. # II. The dbKona API ## dbKona API reference Package weblogic.db.jdbc Package weblogic.db.jdbc.oracle (Oracle-specific extensions)
    Class java.lang.Object
      Class weblogic.db.jdbc.Column
       (implements weblogic.common.internal.Serializable)
      Class weblogic.db.jdbc.DataSet
       (implements weblogic.common.internal.Serializable)
        Class weblogic.db.jdbc.QueryDataSet
        Class weblogic.db.jdbc.TableDataSet
           Class weblogic.db.jdbc.EventfulTableDataSet
            (implements weblogic.event.actions.ActionDef)
        Class weblogic.db.jdbc.Enums
        Class weblogic.db.jdbc.KeyDef
        Class weblogic.db.jdbc.Record
           Class weblogic.db.jdbc.EventfulRecord
            (implements weblogic.common.internal.Serializable)
        Class weblogic.db.jdbc.Schema
         (implements weblogic.common.internal.Serializable)
        Class weblogic.db.jdbc.SelectStmt
        Class weblogic.db.jdbc.oracle.Sequence
        Class java.lang.Throwable
           Class java.lang.Exception
               Class weblogic.db.jdbc.DataSetException

        Class weblogic.db.jdbc.Value
## dbKona objects and their classes Objects in dbKona fall into three categories: Data container objects hold data retrieved from or bound for a database, or they contain other objects that hold data. Data container objects are always associated with a set of data description objects and a set of session objects. TableDataSet and Record objects are examples of data container objects. Data description objects contain the metadata about data objects, that is, a description of how the data is structured and typed, and parameters for its retrieval from the remote DBMS. Every data object or its container is associated with a set of data description objects. Schema and SelectStmt objects are examples data description objects. Miscellaneous objects store information about errors, provide symbolic constants, etc. These broad categories of objects depend upon each other in application building. In a general way, every data object has a set of descriptive objects associated with it. ### Data container objects in dbKona There are three basic objects that act as data containers: a DataSet (or one of its subclasses, QueryDataSet or TableDataSet) contains Records. A Record contains Values. DataSet QueryDataset TableDataSet EventfulTableDataSet Record Value ### DataSet The general container for data objects in dbKona is the DataSet. dbKona uses the concept of a DataSet to cache records retrieved from a DBMS server. It is roughly equivalent to a table in SQL. The DataSet class has two subclasses, QueryDataSet and TableDataSet. In the multitier model using the WebLogic JDBC Server, DataSets can be saved (cached) on the WebLogic Server. A T3Client can connect to a WebLogic JDBC Server, create and cache a DataSet, disconnect, and connect again and use the same DataSet. A DataSet is constructed as a QueryDataSet or a TableDataSet to hold the results of a query or a stored procedure. A DataSet's retrieval parameters are defined by a SQL statement, or by the dbKona abstraction for SQL statements, a SelectStmt object. A Dataset is populated with Records, which contain Values. Records are accessible by index position (0-origined). A DataSet is described by and bound to a Schema, which stores information its attributes, like column name, data type, size, and order of each database column represented in the DataSet. Column names in a Schema are accessible by index position (1-origined). The DataSet class (see weblogic.db.jdbc.DataSet) is the abstract parent class for QueryDataSet and TableDataSet. ### QueryDataSet A QueryDataSet is a type of DataSet that holds the results of an arbitrary SQL query. The SQL parameters are run against the DBMS when the application asks for the first record in the QueryDataSet. A QueryDataSet makes the results of an SQL query available as a collection of Records that are accessible by index position (0-origined). Unlike the case with a TableDataSet, changes and additions to a QueryDataSet cannot be saved into the database. There are two functional differences between a QueryDataSet and a TableDataSet: First, changes made to a TableDataSet can be saved to a database; you can make changes to Records in a QueryDataSet, but those changes cannot be saved. Second, you can retrieve data into a QueryDataSet from more than one table. A QueryDataSet is constructed in the context of a java.sql.Connection or with a java.sql.ResultSet; that is, you pass the Connection object as an argument to the QueryDataSet constructor. A QueryDataSet's data retrieval is specified by a SQL query and/or by a SelectStmt object. A QueryDataSet is populated with Records (accessible by 0-origined index), which contain Values (accessible by 1-origined index). A QueryDataSet is described by a Schema, which stores information about the QueryDataSet's attributes. Attributes include name, data type, size, and order of each database column represented in the QueryDataSet. The QueryDataSet class (see weblogic.db.jdbc.QueryDataSet) has methods for constructing, saving, and retrieving a QueryDataSet. You can specify any SQL for a QueryDataSet, including SQL for joins. The superclass DataSet contains methods for managing record caching details. ### TableDataSet A TableDataSet is a type of DataSet that holds the results of a SQL query on a single table in a database. The results of the query are available as a collection of Records accessible by an integer index. The SQL parameters are run against the DBMS session when the application asks for the first record in the TableDataSet. The functional difference between a TableDataSet and a QueryDataSet is that changes made to a TableDataSet can be saved to a database. With a TableDataSet, you can update values in Records, add new Records, and mark Records for deletion; finally, you can save changes to a database, using the save() methods in either the TableDataSet class to save an entire TableDataSet, or in the Record class to save a single record. Additionally, the data retrieved into a TableDataSet is, by definition, from a single database table; you cannot perform joins on database tables to retrieve data for a TableDataSet. If you intend to save updates or deletes to a database, you must construct the TableDataSet with a KeyDef object that specifies a unique key for forming the WHERE clauses in an UPDATE or DELETE statement. A KeyDef is not necessary if only inserts take place, since an insert operation does not require a WHERE clause. The KeyDef key must not contain columns that are filled or altered by the DBMS, since dbKona must have a known value for the key column to construct a correct WHERE clause. You can also qualify a TableDataSet with an arbitrary string that is used to construct the tail of the SQL statement. When you are using dbKona with an Oracle database, for example, you can qualify the TableDataSet with the string "for UPDATE" to place a lock on the records that are retrieved by the query. A TableDataSet can be constructed with a KeyDef, a dbKona object used for setting a unique key for saving updates and deletes to the DBMS. If you are working with an Oracle database, you can set the TableDataSet's KeyDef to "ROWID," which is a unique key inherent in each table. Then construct the TableDataSet with a set of attributes that includes "ROWID." A TableDataSet is constructed in the context of a java.sql.Connection object; that is, you pass the Connection object as an argument to the TableDataSet constructor. Its data retrieval is specified by the name of a DBMS table. If you intend to save updates and deletes, you must supply a KeyDef object when the TableDataSet is constructed. You may refine a query with the where() and order() methods to set WHERE and ORDER BY clauses after the TableDataSet is created. A TableDataSet has a default SelectStmt object associated with it that can be used to take advantage of Query-by-example functionality. A TableDataSet is populated with Records (accessible by 0-origined index), which contain Values (accessible by 1-origined index). A TableDataSet's attributes are described by a Schema, which stores information about the TableDataSet's attributes, like column name, data type, size, and order of the database columns represented in the TableDataSet. TableDataSets can be cached on a WebLogic JDBC Server and used by a T3Client over multiple logins to the Server. The setRefreshOnSave() method sets the TableDataSet so that any record inserted or updated during a save is also immediately refreshed from the DBMS. Set this flag if your TableDataSet has columns altered by the DBMS, such as the Microsoft SQL Server IDENTITY column or a column modified by an insert or update trigger. The Refresh() methods refresh records in the TableDataSet that would be saved in the database, that is, records that you have changed in the TableDataSet. Any changes you have made to a record are lost and the record is marked clean. Records you have marked for delete are not refreshed. A record you have added to the TableDataSet raises an exception stating that there is no DBMS representation of the row from which to refresh. The saveWithoutStatusUpdate() methods save TableDataSet records to the DBMS without updating the save status of the records in the TableDataSet. Use these methods to save TableDataSet records within a transaction. If the transaction is rolled back, the records in the TableDataSet are consistent with the database and the transaction can be retried. After the transaction is committed, call updateStatus() to update the save status of records in the TableDataSet. Once you have saved a record with saveWithoutStatusUpdate(), you cannot modify it until you call updateStatus() on the record. The TableDataSet.setOptimisticLockingCol() method allows you to designate a single column in the TableDataSet as an optimistic locking column. Applications use this column to detect whether another user has changed the row since it was read from the database. dbKona assumes the DBMS updates the column whenever the row is changed, so it does not update this column from the value in the TableDataSet. It uses the column in the WHERE clause of an UPDATE statement when you save the record or the TableDataSet. If another user has modified the record, dbKona's update fails; you can retrieve the new values for the record using Record.refresh(), make your changes to the record, and try to save the record again. The TableDataSet class (see weblogic.db.jdbc.TableDataSet) has methods for: Constructing a TableDataSet Setting its WHERE and ORDER BY clauses Getting its KeyDef Getting its associated JDBC ResultSet Getting its SelectStmt Getting its associated DBMS table name Saving its changes to a database Refreshing its records from the DBMS Getting other information about it The superclass DataSet contains methods for managing record caching. ### EventfulTableDataSet An EventfulTableDataSet, for use within WebLogic, is a TableDataSet that sends and receives events when its data is updated locally or in the DBMS. EventfulTableDataSet implements `weblogic.event.actions.ActionDef`, which is the interface implemented by all Action classes in WebLogic Events. The action() method of an EventfulTableDataSet updates the DBMS and notifies all other EventfulTableDataSets for the same DBMS table of the change. (You can read more about WebLogic Events in the whitepaper and the Developers Guide for WebLogic Events.) When an EventfulRecord in an EventfulTableDataSet changes, it sends an EventMessage to the WebLogic Server with a ParamSet that contains the row that changed as well as the changed data, for the topic WEBLOGIC.[tablename], where the tablename is the name of the table associated with an EventfulTableDataSet. EventfulTableDataSet takes action on the received, evaluated event to update its own copy of the record that changed. An EventfulTableDataSet is constructed in the context of a java.sql.Connection object, as an argument to the constructor. You must also supply a T3Client object, a KeyDef to be used for inserts, updates, and deletes, and the name of the DBMS table. Like a TableDataSet, an EventfulTableDataSet has a default SelectStmt object associated with it that can be used to take advantage of Query-by-example functionality. An EventfulTableDataSet is populated with EventfulRecords (accessible by a 0-origined index). Like Records, EventfulRecords contain Values (accessible by a 1-origined index). An EventfulTableDataSet's attributes are described by its Schema, in the same way as a TableDataSet. For example, an EventfulTableDataSet might be used by a warehouse inventory system to automagically update many views of a table. Here is how it works. Each warehouse employee's client app creates an EventfulTableDataSet from the "stock" table and displays those records in a Java application. Employees doing different jobs might have different displays, but all of the client applications are using an EventfulTableDataSet of the "stock" table. Because a TableDataSet is "eventful," each record in the data set has registered an interest in itself automatically. The WebLogic Topic Tree has a registration of interest for all the records; for each client, there is a registration of interest in each record in the TableDataSet. When a user changes a record, the DBMS is updated with the new record. At the same time, an EventMessage (embedded with the changed Record itself) is automatically sent to the WebLogic Server. Each client using an EventfulTableDataSet of the "stock" table receives an event notification that has embedded in it the changed Record. The EventfulTableDataSet for each client accepts the changed Record and updates the GUI. ### Record Record objects are the dbKona equivalent of SQL rows. Each Record contains a collection of Values that represent the data (and knowledge about how the data is stored in the DBMS). The Schema object of the parent DataSet describes the name, data type, size, and order of each field represented in a Record. Records are created as part of a DataSet. You can also construct Records manually in the context of a DataSet and its Schema, or the Schema of an SQL table known to an active Database session. Records in a TableDataSet may be saved to the database individually with the save() method in the Record class, or corporately with the save() method in the TableDataSet class. Records are constructed when a DataSet is created and its query is executed. A Record may also be added to an existing DataSet with the DataSet.addRecord() method or with a Record constructor (after the DataSet's fetchRecords() method has been called to get its Schema). A Record contains a collection of Values. Records are accessible by 0-origined index position. Values within a Record are accessible by 1-origined index position. A Record is described by the Schema of its parent DataSet. The Schema associated with a Record holds information about the name, data type, size, and order of each field in the Record. The Record class (see weblogic.db.jdbc.Record) has methods for: Constructing a Record object Determining its parent DataSet and Schema Determining the number of columns in it Determining its save or update status Determining the SQL string used to save or update a Record to the database Getting and setting its Values Returning the value of each of its columns as a formatted string ### Value Value objects correspond to the attributes of a Record object. A Record with n attributes has n Value objects, each accessible by (column) name or (1-origined) index position. There are no public constructors for Values, since they are created as part of a Record. A Value object has an internal type, which is defined by the Schema of its parent DataSet. A Value object can be assigned a value with a data type other than its internal type, if the assignment is legal. A Value object can also return the value of a data type other than its internal data type, if the request is legal. The Value object acts to shield the application from the details of manipulating vendor-specific data types. The Value object "knows" its data type, but all Value objects can be manipulated within a Java application with the same methods, no matter the internal data type. Values are created when Records are created. The internal data type of a Value object may be among the following: Boolean Byte Byte[] Date Double-precision Floating-point Integer Long Numeric Short String Time Timestamp NULL These types are mapped to the JDBC types listed in java.sql.Types. Values are described by the Schema associated with its parent DataSet. The Value class (see weblogic.db.jdbc.Value) has methods for getting and setting the data and data type of a Value object. ### Data description objects in dbKona Data description objects contain metadata; that is, information about data structure, how data are stored on and retrieved from the DBMS, whether and how data can be updated. Some of the data description objects that dbKona uses are implementations of the JDBC interface; a brief description and how to use these is provided here. Schema Column KeyDef SelectStmt ### Schema A Schema object holds metadata, such as the name, data type, size, and order of each field, for each of the attributes associated with a DataSet. A Schema object is a collection of Column objects; each Column holds the metadata for a table attribute. When you instantiate a DataSet, you implicitly create the Schema that describes it, and when you fetch its Records, its Schema is updated. A Schema is constructed automatically when a DataSet is instantiated. A DataSet's attributes (and therefore, attributes of QueryDataSets and TableDataSets, and their associated Records) are defined by a Schema, as are the attributes of a Table. Schema attributes are described as a collection of Column objects. The Schema class (see weblogic.db.jdbc.Schema) has methods for: Adding and returning the Columns associated with the Schema Determining the number of columns in a Schema Determining the (1-origined) index position of a particular column name in the Schema ### Column A Column object describes the individual attributes in a Schema. A Column object is constructed automatically when a Schema is created. The Column class (see weblogic.db.jdbc.Column) has methods for: Setting the Column to a particular data type Determining the data type of a Column Determining the database-specific data type of a Column Determining the name, scale, precision, and storage length of a Column Determining whether NULL values are allowed in the native DBMS column Determining if the Column is read-only and/or searchable ### KeyDef A KeyDef object defines the fields in a TableDataSet that will be used in SQL UPDATE and DELETE operations. It is used to construct the WHERE clause in an SQL update or delete statement, after the pattern "WHERE attribute1 = value1 and attribute2 = value2," and so on, to uniquely identify and manipulate a particular database record. The attributes in a KeyDef should correspond to unique key in the database table. The KeyDef object with no attributes is constructed in the KeyDef class. Use the addAttrib() method to build the attributes of the KeyDef, and then use the KeyDef as an argument in the constructor for a TableDataSet. Once the KeyDef is associated with a DataSet, you can't add any more attributes to it. When you are working with an Oracle database, you can add the attribute "ROWID," which is an inherently unique key associated with each table, to be used for inserts and deletes with a TableDataSet. The KeyDef class (see weblogic.db.jdbc.KeyDef) has methods for: Adding attributes Determining the number of attributes in it Determining if it has an attribute that corresponds to a particular column name or index position. ### SelectStmt A SelectStmt object contains vendor-neutral SELECT statements used for retrieving data from the database. SelectStmt objects provide access to QBE (query-by-example) functionality. A SelectStmt object is constructed in the SelectStmt class. Then add clauses to the SelectStmt with methods in the SelectStmt class, and use the resulting SelectStmt object as an argument when you create a QueryDataSet. A TableDataSet also has a default SelectStmt associated with it that can be used to further refine data retrieval after the TableDataSet has been created. Methods in the SelectStmt class (see weblogic.db.jdbc.SelectStmt) correspond to the clauses in a SQL statement, which include: Field (and an alias) From Group Having Order by Unique Where There is also full support for setting and adding Query-by-example clauses. Note that with the `from()` method, you can specify a string that includes an alias, in the format "tableName alias". With the `field()` method, you can use a string after the format "tableAlias.attribute" as an argument. You are not limited to a single table name when constructing a SelectStmt object, although its usage may dictate whether or not a join is useful. A SelectStmt object associated with a QueryDataSet can join one or more tables, whereas a TableDataSet cannot, since it is by definition limited to the data in a single table. ### Miscellaneous objects in dbKona Other miscellaneous objects in dbKona include Exceptions and Constants. Exceptions Constants ### Exceptions dbKona methods can raise the following Exceptions: DataSetException LicenseException java.sql.SQLException In general, DataSetExceptions occur when there is a problem with a DataSet, including errors generated from stored procedures, or when there is an internal IO error. java.sql.SqlExceptions are thrown when there is a problem building an SQL statement or executing it on the DBMS server. A LicenseException is thrown when license information does not match the information encoded in your weblogic.properties file. ### Constants There are two classes, weblogic.db.jdbc.Enums and java.sql.Types that contain constants (final static ints) used in dbKona. The Enums class contains constants for the following: Trigger states Vendor-specific database types INSERT, UPDATE, and DELETE database operations The java.sql.Types class contains constants for data types. # Entity relationships ### Inheritance relationships The following illustrations show important descendancy relationships between dbKona classes. One class is subclassed: DataSet DataSet is the abstract base class for QueryDataSet and TableDataSet. Other dbKona objects descend from DbObject. Most dbKona Exceptions, including DataSetException and LicenseException, are subclassed from java.lang.Exception and weblogic.db.jdbc.DataSetException. LicenseException is subclassed from RuntimeException. ### Possession relationships Each dbKona object may have other objects associated with it that further define its structure. The following illustrations show these relationships. DataSet A DataSet has Records, each of which has Values. A DataSet has a Schema that defines its structure, which is made up of one or more Columns. A DataSet may have a SelectStmt that sets parameters for data retrieval. TableDataSet A TableDataSet has a KeyDef for updates and deletes by key. Schema A Schema has Columns that define its structure. # III. Implementing with dbKona Outlined below is a set of working examples that illustrate several steps to building a simple Java application that retrieves and displays data from a remote DBMS. Accessing a DBMS with dbKona Step 1. Importing packages Step 2. Setting properties for making a connection Step 3. Making a connection to the DBMS Preparing a query, retrieving, and displaying data Step 1. Setting parameters for data retrieval Step 2. Creating a DataSet for the query results Step 3. Fetching the results Step 4. Examining a TableDataSet's Schema Step 5. Examining the data with htmlKona Step 6. Displaying the data with htmlKona Step 7. Closing the DataSet and the Connection Code summary Using a SelectStmt object to form a query Step 1. Setting SelectStmt parameters Step 2. Using QBE to refine the parameters Modifying DBMS data with a SQL statement Step 1. Writing SQL statements Step 2. Executing each SQL statement Step 3. Displaying the results with htmlKona Code summary Modifying DBMS data with a KeyDef Step 1. Creating a KeyDef and building its attributes Step 2. Creating a TableDataSet with a KeyDef Step 3. Inserting a Record into the TableDataSet Step 4. Updating a Record in the TableDataSet Step 5. Deleting a Record from the TableDataSet Step 6. More on saving the TableDataSet Step 7. Verifying the changes Code summary Using a JDBC PreparedStatement with dbKona Using stored procedures with dbKona Step 1. Creating a stored procedure Step 2. Setting parameters Step 3. Examining the results Using byte arrays for images and audio Step 1. Retrieving and displaying image data Step 2. Inserting an image into a database Using dbKona for Oracle sequences Constructing a dbKona Sequence object Creating and destroying sequences on an Oracle server from dbKona Using a Sequence Code summary ## Accessing a DBMS with dbKona Step 1. Importing packages Step 2. Setting properties for making a connection Step 3. Making a connection to the DBMS ### Step 1. Importing packages To begin, any application that uses dbKona will need access to certain packages, including java.sql (JavaSoft's JDBC package), and the WebLogic dbKona package (weblogic.db.jdbc). You will need to import any other Java classes that you will use; in this case, we also import the Properties class from java.util, which we'll use during the login process, and the weblogic.html package. We'll be using htmlKona for displaying data retrieved from the database.
      import java.sql.*;
      import weblogic.db.jdbc.*;
      import weblogic.html.*;
      import java.util.Properties;
Note that you do not import the package for your JDBC driver. The JDBC driver is established during the connection phase. For version 2.0 and later, you do not import weblogic.db.common, weblogic.db.server, or weblogic.db.t3client. ### Step 2. Setting properties for making a connection The second step in retrieving data with dbKona is to set properties like username, password, and server name that will be used to instantiate a connection to the DBMS. You set these properties using the put() method for a JDBC Properties object, and then you use the Properties object as one of the arguments to the constructor of a JDBC Connection. The JDBC Properties object (java.util.Properties) allows you to set login information like username, password, and server name for connecting to a DBMS. The following code example is a method for creating the Properties object that will be used later in this tutorial to make a connection to an Oracle DBMS. Each property is set with a double-quote-enclosed string.
    public class tutor {

      public static void main(String argv[])
        throws DataSetException, java.sql.SQLException,
        java.io.IOException, ClassNotFoundException
       {
        Properties props = new java.util.Properties();
        props.put("user",      "scott");
        props.put("password",  "tiger");
        props.put("server",    "DEMO");
        (continued below)
The Properties object will be used as an argument to create a Connection. The JDBC Connection object will become an important context for other database operations. ### Step 3. Making a connection to the DBMS The third step in retrieving data with dbKona is to create a JDBC Connection. A Connection object is created by loading the JDBC driver class with the `Class.forName()` method, and then calling the `java.sql.DriverManager.getConnection()` constructor, which takes two arguments, the URL of the JDBC driver to be used and a java.util.Properties object. You can see how to create the Properties object, props, in Step 2.
      Class.forName("weblogic.jdbc.oci.Driver");
      Connection conn =
         DriverManager.getConnection("jdbc:weblogic:oracle", props);
      conn.setAutoCommit(false);
The Connection conn becomes an argument for other actions that involve the DBMS, for instance creating DataSets to hold query results. For details about connecting to a DBMS, see the developers guide for your your driver. A list of BEA WebLogic JDBC resources is available in the document WebLogic JDBC Options. Connections, DataSets (and, if you use them, JDBC ResultSets), and Statements should be closed with the `close()` method when you have finished working with them. Note in the code examples that follow that each of these is explicitly closed. Note on autoCommit: The default mode of `java.sql.Connection` sets autocommit to true. Oracle will perform much faster if you set autocommit to false, as shown above. ## Preparing a query, retrieving, and displaying data Step 1. Setting parameters for data retrieval Step 2. Creating a DataSet for the query results Step 3. Fetching the results Step 4. Examining a TableDataSet's Schema Step 5. Examining the data with htmlKona Step 6. Displaying the data with htmlKona Step 7. Closing the DataSet and the Connection Code summary ### Step 1. Setting parameters for data retrieval Once you have a connection, you are ready to work with data. There are two parts to retrieving data: setting parameters for what data should be retrieved, and then instantiating a DataSet (either a TableDataSet or a QueryDataSet) to hold the results of the query. Once you have created the DataSet, you can continue to refine your data parameters. In dbKona, there are several ways to set parameters -- to compose the SQL statement and set its scope -- for retrieving data. Here we show how dbKona can interact at a very basic level with any JDBC driver, by taking the results of a JDBC ResultSet and creating a DataSet. In this example, we use a Statement object to execute a SQL statement. A Statement object is created with a method from the JDBC Connection class, and then the ResultSet is created by executing the Statement.
      Statement stmt = conn.createStatement();
      stmt.execute("SELECT * from empdemo");
      ResultSet rs = stmt.getResultSet();
You can use the results of a query executed with a Statement object to instantiate a QueryDataSet. This QueryDataSet is constructed with a JDBC ResultSet:
      Statement stmt = conn.createStatement();
      stmt.execute("SELECT * from empdemo");
      ResultSet rs = stmt.getResultSet();
      QueryDataset ds = new QueryDataSet(rs);
Using the results from the execution of a JDBC Statement is only one way to create a DataSet. It requires knowledge of SQL, and it doesn't give you much control over the results of your query: basically, you can iterate through the records with the JDBC `next()` method. With dbKona, you do not have to know much about SQL to retrieve records; you can use methods in dbKona to set up your query, and once you have created a DataSet with your records, you have a much finer control over manipulating the records. ### Step 2. Creating a DataSet for the query results dbKona can create SQL automatically for you. There are methods in the DataSet class (and its subclasses TableDataSet and QueryDataSet) and other dbKona classes that allow you to construct very complex query parameters without knowing much about SQL. All you really need is a basic understanding of how an SQL statement is built, and at least some rudimentary understanding of how data is stored in a table. In this document, we assume that you have that knowledge; that you know how to write a basic SQL statement, and that you know at least a little about WHERE, ORDER BY, HAVING, and other SQL clauses. Instead of requiring you to compose an SQL statement, dbKona lets you use methods to set certain parts of the statement. You create a DataSet (either a TableDataSet or a QueryDataSet) for the results of the query. For example, the simplest data retrieval in dbKona is into a TableDataSet. Creating a TableDataSet requires just a Connection object and the name of the DBMS table that you want to retrieve, as in this example that retrieves the Employee table (alias "empdemo"):
      TableDataSet tds = new TableDataSet(conn, "empdemo");
A TableDataSet can be constructed with a subset of the attributes (columns) in a DBMS table. If you want to retrieve just a few columns from a very large table, specifying those columns is more efficient than retrieving the entire table. To do this, pass a list of table attributes as a string in the constructor. For example:
      TableDataSet tds = new TableDataSet(conn, "empdemo", "empno, dept");
Use a TableDataSet if you want to be able to save changes to the DBMS, or if you do not plan to do a join of one or more tables to retrieve data; otherwise, use a QueryDataSet. In this example, we use the QueryDataSet constructor that takes two arguments: a Connection object and a string that is the SQL:
      QueryDataSet qds = new QueryDataSet(conn, "select * from empdemo");
You do not actually begin receiving data until you call the fetchRecords() method in the DataSet class. After you create a DataSet, you can continue to refine its data parameters. For instance, we could refine the selection of records to be retrieved in the TableDataSet with the where() method, which adds a WHERE clause to the SQL that dbKona composes. The following retrieves just one record from the Employee table by using the `where()` method to create a WHERE clause.
      TableDataSet tds = new TableDataSet(conn, "empdemo");
      tds.where("empno = 8000");
### Step 3. Fetching the results When you are happy with the data parameters, call the fetchRecords() method from the DataSet class, as in this example:
      TableDataset tds = new TableDataSet(conn, "empdemo", "empno, dept");
      tds.where("empno = 8000");
      tds.fetchRecords();
The fetchRecords() method can take arguments to fetch a certain number of records, or to fetch records starting with a particular record. In the following example, we fetch no more than the first 20 records and discard the rest with the clearRecords() method.
      TableDataSet tds = new TableDataSet(conn, "empdemo", "empno, dept");
      tds.where("empno > 8000");
      tds.fetchRecords(20)
         .clearRecords();
When dealing with very large query results, you may prefer to fetch a few records at a time, process them, and then clear the DataSet before the next fetch. Use the clearRecords() method from the DataSet class to clear the TableDataSet between fetches, as illustrated here.
      TableDataSet tds = new TableDataSet(conn, "empdemo", "empno, dept");
      tds.where("empno > 2000");
      while (!tds.allRecordsRetrieved()) {
         tds.fetchRecords(100);
         // Process the hundred records . . .
         tds.clearRecords();
      }
You can also reuse a DataSet with a method that was added in release 2.5.3. This method, DataSet.releaseRecords(), closes the DataSet and releases all the Records but does not nullify them. You can reuse the DataSet to generate new records, yet any records from the first use still held by the application remain readable. ### Step 4. Examining a TableDataSet's Schema Once you have fetched the first Record in the DataSet, the Schema associated with the DataSet is available. The Schema object is a data description object that describes the DataSet's columns, number of records, size, and other metadata. Here is a simple example of how you can examine the Schema information for a TableDataSet. The toString() method in the Schema class displays a newline-delimited list of the name, type, length, precision, scale, and null-allowable attributes of the columns in the table queried for a TableDataSet tds.
      Schema sch = tds.schema();
      System.out.println(sch.toString());
If you use a Statement object to create a query, you should close the Statement after you have completed the query and fetched its results.
      stmt.close();
### Step 5. Examining the data with htmlKona After the DataSet has been created and populated, you can examine each record by looking at its Values. A Value corresponds to the cells in one row of a DBMS. The following example shows how you might use a htmlKona UnorderedList to examine the data. This example uses DataSet.getRecord() and Record.getValue() to examine each record in a for loop. This finds the name, ID, and salary of the employee making the most money from the records retrieved in the QueryDataSet we created in Step 2.
      // (Creation of Database session object and QueryDataSet qds)
      UnorderedList ul = new UnorderedList();

      String name     = "";
      String id       = "";
      String salstr   = "";
      int sal         = 0;
      for (int i = 0; i < qds.size(); i++) {
        // Get a record
        Record rec = qds.getRecord(i);
        int tmp = rec.getValue("Emp Salary").asInt();
        // Add the salary amount to the htmlKona ListElement
        ul.addElement(new ListItem("$" + tmp));
        // Compare this salary to the maximum salary we have found so far
        if (tmp > sal) {
          // If this salary is a new max, save away the employee's info
          sal    = tmp;
          name   = rec.getValue("Emp Name").asString();
          id     = rec.getValue("Emp ID").asString();
          salstr = rec.getValue("Emp Salary").asString();
        }
### Step 6. Displaying the results with htmlKona You can display results in many ways. htmlKona provides a convenient way to display dynamic data like that produced by the above example. The following example shows how you might construct a page on the fly for displaying the results of your query.
      HtmlPage hp = new HtmlPage();
      hp.getHead()
        .addElement(new TitleElement("Highest Paid Employee"));
      hp.getBodyElement()
        .setAttribute(BodyElement.bgColor, HtmlColor.white);
      hp.getBody()
        .addElement(MarkupElement.HorizontalLine)
        .addElement(new HeadingElement("Query String: ", +2))
        .addElement(stmt.toString())
        .addElement(MarkupElement.HorizontalLine)
        .addElement("I examined the values: ")
        .addElement(ul)
        .addElement(MarkupElement.HorizontalLine)
        .addElement("Max salary of those employees examined is: ")
        .addElement(MarkupElement.Break)
        .addElement("Name: ")
        .addElement(new BoldElement(name))
        .addElement(MarkupElement.Break)
        .addElement("ID: ")
        .addElement(new BoldElement(id))
        .addElement(MarkupElement.Break)
        .addElement("Salary: ")
        .addElement(new BoldElement(salstr))
        .addElement(MarkupElement.HorizontalLine);

      hp.output();
### Step 7. Closing the DataSet and the Connection When you are finished working with the query results, close the DataSet with the close() method. You should always close DataSets.
      qds.close();
      tds.close();
It is also important to close the Connection to the DBMS. It's one line of code that should appear at the end of all of your database operations in a finally block, as in this example:
      try {
      // Do your work
      }
      catch (Exception mye) {
      // Catch and handle exceptions
      }
      finally {
        try {conn.close();}
        catch (Exception e) {
          // Deal with any exceptions
        }
      }
### Code summary Here is a summary of code similar to that used in the previous steps. In this example, we add 10 records to the Employee table with a TableDataSet, and then use a QueryDataSet to retrieve the records in the table. We use htmlKona to view the filtered results.
    import java.sql.*;
    import weblogic.db.jdbc.*;
    import weblogic.html.*;
    import java.util.Properties;

    public class tutor {

      public static void main(String[] argv)
           throws java.io.IOException, DataSetException,
           java.sql.SQLException, HtmlException,
           ClassNotFoundException
      {
      try {
        Properties props = new java.util.Properties();
        props.put("user",      "scott");
        props.put("password",  "tiger");
        props.put("server",    "DEMO");

        Class.forName("weblogic.jdbc.oci.Driver");
        Connection conn =
          DriverManager.getConnection("jdbc:weblogic:oracle",
                                       props);
        conn.setAutoCommit(false);

        // Create a TableDataSet to add 10 records
        TableDataSet tds = new TableDataSet(conn, "empdemo");
        for (int i = 0; i < 10; i++) {
          Record rec = tds.addRecord();
          rec.setValue("empno", i)
             .setValue("ename", "person " + i)
             .setValue("esalary", 2000 + (i * 10));
        }

        // Save the data and close the TableDataSet
        tds.save();
        tds.close();

        // Create a QueryDataSet to retrieve the additions to the table
        Statement stmt = conn.createStatement();
        stmt.execute("SELECT * from empdemo");

        QueryDataSet qds = new QueryDataSet(stmt.getResultSet());
        qds.fetchRecords();

        // Use the data from the QueryDataSet
        UnorderedList ul = new UnorderedList();

        String name     = "";
        String id       = "";
        String salstr   = "";
        int sal         = 0;
        for (int i = 0; i < qds.size(); i++) {
          Record rec = qds.getRecord(i);
          int tmp = rec.getValue("Emp Salary").asInt();
          ul.addElement(new ListItem("$" + tmp));
          if (tmp > sal) {
            sal    = tmp;
            name   = rec.getValue("Emp Name").asString();
            id     = rec.getValue("Emp ID").asString();
            salstr = rec.getValue("Emp Salary").asString();
          }
        }

        // Use an htmlKona page to display the data retrieved, and the
        // statements used to retrieve it
        HtmlPage hp = new HtmlPage();
        hp.getHead()
          .addElement(new TitleElement("Highest Paid Employee"));
        hp.getBodyElement()
          .setAttribute(BodyElement.bgColor, HtmlColor.white);
        hp.getBody()
          .addElement(MarkupElement.HorizontalLine)
          .addElement(new HeadingElement("Query String: ", +2))
          .addElement(stmt.toString())
          .addElement(MarkupElement.HorizontalLine)
          .addElement("I examined the values: ")
          .addElement(ul)
          .addElement(MarkupElement.HorizontalLine)
          .addElement("Max salary of those employees examined is: ")
          .addElement(MarkupElement.Break)
          .addElement("Name: ")
          .addElement(new BoldElement(name))
          .addElement(MarkupElement.Break)
          .addElement("ID: ")
          .addElement(new BoldElement(id))
          .addElement(MarkupElement.Break)
          .addElement("Salary: ")
          .addElement(new BoldElement(salstr))
          .addElement(MarkupElement.HorizontalLine);

        hp.output();

        // Close QueryDataSet
        qds.close();
        }
        catch (Exception e) {
          // Deal with any exceptions
        }
        finally {
        // Close the connection
          try {conn.close();}
          catch (Exception mye) {
            // Deal with any exceptions
          }
        }
      }
    }
Note that we closed each Statement and DataSet after use, and that we closed the Connection in a finally block. ## Using a SelectStmt object to form a query Step 1. Setting SelectStmt parameters Step 2. Using QBE to refine the parameters ### Step 1. Setting SelectStmt parameters You can use a SelectStmt object to create a SQL query rather than creating the SQL statement yourself. SelectStmt objects shield you from the need to know the specifics of how a particular vendor implements SQL, and it also reduces syntax errors. When you create a TableDataSet, it is associated with an empty SelectStmt that you can then modify to form a query. In this example, we have already created a connection conn. Here is how you access a TableDataSet's SelectStmt:
      TableDataSet tds = new TableDataSet(conn, "empdemo");
      SelectStmt sql = tds.selectStmt();
Now set the parameters for the SelectStmt object. In the example, the first argument for each field is the attribute name and the second is the alias. This query will retrieve information about all employees who make less than $2000.
      sql.field("empno", "Emp ID")
         .field("ename", "Emp Name")
         .field("sal", "Emp Salary")
         .from("empdemo")
         .where("sal < 2000")
         .order("empno");
### Step 2. Using QBE to refine the parameters The SelectStmt object also gives you Query-by-example functionality. Query-by-example, or QBE, forms parameters for data retrieval using a set of phrases that follow the format column operator value. For example, "empno = 8000" is a Query-by-example phrase that can select all the rows in one or more tables where the field employee number ("empno", alias "Emp ID") equals 8000. We can further define the parameters for data selection by using the `setQbe()` and `addQbe()` methods in the SelectStmt class, as is shown here. These methods allow you to use vendor-specific QBE syntax in constructing a select statement.
      sql.setQbe("ename", "MURPHY")
         .addUnquotedQbe("empno", "8000");
When you have finished, use the `fetchRecords()` method to populate the DataSet, as we did in the second tutorial. ## Modifying DBMS data with a SQL statement Writing SQL statements Executing each SQL statement Displaying the results with htmlKona Code summary When you retrieve data that you expect to modify, and if you want to save those modifications into the remote DBMS, you should retrieve data into a TableDataSet. Changes made to QueryDataSets cannot be saved. As with most dbKona operations, you should begin by creating the Properties and Driver objects, and then instantiating a Connection. Refer to the first tutorial for this step. ### Step 1. Writing SQL statements Once you have an active connection to the DBMS, you are ready to continue. The first step in modifying data with SQL is to compose the SQL. We will use three SQL statements in this example. The first SQL statement adds a new employee to the "empdemo" table.
      String insert = "insert into empdemo(empno, " +
                      "ename, job, deptno) values " +
                      "(8000, 'MURPHY', 'SALESMAN', 10)";
The second statement changes Murphy's name to Smith, and changes his job status from Salesman to Manager.
      String update = "update empdemo set ename = 'SMITH', " +
                      "job = 'MANAGER' " +
                      "where empno = 8000";
The third statement deletes this record from the database.
      String delete = "delete from empdemo where empno = 8000";
### Step 2. Executing each SQL statement As we execute each SQL statement, we'll save a snapshot of the table into a TableDataSet. Later we'll examine each TableDataSet to verify that the execute operation produced the expected results. Notice that TableDataSets are instantiated with the results of an executed query.
      Statement stmt1 = conn.createStatement();
      stmt1.execute(insert);

      TableDataSet ds1 = new TableDataSet(conn, "emp");
      ds1.where("empno = 8000");
      ds1.fetchRecords();
The methods associated with TableDataSet allow you to specify a SQL WHERE clause and a SQL ORDER BY clause and to set and add to a QBE statement. We use the TableDataSet in this example to requery the database table "emp" after each statement is executed to see the results of the execute() method. With the "where" clause, we narrow down the records in the table to just employee number 8000. Repeat the execute() method for the update and delete statements and capture the results into two more TableDataSets, ds2 and ds3. ### Step 3. Displaying the results with htmlKona There are several ways to display results. We use htmlKona in this example because it provides a convenient way to create a servlet for displaying dynamic data. The following example shows how to display the query results with htmlKona after each SQL statement was executed. Here we display the actual SQL that was used to insert, update, and delete the record for employee number 8000, and then we display what the table contained after each action.
      ServletPage hp = new ServletPage();
      hp.getHead()
        .addElement(new TitleElement("Modifying data with SQL"));
      hp.getBody()
        .addElement(MarkupElement.HorizontalLine)
        .addElement(new TableElement(tds))
        .addElement(MarkupElement.HorizontalLine)
        .addElement(new HeadingElement("Query results afer INSERT", 2))
        .addElement(new HeadingElement("SQL: ", 3))
        .addElement(new LiteralElement(insert))
        .addElement(new HeadingElement("Result: ", 3))
        .addElement(new LiteralElement(ds1))
        .addElement(MarkupElement.HorizontalLine)
        .addElement(new HeadingElement("Query results after UPDATE", 2))
        .addElement(new HeadingElement("SQL: ", 3))
        .addElement(new LiteralElement(update))
        .addElement(new HeadingElement("Result: ", 3))
        .addElement(new LiteralElement(ds2))
        .addElement(MarkupElement.HorizontalLine)
        .addElement(new HeadingElement("Query results after DELETE", 2))
        .addElement(new HeadingElement("SQL: ", 3))
        .addElement(new LiteralElement(delete))
        .addElement(new HeadingElement("Result: ", 3))
        .addElement(new LiteralElement(ds3))
        .addElement(MarkupElement.HorizontalLine);
      hp.output();
### Code summary Here is a summary of the example code we discussed in the sections on using a SelectStmt and modifying DBMS data with a SQL statement.
    import java.io.*;
    import javax.servlet.*;
    import javax.servlet.http.*;
    import java.sql.*;
    import java.util.*;
    import weblogic.db.jdbc.*;
    import weblogic.html.*;

    public class InsertUpdateDelete extends HttpServlet {

      public synchronized void service(HttpServletRequest req,
                                       HttpServletResponse res)
        throws IOException
      {
        Connection conn = null;
        try {
          res.setStatus(HttpServletResponse.SC_OK);
          res.setContentType("text/html");

          Properties props = new java.util.Properties();
          props.put("user",      "scott");
          props.put("password",  "tiger");
          props.put("server",    "DEMO");

          Class.forName("weblogic.jdbc.oci.Driver");
          Connection conn =
            DriverManager.getConnection("jdbc:weblogic:oracle",
                                        props);
          conn.setAutoCommit(false);

          // Create a TableDataSet with a SelectStmt
          TableDataSet tds = new TableDataSet(conn, "empdemo");
          SelectStmt sql = tds.selectStmt();
          sql.field("empno", "Emp ID")
             .field("ename", "Emp Name")
             .field("sal", "Emp Salary")
             .from("empdemo")
             .where("sal < 2000")
             .order("empno");
          sql.setQbe("ename", "MURPHY")
             .addUnquotedQbe("empno", "8000");
          tds.fetchRecords();

          String insert = "insert into empdemo(empno, " +
                          "ename, job, deptno) values " +
                          "(8000, 'MURPHY', 'SALESMAN', 10)";

          // Create a statement and execute it
          Statement stmt1 = conn.createStatement();
          stmt1.execute(insert);
          stmt1.close();

          // Verify results
          TableDataSet ds1 = new TableDataSet(conn, "empdemo");
          ds1.where("empno = 8000");
          ds1.fetchRecords();

          // Create a statement and execute it
          String update = "update empdemo set ename = 'SMITH', " +
                          "job = 'MANAGER' " +
                          "where empno = 8000";
          Statement stmt2 = conn.createStatement();
          stmt2.execute(insert);
          stmt2.close();

          // Verify results
          TableDataSet ds2 = new TableDataSet(conn, "empdemo");
          ds2.where("empno = 8000");
          ds2.fetchRecords();

          // Create a statement and execute it
          String delete = "delete from empdemo where empno = 8000";
          Statement stmt3 = conn.createStatement();
          stmt3.execute(insert);
          stmt3.close();

          // Verify results
          TableDataSet ds3 = new TableDataSet(conn, "empdemo");
          ds3.where("empno = 8000");
          ds3.fetchRecords();

          // Create a servlet page to display the results
          ServletPage hp = new ServletPage();
          hp.getHead()
            .addElement(new TitleElement("Modifying data with SQL"));
          hp.getBody()
            .addElement(MarkupElement.HorizontalRule)
            .addElement(new HeadingElement("Original table", 2))
            .addElement(new TableElement(tds))
            .addElement(MarkupElement.HorizontalRule)
            .addElement(new HeadingElement("Query results afer INSERT", 2))
            .addElement(new HeadingElement("SQL: ", 3))
            .addElement(new LiteralElement(insert))
            .addElement(new HeadingElement("Result: ", 3))
            .addElement(new LiteralElement(ds1))
            .addElement(MarkupElement.HorizontalRule)
            .addElement(new HeadingElement("Query results after UPDATE", 2))
            .addElement(new HeadingElement("SQL: ", 3))
            .addElement(new LiteralElement(update))
            .addElement(new HeadingElement("Result: ", 3))
            .addElement(new LiteralElement(ds2))
            .addElement(MarkupElement.HorizontalRule)
            .addElement(new HeadingElement("Query results after DELETE", 2))
            .addElement(new HeadingElement("SQL: ", 3))
            .addElement(new LiteralElement(delete))
            .addElement(new HeadingElement("Result: ", 3))
            .addElement(new LiteralElement(ds3))
            .addElement(MarkupElement.HorizontalRule);

          hp.output();

          tds.close();
          ds1.close();
          ds2.close();
          ds3.close();
        }
        catch (Exception e) {
          // Handle the exception
        }
        // Always close the connection in a finally block
        finally {
          conn.close();
        }
      }
    }
## Modifying DBMS data with a KeyDef Creating a KeyDef and building its attributes Creating a TableDataSet with a KeyDef Inserting a Record into the TableDataSet Updating a Record in the TableDataSet Deleting a Record from the TableDataSet More on saving the TableDataSet Verifying the changes Code summary Use a KeyDef object to establish keys for deleting and inserting data into the remote DBMS. A KeyDef acts as an equality statement in updates and deletes after the pattern "WHERE KeyDef attribute1 = value1 and KeyDef attribute2 = value2", and so on. The first step is to create a connection to the DBMS. In this example, we use the Connection object conn created in the first tutorial. The database table we use in this example is the Employee table ("empdemo"), with fields empno, ename, job, and deptno. The query we execute retrieves the full contents of the table "empdemo". ### Step 1. Creating a KeyDef and building its attributes The KeyDef object we create for inserts and deletes in this tutorial has one attribute, the "empno" column in the database. Creating a KeyDef with this attribute will set a key after the pattern "WHERE empno = " and the particular value assigned to "empno" for each record to be saved. A KeyDef is created and built in the KeyDef class, as shown in this example.
      KeyDef key = new KeyDef().addAttrib("empno");
If you are working with an Oracle database, you can construct the KeyDef with the attribute "ROWID," to do inserts and deletes on this Oracle key, as in this example:
      KeyDef key = new KeyDef().addAttrib("ROWID");
### Step 2. Creating a TableDataSet with a KeyDef In this example, we create a TableDataSet with the results of our query. We use the TableDataSet constructor that takes a Connection object, a DBMS table name, and a KeyDef as its arguments.
      TableDataSet tds = new TableDataSet(conn, "empdemo", key);
The KeyDef becomes the reference for all changes that we will make to the data. Each time we save the TableDataSet, we change data in the database (according to the limits set on SQL UPDATE, INSERT, and DELETE operations) based on the value of the KeyDef attribute, which in this example is the employee number ("empno"). If you are working with an Oracle database and have added the attribute "ROWID" to the KeyDef, you can construct a TableDataSet for inserts and deletes like this:
      KeyDef key = new KeyDef().addAttrib("ROWID");
      TableDataSet tds =
         new TableDataSet(conn, "empdemo", "ROWID, dept", key);
      tds.where("empno < 100");
      tds.fetchRecords();
### Step 3. Inserting a Record into the TableDataSet You can create a new Record object in the context of the TableDataSet to which it is to be added with the addRecord() method from the TableDataSet class. Once you have added the record, you can set the values for each of its fields with the setValue() method from the Record class. You must set at least one value in a new Record if you intend to save it into the database: the KeyDef field.
      Record newrec = tds.addRecord();
      newrec.setValue("empno",  8000)
            .setValue("ename",  "MURPHY")
            .setValue("job",    "SALESMAN")
            .setValue("deptno", 10);
      String insert = newrec.getSaveString();
      tds.save();
The getSaveString() method in the Record class returns the SQL string (a SQL UPDATE, DELETE, or INSERT statement) used to save a Record to the database. We have save this string into an object that we can display later to examine exactly how the insert operation was carried out. ### Step 4. Updating a Record in the TableDataSet You also use the setValue() method to update a Record. In the following example, we'll make a change to the record we created in the previous step.
      newrec.setValue("ename", "SMITH")
            .setValue("job",   "MANAGER");
      String update = newrec.getSaveString();
      tds.save();
### Step 5. Deleting a Record from the TableDataSet You can mark a record in a TableDataSet for deletion with the markToBeDeleted() method (or unmark it with the unmarkToBeDeleted() method) in the Record class. For instance, deleting the record we just created would be accomplished by marking the record for deletion, as shown here.
      newrec.markToBeDeleted();
      String delete = newrec.getSaveString();
      tds.save();
Records marked for deletion are not removed from a TableDataSet until you save() it, or until you execute the removeDeletedRecords() method in the TableDataSet class. Records that have been removed from the TableDataSet but not yet deleted from the database (by the removeDeletedRecords() method) fall into a zombie state. You can determine whether a record is a zombie by testing it with the isAZombie() method in the Record class, as shown.
      if (!newrec.isAZombie()) {
       . . .
      }
### Step 6. More on saving the TableDataSet At the end of each of the examples above for inserting, updating, and deleting records, we performed a save() operation on the TableDataSet. Saving a Record or a TableDataset will effectively save the data to the database. dbKona performs selective changes, that is, only data that has changed is saved. Inserting, updating, and deleting records in the TableDataSet affects only the data in the TableDataSet until you use the Record.save() or TableDataSet.save() method. ### Checking Record status before saving Several methods from the Record class return information about the state of a Record that you may want to know before a save() operation. Some of these are: needsToBeSaved() and recordIsClean() Use the needsToBeSaved() method to determine whether a Record needs to be saved, that is, whether it has been changed since it was retrieved or last saved. The recordIsClean() method determines whether any of the Values in a Record need to be saved. This method just determines whether a Record is dirty, no matter whether the scheduled database action is insert, update, or delete. Regardless of the type (insert/update/delete), the needsToBeSaved() method will return false after a save() operation. valueIsClean(int) Determines whether the Value at a particular index position in the Record needs to be saved. This method takes the index position of a Value as its argument. toBeSavedWith...() You can check how a Record will be saved with a particular SQL action with the methods toBeSavedWithDelete(), toBeSavedWithInsert(), and toBeSavedWithUpdate() methods. The semantics of these methods equate to the answer to the question, "If this row is or becomes dirty, what action will be taken when the TableDataSet is saved?" If you want to know whether a row will participate in a save to the DBMS, use the isClean() and the needsToBeSaved() methods. When you make modifications to a Record or TableDataSet, use the save() method from either class to save the changes to the database. In the previous steps, we saved the TableDataSet after each transaction as shown below.
      tds.save();
### Step 7. Verifying the changes After making a change to a TableDataSet and saving it to the database, you can verify the changes by retrieving the database table into a QueryDataset or TableDataSet to examine and verify it. After each operation above, we could have created a new TableDataSet to examine and verify that the operations we performed succeeded. Here is the sample code for fetching just a single record, which is an efficient way to verify single-record changes. In this example, we use a TableDataSet with a query-by-example (QBE) clause to fetch just the record we're interested in.
      TableDataSet tds2 = new TableDataSet(conn, "empdemo");
      tds2.where("empno = 8000")
          .fetchRecords();
As a final step, we can display the query results after each step and the strings "insert", "update", and "delete" that we created after each save(). Refer to the code summary in the previous tutorial to use htmlKona for displaying the results. When you have finished with the DataSets, close each one with the close() method.
      tds.close();
      tds2.close();
### Code summary Here a code example that uses some of the concepts covered in this topic.
    package tutorial.dbkona;

    import weblogic.db.jdbc.*;
    import java.sql.*;
    import java.util.Properties;

    public class rowid {

      public static void main(String[] argv)
        throws Exception
      {
        Class.forName("weblogic.jdbc.oci.Driver");
        Connection conn =
          DriverManager.getConnection("jdbc:weblogic:oracle:DEMO",
                                      "scott",
                                      "tiger");

        // Here we insert 100 records.
        TableDataSet ts1 = new TableDataSet(conn, "empdemo");
        for (int i = 1; i <= 100; i++) {
          Record rec = ts1.addRecord();
          rec.setValue("empid", i)
             .setValue("name",  "Person " + i)
             .setValue("dept",  i);
        }

        // Save new records. dbKona does selective saves, that is,
        // it saves only those records in the TableDataSet that have
        // changed to cut down on network traffic and server calls.
        System.out.println("Inserting " + ts1.size() + " records.");
        ts1.save();
        // Close the DataSet now that we're finished with it.
        ts1.close();

        // Define a KeyDef for updates and deletes.
        // ROWID is an Oracle specific field which can act as a
        // primary key for updates and deletes
        KeyDef key = new KeyDef().addAttrib("ROWID");

        // Update the 100 records we originally added.
        TableDataSet ts2 =
          new TableDataSet(conn, "empdemo", "ROWID, dept", key);
        ts2.where("empid <= 100");
        ts2.fetchRecords();

        for (int i = 1; i <= ts2.size(); i++) {
          Record rec = ts2.getRecord(i);
          rec.setValue("dept", i + rec.getValue("dept").asInt());
        }

        // Save the updated records.
        System.out.println("Update " + ts2.size() + " records.");
        ts2.save();

        // Delete the same 100 records.
        ts2.reset();
        ts2.fetchRecords();

        for (int i = 0; i < ts2.size(); i++) {
          Record rec = ts2.getRecord(i);
          rec.markToBeDeleted();
        }

        // Delete records from server.
        System.out.println("Delete " + ts2.size() + " records.");
        ts2.save();

        // You should always close DataSets, ResultSets, and
        // Statements when you have finished working with them.
        ts2.close();

        // Finally, make sure you close the connection.
        conn.close();
      }
    }
## Using a JDBC PreparedStatement with dbKona Part of the convenience of dbKona is that you do not need to know much about how to write vendor-specific SQL, since dbKona will compose syntactically correct SQL for you. In some cases, however, you may want to use a JDBC PreparedStatement object with dbKona. A JDBC PreparedStatement is used to precompile a SQL statement that will be used multiple times. You can clear the parameters for a PreparedStatement with a call to PreparedStatement.clearParameters(). A PreparedStatment object is constructed with the preparedStatement() method in the JDBC Connection class (the object used as conn in all of these examples). In this example, we create a PreparedStatement and then execute it within a loop. This statement has three IN parameters, employee id, name, and department. This will add 100 employees to the table.
      String inssql = "insert into empdemo(empid, " +
                      "name, dept) values (?, ?, ?)";
      PreparedStatement pstmt = conn.prepareStatement(inssql);

      for (int i = 1; i <= 100; i++) {
         pstmt.setInt(1, i);
         pstmt.setString(2, "Person" + i);
         pstmt.setInt(3, i);
         pstmt.executeUpdate();
      }

      pstmt.close();
You should always close a Statement or PreparedStatement object when you have finished working with it. You can accomplish the same task with dbKona without worrying about the SQL. Use a KeyDef to set fields for update or delete. Check the tutorial Modifying DBMS data with a KeyDef for details. ## Using stored procedures with dbKona Creating a stored procedure Setting parameters Examining the results Access to the functionality of procedures and functions stored on a remote machine that can carry out specific, often system-independent or vendor-independent tasks extends the power of dbKona. Using stored procedures and functions requires an understanding of how requests are passed back and forth between the dbKona Java application and the remote machine. Executing a stored procedure or function changes the value of a supplied parameter. The execution of a stored procedure or function also returns a value that indicates its success or failure. The first step, as in any dbKona application, is to connect to the DBMS. The example code uses the same Connection object, conn, that we created in the first tutorial topic. ### Step 1. Creating a stored procedure We use a JDBC Statement object to create a stored procedure by executing a call to CREATE on the DBMS. In this example, parameter "field1" is declared as an input and output parameter of type integer.
      Statement stmtl = conn.createStatement();
      stmtl.execute("CREATE OR REPLACE PROCEDURE proc_squareInt " +
                    "(field1 IN OUT INTEGER, " +
                    " field2 OUT INTEGER) IS " +
                    "BEGIN field1 := field1 * field1; " +
                    "field2 := field1 * 3; " +
                    "END proc_squareInt;");
      stmtl.close();
### Step 2. Setting parameters We use a JDBC CallableStatement in dbKona to execute a stored procedure. It allows us to set IN/OUT parameters and get return values. A CallableStatement object is constructed with the prepareCall() method in the JDBC Connection class. In this example, we use the setInt() method to set the first parameter to the integer "3". Then we register the second parameter as an OUT parameter of type java.sql.Types.INTEGER. Finally, we execute the stored procedure.
      CallableStatement cstmt =
        conn.prepareCall("BEGIN proc_squareInt(?, ?): END;");
      cstmt.setInt(1, 3);
      cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
      cstmt.execute();
Note that Oracle does not natively support binding to "?" values in a SQL statement. Instead it uses ":1", ":2", etc. We allow you to use either in your SQL. ### Step 3. Examining the results You can use several methods to display results. In this case, we'll use the simplest method and print the results to the screen.
      System.out.println(cstmt.getInt(1));
      System.out.println(cstmt.getInt(2));
      cstmt.close();
## Using byte arrays for images and audio Retrieving and displaying image data Inserting an image into a database You can store and retrieve binary large object files from a database with a byte array. Being able to handle large database data like image and sound files is necessary for multimedia applications, which often manage data in a database. You will probably also find htmlKona useful, which will make it easy to integrate database data retrieved with dbKona into an HTML environment. The example code that we use in this tutorial depends on htmlKona. ### Step 1. Retrieving and displaying image data The first step in using a byte array for data is to connect to the DBMS. The examples in this tutorial use WebLogic JDBC, so the connection sequence is a little different. For more information on connecting with WebLogic JDBC, see the Developers Guide. In this example, we use server-side Java running on a Netscape server posted from an htmlKona form to retrieve the name of the image that the user wants to view. With that image name, we query the contents of a database table called "imagetable" and get the first record of the results. You will notice that we use a SelectStmt object to construct a SQL query by QBE. After we retrieve the image record, we set the HTML page type to the image type and then retrieve the image data as an array of bytes (`byte[]`) into an htmlKona ImagePage, which will display the image in a browser.
      t3 = new T3Client("t3://bigbox:7005");
      t3.connect();

      // Get the name of the image from the user
      String iname = null;
      try {
        iname = (String)getFormData().get("imagename");
      } catch (IOException e) {;}

      Properties dbprops = new java.util.Properties();
      dbprops.put("user",                    "scott");
      dbprops.put("password",                "tiger");
      dbprops.put("server",                  "DEMO");

      Properties t3props = new java.util.Properties();
      t3props.put("weblogic.t3",           t3);
      t3props.put("weblogic.t3.dbprops",   dbprops);
      t3props.put("weblogic.t3.driver",    "weblogic.jdbc.oci.Driver");
      t3props.put("weblogic.t3.url",       "jdbc:weblogic:oracle");
      t3props.put("weblogic.t3.cacheRows", "0");

      Class.forName("weblogic.jdbc.t3client.Driver");
      Connection conn =
        DriverManager.getConnection("jdbc:weblogic:t3client",
                                    t3props);

      if (iname != null) {
        // Retrieve the image from the database
        TableDataSet tds = new TableDataSet(conn, "imagetable");
        tds.selectStmt().setQbe("name", iname);
        tds.fetchRecords();

        Record rec = tds.getRecord(0);

        this.returnNormalResponse("image/" +
                                  rec.getValue("type").asString());

        ImagePage hp = new ImagePage(rec.getValue("data").asBytes());
        hp.output(getOutputStream());
      }
For the full working example, look at Displaying an image stored in a database on the htmlKona Examples page. ### Step 2. Inserting an image into a database We can also use dbKona to insert image files into a database. Here is a snippet of code that adds two images as byte array objects to a database by adding a Record for each image to a TableDataSet, setting the Values of the Record, and then saving the TableDataSet.
      TableDataSet tds = new TableDataSet(conn, "imagetable");
      Record rec = tds.addRecord();
      rec.setValue("name", "vars")
         .setValue("type", "gif")
         .setValue("data", "c:/html/api/images/variables.gif");

      rec = tds.addRecord();
      rec.setValue("name", "excepts")
         .setValue("type", "jpeg")
         .setValue("data", "c:/html/api/images/exception-index.jpg");

      tds.save();
      tds.close();
## Using dbKona for Oracle sequences Constructing a dbKona Sequence object Creating and destroying sequences on an Oracle server from dbKona Using a Sequence Code summary dbKona provides a wrapper -- a Sequence object -- to access the functionality of Oracle sequences. An Oracle sequence is created in dbKona by supplying the starting number and increment interval for the sequence. ### Constructing a dbKona Sequence object You construct a Sequence object with a JDBC Connection and the name of a sequence that already exists on an Oracle server. Here is an example:
      Sequence seq = new Sequence(conn, "mysequence");
### Creating and destroying sequences on an Oracle server from dbKona If the Oracle sequence doesn't exist, you can create it from dbKona with the `Sequence.create()` method. That method takes four arguments: a JDBC Connection, a name for the sequence to be created, an increment interval, and a starting point. Here is an example that creates an Oracle sequence "mysequence" beginning at 1000 and increasing in increments of 1:
      Sequence.create(conn, "mysequence", 1, 1000);
You can drop an Oracle sequence from dbKona, also, as in this example:
      Sequence.drop(conn, "mysequence");
### Using a Sequence Once you have created a Sequence object, you can use it to generate autoincrementing ints, for example, to set an autoincrementing key as you add records to a table. Use the `nextValue()` method to return an int that is the next increment in the Sequence. For example:
      TableDataSet tds = new TableDataSet(conn, "empdemo");
        for (int i = 1; i <= 10; i++) {
            Record rec = tds.addRecord();
            rec.setValue("empno", seq.nextValue());
        }
You can check the current value of a Sequence with the `currentValue()` method, but only after you have called the `nextValue()` method at least once.
      System.out.println("Records 1000-" + seq.currentValue() + " added.");
### Code summary Here is a working code example that illustrates how to use concepts discussed in this section. First we attempt to drop a sequence named "testseq" from the Oracle server; that insures that we do not get an error when we try to create a sequence if one already exists by that name. Then we create a sequence on the server, and use its name to create a dbKona Sequence object.
    package tutorial.dbkona;

    import weblogic.db.jdbc.*;
    import weblogic.db.jdbc.oracle.*;
    import java.sql.*;
    import java.util.Properties;

    public class sequences {

      public static void main(String[] argv)
        throws Exception
      {

        Class.forName("weblogic.jdbc.oci.Driver");
        Connection conn =
          DriverManager.getConnection("jdbc:weblogic:oracle:DEMO",
                                      "scott",
                                      "tiger");

        // Drop the sequence if it already exists on the server.
        try {Sequence.drop(conn, "testseq");} catch (Exception e) {;}

        // Create a new sequence on the server.
        Sequence.create(conn, "testseq", 1, 1);

        Sequence seq = new Sequence(conn, "testseq");

        // Print out the next value in the sequence in a loop.
        for (int i = 1; i <= 10; i++) {
          System.out.println(seq.nextValue());
        }

        System.out.println(seq.currentValue());

        // Drop the sequence from the server
        // and close the Sequence object.
        Sequence.drop(conn, "testseq");
        seq.close();

        // Finally, close the connection.
        conn.close();
      }
    }

Copyright © 2000 BEA Systems, Inc. All rights reserved. Required browser: Netscape 4.0 or higher, or Microsoft Internet Explorer 4.0 or higher. Last updated 01/13/2000