PowerJ makes it easy to create a database form. Typically, you use the Form wizard to create the form. The wizard gathers your specification and sets up the transaction object, query object, and bound controls that will display the retrieved data. PowerJ takes care of most of the code for instantiating the objects, making the database connection, executing the query, and populating the controls.
In a PowerJ program, the transaction object handles the database connection. Its properties store information about the database you want to connect to, and it manages SQL transactions via commits and rollbacks.
The transaction object is in the Database page of the component palette. To add a transaction object to your form, you select the transaction icon in the palette and click on the form. If you used the database option in the Form wizard, the transaction object is added automatically.
Transaction properties The transaction object has properties for connection and transaction management. You can set them in the wizard or on the object’s property sheet. You need to specify the JDBC driver and the URL for the database, among other properties. You can also specify that you want to connect to the database automatically when the form is created, whether each database operation gets committed automatically when it is completed, and whether updates are allowed.
You can also set properties at runtime. It is typical to let the user specify a user ID and password and set the UserId and Password properties in code. The following code sets these properties with values saved in string variables:
transaction_1.setUserID( userid );
transaction_1.setPassword( password );
Connecting If you have not set up AutoConnect behavior, you can connect to the database using the connect method of the transaction object:
transaction_1.connect( );
Transaction management If you have not set up AutoCommit behavior, you can commit or roll back changes explicitly with methods of the transaction object:
transaction_1.commit();
transaction_1.rollback();
A query object represents a query on a specific database and can be used to execute any SQL statement. After the transaction object connects to the database, interactions with the database are done through query objects.
How the query object manages data A query object has several data buffers. The buffers store the data as it was retrieved from the database, the current state of the data, rows that have been deleted, and rows that are temporarily filtered out of view.
To update the database, PowerJ generates SQL statements to modify the contents of the database so that they match the contents of the primary buffer.
Query object properties The query object has properties that associate it with a transaction object, describe the SQL statement for the query, and provide information necessary for updating the database, such as primary key columns. You can set the properties in the query object property sheet at design time or you can use query object methods at runtime.
For the SQL statement, you can type the text in the property sheet or use the PowerJ Query Editor to construct the query. If you want to change the SQL statement at runtime, you can use the setSQL method:
String userStatement = "select * from dba.employee";
query_1.setSQL( userStatement );
Executing the query If you set the query’s AutoOpen property, your program will automatically execute the query when the query object is created. To execute the query at runtime, you call the open method:
query_1.open();
If the query’s SQL statement returns a result set, you can call methods that work with the data, often using bound controls.
A data-bound control is an object whose value is automatically updated by query results. When you use bound controls, you can display database data with very little coding effort. For example, you can bind a text box to a query object so that the text box always shows the value of a specified column in the current row. If you move the cursor to a different row of data, the text box automatically changes to show the value in the same column of the new row.
If the user changes the value of a bound control, it typically changes the corresponding value in the query object’s primary buffer. For example, if a text box displays the value of Column 1, changing the value of the text box typically changes the value of Column 1 in the current row (as stored in the primary buffer). Changes made in the primary buffer can be incorporated in the database itself using the update method.
A bound control, such as a text box or check box, displays values from a single database column. Controls such as grids can display values from several columns.
To use an object as a bound control, you check the Bound Control check box on the object’s property sheet at design time. You can’t convert an ordinary control to a bound control at runtime.
Data for bound controls To associate data with the bound control, you set the control’s DataSource and DataColumns properties. You can set them on the property sheet at design time or with the set methods at runtime.
The DataSource property specifies the query object to which the control will be bound. To set its value at runtime, use the control’s setDataSource method:
textf_1.setDataSource( query_1 );
The DataColumns property determines which column’s value is displayed by the bound control. To set its value at runtime, use the setDataColumns method. The string identifying the column can be the column’s name or number:
textf_1.setDataColumns( "emp_id" );
To specify multiple columns for controls such as grids, you can list more than one column, with entries separated by semicolons:
grid_1.setDataColumns( "emp_id;dept_id" );
Data navigator PowerJ’s data navigator control provides a simple way for the user to move through a database. It displays forward and back buttons and buttons for adding, editing, and deleting rows.
Data navigators are bound controls, just like the controls that display data. They control the position of the cursor in the result set by calling methods of the query object, such as moveFirst or movePrevious.
